19

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.

Improve this question
8
  • Why can't you just house this data in a Google Spreadsheet and then share the sheet with them, skipping the CSV altogether? I guess my question is why not use the Google sheet to pull the data instead of it dumping into a CSV.
    – OnenOnlyWalter
    Feb 19, 2013 at 21:07
  • The data in question is inventory questions about use of the website, e.g. a list of user-created objects on the site for administrators, a sort of activity report on the site itself. If there's a way to pull this data directly into a Google spreadsheet without it being in CSV, I'd love to hear about it.
    – pjmorse
    Feb 19, 2013 at 21:11
  • Are you suggesting using something like github.com/tricycle/gdata_spreadsheet to push the data directly to the spreadsheet?
    – pjmorse
    Feb 19, 2013 at 21:25
  • Exactly, using the gdata library, you can import data and write directly to a Spreadsheet, I'm not an expert in that area however, but thought I'd ask if there was some reason you're avoiding the gdata info.
    – OnenOnlyWalter
    Feb 19, 2013 at 21:57
  • I'm not avoiding it, I just found it easier (so far) to export CSV because I'm not familiar with the gdata libraries (yet). (Don't get me started on the CSV library I ended up with...) I'll look into the gdata plugin and see where that lands me.
    – pjmorse
    Feb 20, 2013 at 0:33

2 Answers 2

Reset to default
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.

Improve this answer
5
  • but the file is printing some wierdo data, how to avoid that?
    – Mr X
    Nov 17, 2014 at 8:48
  • @atjoshi: the cells with the imported data are still the function result. editting could lead to removal of the whole data. Better to copy and paste values only after the load, if you need to edit/print the data.
    – Mindwin Remember Monica
    Apr 8, 2017 at 10:43
  • @atjoshi "How to avoid printing weirdo data" is a new and different question, but probably one worth asking.
    – Amanda
    Oct 25, 2018 at 17:36
  • Great, but the trouble is it doesn't auto-update. importData also doesn't allow you to reference NOW() function - which I tried to put to the GET parameter to always have a fresh url
    – Fanky
    Sep 10, 2019 at 10:46
  • It should update hourly: "Sheets will update the data automatically at up to 1 hour intervals , using the IMPORTDATA() function, which is very similar to IMPORTRANGE(). To force a refresh of the data synced into Sheets, you can go to the cell in Sheets which has the formula, delete it, and then undo the change."
    – Kevinleary.net
    Jan 5 at 0:13
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.