Best answer

App to retrieve csv from url

  • 13 April 2020
  • 5 replies
  • 865 views

Hello all - 

I am working on building a zap that will retrieve a csv that is hosted online and pull the data over to a google sheet. However, whenever I do this, it takes all the data from the csv and crams it into one cell. The desired output would preserve rows and columns from the original csv.

I’m currently using Scheduler → Webhooks → Google Sheets.

Thanks for any input!

icon

Best answer by Danvers 23 April 2020, 14:44

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.

5 replies

Userlevel 7
Badge +11

Hi Phil,

The first thing I’d suggest is to check out our Formatter app’s Import CSV File utility:

 

 

Assuming you have fewer than about 1,000 rows of data you’ll then be able to use Google Sheets’ Create Spreadsheet Row(s) action (the one that supports line items) to get the data into your Google Sheet. Can you give that a shot to see if it solves your problem?

Nick - 

Thank you! Yes, this worked. I am able to do this for smaller payloads that have periodic updates. 

Two follow-up questions:

  1. Is there an app that can delete multiple rows at once from a google sheet - or overwrite previous content? An alternative would be to delete the entire sheet then re-run the full process to pull in the csv and rename the sheet accordingly.
  2. Any suggestions to deal with payloads of 4-6 megabytes (~80k rows)? I am not able to edit the import csv unfortunately.

Thanks - Zapier has been game changing!

Phil

Userlevel 7
Badge +12

Hi @philp

To answer your questions:

  1. Is there an app that can delete multiple rows at once from a google sheet - or overwrite previous content? An alternative would be to delete the entire sheet then re-run the full process to pull in the csv and rename the sheet accordingly.

You could do this with Zapier, though it’s a little fiddly. You’d need to user the Create New Spreadsheet Row(s) action to add the rows to a new sheet. You would then use a second Zap that triggers from the new rows in that sheet and then updates the original sheet. 

 

  1. Any suggestions to deal with payloads of 4-6 megabytes (~80k rows)? I am not able to edit the import csv unfortunately.

To be honest, if you have that many rows then Zapier might not be the right tool for the job here. The Formatter Import CSV File can’t handle that many rows, and that’s the only tool that Zapier has that would help here, sorry about that!

Got it! Good to use the right tool for the right problem. Love zapier automation / transactional processes! Thanks!

Userlevel 1

I had a situation where I needed to take a csv attached to a daily email and update a Google Sheet with the data. I ended up using the csv in a “Replace File in Google Drive” step so it overwrites an entire file, and in the destination Google Sheet, I used the =IMPORTRANGE() function to get the contents of the csv. Maybe a similar solution can help here?