I have a monthly spreadsheet that relies on figures from the previous month. I'd like to import these values dynamically rather than cutting and pasting them. Is this possible? How do I do it?

  • This question was posted during the private beta of this site. It was closed because it doesn't meet the current quality standards and locked for historical reasons as nowadays it looks to be a trivial question. Commented Apr 7, 2023 at 18:47

8 Answers 8


IMPORTRANGE() appears to be the function you want.

From the Google spreadsheets function list:

Google Spreadsheets lets you reference another workbook in the spreadsheet that you're currently editing by using the ImportRange function. ImportRange lets you pull one or more cell values from one spreadsheet 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("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.

=importrange(A1,B1) Cell A1 contains the string ABCD123ABCD123 and cell B1 contains sheet1!A1:C10

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

"key" is, of course, the string in the URL for the spreadsheet that matches to the key= parameter.

I just tested it by creating two spreadsheets. In cell A1 of the first, I put a string. In cell A1 of the second, I put =importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1") and it displayed the string from the first spreadsheet. (Your key will obviously be different.)

(The format of the function may depend on your locale. In France the formula is not valid with a comma, so you'll need to replace it with a semi-colon: =importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1"))


  1. Google currently sets a hard limit of 50 "cross-workbook reference formulas" per spreadsheet. Source: Google Docs, Sheets, and Slides size limits. (h/t JJ Rohrer)

  2. The "new" Google Spreadsheet (soon to be the standard) removes the 50 limit of 50 "cross-workbook reference formulas (Google Support) (h/t Jacob Jan Tuinstra)

  3. In the "new" Google Sheets you also use the entire URL as the key (Google Support) (h/t Punchlinern)

  • +1 Whoa! I didn't realize there was an in-line version. The other option is to use Google Apps scripting. Create a function that uses SpreadsheetApp.openById([ID]).getRange() and set a local range with that. Commented Feb 16, 2012 at 8:09
  • 4
    It might be worth noting that you'll be limited to 50 of these (per spreadsheet?) (a hard Google limit): support.google.com/drive/answer/37603?hl=en
    – JJ Rohrer
    Commented Oct 10, 2013 at 3:34
  • @JJRohrer: Good catch. I'll update my answer to include that.
    – ale
    Commented Oct 10, 2013 at 13:11
  • 6
    In the new Google Spreadsheet (soon to be the standard), the 50 limit has been removed: support.google.com/drive/answer/3093340?hl=en
    – Jacob Jan
    Commented Apr 1, 2014 at 21:58
  • 6
    Also, in the new Google Sheets you use the entire URL as key. Furthermore, you're prompted to grant access to the requested sheet when you first enter the function. Commented Feb 21, 2015 at 21:10

FYI if you want to reference another sheet within that other spreadsheet, the sheet name should NOT be re-quoted:


=importRange("tgR2P4UTz_KT0Lc270Ijb_A","Sheet Name!A1:A10")

instead of

=importRange("tgR2P4UTz_KT0Lc270Ijb_A","'Sheet Name'!A1:A10")

  • 7
    Thanks. You should add this as a comment to other answer or even just edit that answer.
    – studgeek
    Commented Nov 1, 2013 at 19:10

In the new interface, you should simply be able to type = in the cell, then just go to the other sheet and pick the cell you want. If you want to do it manually, or are using the old interface, you can simply do =Sheet1!A1, where Sheet1 is the name of the sheet, and A1 is the cell on that sheet you care about. This is identical to Microsoft Excel.

  • 7
    if the sheetname has whitespaces in it, use 'This Other Sheet'!A1
    – akira
    Commented Jul 1, 2010 at 14:35
  • 13
    What about from an entirely different document, as I asked in the question?
    – Jeff Yates
    Commented Jul 1, 2010 at 14:46
  • @jeff Sorry; you didn't mention "document" in the body, and for some reason, in my head, I thought "sheet." As far as I know, you can't dynamically link to another document. But you could use multiple sheets, rather than multiple documents, at which point you could do what I suggested. This is how I run my own monthly finances, for precisely this reason. Commented Jul 1, 2010 at 15:09
  • It's okay :) I know there's a way to do it as I've actually done it myself, but I've forgotten and I can't find the sheet I did it in.
    – Jeff Yates
    Commented Jul 1, 2010 at 15:31
  • This doesn't answer the question posted - it is talking about other sheets in the same document.
    – brendan
    Commented Jul 26, 2018 at 14:52

I found this syntax using Insert -> Define new range


Whole A column: 'Spreadsheet With Spaces in the name'!A:A

Whole B column: 'Spreadsheet With Spaces in the name'!B:B


Then you can use it as:

=COUNTIF('First Page'!B:B, "valueToMatch")
  • 1
    Is this also possible for referencing outside the spreadsheet?
    – Jacob Jan
    Commented Dec 30, 2012 at 20:11
  • @JacobJanTuinstra what do you mean 'outside' the spreadsheet? Commented Dec 31, 2012 at 13:51
  • 6
    Well, the IMPORTRANGE function can import data from another spreadsheet. Setting ranges is only meant (possible) within a spreadsheet. That's what I meant with outside.
    – Jacob Jan
    Commented Dec 31, 2012 at 14:11
  • This doesn't answer the question posted - it's only describing how to refer to another sheet in the same document.
    – brendan
    Commented Jul 26, 2018 at 14:53
  • @brendan use IMPORTRANGE as Jacob said. Commented Jul 29, 2018 at 3:57

here is how I did it (reimplemented 'importrange()'):

  • open the script editor ("tools" -> "scripts" -> "script editor")
  • provide a function like this (without any checks, this has to be improved but you get the general idea):
function REMOTEDATA(inKey, inRange) {

  var outData;  
  var ss = SpreadsheetApp.openById(inKey);

  if (ss) {
     outData = ss.getRange(inRange).getValues();

  return outData;
  • use this formula like this in your spreadsheet:
=SUM(REMOTEDATA("key", "SheetName!A1:A10"))

"key" is the key of the document, you find it in the "key=xyz" parameter of the document URL.

the documentation for 'spreadsheet services' provides more information on the topic.

  • 4
    What would be the advantage of implementing this yourself rather than using the existing function?
    – Jeff Yates
    Commented Jul 1, 2010 at 16:22
  • 1
    @Jeff Yates: none, i just implemented it without knowing importrange(). sportsmanship, you know? all in the way of just- because-i-can :)
    – akira
    Commented Jul 1, 2010 at 18:45
  • 1
    unfortunately spreadsheet custom functions cannot open anymore other spreadsheets using SpreadsheetApp.openById() or SpreadsheetApp.openByUrl() -> developers.google.com/apps-script/guides/sheets/… Commented Mar 30, 2016 at 21:11

Yes, it is possible to import values from one spreadsheet to another dynamically in Google Sheets. To do this, you can use the IMPORTRANGE function.

The IMPORTRANGE function allows you to import a range of cells from one spreadsheet to another. You can use it to import values from a previous month's spreadsheet into your current monthly spreadsheet.

Here is the syntax for the IMPORTRANGE function:

IMPORTRANGE(spreadsheet_url, range_string)
  • spreadsheet_url: The URL of the spreadsheet that you want to import data from.
  • range_string: The range of cells that you want to import, in A1 notation. For example, "Sheet1!A1:B5" would import the range of cells from A1 to B5 on Sheet1 of the source spreadsheet.

Here is an example of how you can use the IMPORTRANGE function to import values from a previous month's spreadsheet:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1fZwYzY4H5Vuq4vQP7g-kTdF1Xn3nqr2_KjtSlB", "Sheet1!A1:B5")

This formula will import the range of cells from A1 to B5 on Sheet1 of the source spreadsheet (located at the URL provided) into the current spreadsheet.

Keep in mind that the IMPORTRANGE function only works if the source spreadsheet is shared with you, and you have permission to access it. You will also need to authorize the import by following the prompts that appear when you enter the formula.

I hope this helps! Let me know if you have any other questions.


As the question did not specify how the data is to be used, it is worth noting that IMPORTRANGE can be used anywhere a range can be used, for example, as the range argument to a VLOOKUP:

=VLOOKUP($C24,IMPORTRANGE("https://docs.google.com/spreadsheets/d/ABCDEFGHIJKLMNOP1234/edit#gid=0","Product List!$C$3:$H$31"), 4, false)

(fake spreadsheet URL for demo purposes).


I did it the very easy way by using Query, for example, I have 2 sheets ABC and XYZ and I want to import range from A1 to C30 from Abc into XYZ, click the cell where you want the range to appear and write this:


Note: If your sheet name has spaces then, of course, you write this:

=QUERY('ABC 1'!A1:C30)

  • 7
    The OP asked how to import cells from another document. Can QUERY do that? Commented Nov 6, 2012 at 14:08
  • 4
    @VidarS.Ramdal It can, but it needs importrange("abcd123abcd123", "sheet1!A1:C10") e.g., =query(importrange("abcd123abcd123", "sheet1!A1:C10")) ;-) Commented Jun 2, 2014 at 18:05

Not the answer you're looking for? Browse other questions tagged or ask your own question.