Question

Extract data from Google Sheet spreadsheet and add into another spreadsheet?

  • 12 October 2021
  • 5 replies
  • 971 views

Summary of Project:

I receive a daily email with an XLS attachment (call data for the day)

I want to take that data and update an existing Google sheet with the data for that day

 

What I have so far:

I’ve created a zap that saves the XLS attachment from my Gmail to my Google Drive

I have the final KPI spreadsheet created.

 

Issue:

How do I take the Spreadsheet saved into my Google Drive folder and extract the data in that saved attachment to create a new row on my KPI spreadsheet???


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

5 replies

Userlevel 7
Badge +14

Hi @AnthonyP 

This will depend on the nature of the XLS file contents.

How many rows are in the received files?

 

The data needs to be on 1 tab, and in a flat file format (aka CSV with headers and rows w/o gaps in rows/columns)

CSVs can be imported using the action: Formatter > Utilities > Import CSV File

 

GDrive spreadsheet files produce a CSV version of the file.

Try using the GDrive Find File action to get this URL.

 

GSheets has an action to Create Row(s).

 

Looping app can also be used to handle 1+ line items: https://zapier.com/apps/looping/integrations

Hi @AnthonyP 

This will depend on the nature of the XLS file contents.

How many rows are in the received files?

 

The data needs to be on 1 tab, and in a flat file format (aka CSV with headers and rows w/o gaps in rows/columns)

CSVs can be imported using the action: Formatter > Utilities > Import CSV File

 

GDrive spreadsheet files produce a CSV version of the file.

Try using the GDrive Find File action to get this URL.

 

GSheets has an action to Create Row(s).

 

Looping app can also be used to handle 1+ line items: https://zapier.com/apps/looping/integrations

 

2 rows, 1st row is the header, 2nd row is the data I want.

I tried to use the formatter to CSV as indicated and create the new spreadsheet row, but that’s exactly where I get lost.  It doesn’t give me the option to pull the data from within the spreadsheet.

Userlevel 7
Badge +14

@AnthonyP 

Check out this related topic:

You have to first upload the XLS file to GDrive and choose to save as a GSheet using a GDrive Upload File step.

Then you have to find the uploaded File using a GDrive Find File step.

That will return a link that has the CSV format up the uploaded file.

Search hfor “text/csv”.

@AnthonyP

Check out this related topic:

You have to first upload the XLS file to GDrive and choose to save as a GSheet using a GDrive Upload File step.

Then you have to find the uploaded File using a GDrive Find File step.

That will return a link that has the CSV format up the uploaded file.

Search hfor “text/csv”.

 

Troy - sent you an email through your website.  I’m still stuck, hopefully you can help.

Userlevel 7
Badge +14

@AnthonyP 

Sent you a reply, thanks.