How To Import Data From Other Google Spreadsheets

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 use Google Spreadsheets as an RSS reader (a neat hack for the cubicle dwellers), how to use Google Forms and Google Spreadsheets to create a self-grading quiz, and a few more useful Google Spreadsheet Functions.

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

Join live MakeUseOf Groups on Grouvi App Join live Groups on Grouvi
Awesome Websites
Awesome Websites
127 Members
Deep Web Communities
Deep Web Communities
78 Members
Best Music Services
Best Music Services
42 Members
Web for Kids
Web for Kids
31 Members
Ads by Google
Comments (12)
  • Tom B

    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).

  • Gomi

    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

  • Hema

    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.

  • marc

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

  • Fabrizio

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

Load 10 more
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.
Affiliate Disclamer

This review may contain affiliate links, which pays us a small compensation if you do decide to make a purchase based on our recommendation. Our judgement is in no way biased, and our recommendations are always based on the merits of the items.

For more details, please read our disclosure.