How to use the IMPORTRANGE function in Google Sheets

1. IMPORTRANGE Function

Figure 1.1 Data Extraction from a different Workbook

1.1 Data Extraction

Most of the time our data may come from different sources such as different Google Spreadsheets or Workbooks, and the usual problem is how to consolidate all of these data from different workbooks into one. We can do this manually, but it becomes tedious if the data are updated regularly or if there are a lot of workbooks to consider. Luckily, we can automate the extraction of data using the IMPORTRANGE function.  

Fig. 1.1 shows a common scenario of data extraction. Cell A1 in Example 2 Workbook contains the IMPORTRANGE function, which extracts the data from the Example Workbook to Example 2 Workbook. We can notice immediately the difference between the original data and the extracted data, and that is the formatting. Although we successfully extracted the data, the formatting was not copied. It’s one of the weaknesses of the IMPORTRANGE function, which will be explored more in the succeeding sections, but this does not outweigh the things that the function can do. Let’s delve in deeper and explore why this function is powerful. 

1.2 What does the IMPORTRANGE do in Google Sheets?

The IMPORTRANGE function is a straightforward function, and in fact, the name of the function itself is the concise summary of what it can do. It basically imports or extracts data stored in a range of cells from another workbook. It requires two things: the URL of the source Google Sheets, and the references of the cells where the data set is stored. 

It’s important to emphasize the references here because workbooks can contain more than one sheet. Therefore, when we are referencing the data, we need to include the sheet reference together with the cell references to be able to extract the correct data. 

1.3 IMPORTRANGE Syntax

Below is the syntax for IMPORTRANGE:

The IMPORTRANGE function requires two inputs: spreadsheet_url and range_string. The spreadsheet_url is the URL of the Google Sheets while the range_string consists of the sheet and cell references. Both of these inputs must be written in text format, and therefore, if we write them directly to the formula, we need to enclose them with quotation marks. Let’s take a look at a basic example (Fig. 1.2)  to help us build an intuition on how the formula works. 

The two inputs guide the function from Workbook -> Sheet -> Cells, and the IMPORTRANGE will extract the data from the location. The range_stirng consists of two referefences: the sheet reference and the cell reference. The sheet reference is an optional part of the range_input. This means that it has a default value, which is the reference of the first sheet in the source workbook. Optional inputs can be omitted from the formula, and if they are omitted, they will take their default values. Another thing is that the sheet reference and the cell reference are separated by an exclamation mark (!). 

Figure 1.2 Basic example of IMPORTRANGE Process

The new location of the extracted data doesn’t necessarily need to match the reference from the source workbook. We can place the extracted data to any location in the sheet. The IMPORTRANGE function can also be used as an input for other functions. Therefore, we can use the IMPORTRANGE function for any tasks that requires processing of data from a different workbook. It’s also worth emphasizing the keyword workbook (or spreadsheet) because the IMPORTRANGE function can only extract data from a workbook and not other sources such as html. 

2. Breaking Down the Formula

Figure 2.1 IMPORTRANGE Function

In this section, we’ll talk about the rules and criteria for each IMPORTRANGE input, how to write them, how IMPORTRANGE processes these inputs and how do the inputs affect the output. We’ll build an intuition to make it easier for us to visualize and create an IMPORTRANGE formula for your problems.

2.1 IMPORTRANGE Input/Arguments

2.1.1 Spreadsheet_URL

Here are the important rules that you need to consider for the search_key:

2.1.1.1 Syntax and Location of the Spreadsheet_URL

In Google Sheets, the search_key can be written or coded in two different ways depending on the scenario:

1. Reference to a cell: 

Figure 2.2 Storing the URL in a Cell

Typically, our data set is stored in a sheet, and we want to be able to use this set of data in our formulas. In order to do that, we write the references of the cells where the data set is located. In the formula above, the reference B1 means that the value is in column B, row 1 (Fig. 2.2). The reference defines the address of the cell, and the formula uses the reference to access the value within the referenced cell. 

We can store our URL in a cell and reference it inside the IMPORTRANGE function. This makes the formula more compact and easier to read. The main advantage of referencing the cells is that the formula becomes dynamic. We can change the values within the referenced cells, and the formula will update automatically in response to the changes. 

We can write the reference in the formula or highlight the cell. If we perform the latter, the spreadsheet automatically writes the references for us (Fig. 2.3), so we don’t have to write the references manually.

Figure 2.3 Referencing cells by highlighting them

2. A Constant value:

We can also write the speadsheet_url directly into the formula as a constant: (Make sure that it is enclosed by quotation marks.)

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1I942QipA0qwijVWMFqyaiEMx0A-LiW1OL6T91y_-bHg/”,”Context!A1:C6″)

2.1.1.2 Dimension and Size of Spreadsheet_URL

In spreadsheets, the dimension is related to rows and the columns. If our data is written in one column but multiple rows or one row but multiple columns, the dimension of our data is one-dimensional (1D). If our data is in a table with multiple rows and columns, then the dimension is two dimensional (2D). 

The rows and columns serve as coordinates of the cells within the spreadsheet. For 1D data, we only need to specify one coordinate (row or column) to know where the value is located. For 2D, we need to define the row and column to locate the value. 

The size of the data is related to the number of rows and columns, which also translate to the number of entries. 

Now that we’ve defined the dimension and size, let’s define these properties for the Spreadsheet_URL. The IMPORTRANGE function can only evaluate one workbook. Therefore, we can’t input a list of URLs at a time. This means that the spreadsheet_url is a single-value input. 

2.1.1.3 Data Type of Spreadsheet_URL

The spreadsheet_url must be written as text in cells or in formula. If it’s written directly in formula, then we need to enclose it with quotation marks. 

2.1.2 Range_String

As previously discussed, the range_string consists of two parts: the sheet reference and cell reference. The sheet reference is optional, and therefore can be omitted in the formula. If the sheet reference is omitted, then it will take its default value, which is the sheet reference of the first sheet (first tab) of the workbook. To reference a sheet, we just need to write it’s name, and make sure that the upper and lowercase letters are correct because the sheet reference is case-sensitive. 

2.1.2.1 Syntax and Location of the Range

1. Reference to a cell:

Just like the spreadsheet_url, the range_string can also be stored in a cell (Fig. 2.4). 

Figure 2.4 Storing Range_Sting in a cell (B2)

2. Constant Value: 

We can also encode the range_string directly in the formula. Make sure that you write the exclamation point (!) after the sheet reference. 

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1I942QipA0qwijVWMFqyaiEMx0A-LiW1OL6T91y_-bHg/”,”Context!A1:C6″)

2.1.2.2 Dimension and Size of the Range_String

The range_string can be a single cell , 1D list or 2D list. 

2.1.2.3 Data Type of the Range_String

Just like the spreadsheet_url, the range_stirng must also be in text format. 

2.2 The IMPORTRANGE Process

2.2.1 How does the IMPORTRANGE work?

We already had a glimpse of how it works from section 1.3, and in this section we’ll look at more detailed examples of how the IMPORTRANGE processes its inputs.

1. Data

Based on the previous discussions, we know that the IMPORTRANGE function can only extract a value or values from a cell or a range of cells, respectively, from one sheet in another workbook. Therefore, it can only extract one data set at a time. It’s important to note the keyword “data” here because it can only extract the data. As shown in Fig. 1.1, the formatting of the cell is not included. Therefore, we need to reapply the formatting again in the new workbook. Another thing is the data set itself. The IMPORTRANGE can only transfer a data set that contains values. What does this mean? Let’s look at the example in Fig. 2.5.

The data set below at the left contains an image in C4. If we extract this set of data, instead of an image, the #REF! Error is returned to the cell that corresponds to C4 (C6 in the new sheet). Another thing that can’t be extracted are comments and formulas. In short, the IMPORTRANGE can only extract values. 

Figure 2.5 An Image in Cell C4 of the source data (Left). The #REF! Error at the right corresponds to the image. 

2. Permission

Extracting data from another workbook using the IMPORTRANGE function requires permission (Fig. 2.6). Once access of data is granted, then the function will automatically extract the data. You only need to do this once unless the access has been removed. 

Figure 2.6 Accessing the Data from another workbook

3. Editing the Extracted Data

The extracted data can’t be edited. If we want to make any changes, then we need to update the source data. If we edit the extracted data, the IMPORTRANGE function will return the #REF! Error (Fig. 2.7). The text “Edit here” is obstructing the expansion of the data from the formula in A3. Therefore, we also need to make sure that there are enough vacant cells for the extracted data to occupy. 

Figure 2.7 Editing the extracted data will result to the #REF! Error

4. Updating the Data

Any updates in the values in the source data will automatically apply to the IMPORTRANGE function. If the data is increasing or decreasing in size, then we need to take this into consideration in the formula. We can increase the size ( no. of rows and columns) in the reference to accommodate any changes in the size of the data or make the formula more dynamic using other functions (e.g., OFFSET, INDEX, INDIRECT) that can return references.

If we omit the sheet reference, then we need to take into consideration if there’s a change in sheet order in the source workbook. This change doesn’t reflect automatically in the formula. We need to refresh the sheet where the formula is written. Therefore, it’s better to write the sheet reference instead of omitting it, especially if the order of the sheets always changes. 

2.3 IMPORTRANGE Output

2.3.1 Dimension and Size of the Output

The dimension, size and arrangement of the extracted data will be the same to the source data. We just need to make sure that the destination cells of the outputs are vacant cells. If there are values that are occupying the cells where the extracted data should be displayed, then an error will be returned instead of the data (Fig. 2.7). 

2.3.2 Data Type of the Output

The data type of the extracted data will also be the same to the data type of the source data. 

3. Applications of IMPORTRANGE

In this section, we’ll tackle how to formulate your own IMPORTRANGE for different scenarios. Some complicated tasks will require some tricks, but basically it all boils down to the rules of the IMPORTRANGE function. We just need to follow the rules, and we’ll be able to create the IMPORTRANGE formula that we want.

3.1 How to formulate a IMPORTRANGE 

1. First we need to identify the inputs for our IMPORTRANGE:

1. Spreadsheet_URL: It’s better to start determining the location of the URL. Do we want the URL to be stored in a cell or to be hard-coded in the formula?

2. Range_String: Next, we also need to know if we want to store the range_string in a cell or if the data set is dynamic. Are there any images? Comments?

2. Preprocessing of Raw Data

Step 1 helps us identify if our data are suited as inputs for the IMPORTRANGE function.  Most of the time, we don’t have the control on the arrangement and values of our raw data. With this step, we can carefully follow the rules for each of the inputs and see if we need to take some preprocessing steps before we can apply the inputs to the function. 

3. Setting up the IMPORTRANGE Formula:

Step 1 and Step 2 will give us an idea how to arrange the inputs of the functions and the format of the resulting output. It’s always better to visualize the inputs when constructing the formula. This will help us predict the dimension, size and arrangement of the results. Just remember that if the output is an array, we need to make sure that there are vacant cells for them to fill up. Otherwise, an error will be returned instead of the output. 

4. Steps to write the formula:

1.  In a cell where you want to write the formula, write =IMPORTRANGE(

As you write the formula, Google Sheets will display the formulas that match what you’re typing (Fig. 3.1). You can then select from the options that are displayed by pressing the TAB key,  or just complete the formula by writing it manually. 

Figure 3.1 Writing the IMPORTRANGE function

2. Next, after the open parenthesis (, write the spreadsheet_url or write or highlight the cell reference if the URL is stored in a cell. If you highlight the cell, the spreadsheet will automatically write the reference for you (Fig. 3.2). Don’t forget to enclose the URL with the quotation marks if you’ll write it as a constant.

Figure 3.2 Highlighting the cells when you’re constructing the formula will automatically write the reference of the highlighted cells. 

3. Write a comma to separate the first input, and then, write the range_input. You can also write it as a constant text or reference the cell where it’s stored (Fig. 3.3). Make sure to enclose the reference with quotation marks if you’ll write it as a constant. 

Figure 3.3 Highlighting the Range

4. We can write the close parenthesis or just hit ENTER, which will automatically complete the formula for us. 

3.2 How to Combine Data Sets to One Sheet using IMPORTRANGE

The task in the first scenario is to consolidate the data sets from different sheets of another workbook (Fig. 3.4). The 1st data set is from the Context Sheet while the 2nd data set is from Context2 Sheet. Both sheets are in the same workbook. In terms of arrangement, the 1st data set will be on top of the 2nd data set. So how do we do this?

Luckily, we can arrange and combine ranges of cells using the curly brackets {}. We normally use curly brackets to write arrays in formulas. The curly brackets combine and arrange the constants inside it into an array depending on how we set the arrangement (using commas for columns and semicolons for rows). In Google Sheets, the curly brackets can go beyond constants and can use ranges of cells. This makes it more powerful because we can use this to combine ranges of cells into one list. Therefore, we can combine two or more vertical lists or horizontal lists or rearrange tables.

Using the above information, we can use the curly brackets to overlay the 1st data set on the 2nd data set. We’ll use the semicolon to tell the curly brackets that we are adding the 2nd data set as new rows of data to the 1st set. 

To combine the two data sets, we need to extract each of them using the IMPORTRANGE function, and the two arrays resulting from the two IMPORTRANGE formulas will be combined into one data set. The reference for the 1st data set (B2) includes the headers of the table since it will be at the top. In the 2nd reference (B4), we omit the headers. 

It’s important to emphasize here that when we are joining ranges of cells in this manner (adding rows of data), the column sizes of both data sets must match. If both data sets have different no. of columns, then the joining will result to an error. This is also true if we add columns of data. The row sizes of the data sets that will be joined must be the same. Take note that we can also do this with different workbooks. 

Figure 3.4 IMPORTRANGE with Curly Brackets

3.3 How to do IMPORTRANGE with Filter Function

In cases where we only want selected parts of the source data, we can use the FILTER function to extract the specific information that we want. Let’s look at the figure below. 

The task is to extract the entries from the source data (same data from the previous examples) that have Description Values that are “COMPLETE” (Fig. 3.5). We are not interested in the whole data, and we only want the data that will satisfy the condition. We’ll use the FILTER function to filter the data that will be extracted by the IMPORTRANGE. 

The FILTER function has two main inputs: the range and the condition. The range is the data that will be filtered and the condition is made up of one of the columns within the range and the criterion to check against. Basically, it filters a table based on a criterion that will be applied to a column. This is the same to the filter tool within Google Sheets, but we are automating it using a function. The conditions can be expanded in the form of optional inputs. Therefore, we can add more conditions to filter the data. 

In the current scenario, the range will be the array from the first IMPORTRANGE in the formula. The first IMPORTRANGE extracts the whole source data except for the headers. We’re using the Description Column from the source data as part of the condition, and we are checking each cell if they are equal to “COMPLETE.” In order to do this, we also need to extract the Description Column (See B3 for reference) excluding the header. Therefore, we use the 2nd IMPORTRANGE to do this and add the criterion to complete the condition. There are three entries that satisfy the condition, and therefore, these entries are extracted to the new sheet (A5:C7). 

Figure 3.5 IMPORTRANGE with FILTER function

3.4 How to do IMPORTRANGE with Query Function

Another way to extract specific information from the source data is by using the QUERY function. The QUERY function can do lots of data manipulation tasks (e.g., filter and sort). It mainly consists of two inputs: the range, which is the data set, and the query, which are clauses that perform specific actions. This function is a bit complicated, so we won’t delve in too much. Let’s just look at one simple example together with the IMPORTRANGE function. 

In the scenario below, we only want to extract the 1st and 3rd columns from the source data (Fig. 3.6). In order to do this, we’ll use the select clause in the QUERY function. The IMPORTRANGE function will extract the whole source data. The extracted data will be the range input for the QUERY function. Next, we use the select clause to choose which columns to return. The query must be in text format, and therefore, we need to enclose it with quotation marks as seen in the formula.  We start with the select clause (SELECT) followed by a space and the columns that we want to extract. In a normal range, the syntax for the columns are based on the column headers of the spreadsheet (e.g., A, B, C, etc.), but if the range is from the IMPORTRANGE function, the syntax that must be used are Col1, Col2 and so on. 

Figure 3.6 IMPORTRANGE with QUERY function

3.5 How to do IMPORTRANGE with Sort Function

We can also sort the extracted data from the IMPORTRANGE function using the SORT function.  Let’s look back at the scenario from Fig. 3.4. Instead of adding the 1st data set at the top, let’s overlain it with the 2nd data set (Fig.3.7). Therefore, we’ll have a combined data set that is not sorted. 

Look at B2 and B4, the references are swapped (Fig. 3.7). The 1st data set is now at the bottom while the 2nd data set is at the top. First, we combine the two extracted data sets using the curly brackets. Next, we sort the consolidated array using the SORT function. The SORT function has three main inputs: range, sort_column and is_ascending. The range is the data that will be sorted. The sort_column is the column index that will dictate the sorting, and the is_ascending is the order of the sorting, which is TRUE for ascending and FALSE for descending. 

For this case, we sort the extracted data using the 1st column (Code Column). We also set the is_ascending to TRUE. Therefore, the extracted data from the IMPORTRANGE functions will be rearrange using the 1st column in ascending order. 

Figure 3.7 IMPORTRANGE with SORT function

3.6 How to do IMPORTRANGE with Relative References

When writing formulas, we normally want to apply it to a larger group of data and want the formula to be able to adjust the cell references based on its position. If we look at the scenario in Fig. 3.8, the task is to link the Column C of SpreadSheet_URL Sheet, which is the source sheet, to the Column B of the new sheet. We start by writing the formula in B2 by equating it to C4 from the source sheet. Now, we don’t want to repeat the process of writing the formula for each of the cells in Column B of the new sheet. Therefore, we copy B2 and paste it to the other cells or drag the formula from B2 down to the last row. Now, if we look at the formula for let’s say B3, the formula will be =Spreadsheet_URL!C5, and if we move on to the next cell, we will notice that the cell references in the formulas have changed relative to their positions. We call these cell references as relative references. If we want the references to be static, then we add the dollar sign ($) to the front of the column letter or/and the front of the row number. $C4 means that the column is fixed but the row is relative while C$4 means that the row is fixed but the column is relative. We can also do both, $C$4, which means that the cell reference is completely fixed. Cell references that have dollar signs are called absolute references.

Now, that we’ve defined relative references and absolute references, let’s look at how these concepts are applied to the IMPORTRANGE function.

Figure 3.8 Relative References

We usually extract a range of cells using the IMPORTRANGE function, but we can also extract a single cell. If we do the latter and copy or drag the formula to the other cells, you’ll notice that the formula is not relative (Fig. 3.9). This is because the range_string is a constant. So how do we convert the range_string to a relative reference? 

Figure 3.9 IMPORTRANGE with One Cell

We’ll use the ADDRESS function to replace the reference in the range_string. The ADDRESS function returns a cell reference as a string, which is perfect for the range_string. 

Here’s the syntax:

The main inputs of the functions are the row and column indices, which are basically the row and column coordinates within the sheet. If we supply row = 1 and column = 2, this is equivalent to B1. There are three optional inputs but the important one right now is the sheet, which is the name of the sheet. We can omit the other two optional inputs. 

The trickier part is how to supply the row and column for the ADDRESS function. The aim is to be able to make the ADDRESS return a reference that is relative to its position. Therefore, we want to make the row and column dynamic. In order to do this, we’ll use the ROW and COLUMN functions. Both functions return the row and column coordinates, respectively, of a given cell. ROW(A2) will return 2 while COLUMN (A2) will return 1. The cell input is optional, and therefore, we can omit it in the formulas. The default value will be the reference of the current cell where the formula is located. If ROW() is written in A2, it will return 2 and so on. We’ll use the two functions as the row and column inputs of the ADDRESS function. Let’s look at the application of the combination of these functions.

Figure 3.10 IMPORTRANGE with ADDRESS function

The URL is stored in E1, and since we’re dragging the formula, we want its E1 to be absolute. The source sheet’s name is Context and the cells to be extracted are from column C of the source data. Therefore, we need to add 1 in the COLUMN() function so that the ADDRESS function will return column C. If we don’t add 1, the function will return the values from column B instead because it will always return 2 in this scenario (COLUMN(B2) = 2, COLUMN(B3) =2 and so on). We don’t need to add anything to the ROW() because the source data and the destination cells have the same row coordinates. Also, notice the omitted optional inputs. They are not important to our scenario right now. If we drag the formula in B2 down to the other cells, the ROW and COLUMN functions will return the row and column coordinates relative to their positions. This will also make the reference returned by the ADDRESS function relative, which will affect the IMPORTRANGE. So for B3, the ROW() will return 3 and COLUMN()+1 will return 3. Therefore, the ADDRESS function will return C3 from the Context Sheet and so on.  

In the end, you may think that this is useless because you can just extract the whole column using one IMPORTRANGE formula, but there is one special case in which this is very useful. 

Let’s reuse the example in Fig. 3.9 and 3.10, but instead of extracting the Amount Column, we want to apply a Conditional Formatting to the Code Column using the Amount Column from the source sheet. This is a special case where we want to apply Conditional Formatting based on values that are in a different workbook. So how do we do that?

Steps:

1. Highlight the range of cells where you want to apply the Conditional Formatting (Fig. 3.11). 

2. Go to Format -> Conditional Formatting (Fig. 3.11).

3. A menu will appear on the right side. Go to Format Rules -> Format Rules if. Select Custom Formula (Fig. 3.12).

Figure 3.11 Conditional Formatting

Figure 3.12 Conditional Formatting: Custom Formula

4. In the Formula Textbox, we will write the formula that will be checked by the Conditional Formatting. If the formula returns TRUE, then it will apply the formatting that we will set. For this case, let’s format the cells in Code Column to color red if the corresponding amount from the source sheet is greater than 1,000. Therefore, the formula will be: 

=IMPORTRANGE($E$1,ADDRESS(ROW(A2),COLUMN(C2),,,”Context”))>1000

We adjusted the formula from Fig. 3.10 to satisfy the requirements of Conditional Formatting. The ROW and COLUMN functions must contain cell inputs, and therefore, we added the necessary cell inputs in the formula. We don’t need to add 1 to the COLUMN because we’re using C2 (COLUMN(C2) = 3). We also added the logical operator (>) and the criterion, which is 1,000. Next, Set the cell color to red if the condition is satisfied. Now, we’re all set. Click Done. 

Figure 3.13 Conditional Formatting with IMPORTRANGE

Figure 3.14 Conditional Formatting Result

If we look at the source data, all cells  except C2 in the Amount Column are greater than 1,000. Therefore, A3:A6 of the current data will be highlighted (Fig. 3.14). If you look back at the formula, it’s only relative to the first cell, which is A2, but the formula is applied to the rest of the cells. So how does the Conditional Formatting process the custom formula? 

Remember the scenario in Fig. 3.8, where we drag the formula from B2, it’s the same here. In essence, the Conditional Formatting drags the formula to the other cells, and the references will adjust if they are relative.