Question

Issues copying data from multiple Google Sheets into one master spreadsheet.


I have a reoccurring daily email which attaches a Google Sheet of the same name and sender address. 

I am trying to automate a Zap which copies the information off of each day’s new google sheet into one sheet (with the same headers.) I’ve been able to run two Zaps which adds each new attachment to a folder on my Drive with a different name for each (the date- and then the static name.) Yet, now I am stuck on how I can copy the information from one sheet to another. I ran a few Zaps that just added blank rows to the Final google sheet, but it failed to copy in the new information. 

I also tried transferring the data, but that didn’t seem to work as each file has new names, but it’s possible I have just been putting in the information wrong.

End goal is get multiple folder files to copy onto one google sheet.

Let me know what you think the best operations to achieve this outcome would be. 


3 replies

Userlevel 7
Badge +11

Hi @Brittany F, welcome to the Community! 👋

This sort of this has been asked about before in Community, I’d recommend checking out the related topic here: 


The best answer there gives a couple of different options for how to handle it based on on how many rows are in the Google Sheet. I’m thinking you’d want a similar approach assuming the Google Sheet attached the the Email is in CSV format?

If it is, you’d want to set the Zap tor trigger off of those daily emails instead of the using the trigger mentioned on that related topic. And it looks like you could ignore the Webhooks actions too as that wouldn’t be necessary. For example:

  • Trigger: New Attachment (Gmail) - set to only look for attachments in new emails with a specific subject same name/sender address
  • Action: Formatter (Utilities > Import CSV) - imports the CSV file into the Zap so you can use it in later actions of the Zap
  • ActionCreate Spreadsheet Row(s) (Google Sheets) - creates one or multiple rows using information supplied by the previous Formatter action.


The above assumes you’re using Gmail for your email here but you can likely achieve a similar approach with a different email app. It also assumes that it’s just new rows that are in the CSV file. It’s not and you’d need to also update existing rows then you’d likely need to use a search action for Google Sheets like Lookup Spreadsheet Row(s) to see if there’s an existing row, and use that with a Update Spreadsheet Row(s) (Google Sheets) action to update the rows.

Please note that the Lookup Spreadsheet Row(s) action is limited to 500 rows so if the CSV file contains more than that you might need to splitting up the CSV file into smaller files with less rows so that it can be processed by the Zap. In which case an app like Split CSV might be useful for that.

Hope that helps to get you pointed in the right direction. If I’ve misunderstood what you’re looking to do here or you get stuck at all, just let us know! 🙂

The attachment is in an xlsx format so I am unable to use the utilities function to create a new sheet. There is less then 100 rows on average for each daily sheet to address that variable. 

When I run the attachment trigger, with the qualification of the subject: xxx of the email, it also pulls up other attachments not under that subject line. Is there a way to make my search more specific so that it only returns back the group of documents I need under that specific qualification? I am not sure if this is just a test data glitch or if it would run in the final zap as well.

Thanks 

 

Userlevel 7
Badge +6

Hi there @Brittany F,

I’m jumping in to see if I can help!

I've got a couple of suggestions for you to try out! One option is to use Filter by Zapier. With this, you can set up a filter to check if the email subject or attachment name contains a specific string, like "xxx" (or whatever other qualification you have in mind).

Another idea is to use the "New Email Matching Search" trigger. Here, you can utilize specific search operators to filter the results and only get the emails that match your criteria. For instance, if you want to match the subject line, you can use the "subject" search operator followed by a colon and the exact subject you want to match. For example, if your subject is "xxx", the search string would be "subject:xxx". If you also want to include the attachments in the search, you can use this search string: "subject:xxx has:attachment".

Hopefully, this points you to the right direction! 😊

 

Reply