08 Jun 2017
functions 5 minute read

Use the IMPORTRANGE function in Google Sheets to aggregate and link information across spreadsheets.


Imagine being able to link sheets together. Not by manually copying and pasting information, constantly checking to see if it needs to be updated, but actual live linking. Imagine your data being updated automatically whenever the source data changes. This is exactly what the importrange function does, it let’s you import real-time data from another sheet. Here is how to get the best out of it.


Specifying the data source

importrange is all about importing, so the most important thing to do is to specify where the data is coming from (the source spreadsheet). To do this, you will need to supply either the spreadsheet id or the spreadsheet url. To get either, you’ll need to jump onto your source spreadsheet and head up to the browser address bar. The url for the spreadsheet should begin with ‘’. The entire string is referred to as the URL (it normally ends with ‘edit’ or ‘view’). The ID of the spreadsheet is the seemingly random long set of letters and numbers that you will find between the forward slashes (/) that delineate parts of the entire page address.

Writing the formula

Once you’ve copied ctrl-c the id or whole address of the spreadsheet, you can create your formula in the sheet where you want the linked data to appear (the destination sheet). An example is shown below. Make sure that you quote your URL or ID using double-quote marks. As well as providing the source sheet address, you will also need to specify which data you want to import / link. This is supplied (quoted as well) in the same way you would reference cells in normal formulas, namely by providing the sheet name, following by an exclamation mark, and then the range.


The first time in each destination sheet that you run the formula (for a particular source sheet) you will be asked to authorise access. This effectively saves your access to the source sheet, meaning that if another user (who doesn’t have access to the source sheet) does have access to destination sheet, will still be able to ‘see’ the data that you have imported. To authorise it, hover over the cell and click on the button that will pop up. Remember that this prompt won’t happen if you have embedded the importrange function within a larger formula. So good practice would be to run a test the import (importing a single cell, ‘A1’ for example) first, authorising the import before writing any complex formulas.


Understanding the language of the range is literally the key to getting the correct data. Here are a few examples and explanations.


The standard output of this formula will be to display the imported data in the destination sheet. However, you can also pass the imported data to other functions, such as query, vlookup, match or even sparkline. All of these formulas will allow you to further refine the data being displayed. For example, you might want to filter the data being imported for all the values larger than a defined number (greater than 100) or even by a value in your current sheet (greater than the value in cell A4). Notice how the formulas wrap around each other. As is the case generally in mathematics, the innermost formula is run first, working towards the outermost. So the output of the importrange function is passed (piped) as an input to the query function.


You can use as many formulas as you wish, so you could first filter some data using query and then apply a unique function (to remove duplicate values) before finally a sort, as shown here.


Whenever the underlying (source) data changes, the linked data will be updated to reflect this change. This means that you may end up with more or fewer rows. You should therefore be careful about implying a relationship with other data that shares the same row, as the imported rows may change! You can use importrange in conjunction with almost every other formula, and in almost any case where you can enter a range. The possibilities are almost limitless.

Tagged Google, Sheet

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)