Skip to main content

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Learning Resources
All of our best learning resources can be found here.

Featured Articles Show & Tell Zapier's products AI Hub Zapier Tools Hub

Share your knowledge
Give back to the Community by sharing your automation wins and helping to answer questions.

Share your success See unanswered questions User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

  • Home
  • Get Help
  • How Do I
  • Create Google sheet and use Zapier to import csv to populate the newly created sheet

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.

 

 

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:

 

 

@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?

@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.

@seraphanx 
Just checking in to see if you still need help with this? 

Hi. Yes I still need help if possible. I still have to go into the Google sheet every day to manually delete out the existing data, so any solutions to this would be welcome.

 Hello I would like to weigh in here as well since i am looking to do something similar to Seraphanx.

 

I too would like to ‘overwrite’ G Sheets rows every time I get data from a CSV instead of have Zapier create new rows. I too have been talking to Troy on this thread 

But I did not specify to him that I wanted the GSheet to ‘overwrite’ after the intitial creation of the rows.

 

After the test of the Zap, the GSheet got populated with data. So I do not want to use the Create Spreadsheet Row Action to keep on adding more rows. 

 

Action 3 and 4 above look good. But with just the existing articles I cant work it out. I do have a column for the date so that every time the Google Sheet gets updated, it could Find a Row that does not match the date and update this row. Is this the action that gets looped?. 

From the looping step article https://zapier.com/help/create/other-functions/loop-your-zap-actions , I’m unable to figure out how to apply it to my case.

What should put in the Values to Loop fields?

 

Please could anyone help me figure this out! Thanks

@seraphanx and @P111x 

Can you clarify why you need the GSheet rows to be overwritten?

 

Hi Troy.

Yes, I thought the G Sheet rows should be overwritten or updated, as I’m going to use changes in the Google Sheet as the trigger to Update Live Items in Webflow - and I don’t want to add any duplicate items, just new items. Probably I’m missing a piece (or several) of the puzzle, though.

Thanks!

Piers

 

 

@P111x

I’m still not understanding why GSheet rows would need to be deleted.

OR do you mean updated?

If updated, then use this combo of GSheet steps.

Steps

  1. Trigger:
  2. Action: GSheets - Find/Create Row
  3. Action: Update - Row

Then in your other Zap use this trigger

  1. Trigger: GSheets - New/Updated Row

 

Hi Troy

My stock system outputs a csv file every day, which is exported to a Dropbox folder. I have a Zap as follows:

New file in Dropbox → Zapier Utilities → import csv into Google Sheets

This imports the data from the csv and populates two columns in an existing Google Sheets spreadsheet with the order number and order date of all customer orders. The second tab of this spreadsheet then calculates the number of orders, the oldest order and a few other KPIs and then updates a row in the 3rd tab of the same sheet. I then have another Zap which operates as follows:

New or updated row in Google Sheets → Only continue if (Total orders is greater than 5 and less than 250) → Send email in Gmail

This then sends an email with the data in the updated row in the 3rd tab of the spreadsheet.

The net effect of all this is that a daily KPI email goes out to specific recipients at 5:45am, confirming the KPI data exported from the stock system.

It all works like clockwork, except that I have to remember to go into the Google Sheet every day (after it has sent the email) and clear the data which was imported from the csv file. Otherwise the data is added to the previous day’s data, so the number of orders (and most of the other KPIs) are incorrect.

I either need a way to automate the clearing of the data, or overwrite the data. I don’t think overwriting is ideal anyway, as this would mean that if there were fewer orders today than yesterday, the last few orders from yesterday would not be overwritten.

So really I’m looking for a way to automate the clearing of the two columns of data from the first tab of the Google Sheet. Or any other suggestions which would achieve the same result.

Thanks in advance

@seraphanx

Why not try adding another column to include the date of the imported data from the CSV, then in the GSheet formulas filter only for the current date?

That would make the formulas dynamic and eliminate the need to overwrite or delete data each day.

FILTER: https://support.google.com/docs/answer/3093197?hl=en

QUERY: https://support.google.com/docs/answer/3093343?hl=en

Brilliant. I have added in another column to pick up the import date and then adjusted the formulas so they only pick up orders where the import date matches today’s date. Seems to work fine but I guess we will find out tomorrow morning at 5:45am.

Thanks Troy, this has been bugging me for weeks.

Terms & Conditions