Pinterest Stumbleupon Whatsapp
Ads by Google

The Google Spreadsheet is an incredibly powerful piece of software, yet it seems most users only know and use a small fraction of its potential. For instance, do you know how to get information from one Google Spreadsheet into another? Did you even know it was possible?

Perhaps this is due to the documentation side of things. Google documents things quite well, yet to find what you are looking for you often have to wade through that documentation. There doesn’t seem to be a user-friendly guide written by Google on how to use these things. You either understand the tech jargon and find the answer yourself, or you settle for doing things the way you always did.

So, to answer our original query, there are two ways to share data between spreadsheets. You can either use a spreadsheet script, or you can use an in-line cell function. Since scripting is the more difficult option, we’ll talk through the latter function instead.

Importing Data From Another Spreadsheet Using ImportRange

Importing data from another Google Spreadsheet is actually surprisingly easy to do, once you have learned all the relevant advice on how to the use the function. Until that moment though, there are a dozen ways for stupid errors to thwart your plans. Here’s what you need to do.

To begin with, make sure that you have viewing access to the document you want to get information from. If it’s a public document or you are a collaborator, this process should work.

Next, find the key for the document you are getting data from. In the URL bar after key= you will see the string of letters and numbers. Copy that, ignoring the final #gid= and anything after that.

Ads by Google

You will need to know the exact cell reference from within the spreadsheet you are getting data from. To find this, go to a different tab in the same spreadsheet, press = and then navigate to the cell you want to reference. You should see the cell now has a function that looks like this:

=’Staff Details’!A2

In the spreadsheet you want the data to be imported to, you’ll need to use the ImportRange function. Go to the correct cell and plug in the function details as follows:

=ImportRange(“YOUR-KEY”,”YOUR-CELL-REFERENCE”)

Then take out the single quotes from your cell reference. For instance, mine would now look like this:

=ImportRange(“xyzxyzxyzxyzxyzxyzxyzxyzxyzxyzxyz”,”Staff Details!A2″)

If you don’t take out these extra inverted commas, you’ll get an error message.

error: The requested spreadsheet key, sheet title or cell range was not found.

The ImportRange function will automatically update the data whenever it is changed in the original document, making this an ideal way to import information from another spreadsheet.

Problems With Commas

If you are in a European locale, where commas are used instead of the decimal point, spreadsheets use a semicolon instead of a comma. You may need to write your function as this:

=ImportRange(“YOUR-KEY”;”YOUR-CELL-REFERENCE”)

Import Range Instead Of Individual Cells

You can only use these ImportRange functions 50 times per spreadsheet, so it doesn’t make sense to limit yourself to importing each cell individually. You want to use the function’s full potential and import a RANGE each time you use the function. That is, get a whole column, row or area at a time. To do this, add a colon after the cell reference and make it an area reference.

This would import a 50×3 area:

=ImportRange(“xyzxyzxyzxyzxyzxyzxyzxyzxyzxyzxyz”,”Staff Details!A1:C50″)

This would import the whole A column:

=ImportRange(“xyzxyzxyzxyzxyzxyzxyzxyzxyzxyzxyz”,”Staff Details!A:A”)

Once you’ve done this, you’ll see that the cells affected by this function have Continue functions in them, such as:

=CONTINUE(A2, 2, 1)

Importing Data From Another Spreadsheet Using ImportData

The ImportData method is worth mentioning. However it revolves around you making the data in the original document public, which is not something many people want to do with their spreadsheets. To use it, publish your spreadsheet or one sheet of the spreadsheet, essentially making a copy of it public, using File > Publish to Web. Then in the section marked “Get A Link To Published Data”, you can enter details of the cells you’d like to import into other documents. This will give you a unique URL for those cells.

Then, the ImportData function works like this:

=ImportData(“YOUR-UNIQUE-URL”)

The ImportData function will reference whatever is in the published version of your original spreadsheet. This means that if you stop automatic publishing on the original spreadsheet, the new spreadsheet will not pull in the most up-to-date data. This, coupled with the fact that you need to make your data public in order to use the ImportData function, makes it the less appealing of the two options.

More Advanced Google Spreadsheets Tips

If you love Google Spreadsheets and want to learn a few more advanced tricks, check out how to add the current time to a Google Spreadsheet How to Add the Current Time to a Google Spreadsheet How to Add the Current Time to a Google Spreadsheet Read More , how to use Google Spreadsheets as an RSS reader Create an RSS Feed Reader Using Google Spreadsheet Create an RSS Feed Reader Using Google Spreadsheet Looking for a more practical, everyday use for my growing collection of Google spreadsheets, I decided to see if I could use one function I haven't covered here at MUO yet called "ImportFeed", to try... Read More (a neat hack for the cubicle dwellers), how to use Google Forms and Google Spreadsheets to create a self-grading quiz How To Use Google Forms To Create Your Own Self-Grading Quiz How To Use Google Forms To Create Your Own Self-Grading Quiz Are you a teacher or trainer? Someone involved in a hiring process? Do you need to check somehow that other people have the skills or knowledge that you expect them to? Well, you're going to... Read More , and a few more useful Google Spreadsheet Functions Perform Amazing Feats With These Useful Google Spreadsheet Functions Perform Amazing Feats With These Useful Google Spreadsheet Functions I am ashamed to admit that I've only recently decided to start experimenting with Google Spreadsheets, and what I've discovered is that whatever allure or power Excel had over me due to the amazing things... Read More .

What’s your favourite advanced tool for Google Spreadsheets? Let us know!

  1. Jeffrey
    September 2, 2016 at 2:41 pm

    Outdated and confusing in 2016-09.

  2. Robert
    May 31, 2016 at 11:38 am

    There is actually a new Google Sheet Add-on called "Import Sheet" that automates the process described in this article.

    Maybe it's useful for some of you:

    http://importsheet.com/

    Warmly,

    ~Robert

  3. Claudio
    May 28, 2016 at 11:10 am

    Just to reply to Eric:

    You are evidently right. I didn't find any "key" label in the URL too but I just copied whatever was in-between "https://docs.google.com/spreadsheets/d/" and "#gid=" and it worked well!

    Google Sheets will then ask you to allow the linking of the two sheets and once that is done by pressing a light blue button (after a few second of "Loading ...") the content of the first Sheet's cell will appear in the second one.

  4. Claudio
    May 28, 2016 at 11:03 am

    Thank you so much Ange!!! It instantly worked for me! :)

  5. Eric Schulzke
    March 24, 2016 at 8:24 pm

    I think the current google spreadsheets do not use "key=" in the URL. I have no idea what to do about this.

    • Eric Gross
      March 26, 2016 at 8:35 pm

      According to the comments in the Import Range function, in the new version Google they changed it so you now have to use the entire URL instead of just the key.

      =importrange("https//docs.google.com/a/yourdomain/key/edit/#gid", "Sheet1!B2")

      This works, but only if you are importing the range into a new version of Google. For older versions that you are referencing in a new google document you can still just use what's after key= and the string of letters and numbers up until the #gid=.

      When you do import range into the new version you'll receive a pop up "You need to connect these sheets." and a button to "Allow access", after clicking this your data should populate. I've not had any issues with this in new version -> new version and importing old version -> new version.

      However, the problem is if you are trying to import new version -> old version (that still has key= in the url), even using the entire URL will not work, because the pop up "You need to connect these sheets"/Allow access does not work. Therefore, it will default to the standard invalid import range error "error: The requested spreadsheet key, sheet title, or cell range was not found."

      Probably the easiest solution is to convert the old version to new: https://support.google.com/drive/answer/3544847?hl=en

      FYI I have a co-worker that thinks the pop to "Allow access" may be buggy based off browser, but so far it's been working for me in Firefox & Chrome.

      Hope this helps.

    • Bastiaan
      July 2, 2016 at 5:08 pm

      I just did this (and it works):
      For example (i randomized the URL a bit) this was my URL:
      https://docs.google.com/spreadsheets/d/1yLjta1NfHDp9HOx1gV3AJore8URa01239M5G1V0z2sk/edit#gid=0

      I just copied the 1yLjta1NfHDp9HOx1gV3AJore8URa01239M5G1V0z2sk part and it worked fine!

      Thanks for this helpful article man, life saver ^^

  6. Tom B
    June 11, 2015 at 3:35 pm

    Import Range seems to pull data in. It doesn't seem to pull data validation rules associated with that block of data in. This means if I have drop down menus for selecting data in the original sheet, I don't see the drop downs in the sheet I import to.

    Can I import the validation rules as well as the data somehow? What about conditional formatting?

    It seems like there ought to be a way to bring in both data and validation/format (data + presentation).

  7. Gomi
    April 11, 2015 at 9:28 am

    Hi

    I´m using a tool calle Import Manager that Centralizes a large variety and volume of data in a quick and easy way. Much easier and faster to use than Import range. You can try it out for free here https://chrome.google.com/webstore/detail/import-manager/baljgggfhdleglaneclmjhhmflaombln?utm_source=permalink

  8. Hema
    February 18, 2015 at 4:43 am

    Hi,
    importrange works fine with data from one file . But How to import more than 2 files dynamically in one sheet(Here actually data type , columns are the same)can you just help me for this issue.

  9. marc
    February 12, 2015 at 9:15 pm

    do you explain what your-key is. probably should be done.

  10. Fabrizio
    February 12, 2015 at 5:57 pm

    Comma or semicolon, this is the question
    Thanks Angela, you have resolve my issue in europe

  11. Mariusz
    January 26, 2015 at 11:43 am

    Hi!
    This was 100% The commas problem.
    Thanks for the help

  12. Cynthia
    March 12, 2014 at 6:28 pm

    I was reading a few of these "How to" responses and yours is the one that helped me figure it out! Very well written! Thank you!

  13. tA
    February 10, 2014 at 11:56 am

    Many thanks for semicolon-hint! This thing drives me crazy

    • Angela A
      February 15, 2014 at 10:02 am

      Definitely can be crazy-making.

  14. Anthony
    January 29, 2014 at 4:36 pm

    This is great...unfortunately if you switched to the new Google Sheets you will get..."This function is not quite ready in Google Sheets"...bummer

  15. Gustavo Dambiski
    January 21, 2014 at 2:44 am

    Thanks! I was with the commas' problem and really didn't know what was happening. Not only european countries, but also Brazil has this problem. Probably all latin-america too. Thanks again!

  16. ren bor
    December 15, 2013 at 12:49 am

    looking to export graded quizzes on google to google grade book

Leave a Reply

Your email address will not be published. Required fields are marked *