Question

Create Google sheet and use Zapier to import csv to populate the newly created sheet

  • 7 April 2021
  • 5 replies
  • 31 views

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:

  1. Trigger: New file in Dropbox folder (the automated csv file)
  2. Formatter to import the csv
  3. 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:

 

  1. Trigger: Updated row in Google sheets
  2. 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.

 

 


5 replies

Userlevel 7
Badge +10

Hi @seraphanx 

Try going about this approach…

Steps

  1. Trigger: Dropbox - New File in Folder
  2. Action: Formatter > Utilities > Import CSV File
  3. Action: Looping (https://zapier.com/apps/looping/integrations)
  4. Action: GSheets - Find/Create Row
  5. Action: GSheets - Update Row (map the Row ID from Step 4)

 

 

Thanks Troy, really appreciate your help.

I have tried the approach you outlined above (I didn’t even know about looping, so thanks for introducing me to that), but when I get to step 4, I don’t have the option for ‘Find/Create Row’. I only have the following options:

 

 

Userlevel 7
Badge +10

@seraphanxTry Lookup Row.

 

Thanks Troy.

I tried that but it seems to be looking for an existing value in the row and there isn’t one as the worksheet is empty. What I need is to use the zap to import the values from the csv into the worksheet, but then I need to clear those rows once the email has been sent. Either clearing the data from the rows or deleting the rows entirely should work.

Maybe something like ‘Delete row’ and then loop it using the row numbers in the sheet when the data is in there?

Userlevel 7
Badge +10

@seraphanx 

you might want to think about approaching this differently then using formulas/functions in another tab to filter to just the newest data based on a date column.

Reply