Need some help with an automation project.
My stock system exports a csv daily to a Dropbox folder at 5:45am showing data for a particular distribution centre. I need to use this to create a KPI email showing total orders, number of orders over 3 days old, number of orders over 5 days old and the oldest order on the list. So far I have a zap which does the following:
- Trigger: New file in Dropbox folder (the automated csv file)
- Formatter to import the csv
- Update rows in Google sheet
The rows are added to a worksheet in Google sheets. The second tab in the same sheet does a bunch of calculations based on the data updated into the first tab and then the third tab in the same sheet updates a single row with all the KPIs referred to above, based on the data imported from the csv.
Next I have a second Zap which sends the email, as follows:
- Trigger: Updated row in Google sheets
- Send email using gmail (recipients, subject line and the body of the email all populated automatically.
All of this works smoothly and the email goes off without a hitch. However, there is a snag:
Point 3 of the first Zap, when it updates the rows in the Google sheet, it adds data to the data already there. So if there are 140 open orders on Monday and the zap runs, the email goes out fine, no problem. But then if there are 150 open orders on Tuesday (which includes the 140 from Monday), then the email will claim that there are 290 open orders. To get around this, I have been going in after the email runs and deleting the data, so that when it runs the next morning, the sheet is empty and the numbers are correct. But this isn’t ideal as I have to remember to go in every day and do this, and my memory is terrible. It would be better if I could automate the removal of the data somehow but I can’t see any way to do this in Zapier. I can delete specific rows but not a range.
I’ve tried to get around it by creating a new spreadsheet every time the csv arrives in the Dropbox folder but I need those 2 tabs in the Google sheet to do the KPI calculations so the new spreadsheet needs to include those tabs. I’ve tried setting up a ‘template’ and then using the ‘create new spreadsheet’ action to duplicate that template, (which it does) but I can’t get the formatter to add the csv data into the newly created spreadsheet because it asks me to specify the spreadsheet to add the data into, but the spreadsheet doesn’t exist until after the zap runs, so I can’t (although I can in the testing process, which works perfectly).
Been on this all day today so if anyone can help I would really appreciate it.