Best answer

Copy data from a new spreadsheet to an existing sheet (drive)


Userlevel 1

The attached CSV file is automatically uploaded to a google folder, where it is displayed in spreadsheet format. I want to transfer the data from this to a spreadsheet already in the folder.


I selected the new file in folder google drive trigger, and for the first action I selected Create Spreadsheet Row in Google Sheets. But here I only see the document data, not the data in the sheet. Can anyone help me?

 

 

icon

Best answer by shalgrim 24 May 2023, 19:54

View original

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

13 replies

Userlevel 7
Badge +6

Hi there @Andi11111,

Great to have you in the Community! 🎉

Just to clarify, are you attempting to extract data from a Google Sheets spreadsheet? If that is the case, do you have headers on your sheet? We have a helpful article that explains how to work with Google Sheets in Zaps which may provide some guidance: https://help.zapier.com/hc/en-us/articles/8496276985101-Work-with-Google-Sheets-in-Zaps

Please keep us posted! 😊

 

Userlevel 1

Yes, I would like to transfer the data from the report to the summary table when the report document is uploaded from the email. There is text everywhere in the header.

 

 

Userlevel 6
Badge +8

Hey there,

You’re right that the New File trigger is only going to give you information about the file, not its contents.

I recommend trying as your second step the Copy Worksheet in Google Sheets action

 

Userlevel 1

This is what the process looks like now

 

I don't know what to select here, because I always want to copy from the new table

Also here

 

Userlevel 6
Badge +8

Let’s start with the first one...”Spreadsheet Containing the...”

There should be an ID field from the first step for you to choose. Is there?

Userlevel 1
yes, I found this

 

Userlevel 6
Badge +8

Great. Now, since it’s a CSV, the spreadsheet should only have one worksheet. In that case, Google usually assigns the ID of 0 to that worksheet. So you should just be able to type 0 into the field asking for a worksheet.

Now, before we go on, let’s talk about how we want this data copied. Coming back to this photo, I can’t quite see the details very well.

Do you want every row in the new file copied into the already-existing worksheet in the already-existing file? Or do you want a new worksheet in that file? 

Do you want the header row copied, too?

Do both sheets have the same header row?

 

Userlevel 1

Yes, I want every row in the new file copied into the already-existing worksheet in the already-existing file.

I do not want the header row copied.

Yes, both sheets have the same header row.

Userlevel 6
Badge +8

Okay, great, I understand.

So the Copy Worksheet step isn’t going to work here. Sorry for steering you in the wrong direction.

What you really want is the Get Many Spreadsheet Rows action. 

You’ll set it up as follows:

  • Spreadsheet - Use the ID field from your Google Drive step
  • Worksheet - Click on the dropdown, select the Custom tab, then manually enter 0 in the field above the dropdown
  • Columns - You can adjust if you want, but the default A:Z will probably work based on your screenshot
  • Row Count - Choose a number that’s higher than the number of rows you ever expect to be here (limit 500)
  • First Row - Enter 2

Then go ahead and test your step

The next action in your Zap will be Create Multiple Spreadsheet Rows. Here you will choose the Spreadsheet and Worksheet that you want to paste into. Then you’ll map each field in that sheet to the relevant column coming from the sheet in the previous step

 

Do you want to give that a shot and let me know how that works for you?

Userlevel 1

Unfortunately it's not working yet, I'll paste the process here to see where I am:


Trigger
 
1. action
Test:
Nevertheless, I tried to take it further
Another problem is that I need to upload data in several columns, but I can't find the right data

 

Userlevel 6
Badge +8

Hi @Andi11111 ,

Okay, I worked on this for a bit and got it working for myself in a four-step Zap.

The first thing you need to do, though, is to go to your Google Drive settings and check the box next to “Convert uploads to Google Docs editor format” … this will make sure the CSV when uploaded gets converted to Google Sheets

 

Then your Zap will be set up as follows:

 

  1. Google Drive → New File (or New File in Folder if you know the folder it will go in to)
  2. Google Sheets → Find Worksheet in Google Sheets
    1. In the Spreadsheet field, use the ID field from Step 1
    2. In the Title field, use the Title field from Step 2 and then type .csv after it like so:
    3.  

  3. Google Sheets → Get Many Spreadsheet Rows (Advanced, output as Line Items)
    1. In the Spreadsheet field, use the ID field from Step 1
    2. In the Worksheet field, use the Sheet Id field from Step 2
    3. (all other defaults should work)
  4. Google Sheets → Create Multiple Spreadsheet Rows in Google Sheets
    1. In the Spreadsheet field, select the Sheet you want the rows copied to
    2. In the Worksheet field, select the Worksheet you want the rows copied to
    3. The rest of the fields will be the headers of the existing sheet. You should be able to map the fields from Step 2...they should be titled something like “Formatted Rows Col A,” “Formatted Rows Col B,” and so on

This setup will go most smoothly if you can get one of the actual files that will be used as the test data in Step 1. So I’d recommend changing your Google Drive setting first, then uploading one of the files to that drive, and then pulling in the sample.

Let me know if you have any troubles from here!

Scott

Userlevel 1

Thank you very, very much, it works perfectly!

Userlevel 7
Badge +9

Ah, so glad to hear his recommendation did the trick for you, @Andi11111! He’s a real 💎 that, @shalgrim.

Thanks for keeping the thread updated and happy zapping! ⚡️