How To Import Data From Other Google Spreadsheets

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.

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 50x3 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.