QUERY + IMPORTRANGE in Google Sheets: Real-Life Formula Examples 2023

QUERY + IMPORTRANGE in Google Sheets: Real-Life Formula Examples 2023

Are you an experienced Google Sheets user? Then you probably know how to link data between multiple spreadsheets and use the IMPORTRANGE function for that. Meanwhile, you could become an expert and do better. Professionals combine IMPORTRANGE with QUERY and manipulate the imported dataset at the same time. If you want to join the experts and improve your Google Sheets skills – welcome to this blog post.

IMPORTRANGE function explained 

The IMPORTRANGE function allows you to import a data range from a specified spreadsheet.

IMPORTRANGE syntax

=IMPORTRANGE("spreadsheet_url","range_string")
  • spreadsheet_url – insert the URL of the spreadsheet to import data from. For example, 
https://docs.google.com/spreadsheets/d/1h3pPtbMAsPM_jS2wMLG_GflqyAd

Alternatively, you can use the spreadsheet ID instead of the entire URL

1h3pPtbMAsPM_jS2wMLG_GflqyAd
  • range_string – insert a string that specifies the data range for the import. For example, Sheet1!A1:C13. The string consists of the sheet_name! and the range.
    • range – specify the range of cells to import.
    • sheet_name! – specify this parameter if you are not importing data from the first sheet of the document.

IMPORTRANGE formula example

We have a database in the spreadsheet called Orders from Airtable. Let’s import all the values in the columns E to I. Here is the formula for this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!E1:I21")

You can check out the formula example in this tab.

Related Posts

QUERY function explained 

The QUERY function lets you manipulate data while importing it from another sheet. You can select, filter, sort, and do other manipulations.  

QUERY syntax

=QUERY(data_range,"query_string")
  • data_range – insert a range of cells to query. data_range may include columns with boolean, numeric, or string values. If a column contains different data types, QUERY will pick the majority data type as the column data type. 
  • query_string – insert a string made using clauses of the Google API Query Language. Alternatively, you can refer to a cell that contains the query_string (in this case, avoid double quotation marks both in the QUERY formula and the query_string). 

Optionally, you can enhance the QUERY formula with a part that defines the number of headers in your data range. In this case, the QUERY syntax will be the following:

=QUERY(data_range,"query_string",headers_number)

One header row is used by default.

QUERY formula example

Let’s use the data range that we imported using IMPORTRANGE in the previous example and do the following manipulations:

  • Select columns A, D and E.
  • Filter out the products with a total price of more than $50.
  • Order the outcome by quantity in ascending order.
  • Change the A column header from “Product” to “Sandwich name”.

Here is the QUERY formula to do that:

=QUERY('IMPORTRANGE example'!A:E,"select A,D,E where E>50 order by D label A 'Sandwich name'")

You can check out the formula example in this tab.

We blogged about this function in “Google Sheets Query”

Using QUERY and IMPORTRANGE together

When you apply QUERY and IMPORTRANGE together, you can query a data range in another spreadsheet (or multiple spreadsheets).

QUERY+IMPORTRANGE syntax

=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string")
  • spreadsheet_url – insert the URL of the spreadsheet to import data from. 
  • data_range – insert a range of cells to query. 
  • query_string – insert a string made using clauses of the Google API Query Language.

Optionally, you can enhance the formula with a part that defines the number of headers in your data range. In this case, the syntax will be the following:

=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string", [headers])

One header row is used by default.

QUERY+IMPORTRANGE formula example

Let’s repeat the examples above but using only one formula. So, we need to:

  • import a data set from the spreadsheet called Orders from Airtable.
  • do a few manipulations with it:
    • Select columns A, D and E.
    • Filter out the products with a total price of more than $50.
    • Order the outcome by quantity in ascending order
    • Change the A column header from “Product” to “Sandwich name”.

Here is the formula:

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!E1:I21"), "select Col1,Col4,Col5 where Col5>50 order by Col4 label Col1 'Sandwich name'")

All orders!E1:I21 is our target data_range. During the import, the column reference will change as follows:

E to A, or simply Column #1 – Col1

F to B, or simply Column #2 – Col2

G to C, or simply Column #3 – Col3

H to D, or simply Column #4 – Col4

I to E, or simply Column #5 – Col5

That’s why we recommend you apply Col reference in your QUERY formulas to avoid errors. 

You can check out the formula example in this tab.

QUERY+IMPORTRANGE alternative

There’s also a simpler option that allows you to avoid cumbersome formulas and save your time. You can easily transfer data from spreadsheets using Google Sheets importer in Coupler.io

One of its important advantages is the automatic data refresh feature. You can set up a schedule, and your information will be regularly updated based on new data from one or several spreadsheets. The syncs can be planned on certain days or just run as often as every 15 minutes. This is very convenient when you work with live reports or build complex dashboards.

In contrast, IMPORTRANGE doesn’t allow you to synchronize data on a schedule. And it won’t even load any updates until you manually open the spreadsheet.

Here’s how to use Coupler.io Google Sheets importer:

  • Sign up for Coupler.io with your Google or Microsoft tài khoản.
    • Hit the Add new importer button and type in a title for your first importer.
  • Then, specify the source – where your data should be taken from. In our example, it’s Google Sheets.
    • After this, connect to your Google tài khoản. 
    • Choose a spreadsheet you need to export your data from. Then specify a particular sheet or several sheets in this file for importing. Keep in mind that merging data from several sheets works best when the sheets have the same structure (for example, invoices).
    • Depending on your needs, Coupler.io can export all data from your source, or you can select a specific range for data transferring. For example, A1:Z9.
  • Turn on the Automatic data refresh and set your schedule if you want to keep your spreadsheet synchronized with the source.
  • Click Save & Run to save your settings and import your data.

Take a look at the data set transferred with the Google Sheets importer from the Orders from Airtable spreadsheet:

Note: apart from the web app, Coupler.io also has an add-on for Google Sheets. If you need to import data from multiple spreadsheets often, this option may be more convenient for you. Install the Coupler.io add-on from the Google Workspace Marketplace.

You can also use Coupler.io importers to extract data from other sources. For example, you can fetch data from Airtable, Jira, Shopify or other systems, and import them to Google Sheets, Excel, or BigQuery.

What you can do with IMPORTRANGE+QUERY functions (real-life formula examples)

Now, let’s discover what a combination of IMPORTRANGE and QUERY can provide. Orders from Airtable will be our data source spreadsheet. All the rest is the magic of QUERY language clauses.

Import a specific range of data with the select QUERY clause

The select QUERY clause allows you to choose exactly which columns you want to pull. Check out more about Google Sheets Query: Select.

Task: 

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable.

QUERY+IMPORTRANGE formula example 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9")

You can check out the formula example in this tab.

Import data from multiple spreadsheets and combine it into one

You can transfer data from several Google spreadsheets with the same columns structure and merge them together using QUERY + IMPORTRANGE. For this, you need to wrap several IMPORTRANGE functions in curly braces {} and separate them by either commas (to merge data horizontally) or semicolons (to merge data vertically).

QUERY + IMPORTRANGE formula syntax to merge spreadsheets horizontally:

=QUERY({IMPORTRANGE("spreadsheet1_url", "data_range"),IMPORTRANGE("spreadsheet2_url", "data_range"),IMPORTRANGE("spreadsheet3_url", "data_range"),..}, "query_string")

QUERY + IMPORTRANGE formula syntax to merge spreadsheets vertically:

=QUERY({IMPORTRANGE("spreadsheet1_url"; "data_range"),IMPORTRANGE("spreadsheet2_url"; "data_range"),IMPORTRANGE("spreadsheet3_url"; "data_range");..}, "query_string")

data_range must contain the same column range for each spreadsheet_url, whereas the number of rows may differ.

The reason is quite simple: when you IMPORTRANGE data from a particular spreadsheet for the first time, the formula requests to allow access: you need to click on a small button that pops up from the cell.

But there is no such button when you do it for several sources. Check out our blog post Why IMPORTRANGE Is Not Working to handle other errors.

Task:

  • Import columns B, E, H and I from the spreadsheet#1, Orders from Airtable
  • Import columns B, E, H and I from the spreadsheet#2, Orders from Airtable#2
  • Import columns B, E, H and I from the spreadsheet#3, Orders from Airtable#3.
  • Skip the first row when exporting data from spreadsheets #Hai and #3.

QUERY+IMPORTRANGE formula example

=QUERY({IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A1:I21"); IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A2:I21"); IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A2:I21")}, "select Col2,Col5,Col8,Col9")

Once we allowed access to the final spreadsheet, our formula for multiple sources immediately worked.

You can check out the formula example in this tab.

Import data from multiple spreadsheets with Coupler.io

Coupler.io can automatically combine your data from several spreadsheets in one specified document. In addition, it will keep updating your information, fetching it from multiple sources according to your schedule. This tool is also simpler to use than IMPORTRANGE as you don’t need to deal with formulas.

To start transferring data, you can use the Coupler.io web app or the add-on for Google Sheets. The process will largely be the same as discussed above in the QUERY+IMPORTRANGE alternative section. The only difference is that you’ll need to create several separate importers. Each of them will be transferring information from a different spreadsheet to the same document.

Here’s how to do this:

  • Sign in to Coupler.io and create Importer Một with the following parameters:
    • Source:
      • Select Google Sheets as a source app
      • Specify the spreadsheet and sheet to fetch the data from
    • Destination
      • Select Google Sheets as a destination app
      • Specify the spreadsheet and sheet to load data to
    • Choose the replace import mode. This will replace all the data in your document with the newly exported rows. If you already have some data in your destination sheet and want to keep it, then it’s better to use the append mode. This will add the new records below the existing data.
    • Set up the schedule for automatic updates
    • Press Save and Run
  • Then, create Importer 2 (you can copy the Importer Một and edit the parameters so that you don’t need to create a new importer from scratch)
    • Choose another spreadsheet as your data source
    • Add the same destination file as for the previous importer
    • Choose the append import mode, so that your data from the second source doesn’t erase your information from the first one
    • Set up your synchronization schedule, which should have the time preferences later than the schedule for the Importer 1
    • Press Save and Run
  • Create new importers following the steps for Importer 2. You can add as many as you want, one importer for each data source.
  • When all your importers are ready, run them one by one. Meet your data:

You’ll need to go through all these steps just once. And as a result, you’ll get an always up-to-date document that’s being synchronized with multiple spreadsheets.

Cut off unnecessary rows of the imported data with the limit and offset QUERY clauses

The limit QUERY clause allows you to define the total number of rows (excluding the header) to be imported. The offset QUERY clause allows you to define how many rows to cut off from the top of the imported data range. Check out more about Google Sheets Query: Limit and Offset.

Task #1:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable.
  • Reduce the number of imported rows to 10 (excluding the header).

QUERY+IMPORTRANGE formula example: 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9 Limit 10")

You can check out the formula example in this tab.

Task #2:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable.
  • Reduce the number of imported rows to 10 (excluding the header).
  • Skip five rows from the top of the spreadsheet we export data from (excluding the header). 

QUERY+IMPORTRANGE formula example: 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9 Limit 10 offset 5")

You can check out the formula example in this tab.

Change names of the imported columns with the label QUERY clause

The label QUERY clause allows you to change columns’ names (headers). Check out more about Google Sheets Query: Label.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Reduce the number of imported rows to 10 (excluding the header).
  • Rename the headers of the imported columns:
    • Date of order => Time
    • Product => Sandwich
    • Quantity => How many
    • Total price => How much

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9 Limit 10 label Col2 'Time', Col5 'Sandwich', Col8 'How many', Col9 'How much'")

You can check out the formula example in this tab.

Format values in the imported columns with the format QUERY clause 

The format QUERY clause allows you to apply specific formats to imported columns. Check out more about Google Sheets Query: Format.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Reduce the number of imported rows to 10 (excluding the header).
  • Format values in the B column to display month and year only.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9 Limit 10 format Col2 'mmm-yyyy'")

You can check out the formula example in this tab.

Import the filtered data with the where QUERY clause

The where QUERY clause allows you to filter rows in the imported columns based on set conditions. Check out more about Google Sheets Query: Where.

Task:

  • Import columns B, E, H and I from the spreadsheet Orders from Airtable
  • Filter the imported data by the following conditions:
    • the value in column I should be greater than or equal to 50.
    • the value in column E should start with the letter “S”.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9 where Col5 starts with 'S' and Col9>=50")

You can check out the formula example in this tab.

Sort the imported data with the order by QUERY clause 

The order by QUERY clause allows you to sort data based on the chosen column in ascending/descending order. Check out more about Google Sheets Query: Order By.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Filter the imported data by the column I where the values should be greater than or equal to 50.
  • Sort the imported data by the date of order (column B) in descending order

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,Col9 where Col9>=50 order by Col2 DESC")

You can check out the formula example in this tab.

Arithmetic operations with the imported data

You can add (+), subtract (-), multiply (*), and divide (/) columns from a spreadsheet and import the outcome as a separate column. Check out more about Google Sheets Query: Arithmetic Operators.

Task:

  • Import columns E and I from the spreadsheet Orders from Airtable
  • Multiply the Price (column G) by the Quantity (column H) and import the outcome as a separate column named “Calculated total price”.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col5,Col7*Col8,Col9 label Col7*Col8 'Calculated total price'")

You can check out the formula example in this tab.

Aggregation operations with the imported data

Within the select, order by, label, and format QUERY clauses, you can apply the following aggregation functions to the imported columns:

  • avg() – provides the average of all numbers in a column.
  • sum() – provides the sum of all numbers in a column.
  • count() – provides the quantity of items in a column (rows with empty cells are not calculated).
  • max() – provides the maximum value in a column.
  • min() – provides the minimum value in a column.

Aggregation functions are not applicable to where, group by, pivot, limit and offset QUERY clauses.

Check out more about Google Sheets Query: Aggregation Functions.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Provide the minimum value in column B.
  • Count the items in column E.
  • Provide the sum of all numbers in column H.
  • Provide the average of all values in column I.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select min(Col2),count(Col5),sum(Col8),avg(Col9)")

You can check out the formula example in this tab.

Scalar operations with the imported data

You can use scalar functions to convert imported parameters into a single value. For example, if you apply the year() scalar function, it will fetch the year value out of the YYYY-MM-DD format. Check out more about Google Sheets Query: Scalar Functions.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Fetch the day of the week from date values in column B.
  • Replace all the letters with uppercase ones in column E.
  • Calculate the difference between the date of order and current time, and import the values in a separate column named “Time difference in days”.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select dayofweek(Col2),upper(Col5),Col8,Col9,dateDiff(Col2,now()) label dateDiff(Col2,now())'Time difference in days'")

You can check out the formula example in this tab.

Aggregate the imported data across rows with the group by QUERY clause

The group by QUERY clause allows you to group values across the selected data range based on a specific condition. The clause can be applied if an aggregation function has been used within the select clause. Check out more about Google Sheets Query: Group By.

Task:

  • Import columns B, E, H and I from the spreadsheet, Orders from Airtable
  • Summarize the Total price data (column I).
  • Group the aggregated values by the Product name (column E).
  • Sort the imported data by the grouped values (column E) in ascending order.

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select Col2,Col5,Col8,sum(Col9) Group by Col5,Col2,Col8 order by Col5")

You can check out the formula example in this tab.

Aggregate the imported data and rotate rows into columns with the pivot QUERY clause

The pivot QUERY clause allows you to convert rows into columns, and vice versa, as well as aggregate, transform and group data by any field. The clause can be applied if an aggregation function has been used within the select QUERY clause. Check out more about Google Sheets Query: Pivot.

Task:

  • Import columns E and I from the spreadsheet Orders from Airtable
  • Summarize the Total price data (column I).
  • Pivot the aggregated values by the Product name (column E).

QUERY+IMPORTRANGE formula example

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g/edit?usp=sharing","All orders!A:I"), "select sum(Col9) pivot(Col5)")

You can check out the formula example in this tab.

So QUERY and IMPORTRANGE or Coupler.io?

Well, this was a pretty long journey, but you handled it! Now you know everything you can do with QUERY and IMPORTRANGE in Google Sheets. Also, if you don’t want to sink into formulas, or you need to automate data import, you can go with Coupler.io. Whichever solution you prefer, good luck with your data activity, and here’s hoping you never get #ERROR. 🙂

Back to Blog


--- Cập nhật: 18-01-2023 --- nongdanmo.com tìm được thêm bài viết How to Use IMPORTRANGE in Google Sheets to Import Data Across Spreadsheets từ website blog.coupler.io cho từ khoá google sheets importrange filter.

To import a data range from another spreadsheet (Google Sheets document), there is a Google Sheets function – IMPORTRANGE. Let’s check out how it works and discover the IMPORTRANGE alternative, which lets you automate data import across spreadsheets.

Is there a worth-watching video tutorial on IMPORTRANGE Google Sheets?

Some users may prefer watching to reading. In this case, check out this step-by-step IMPORTRANGE function tutorial for Google Sheets by Railsware Product Academy.

Understanding IMPORTRANGE Google Sheets

IMPORTRANGE allows you to import a data range from one spreadsheet to another. It’s a pure Google Sheets function – i.e. there is no Excel IMPORTRANGE.

IMPORTRANGE Google Sheets syntax

=IMPORTRANGE("spreadsheet","range_string")
  • spreadsheet – insert the URL or ID of the spreadsheet to import data from. 
  • range_string – insert a string that specifies the data range to import. The string should include the name of the sheet and the range of cells. 

Google Sheets IMPORTRANGE formula example

We have a spreadsheet with the data imported from Trello. Let’s pull columns A to E from it. Use the formula with the spreadsheet URL:

=importrange("https://docs.google.com/spreadsheets/d/1jUPrXMmsZwJNnWcxm-pRVz1xBY-TEoVGsq5c2jHunvI/edit#gid=590318270","Trello Board!A:E")

or with the spreadsheet ID:

=importrange("1jUPrXMmsZwJNnWcxm-pRVz1xBY-TEoVGsq5c2jHunvI","Trello Board!A:E")

IMPORTRANGE Allow Access to the spreadsheet in Google Sheets

When you import a range from an unshared spreadsheet, IMPORTRANGE will require you to connect the source and the target spreadsheets. 

Click Allow access, and IMPORTRANGE will then work. You’ll have to do this only once, at the first data import.

Meanwhile, you can’t revoke the connection if both the source and the target spreadsheets belong to the same user. 

Google Sheets IMPORTRANGE drawbacks

  1. Poor import performance if multiple IMPORTRANGE formulas are used. The more IMPORTRANGE formulas your spreadsheet has, the more time it will require to process requests. At some point, it can even stop working completely. 
  2. Data loads only when the spreadsheet is open. The imported data may not be available until you open the spreadsheet with the IMPORTRANGE formula. This is troublesome if the data in the spreadsheet is synchronized with a third-party app or tool.
  3. Limited functionality. With IMPORTRANGE, you can’t import the entire sheet; you have to specify a cell range; you can’t schedule data import; you can’t import a consolidation of sheets from a spreadsheet, etc.

If you’ve faced any of the issues above, you might need an alternative to IMPORTRANGE. 

IMPORTRANGE Google Sheets alternative to automate data import on a schedule

Coupler.io is a solution to automate imports of data from multiple apps including Airtable, Pipedrive, CSV, and others to Google Sheets, Excel, or BigQuery. To automate data import across spreadsheets, Coupler.io provides a Google Sheets integration.

Google Sheets integration

The Google Sheets integration by Coupler.io lacks the IMPORTRANGE drawbacks mentioned above and allows you to automate data import across Google Sheets. Read or watch our YouTube video about how to get it on board:

Article post on: nongdanmo.com

Sign up to Coupler.io and start your data integration journey by creating your first importer. Click the Add new importer button and select Google Sheets as both the source and destination apps. Next, you need to complete the following steps:

Source 

  • Connect your Google tài khoản or choose one from the drop-down list if you’ve already connected some.
  • Select a Google Sheets file on your Google Drive to transfer data from. 
  • Select one or several sheets to export data.
  • Click Jump to Destination Settings if you’re okay that the entire data set will be imported from your sheet (s). However, you can choose a range in the spreadsheet you want to export data from, i.e. A1:Z9. To do this, click Continue and specify the range in the optional field.

Destination

  • Connect to your Google tài khoản. 
  • Choose a Google Sheets file on your Google Drive to transfer data to. 
  • Type a name to create a new sheet or pick an existing one. 
  • If you want to change the first cell where to import your data range, specify your value in the Cell address field. The A1 cell is set by default. 
  • Choose the import mode for your data: you can replace your previous information or append new rows under the last imported entries

Schedule  

You can import your data range immediately by clicking Save and Run. At the same time, you can also automate imports every hour, every day, or on another frequency. Toggle on the Automatic data refresh and set up a schedule:

  • Select Interval (from 15 minutes to every month)
  • Select Days of the week
  • Select Time preferences
  • Schedule Time zone

For more about the Google Sheets integration setup flow, refer to the Coupler.io knowledge base.

IMPORTRANGE Google Sheets how-to guide

This will cover how to use IMPORTRANGE in practice, and various tasks that you can carry out with this Google Sheets function. 

How to import data from multiple sheets with IMPORTRANGE in Google Sheets

We need to import the same data range (A:E) from three sheets (Airtable orders 2017, Airtable orders 2018, and Airtable orders 2019) of one spreadsheet.

You can import data with IMPORTRANGE with the following options:

Import and merge data vertically

If you’re importing a limited data range like A1:E21, you can use an array (curly brackets) and IMPORTRANGE formulas separated with semicolons. For example:

={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:E21"); IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:E21"); IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:E21")}

Click Allow access to connect the sheets.

The data will be imported and merged vertically. However, to import and merge an unlimited data range (A1:E), you’ll need to nest IMPORTRANGE with FILTER and LEN as follows:  

={FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#1-name}!{sheet#1-first-column})>0); FILTER(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-range}"),LEN(IMPORTRANGE("{spreadsheet-ID}", "{sheet#2-name}!{sheet#2-first-column})>0);...} 
  • {spreadsheet-ID} – the ID or URL of the Google Sheets document, you’re importing data from
  • {sheet#1-name} – the name of the first sheet
  • {sheet#1-name} – the name of the second sheet 
  • {sheet#2-range} – the data range from the first sheet including the title row.
  • {sheet#2-range} – the data range from the second sheet without the title row. 
  • {sheet#1-first-column} – the first column of the data range from the first sheet.
  • {sheet#2-first-column} – the first column (without the title row) of the data range from the second sheet.

In our case, the formula will look like this:

={FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:A")))}

In our formula, we used the same data range for all sheets including the first rows from each sheet. 

If you don’t need this, replace A1:E with A2:E for the second and the third sheets to merge:

={FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A2:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A1:A"))); FILTER(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A2:E"),LEN(IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A1:A")))}

Import and merge data horizontally

You can import and append data horizontally with IMPORTRANGE only if the data range to fetch in each sheet is the same. In order to do this correctly, use an array (curly brackets) and several IMPORTRANGE formulas separated with commas: 

={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A:E"), IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A:E"), IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A:E")}

If you try to import different data ranges, the IMPORTRANGE formula will return an Error: Function ARRAY_ROW parameter 3 has mismatched row size. Expected: 20. Actual: 21.

={IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2017!A2:E"), IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2018!A2:E"), IMPORTRANGE("1yst6BVpIUtPqeJxhAGRbEQWBTi8TpJOkMAiYj0kI3TI","Airtable orders 2019!A:E")} 

For more on this, read our blog post Why IMPORTRANGE Is Not Working: Errors and Fixes.

Import data from multiple sheets with the IMPORTRANGE alternative in Google Sheets

With the Google Sheets integration by Coupler.io, you can import data from multiple sheets more easily. You need to specify the following parameters:

  • the names of the sheets to merge 
  • the data range 

Once you run the integration, it will import and merge data vertically. The Google Sheets integration adds a Sheet Name column, so you could differentiate where the data came from. It also automatically skips the column headers from the appended sheets if they are identical to the column headers of the first sheet.

If the specified sheets have different column headers, the importer will merge vertically only the identical ones, and the rest will be appended horizontally to the right. Here is how it will look:

How to import data from multiple spreadsheets with IMPORTRANGE Google Sheets

We need to import the same data range (A:E) from three separate spreadsheets.

Import and merge data vertically

Use an array (curly brackets) and IMPORTRANGE formulas nested with FILTER and LEN:

={FILTER(IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A1:E"),LEN(IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A1:A")); FILTER(IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A2:E"),LEN(IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A2:A")); FILTER(IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A2:E"),LEN(IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A2:A"))}

Import and merge data horizontally

You can import and append data horizontally with IMPORTRANGE only if the data range to fetch in each sheet is the same. To do this in the correct way, use an array (curly brackets) and several IMPORTRANGE formulas separated with commas: 

={IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A:E"), IMPORTRANGE("1z2yS9uY5Zpp01qZ_opbaO7SnnehS9rknAZlJozXA7G4","All orders!A:E"), IMPORTRANGE("1PuEsRXSShgbBKiz-6uxP6OGeS9kBHW0d-rpUAaMl2_g","All orders!A:E")}

How to IMPORTRANGE with conditions in Google Sheets?

If you combine IMPORTRANGE with the QUERY function, you can import data across spreadsheets according to different conditions such as:

  • Import a specific range of data
  • Cut off unnecessary rows of the imported data
  • Change names of the imported columns
  • Format values in the imported columns
  • Import the filtered data
  • Sort the imported data
  • Perform arithmetic, aggregation, and scalar operations on the imported data

For more on Google Sheets QUERY Function, read our dedicated blog post.
Let’s check out how it works on a simple example: We’ll import a data range A:E from a spreadsheet, and filter the imported data on the values in column E that start with the letter “S“.

Here is how the formula will look:

=QUERY(IMPORTRANGE("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!A:E"), "select * where Col5 starts with 'S'")

How to hide formulas with IMPORTRANGE Google Sheets?

Let’s say you have a spreadsheet that some stakeholders can view. This spreadsheet contains specific formulas you’re not willing to disclose. How can you let the stakeholders see the values, and hide the formulas at the same time? 

If this is what you need, IMPORTRANGE can offer you a workaround to hide formulas. The idea is to create two Google Sheets documents:

  • In the first one, you will store formulas.
  • In the second one, you will import the values from the first spreadsheet using IMPORTRANGE. 

For example, the original spreadsheet with our formulas is called “Formulas to hide”. It contains three formulas: COUNTIF, FILTER, and SUMIF.

Clone this Google Sheets doc (either make a copy or simply copy and paste data from it) and replace the formulas with the IMPORTRANGE formulas that will reference the original document.

For COUNTIF:

=importrange("17wJv3x8lZkpDXWFwNkiLINWh2zJVsRXoHjQMHg9cEp8","'Sheet1'!A2:A")

For FILTER:

=importrange("17wJv3x8lZkpDXWFwNkiLINWh2zJVsRXoHjQMHg9cEp8","'Sheet1'!C2:C")

For SUMIF:

=importrange("17wJv3x8lZkpDXWFwNkiLINWh2zJVsRXoHjQMHg9cEp8","'Sheet1'!E2:E")

Then connect these sheets, of course. Your spreadsheet will look the same as the original doc. However, instead of the original formulas, you and those you share the spreadsheet with will only see the IMPORTRANGE formulas:

Functions you can use with IMPORTRANGE in Google Sheets

IMPORTRANGE with QUERY in Google Sheets

QUERY+IMPORTRANGE allows you to import data based on certain conditions. 

QUERY IMPORTRANGE Syntax:

=QUERY(IMPORTRANGE("spreadsheet", "data_range"), "query_string")
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 
  • query_string – a string made using clauses of the Google API Query Language.

This topic has been mentioned above and described in full in our dedicated blog post: QUERY + IMPORTRANGE in Google Sheets. 

IMPORTRANGE with VLOOKUP in Google Sheets

VLOOKUP IMPORTRANGE Syntax:

=VLOOKUP(search_key,IMPORTRANGE("spreadsheet", "data_range"),index,[sorted_boolean])
  • search_key – the value (or a cell range) to search for. 
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 
  • index – the index (number) of the imported column in the range to return the value.
  • [sorted_boolean] – a TRUE/FALSE boolean indicating whether the specified column is sorted. TRUE (sorted) is set by default, but in most cases FALSE is recommended. 

For example, we have a spreadsheet with search criteria of specific customer names. The other spreadsheet contains a list of orders per customer. The combination of VLOOKUP and IMPORTRANGE will let us match data across these spreadsheets. Here is the formula:

=vlookup( A2:A, importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"), 3, false)

If you modify the formula above by adding the ARRAYFORMULA function and IF + LEN, you’ll get the following:

=arrayformula( if(len(A2:A)=0,, vlookup( A2:A, importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"), 3, false ) ) 

Read our Guide of Using ARRAYFORMULA in Google Sheets For All.

IMPORTRANGE with FILTER in Google Sheets

FILTER + IMPORTRANGE allows you to filter the imported data by a certain value. 

FILTER IMPORTRANGE Syntax:

=FILTER(IMPORTRANGE("spreadsheet", "data_range"),[condition_1, condition_2,...])
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 
  • condition – a range that contains the filter criteria.

For example, we need to import a data range from a spreadsheet and filter it by a specific product name: “Denver sandwich“. Here is how it may look:

=filter( importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I"), importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!E2:E")="Denver sandwich" )
  • importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!C2:I") – data range to import
  • importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!E2:E") – a column from the imported data range where the filter criteria is
  • "Denver sandwich" – filter criteria  

Read the FILTER Function Tutorial to learn more about filtering in Google Sheets.

IMPORTRANGE with SUM in Google Sheets

SUM + IMPORTRANGE allows you to sum the imported data range. 

SUM IMPORTRANGE Syntax:

=SUM(IMPORTRANGE("spreadsheet", "data_range"))
  • spreadsheet – the URL or ID of the spreadsheet to import data from. 
  • data_range – a range of cells to query. 

For example, we need to import a column from a spreadsheet and return the sum of its values. Here is the formula:

Source: nongdanmo.com

=sum(importrange("1iUwZDui3Q6mBpswPuU7e9NdvKwNGRA9A3JAhqFDMFbk","All orders!I2:I"))

IMPORTRANGE with SUMIF in Google Sheets

You can’t use the IMPORTRANGE function within a SUMIF formula. If you need to sum a range from a spreadsheet according to a set criteria, you can do one of the following:

  • Use QUERY+IMPORTRANGE
  • Import the data with IMPORTRANGE, then apply SUMIF to the already imported data. 

These are the most popular inquiries of Google Sheets functions to combine with IMPORTRANGE. If you know other in-demand cases, mention them in the comments section below, so we may include them in the blog post. 

IMPORTRANGE or Google Sheets integration by Coupler.io?

You should select the option to import data from Google Sheets documents based on your requirements:

  • IMPORTRANGE works well if you need to import smaller amounts of data. However, this Google Sheets function is limited in functionality, requires a lot of manual work, and can slow the productivity of your spreadsheet if many IMPORTRANGE formulas are applied.
  • Coupler.io is a stable solution that can handle large amounts of data and multiple calculations in a single document. Moreover, this Google Sheets add-on lets you automate data import across spreadsheets on schedule.

Check out the following comparison table to make the final decision:

Google Sheets integration IMPORTRANGE
Web app and a Google Sheets add-on Entity Google Sheets function
Google Spreadsheets limits or Google Sheets API limits Limitation Max 50 connections per spreadsheet
– Specified data range on a single sheet
– Entire sheet
– Consolidation of sheets of a spreadsheet
Data to import – Specified data range on a single sheet
– Consolidation of sheets of a spreadsheet
Automatic and manual options are available Data refresh Automatic within a few seconds
Available Data update on schedule Not supported
No limitations Data availability When you open the spreadsheet
Available Append imported data Not supported

Choose the best solution for your project and good luck with your data!

Back to Blog


--- Cập nhật: 23-01-2023 --- nongdanmo.com tìm được thêm bài viết Filtering IMPORTRANGE data in Google Sheets từ website yagisanatode.com cho từ khoá google sheets importrange filter.

Working with IMPORTRANGE data in Google Sheets can be a little tricky. It may feel at times that it does not play by the same rules as when you are building formulas with data in the same Google Sheet.

In this tutorial, we’ll go through two approaches to filtering and sorting your IMPORTRANGE data by using the FILTER and QUERY functions. We’ll run through some examples of each and look at some of their pros and cons.

Then, we’ll wrap things up with a walkthrough and example on how to build your very own dynamic data dropdown dashboard from IMPORTRANGE data that lets us look at a set of sales by any company from our imported data any sales rep that makes a sale to them.

I encourage you to play along with the examples. You can find a copy of the Google Sheet that we will be importing here:

Click on the ‘Make a copy’ button to create your very own copy of the sales sheet. There are heaps of bonus formulas in there too along with a few fun Easter Eggs for the curios. 

The sample import Sales.sheet

This tutorial is designed for those who want to enjoy a full walkthrough of the topic or those who just want to jump to the formulas with the Table of Contents below.

Filtering IMPORTRANGE data with FILTER

We can use the Google Sheets FILTER function on IMPORTRANGE to filter data. The FILTER function takes a range to display as the first argument and then any number of conditions in the following arguments. To use FILTER with IMPORTRANGE it would look a little like this.:

Example  1: Get All Sales by a Specific Company

In this example, we want to display a list of all the sales by a specific company. Let’s say we want to see all the sales for the company ‘Kulas Moen’. Our formula would look like this:

In our first FILTER argument, we add the IMPORTRANGE formula. Here, we select the Sales sheet from range from A2:0 selecting all the columns in the range. This will be the range we want to be displayed. Line 2

For the next FILTER argument, we add IMPORTRANGE again, but this time we only want to look at the range in column B where our list of companies is contained. Here we select from range B2:B excluding the headers.

We then compare this argument to see if it is equal to the condition, ‘Kulas-Moen’. Line 3

Example  2: Get All Sales by a Sales Rep

Just like we did in the example above, we could display a list of all of the rows by a specific sales rep to check on their performance.

Let’s take a look at how ‘Gilbert Chikli’ is doing in our Sales Rep column over in Column N.

Here’s the formula:

Again, on the first line, we grab the entire range data from the  Sales tab. Line 1

You can see that our criteria column had moved to column N to search for the sales rep and find any occurrence of “Gilbert Chikli”.

Example 3: Sorting the Filtered IMPORTRANGE data

We can sort our filtered IMPORTRANGE data by applying the SORT function around the FILTER function. SORT takes a range as its first argument followed by argument pairs in the form of the column to sort and whether the sort is ascending or descending. Here’s what the formula might look like.

Let’s go ahead and apply this to our first example.

We might want to see the total amount sold for each sale for “Kulas-Moen” and order those sales from highest to lowest. If we look at our Sales Google Sheet we can see that this is available in Column L.

The SORT function requires us to indicate the column as a number counting from the start of our range (Col A) to the end (Col L).

HINT! You don’t have to count this by hand. One easy way to get the number of columns from your start col to your end is to use the COLUMN function. If your range starts from Col A, then simply enter your COLUMN function in the desired column to extract the column number. In our case, this is Col L.

Google Sheets Use COLUMN to find the column you need to sort by

If your range starts from a different column, then you could subtract your end column from your start column range and then subtract by 1. E.g. =COLUMN(S1) - COLUMN(C1) - 1 = 15.

Here’s the formula:

Note that we have selected FALSE to sort descending from highest to lowest values.

Example 4: Filter All Sales by a selected sales rep that are greater or equal to $1,000 and sort from the highest.

To make compound FILTER conditions with IMPORTRANGE, we need to call the IMPORTRANGE function on each condition.

In this example, we want to see all the sales for the rep, ‘Simon Lovell’ that had a ‘Total’ value greater than or equal to $1,000.

Pros and Cons of using FILTER in IMPORTRANGE

Pros:

  • FILTER is easy to use and understand.
  • FILTER brings over images, links and formulas.

Cons:

Via @: nongdanmo.com

  • The formula can get really large and unwieldy really fast making it hard to read.
  • It is really difficult to only select certain columns to display. Basically, you would have to run an IMPORTRANGE on every range you want to be displayed and then add that into an array with curly braces before continuing with your FILTER formula.
  • You have to use other formulas like SORT to sort the data.

I would probably only use this approach if I need to display the images and links in a range.

Watch the VIDEO

Filtering IMPORTRANGE data with QUERY

QUERY has been called the secret superpower of Google Sheets by many a spreadsheet guru for a long time now. QUERY uses a syntax similar to what you may have seen before in relational databases with SQL called Google Visualization API Query Language.

We can use the QUERY function on IMPORTRANGE in a similar way that we would use QUERY on ranges in a single Google Sheet. However, we must change our column references from using letters (e.g. A, B, C), to Column numbers that reference a range (e.g. Col1, Col2, Col3).

For example, if we select the range C3:F100, then we would reference the first column in our range (C) as Col1 and the last column (F) as Col4.

This will become clearer for you as we work through an example in a moment.

The QUERY function takes 3 arguments:

  1. The select range to query.
  2. The query parameters.
  3. The number of headers in the query.

Incorporating IMPORTRANGE into our QUERY would look like this:

To me, it looks a lot tidier than the FILTER function. We are only using the IMPORTRANGE function once in our formula and our query argument handles what we want to be displayed on our destination spreadsheet.

Example 5: Total sales by a select client.

Let’s duplicate example Một in our FILTER section of this tutorial by using QUERY.

Here, we will get the total sales for the company ‘Labadie Ltd’. We will go ahead and save ourselves some work and include the header in our query too. Take a look:

You can see that our first argument is our IMPORTRANGE where we have selected the Sales tab over the range A1:O to query. Line 2

Next, we make our query. Here we are saying that we want to select all columns where the second column has the cell ‘Labadie Ltd’Line 3

We use the asterisk (*) wildcard to select all columns here. Interestingly, we can also omit the ‘SELECT *’ portion and it will select all columns for you regardless. It’s a personal preference to keep it in more than anything to help me understand the query better at a glance.

On the last argument of our QUERY, we select 1, because we only have one header row to display. If you don’t want to display the header, make it a zero.

Note! You may have noticed that, unlike FILTER, QUERY doesn’t carry over formulas, images and links. 

Example 6: Total sales by a select client with selected columns.

One of the cool things about using QUERY is that we can specify which columns we want to be displayed in our query.

Let’s say that we want to just display the company ID and name, the description of the item sold along with the price, quantity and total sales and finally the rep who sold the item and their id.

If you take a look at the sales data this would be Cols A, B, H, J to N and would translate to Col1, Col2, Col8, Col10, Col11, Col12, Col13 and Col14.

Using QUERY on IMPORTRANGE in Google Sheets to review sales to a company with selected columns to display

Here is what our formula would look like:

Which will would result in this:

Using QUERY on IMPORTRANGE in Google Sheets to review sales to a company with selected columns to display – result

Example 7: Total sales by a select client with selected columns ordered by Total sales.

Let’s expand on example 6 further and sort our results by the total of each sale. Unlike FILTER where we need to use the SORT function to order our data, we can use the syntax in QUERY to order our data using the ORDER BY clause.

This can be done by simply appending ‘ORDER BY’ to our query followed by the column to order (For our example this will be Col12 the ‘Total’ column) and then whether or not we want our order to be ascending (asc) or descending (desc).

Take a look at our updated formula (I’ll put each part of the query on a separate line for ease of reading):

Example 8: Total sales by selected client & sales rep over selected columns and ordered by total sales.

In our final example for QUERY, let’s see what sales were made by a sales rep for a selected company. We will keep our nice neat columns and sort the sales total from highest to lowest to see how well the rep did.

Here we want our ‘WHERE’ clause to look at two conditions. We want to show all the rows that contain the company ‘Kulas-Monen’ in column B (Col2) so long as the sales rep, ‘Kevin Foster’ is in the same row in column N (Col14).

We can join these two conditions together simply by using the ‘AND’ clause in our QUERY syntax.

Take a look at our updated formula:

Notice the inclusion of our ‘AND’ clause and our search for Kevin in Col 14. Line 4

This will now display our data like this in our Google Sheet:

Using QUERY on IMPORTRANGE in Google Sheets_Sales for Kulas Moen by rep Kevin Foster ordered by Total for select cols

Pros and Cons of using QUERY in IMPORTRANGE

Pros:

  • Only a single use of the IMPORTRANGE function.
  • Handles all the conditions and queries elegantly using the query language.
  • Can provide a convenient header.
  • Can help you select only the columns you want to be displayed.
  • A lot easier to read what is going on in the formula than in the FILTER example.

Cons:

  • Does not display formulas, images or links.
  • Has a bit of a learning curve to master the query language.

Watch the video

Release date 12 May 22

Creating a Dynamic Dropdown Dashboard using IMPORTRANE data in Google Sheets

One of the best ways of getting yourself comfortable with filtering IMPORTRANGE data and figuring out how and when to use it is by running through an example.

In this example, we will expand example 8 above and instead of using a static company name and sales rep that we have to dig into the formula to manually change, we will create a dynamic dropdown list. You will be able to select a company and then select from a list of sales reps who made sales to that company.

Take a look at the end result:

Using QUERY on IMPORTRANGE in Google Sheets: Total sales for any client and rep combination

Take a special note of the dropdown menus for the company (C1) and sales rep (E1).

Step 1: Reference the company and sales rep in our QUERY from selected cells.

Setup the selection row

First, we need to set up the main title row. Follow these steps:

  1. In cell A1, add: “Total Sales For Client:” and then merge cells A1:B1.
  2. In cell C1, add “Labadie Ltd” as our temporary static company. We will change this to a dropdown later.
  3. In cell D1, add “and Rep: “.
  4. In cell E1, add “Simon Lovell” as our temporary static sales rep. We will change this to a dropdown later. Merge this cell to cover range E1:F1 or E1:G1.

It’s probably a good idea to change the background colour and borders for your rep and client names so that your users can see that they can use these as dropdowns.

Your top row should look a little like this (minus the dropdown arrows. We haven’t got there yet):

Update the formula

Next, we are going to convert our formula in example 8 so that the company value is drawn from C1 and the sales rep value is drawn from E1. You can copy and paste that formula over to cell A2 or copy it from below.

Next, we need to reference our client and rep cells. To do this we break out of the query by closing it with a double quotation mark (") and then join or concatenate it to our client cell C1 or rep cell E1 using an ampersand (&). We then need to reverse the process by connecting back to the query with an ampersand and double quotation mark (&").

It is important to note that we are still using our double quotation mark around the cell reference. It is common to forget this and get an error.

Have a look at our QUERY now with the new changes.

Step Hai Creating the dropdown data validation.

For our Company and Sales Rep dropdowns, we need to create a list of all the companies and sales reps to choose from. To do this we are going to use our IMPORTRANE function again.

Normally a good practice would be to create a separate sheet tab and add the two dropdown lists there before hiding this sheet tab so other users can’t see where they are.

In this example, we will put them to the right of our data separated by a column in columns J and K respectively so it is easier for us to see what we will be doing. By the time you finish this step your Google Sheet should look a little like this:

Importing the list of companies

In Col J2, type ‘Company’ as a header.

Next, we need to generate a unique list of all the companies from our imported Sales Google Sheet. We will be combing our IMPORTRANGE this time with the SORT and UNIQUE functions.

We will be putting the following formula in cell J3.

First, we use our trusty IMPORTRANGE on column B or the Sales tab (Sales!B2:B). This will give us the full list of all the companies in the column. Line 3

We then want to remove the duplicates by using the UNIQUE function to only select the first occurrence of each business name.  UNIQUE takes a range as an argument and this range is our imported list of companies. Line 2

Next, we want to SORT our list alphabetically to make it easier for our users to find the company that they are looking for. The first argument for SORT is our unique list of companies. We only have one column so our sort column is Một for our next argument and this column will be sorted in ascending order so we set the final argument to true. Lines 1, 5, 6

importing the list of sales reps

In Col K2, type ‘Rep’ as a header.

Extracting the list of sales reps follows the same process as the company dropdown but we will be extracting our list of reps from col N (Sales!N2LN).

Add the following formula to cell K3.

creating the data validation

Now that we have our company and rep lists let’s create our two dropdown menus using data validation.

A dialogue pop-up window will appear.

  1. Ensure the ‘Cell range’ matches C1.
  2. Ensure the ‘Criteria’ is set to ‘List from a range’.
  3. Click on the little grid next to the criteria and then select from column J2:J1001 (or at least up to 200 just in case new companies are added to the list).
  4. Check, ‘Show dropdown list in cell’.
  5. Check, ‘Reject input’. 
  6. Select, Save.

Done.

You should now be able to select among companies and see the sales for the current rep.

Next, repeat the steps to create your data validation dropdown for your sales rep in cell E1 with your rep list in range K2:K1001.

Now you will be able to select from either dropdown to generate the sales for the selected company by the selected rep.

A small problem

You might have frustratingly noticed that sometimes the sales rep did not make a sale for a particular company. As such, you just end up with some empty data. This is pretty annoying. Let’s fix this up in the final step of this project.

Step 3: Improving the sales rep selection so that it only displays the sales reps who made sales for the selected company

In this last step, we want to improve our Sales Rep dropdown list so that it will only display the sales rep who made a sale for the selected company and not display any other rep.

QUERY to the rescue again.

Let’s update our sales rep list formula in cell K3 with the following:

First, we will adjust our IMPORTRANGE to include column A through to column N. We need to check the ‘Company’ column, column B (Col2). Line 3

Next, we want to still select the rep column (Col14), but only when the company column matches the company in C1Lines 4, 5

Then, we order our list of reps inside our QUERY in ascending order. Line 6

Finally, we set our header to zero to effectively remove it.

Now, when we change the company you will notice that the list of sales reps will change along with it.

You have just built a dynamic dropdown dashboard to review sales by company and sales rep in a Google Sheet separate from your core Sales sheet.

Great job!

Watch the video

Release date: 18 May 2022

Conclusion

In this tutorial, we covered two approaches to filtering IMPORTRANGE data in Google Sheets; using QUERY and FILTER. While FILTER can display formulas, images and links, QUERY is much more flexible in how it displays your selected data.

You then build your very own dashboard with dropdowns drawn from imported data using more QUERY, SORT and the UNIQUE function.

So now that you have a good understanding of filtering IMPORTRANGE data in Google Sheets, what are you going to do with it? I love to hear how folks apply these technique and expand on their in their own products. So please share them in the comments below.

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

Learn more about importing range data between Google Sheets:

  • Google Sheets IMPORTRANGE: Looking up data with the VLOOKUP function
  • Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet
  • Copy and Paste Range Values from one Google Sheet into another with Google Apps Script
  • Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values
  • Google Sheets: How to use OR inside a FILTER

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

Article post on: nongdanmo.com

Recommended For You

About the Author: Bảo