Saturday, September 8, 2012

Google Spreadsheet ImportRange

ImportRange


"
0AmMTqpzD9YRndGYxxxxx" is the value in the "key=" attribute on the URL of the target spreadsheet and "sheet1!A1:D20" is the range which is desired to be imported.
Cell A1 contains the string "
0AmMTqpzD9YRndGYxxxxx" and cell B1 contains sheet1!A1:D20

Google spreadsheets lets you reference another sheet from the spreadsheet that you're currently editing by using the ImportRange function. ImportRange lets you pull one or more cell values from one sheet into another.



To create your own ImportRange formulas, enter =importRange(spreadsheet_key, range). For languages where comma is used for decimal separation, use a semicolon instead of a comma to separate arguments in your formula.


Spreadsheet_key is a STRING which is the key value from the spreadsheet URL.
Range is a STRING representing the range of cells you want to import, optionally including the sheet name (defaults to first sheet). You can also use a range name if you prefer. 
Given that the two arguments are STRINGs, you need to enclose them in quotes or refer to cells which have string values in them.
For example:
=importrange("0AmMTqpzD9YRndGYxxxxx", "sheet1!A1:D20")
=importrange(A1,B1)

Note: In order to use ImportRange, you must be either owner of the spreadsheet from which data is fetched or 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."


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,


2 comments:

  1. Thank you - when the formula that included the spreadsheet URL was not updating from each of the previous sheets, I could not find the solution. I am bookmarking your site. I appreciate the explanation!

    ReplyDelete
  2. It is helpfull but I would like to import whole spreadsheet.
    thanks
    PS

    ReplyDelete