Tech Blog :: Google Docs tip: importRange function to pull in other spreadsheet data


Mar 23 '11 7:58pm
Tags

Google Docs tip: importRange function to pull in other spreadsheet data

A great Google Docs Spreadsheets function I've been using a lot lately is importRange, for pulling in data from one spreadsheet into another. For example, I have one spreadsheet with my income, including calculating my taxes, and another spreadsheet with my budget, where I pull taxes in using importRange. Then I can fill out each sheet and not worry about the other.

The syntax as described in the documentation is:

=importrange("abcd123abcd123", "sheet1!A1:C10")
"abcd123abcd123" is the value in the "key=" attribute on the URL of the target spreadsheet and "sheet1!A1:C10" is the range which is desired to be imported.

A few notes/lessons I've learned:

  • Make sure to use double quotes not single quotes, or it'll give a big "ERROR".
  • The values update when you refresh the spreadsheet, and possibly on some timers, but if you want to update on the fly (say, with both sheets open), go to the start of the importRange block and press Ctrl+R (for Refresh).

Another great function I've been using is sumif (i.e. a conditional sum) - very useful for financial spreadsheets.

Ben,

Do you know how to preserve formatting (color, font, hyperlinks) of the cell-content when using the importrange function?

Saqib