Growth5 Blog

Friday, October 23, 2009

Cross-Workbook Reference in Google Docs

Last Friday we talked about how to scrape data from the web into your Google Doc. Another feature Google Docs has added that is really helpful, is the ability to pull data from one Google Doc to another - a feature that we heavily rely on in Excel.

The formula to accomplish this is:

=ImportRange(spreadsheet_key, sheet!range)

1. The spreadsheet_key is the long string of characters following "key=" in the url of the Google doc you would like to pull data from. For example: if the url of your Google Doc is as follows, the spreadsheet_key is the part of the url highlighted in blue.

spreadsheets.google.com/ccc?key=0ArfzO9kiLDtFcHJhWkdOLVROQ2YtUG44WkM5S2pQR1E&hl=en

2. The sheet!range is the cell or cells of the Google Doc you are pulling data from that you want to import into your active Google doc. For example, if you want to pull cells A1, B1 & C1, from "sheet1" of another Google Doc, the 2nd part of the formula would look like this:

sheet1!A1:C1

3. The full formula would look like this:

=ImportRange(0ArfzO9kiLDtFcHJhWkdOLVROQ2YtUG44WkM5S2pQR1E&hl=en, sheet1!A1:C1)

The data in the three cells you imported will then appear in the cell you put the formula in, and the two cells to the right of it.

4. If I am pulling data from more than one Google Doc, I list the "key" for each of the docs in A1, A2, A3, etc... of one of the worksheets and then put the cell range of that Google Doc I want to import in B1, B2, B3, etc... by doing so, it allows me to simplify the ImportRange formula to this:

=ImportRange(A1,B1)
=ImportRange(A2,B2)
=ImportRange(A3,B3)

Note: In order to use ImportRange, you need to have been added as a viewer or collaborator to the spreadsheet from which ImportRange is pulling the data. Otherwise, you'll get this error: "#REF! error: The requested spreadsheet key, sheet title, or cell range was not found."

You can read more about the ImportRange formula here.

Labels:

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home