Best answer

Webhook Get to new Google Spreadsheet

  • 5 April 2021
  • 7 replies
  • 152 views

Userlevel 1

I’ve got a Webhook GET request that returns a CSV file as Text.  I’m trying to post that content to a new Google Spreadsheet.  I’ve tried using the Sheets app, but it will only create a blank spreadsheet, it will not add rows at the same time.

I’ve also tried uploading the GET contents via the Google Drive app, but it always creates a text document and I can’t force it into a sheet.

I tried using Utilities to convert text to a CSV and then using the CSV text output, but that didn’t help either.

 

icon

Best answer by gspence 6 April 2021, 01:52

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.

7 replies

Userlevel 7
Badge +14

Hi @gspence 

Can you provide some more context about how your current Zap steps are configured as well as the CSV data being returned from the trigger?

Try using this action step in the Zap: GSheets - Create Row(s)

This will work if the CSV contents are line items or can be converted to line items via the Formatter > Utilities.

 

Otherwise, check out the Looping app for handling individual line item records: https://zapier.com/apps/looping/integrations

Userlevel 1

Yeah, I’m trying Create Rows(s), but it’s not working.  Here are more details about the ZAP.

 

  1. Schedule - Every Month (run on the 5th of the month)
  2. Formatter, Numbers - Subtract 1 month from the scheduled day, return just the Month (M)
  3. Formatter, Numbers - Subtract 1 month from the scheduled day, return just the Year (YYYY)
  4. Webhook, GET - Make a GET request to a URL, using the month/year from 2 and 3 to request the file from the previous month.  The CSV results are returned in the “text” field, with headers.
  5. Utilities, Import CSV File - Input is the text from step 4, returns line items of each column as well as csv_text output which looks identical to the input.
  6. Create new Spreadsheet in Google Sheets.  Creates the sheet in a specified folder and names it, using the data from steps 2 and 3 to differentiate it from other files.
  7. Create Spreadsheet Rows(s) - Uses the ID from step 6 in the Spreadsheet value to add to the newly created sheet.  Selected Sheet1 for the worksheet.

 

When I click Continue, it goes straight to Testing, and never gives me the option to specify where the data for the new rows should come from.

 

 

If I click Test, it gives an this error.

 

 

Hope that helps explain my issues.  Seems like when I have all the CSV data, including headers in a text field, there should be an easy way to turn that into a spreadsheet.

 

Userlevel 7
Badge +14

@gspence 

Check out this article about how to configure GSheet steps using a dynamic value for Worksheet/Spreadsheet:

 

Userlevel 1

That article seems to focus more on adding rows to existing sheets and matching up columns.  I don’t need anything near that complicated.  I just want to take a CSV file exactly as is and dump it into a sheet.

This solution feels a bit over-complicated.  Is there not a simpler way to just create a sheet from a CSV without matching columns when I might not know what columns are being downloaded?

 

 

Userlevel 7
Badge +14

@gspence

The GSheet Create Row(s) step relies on mapping data points to columns.

Check out this help article about how to configure a GSheet to work with a Zap:

https://zapier.com/help/doc/how-to-set-up-your-google-spreadsheet-to-work-with-zapier

 

If the you were able to get the CSV file object, rather than the CSV file contents, then you should be able to upload the CSV file to GDrive and specify the file format as GSheet.

Userlevel 7
Badge +14

@gspence 

Perhaps check out this related topic about a private Zap app that can convert line items to a CSV:

https://community.zapier.com/tips%2Dand%2Dinspiration%2D5/new%2Dintegration%2Dadvanced%2Dutilities%2D3587

 

NOTE: You may still have to leverage the Formatter > Utilities > Import CSV File step and then map the desired columns to this new action step, but then you could use a GDrive Upload File step with settings to convert to a GSheet.

 

Userlevel 1

I appreciate the responses.  I don’t think the private app will help, since its goal is to make a CSV.  I have the CSV already as the original format, and can convert it to line items with the Utility app.

 

I eventually made this work, but I was not happy with the solution.  I had to “hard code” the column headers into the “Create Spreadsheet” step.  This alone means my zap will break if they change the format of the file, so I don’t like that.

Then I was able to map the file to those headers, as recommended in one of your earlier posts.  But again, I’d rather not worry about my zap breaking if they change the name of a header, or add a column, or something like that.

It seems that the ability to dump a CSV into a new Spreadsheet is one of the most basic features we should have available, so I’m quite surprised it does not exist without jumping through a bunch of hoops.