Google Sheets – Pull CSV Data from URL

csvgoogle sheets

I have a site which allows authenticated users (read: in-house users) to download certain data from the site in CSV format, e.g. http://example.com/activities.csv . Can I create a Google Spreadsheet which pulls its data directly from that URL?

(The idea is that I could then share that spreadsheet with them – we're on the same Apps domain – and skip the step of downloading and importing a CSV file every time we want to update the spreadsheet.)

I've looked through Spreadsheet's menus and help and haven't found a toehold on this; I'm wondering if it's not possible or if I'm just searching the wrong things.

Best Answer

Drop this formula in the first cell of your google spreadsheet:

=importData("http://example.com/activities.csv")

And it will automatically fill out the rest of the current spreadsheet with as many columns and rows as it needs until all the data from the original source csv is displayed.

There are many other powerful ways to feed Google Spreadsheets from all kinds of external sources. Check out this article for some demonstrations.

Related Solutions

Google-sheets – No force-reload tip on Google Sheets

i would suspect that setting the expire-time for the .csv file could help. that would be something for the server to deliver.

OR you can trick the webbrowser by appending something random at the end of the url you are using to fetching the data (see my comment on your question):

http://example.com/myfile.csv?_invalidate_cache_of_browser_XYZ

and then you have to put some random XYZ into the url, maybe by usuing the rand() function.

Google Sheets – Email Specific Spreadsheet Row Data on Event

Have an additional column, "flag" (leave it blank). Now, in your onEdit event:

Look for rows that have a "Closed" status and an empty flag field. (Search the matrix generated by SpreadsheetApp.getActiveSheet().getDataRange().getValues())
Now, for each row, do the following:
Find the email cell in that row (you know the column number), send the emails
Set the flag cell in that row to 1 or something

That's it :)

Best Answer

Related Solutions

Google-sheets – No force-reload tip on Google Sheets

Google Sheets – Email Specific Spreadsheet Row Data on Event

Related Topic