Question

How can I add a new worksheet to existing spreadsheet whenever a new spreadsheet is added to Google Drive?

  • 21 June 2024
  • 1 reply
  • 9 views

Hi!

Context

Everyday I get a report emailed to me that has a an excel file attached. I currently copy and paste the information from the only tab/worksheet on this report and into an existing google sheet with a new tab/worksheet for each day’s report. I want to automate this task so everyday when I get the email with the excel file, it will automatically add a new tab/worksheet to my existing google sheet.
 

I have set up a first zap that successfully takes the emailed attachment and saves it as a g-sheet in a central g-drive folder.

Issue description

I am having trouble getting zapier to find the most recent file and then copy the relevant worksheet to the appropriate spreadsheet.  

 

Current steps

1.) I’m using “New File in Folder in Google Drive” as the trigger.

  • This seems to be working.
  • When a new file appears in the g-drive, a new worksheet is created in my target spreadsheet, but it is brininging in the wrong data (I’ll describe more below)>

2.) The second step I’m using is “Copy Worksheet in Google Sheets”

  • In the action portion of this step, it makes me pick a “Spreadsheet Containing the Worksheet to Copy”.
  • I want to just select (The most recent file added), but this does not appear to be an option.
  • Instead it makes me pic an existing file in the g-drive. 
  • Then when I run a test by adding new files to the g-drive, it successfully triggers the zap and a new work sheet is created in my target spreadsheet, but it brings over the data from the original file I selected and not the data from the new file. This is my issue that I can’t get over.

  • Does anybody have any advice about how I can grab the data from the most recently uploaded file that is actually trigger instead of getting the data from the original spreadsheet?


1 reply

Userlevel 7
Badge +11

Hi @jnp8893, welcome to the Community! 🎉

If you’re wanting to copy a worksheet from the spreadsheet file that triggers the Zap when it’s added to the folder, then you’d need to use the Custom value option in that Spreadsheet Containing the Worksheet to Copy field select the ID for the file from the trigger:

46676de80d445fa704530f24e5d4024b.png


That said, you’ll then need to select the worksheet from that file so you’ll likely need to add a Get Worksheet (Google Sheets) action ahead of the Copy Worksheet action to first find the worksheet that needs to be copied from that file. Then in the Worksheet to Copy field on the Copy Worksheet action you’d select the ID of the worksheet:
539b30feaf58b26d6480c4daaef1ea2e.png

Can you give that a try and let me know if that works as hoped?

Reply