The IMPORTRANGE Function

• Introduction
• Method
• Furthermore

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

Introduction

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.

Method

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 ‘docs.google.com/spreadsheets’. 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.

IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "SHEET_NAME!A:A")

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.

Ranges

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

• A:C will return all of the first three columns
• A1:C10 will be the first 10 rows of the first three columns
• A2:B will be the all of the first two columns exluding the first row (useful if there are headers you don’t need to import)
• C1:Z1 will return the first row, from the third column, 24 columns wide
• A1:1 will return the entire first row

Furthermore

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.

QUERY(IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "SHEET_NAME!A:D"), "select * where Col1 > 100")

QUERY(IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "SHEET_NAME!A:D"), CONCAT("select * where Col1 > ", A1)

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.

UNIQUE(QUERY(IMPORTRANGE("SPREADSHEET_ID OR SPREADSHEET_URL", "SHEET_NAME!A:D"), CONCAT("select Col2 where Col1 > ", A1))

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.