Automation of data collection and processing from Google Sheets
Project goal:
Set up automatic data import from 25 Google Sheets into a single table with subsequent formatting to a unified format. The data is updated daily, so it is important to ensure stable system operation.
Main functions:
Data import:
Data from 25+ sheets is automatically collected into a single table.
All sheets have a similar but not identical structure.
The tool has the ability to add sheets independently.
At the time of writing this text, the client already has more than 50 sheets.
Formatting to a unified format:
Product sizes in different formats (42 1/2, 42.2/3, 42 (2/3)) are converted to a unified format (for example, 42.5).
Extra spaces, indents, and line breaks are removed. There is also a sheet for filtering special characters or phrases. For example, products without packaging ("Without box") are excluded from processing.
Processing of deleted products:
If a product is removed from the supplier's sheet, the final table indicates 0 in the "Quantity" field instead of disappearing because the website needs to have accurate information in the database.
For suppliers who indicate a quantity of 0 for irrelevant products, this data is also transferred.
Automation of updates:
Data is automatically updated every 10 minutes.
The script checks for new data and updates the final table.
Technical details:
Structure of the tables:
Each supplier's table has a separate sheet in the final table.
Data is collected using formulas and scripts.
Filtering and processing:
Rules are used for size conversion, removal of extra characters, and ignoring unnecessary data.
Automatic removal of rows with incorrect data is set up.
Scripts:
The script transfers data from sheets to the final table, checking for new and deleted products.
If a product disappears from the supplier's table, the final table indicates 0 in the "Quantity" field.
Advantages of the solution:
Automation: Reduced time for manual data updates.
Flexibility: Ability to add new sheets and customize processing rules.
Accuracy: Data from different sources is formatted to a unified format, facilitating further work.
Client feedback:
"Thank you very much for the work! The project was completed efficiently, quickly, and with a clear guide on what works and how! Communication is easy, quick and clear responses, and problem-solving! I am very satisfied that I chose you, I recommend you to everyone!"
Hashtags:
#GoogleSheets #Automation #DataProcessing #Scripts #Freelance #Portfolio
Set up automatic data import from 25 Google Sheets into a single table with subsequent formatting to a unified format. The data is updated daily, so it is important to ensure stable system operation.
Main functions:
Data import:
Data from 25+ sheets is automatically collected into a single table.
All sheets have a similar but not identical structure.
The tool has the ability to add sheets independently.
At the time of writing this text, the client already has more than 50 sheets.
Formatting to a unified format:
Product sizes in different formats (42 1/2, 42.2/3, 42 (2/3)) are converted to a unified format (for example, 42.5).
Extra spaces, indents, and line breaks are removed. There is also a sheet for filtering special characters or phrases. For example, products without packaging ("Without box") are excluded from processing.
Processing of deleted products:
If a product is removed from the supplier's sheet, the final table indicates 0 in the "Quantity" field instead of disappearing because the website needs to have accurate information in the database.
For suppliers who indicate a quantity of 0 for irrelevant products, this data is also transferred.
Automation of updates:
Data is automatically updated every 10 minutes.
The script checks for new data and updates the final table.
Technical details:
Structure of the tables:
Each supplier's table has a separate sheet in the final table.
Data is collected using formulas and scripts.
Filtering and processing:
Rules are used for size conversion, removal of extra characters, and ignoring unnecessary data.
Automatic removal of rows with incorrect data is set up.
Scripts:
The script transfers data from sheets to the final table, checking for new and deleted products.
If a product disappears from the supplier's table, the final table indicates 0 in the "Quantity" field.
Advantages of the solution:
Automation: Reduced time for manual data updates.
Flexibility: Ability to add new sheets and customize processing rules.
Accuracy: Data from different sources is formatted to a unified format, facilitating further work.
Client feedback:
"Thank you very much for the work! The project was completed efficiently, quickly, and with a clear guide on what works and how! Communication is easy, quick and clear responses, and problem-solving! I am very satisfied that I chose you, I recommend you to everyone!"
Hashtags:
#GoogleSheets #Automation #DataProcessing #Scripts #Freelance #Portfolio
Dnepr 
Very busy