Referencing other Google spreadsheets

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:

=ImportRange(“spreadsheet_key“,”sheet_name!range“)

  • 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!

16 thoughts on “Referencing other Google spreadsheets

  1. This might seem like a ridiculously easy question… but do you know how to sort a Google Spreadsheet by a certain column for only a set number of rows? (Example: Jeff’s spreadsheet and wanting to only sort Clip Art pages, versus the whole entire spreadsheet.)

  2. Hi Corrie,

    Your explanation to link to another spreadsheet was just what I was looking for. I will definitely have calculations from cells in multiple spreadsheets.

    THANK YOU.

    QUESTION:
    I think the linked cell gets refreshed periodically.

    That is if a change is made in the original data, it is not reflected instantaneously in the linked cell.

    Is there a way to know the time period the linked cell is updated?

    Is there a way to change the time period the linked cell is updated?

    I have searched but can’t even find where you found the information you posted for =ImportRange().

    Thanks

  3. Hey, bro! Thanks for this blog post! You actually helped me out of a pretty crazy jam. I have a gdoc spreadsheet that is used for a python data export to mysql. Anyway, the thing is huge now (40+ sheets) and it has started breaking down. That’s a very annoying shortcoming of gsheets vs excel (gdocs sucks for huge files). However, your cool solution was just what we needed! Now we can share some key data between two separate gdocs, and we no longer have to store all the generated data in a single doc, so things run smoothly again.

    1. Hi Do you got answer to your query ‘Do you know how to preserve formatting (color, font, hyperlinks) of the cell-content when using the importrange function?’ I have same concern, let me know if you have any solution…….thanx in advanced……:)

  4. FYI Don’t copy paste Corries code directly as the ” marks are the wrong character set or something. Replace the ” when you are trying out the code in the spreadsheets.

  5. I am trying to reference one spreadsheet from another.

    I tried putting the below and have had no success.

    I have a previouse Reference (Contact) Sheet where the contact entered their nickname. In a new spreadsheet, I want the contact person to be a able to select their nickname. On the new spreadsheet.

    I was using the Data Validation and it would not accept the cell range:

    =importrange(“0ApLyLFnFJn8VdENQWk5wYTM0bFE0ak5fWXRzZmFqdUE#gid=0”,”Registration!h2:h”)

    I also tried with “!h2:h4”)”

    Let me know if you have any suggestions.

    Thanks,
    James

  6. Thank you so much for your “Referencing other Google spreadsheets”!
    It was just what I was looking for. It was easy to follow and it worked!

  7. The importrange function is limited to being used 50 times in a spreadsheet. I run two concessions stands. I kept a spreadsheet of sales for each and then I have a third spreadsheet that combines the two. I am unable to complete that spreadsheet due to the limitation. Anyone else know of a way to import data from a one spreadsheet to another other than the importrange function? Basically I want to add sales from Spreadsheet1!Monday to Spreadsheet2!Monday and so on, in the third spreadsheet, so that I have total sales of each day of the week.

  8. My Question is how to link up the two sheets in two different google documents with each other so that if I change even the whole format and data in one sheet it also effect the other sheet’s format and data too. I mean to say when you link up the two sheets they should be mirror of each other. when changes applied to one sheet it reflects in other sheet

  9. thank you!

    But 1 comment here: you have an error!

    you need to put ‘;’ instead of ‘,’

    wrong formula: =ImportRange(“spreadsheet_key“,”sheet_name!range“)

    right formula:
    =ImportRange(“spreadsheet_key“;”sheet_name!range“)

    Anyway thank you so much, this formula helps a lot!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s