Hello,
I have created a zap to upload a csv file into Google Drive and that works correctly. However, I am trying to automatically replace a Google Sheet Worksheet with the CSV from Google Drive each time there is a new file uploaded. Any help on how to automatically take a new CSV file upload from Google Drive and add to Google Sheets would be greatly appreciated. Thank you!
Best answer by MohSwellamBest answer by MohSwellam
I have made this step by step guide so you can easily follow it :)
Those steps are AFTER the Google Drive step
Step 1: Use Formatter by Zapier > Utilities > Import CSV File > choose the CSV you uploaded in Google Drive (or from the original source, both would work)
Step 2: Use Looping by Zapier > Looping using Line Items to loop through the values (this will make the next step trigger individually, i.e.: if you have 120 rows in the CSV the following steps will play 120 times).
You will need to map each field in the CSV in here
Add another Formatter by Zapier step > Numbers > Perform Math Operation, to add 1 to the iteration number in the loop. This is to avoid updating the Header of the Google Sheets. Alternatively you can just map the header in the Looping Step as well BUT this is a bit tricky as Zapier might consider any empty row (we will then need to add a filter after the loop to make sure empty cells dont go through the loop).
Next, add the Update Google Sheet row step, the values here will be as shown. The row number will be the result from the Formatter step to add 1 to the iteration number. The values of the fields will be the result from the Looping by Zapier.
hope this was easy to follow :)
+8Hi
I think the best way to do that is I the same zap you have. After importing the CSV file to Google Drive, you can add a Formatter by Zapier step > Utilities > Import CSV File and use the public link of that file here.
Once you have the data from the CSV using this step, you can then use Update Google Sheet Rows action.
+8Thanks
Hi
I think the best way to do that is I the same zap you have. After importing the CSV file to Google Drive, you can add a Formatter by Zapier step > Utilities > Import CSV File and use the public link of that file here.
Once you have the data from the CSV using this step, you can then use Update Google Sheet Rows action.
Thank you! I’ve gotten to the “Update Spreadsheet Row” portion of the Zap and I have run into this problem. (See Below). Any thoughts on how I could fix this?
+8Hi
How many rows is the CSV? Im guessing you wanna paste it as is yes?
In this case here you will need to add a Loop by Zapier before the Update Google Sheets step.
Can you show me the result of the CSV step please?
i would imagine it has a hunch of line items which we can use the line item number to input the Row in this step dynamically, so it would iterate through the rows of the csv inputting them one by one into Google Sheets
As a start though, you can choose Custom and input 1 and see how it looks first :)
Hello
There are 112 rows in the CSV.
The goal here is to just replace the entire Google Worksheet with the new CSV file each time it is uploaded to Google Drive.
Here is a snapshot of the Formatter Step after uploading the CSV to Google Drive.
+8I actually meant the result of this step not the configuration, but no worries.
After this step, add the Loop by Zapier action, which will update the lines in Google Sheet line by line.
In the Loop you will have to map all the fields in the CSV that you want to update in the google sheet
Oh apologies,
Okay I’ll give this a try. Thank you for your help!
+8My pleasure
After uploading the CSV file to Google Google Drive, the next step is to use the “Utilities In Formatter by Zapier.” Did I set this up correctly? Should I use “Import CSV File” ?
Thank you!
+8Hi
I thought this is what you needed ? Why add another step after that ?
I would like to automatically take the CSV file generated in Google Drive and replace a worksheet in Google Sheets. I am having trouble automating the step from Google Drive to Google Sheets.
+8I have made this step by step guide so you can easily follow it :)
Those steps are AFTER the Google Drive step
Step 1: Use Formatter by Zapier > Utilities > Import CSV File > choose the CSV you uploaded in Google Drive (or from the original source, both would work)
Step 2: Use Looping by Zapier > Looping using Line Items to loop through the values (this will make the next step trigger individually, i.e.: if you have 120 rows in the CSV the following steps will play 120 times).
You will need to map each field in the CSV in here
Add another Formatter by Zapier step > Numbers > Perform Math Operation, to add 1 to the iteration number in the loop. This is to avoid updating the Header of the Google Sheets. Alternatively you can just map the header in the Looping Step as well BUT this is a bit tricky as Zapier might consider any empty row (we will then need to add a filter after the loop to make sure empty cells dont go through the loop).
Next, add the Update Google Sheet row step, the values here will be as shown. The row number will be the result from the Formatter step to add 1 to the iteration number. The values of the fields will be the result from the Looping by Zapier.
hope this was easy to follow :)
Thank you so much for this step-by-step! I’ll give this a shot!
I was able to complete everything except for adding the second Formatter. Zapier would not allow me to nest it.
I ran the Zap and it didn’t send to Google Sheets unfortunately. I appreciate your help on this.
+8You just need to click on the + sign under the loop to add it :)
Nothing was sent over to Google Sheets unfortunately.
+8Hi
First, did you map the fields correctly?
Second, in the TEST it will not update all the fields, it will update just one row. To really test it I would suggest you turn it on then send a CSV (however your trigger is set) with about 3-5 rows and see what it does
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
