Best answer

Copy all data after row 3 in a google sheet to another sheet

  • 30 March 2020
  • 9 replies
  • 1222 views

Hey everyone! I’m new to Zapier and I’m trying to set up a workflow to automate some work tasks. Here is what I’m trying to accomplish and I’m hoping you guys can point me in the right direction.

  • Every day I receive an email from an address that gives me a report of items my customers have purchased through a specific manufacturer. My customers place their orders through them so this is my only reporting from this specific manufacturer 
  • I receive an Excel Spreadsheet in this email with a list of items and quantities, which I have already managed to save daily to a folder in Google Drive and convert to Sheets
  • There is a 3 row fixed header in this file that I would either like to delete completely and copy all rows to another sheet OR copy all rows containing data below line 3 (don’t copy the the fixed header). I would like this to run on any new sheet created in the Google Drive folder
  • Paste this data into another google sheet after all existing rows… so that it’s basically a file with a running tally from the previously received excel files
  • I already have a Zap that sees new rows in this running tally and creates a deal in my CRM

My big issue here is figuring out how to bridge the gap between downloading the file to the Drive Folder and the running tally.

Any help would be much appreciated! I’m a single man small company and I’m trying to remove as much busy work as possible so I can focus on more important things!

Thank you in advance!

6.8.0
icon

Best answer by Davidh88 21 June 2020, 09:16

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.

9 replies

Bump

Userlevel 7
Badge +12

Hi @casedog3000 I’m sorry for the delay in getting back to you on this!

 

Is there anything in the rows of data that is never in the header rows? For example, do the rows have an email address in? And what’s the maximum number of rows that you might have in a day? If there’s not more than 10 rows, then you could use the Google Sheets Find Many Spreadsheet Rows (With Line Item Support) action. You would use a search that would only find the data that you want and not the header rows (for example, if the rows have email addresses in them, you could search for an ‘@’). That would get the data that you want and not the header row, that you could then use with the action Create Spreadsheet Row(s) to add the rows to your other sheet. 

 

I hope that helps!

@Danvers Thank you so much for the help! I would say that more than 10 rows doesn’t happen that often but it does occasionally… is there any way to check if row has any input whatsoever to pull that data and copy it to the other spreadsheet? I do see the Advanced “Get” option that will do 20 rows max. I do have a column that is delivered with true or false, I only collect data from the rows that return “false” in the last column. Could I have it return, then copy every row that is false using a filter, even if there are more than 20 rows?

Userlevel 7
Badge +12

@casedog3000 Having it look for every row with ‘False’ in it sounds like a good approach. Even using the Get Many Spreadsheet Rows action, the Zap will only be able to find the first 20 rows that meet the search criteria, so if there were more than 20, it wouldn’t find those extra rows. 

 

One thing to mention about the Get Many Spreadsheet Rows  action is that it returns the information as a single JSON value and flat rows (line items), so you’ll need to know how to work with that in the next step of the Zap. It is possible, it just requires a bit more technical know-how 🙂

Much appreciated. Maybe you can point me in the right direction even further?

Right now I took that json file and used the Webhooks Zap to POST to a custom zapier URL (from the Catch Webhooks trigger), I’m building the data table in my POST Webhooks zap but I’m unsure how to format the output from the json to fill in the data. I’ve named all of my columns on the left so I can put them in the final fields on my spreadsheet, but not sure how to fill in the field on the right to extract the data from the rows in the json. I’ll use whats posted to the CATCH Webhooks trigger to fill in be spreadsheet rows.

Is there maybe a different way to do this? I’m fairly technical and can figure it out if you can show me the resources you recommend. Thank you!

6.9.0
Userlevel 7
Badge +12

I’m not super familiar with the output of the Get Many Spreadsheet Rows action as I’ve not used it myself. 

 

If it looks like a standard line item array then you might not have to send it off to a different Zap. You can add a Google Sheets Create new Spreadsheet Row(s) action into the Zap, which will add a new row (in your other sheet) for each line item. 

 

If it looks like the data will need some extraction, then you will likely need to use a code step to do that. If you have any questions about the set up of webhooks or code steps, then posting a question in the Developer Discussion category is a good place to start! 

Userlevel 7
Badge +11

Hi @casedog3000!

I wanted to check in with you on this one, since we didn't hear back after our last message. Did you still need help here or were you able to resolve things? Please let us know :)

Thanks for checking in! I ended up hiring someone to do it using google script outside of zapier.

Userlevel 3
Badge +1

I know you have looked to solve this already. So just a general comment for anyone who reads later.

 

Once you have data in a Google sheet you have some functionality in Google to move a manipulate the data.

For example you could use Google Query in your case.

IMO zapier is perfect to move data INTO sheets to build the database table. After that it is not necessarily the right tool.

If Google sheet provides some limitations than Airtable is worth reviewing.