20

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.

pjmorse
  • 303
  • 1
  • 4
  • 10

2 Answers2

28

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.

Zach Harkey
  • 396
  • 4
  • 3
0

Update to fix the above answer for an issue relating to sheets reading the csv correctly:

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.

If you are using .csv, you need to ensure the csv is read as a table. For my use of the importdata() formula, I have been reading a .csv stored in Google Drive. After ensuring the .csv has the correct sharing permissions, I link use a download link for the .csv;

https://docs.google.com/uc?export=download&id=...

as opposed to direct link;

https://drive.google.com/open?id=...

or

https://drive.google.com/file/d/.../view?usp=sharing
Alasdair Duncan
  • 1