I’m more and more impressed with Google spreadsheets as I get into them. Today’s new trick that I learned, as I was helping my sister figure out how to do it, was how to pull in data from one Google spreadsheet into another — and the data will theoretically update automatically in the second spreadsheet when you update the first.
I can’t think of immediate applications yet, so I’m posting this for my own reference in case I need to do it in the future!
Using ImportRange to pull in a range of cells from one spreadsheet to another:
Inside your new spreadsheet, select the top-left cell of the area where you want to pull in data. Then type this formula:
- spreadsheet_key: The “key” variable in the Google spreadsheet link. For example, the bold text here is the spreadsheet key: http://spreadsheets.google.com/ccc?key=abcdefghijklmnop&hl=en
- sheet name: The text that is in the bottom tab of the spreadsheet.
- range: This can be a single cell or a range of cells – for example, B2 (single cell) or A3:A5 (part of a column) or A2:E10 (a block of cells).
Another seemingly roundabout way to do this is to use ImportData.
First, publish your original spreadsheet. In the Publish tab is a link for “more publishing options.”
Change the File format to CSV, pick the specific sheet you want to import, and type the range of cells. Then click the Generate URL button.
Use this URL in the ImportData(“published_url“) formula.
I’m not really sure what the pros and cons are of one method over the other, although ImportRange seems simpler if you’re working with your own spreadsheets as you don’t need to mess with publishing.
Anyway – it takes Google a minute or so to refresh new data from the original spreadsheet, but I think it’s pretty cool!