QUERY with IMPORTRANGE in Google Sheets: formula and 12 examples
Wrapping IMPORTRANGE in QUERY lets you filter, sort and summarize data from another spreadsheet in one formula. This guide shows the formula, explains why columns are called Col1, Col2 and so on, and walks through twelve examples, from selecting columns to merging sheets and aggregating.
To filter, sort or summarize data that lives in another Google Sheets file, put IMPORTRANGE inside QUERY:
=QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A:D"), "select Col1, Col3 where Col2 > 90", 1)
IMPORTRANGE brings the range in, and QUERY works on it before anything reaches the sheet. One thing changes compared with a query on a range of the same file: columns are named Col1, Col2, Col3, counted from the first column of the imported range, not A, B, C.
| You need | Query string |
|---|---|
| Some of the columns | "select Col1, Col3" |
| Rows that meet a condition | "select * where Col2 >= 80" |
| Two conditions | "select * where Col1 starts with 'J' and Col2 >= 80" |
| A sorted result | "select * order by Col4 desc" |
| The first rows only | "select * limit 5" |
| A total per group | "select Col3, sum(Col2) group by Col3" |
The examples below read from a public sample spreadsheet, so each formula can be pasted into a sheet of your own. The first time, the cell shows #REF!: click it and choose Allow access.
The two functions
=QUERY(data, query, [headers])
- data: the range, or here the imported range, to query.
- query: the query string, in quotes.
- headers (optional): the number of header rows at the top of the data.
On a range of the same file, QUERY uses column letters:
=QUERY(B3:C12, "SELECT B, C WHERE C > 90")
=IMPORTRANGE("spreadsheet_url", "range_string")
- spreadsheet_url: the URL of the source spreadsheet.
- range_string: the tab and range to import, for example “Student List!D3:D12”.
IMPORTRANGE on its own imports the range as it is:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!D3:D12")
QUERY with IMPORTRANGE
=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string", [headers])
The source tab holds the student name, the score and the faculty in columns B, C and D:
To return the name and the faculty of the students who scored more than 90:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:D"), "SELECT Col1, Col3 WHERE Col2 > 90", 1)
- “Student List!B:D”: columns B to D of the source tab. Column B becomes Col1, C becomes Col2 and D becomes Col3.
- SELECT Col1, Col3: return the name and the faculty.
- WHERE Col2 > 90: keep the rows with a score above 90.
- 1: the first row of the imported data is a header row.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
Examples
1. Select columns
List the columns you need, in the order you want them:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:D"), "SELECT Col1, Col2, Col3")
2. Merge data from several sheets
Put several IMPORTRANGE functions into curly braces and query the result. A comma places the ranges side by side:
=QUERY({IMPORTRANGE("url_1", "range"), IMPORTRANGE("url_2", "range")}, "query_string")
A semicolon stacks them under each other:
=QUERY({IMPORTRANGE("url_1", "range"); IMPORTRANGE("url_2", "range")}, "query_string")
Here the scores and the faculties are on two tabs of a second file. The formula joins them side by side and returns three of the four columns:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/1oad79FkDOx2iGdeiJDEcKeBcVZGDHDl9MDiK0WAa13g/edit", "Scores!B2:C12"),
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1oad79FkDOx2iGdeiJDEcKeBcVZGDHDl9MDiK0WAa13g/edit", "Faculty!B2:C12")},
"SELECT Col1, Col2, Col4")
Side by side, the ranges need the same number of rows; stacked, the same number of columns. Each source file has to be allowed once:
3. Limit and skip rows
limit sets the maximum number of rows to return, and offset skips rows at the top. Both count data rows, not the header:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:D"), "SELECT Col1, Col2, Col3 LIMIT 5 OFFSET 2")
The formula skips the first two students and returns the next five.
4. Rename a column
label changes the header of a column in the result:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:D"), "SELECT Col1, Col2, Col3 LIMIT 5 LABEL Col2 'Grade'")
5. Format a column
format sets how a column is displayed. The source tab also has a date of birth in column E:
To show only the month and the year:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col1, Col2, Col3, Col4 FORMAT Col4 'mmm-yyyy'")
The cells still hold full dates; only the display changes.
6. Filter rows
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col1, Col2, Col3, Col4 WHERE Col2 >= 80")
7. Filter by several conditions
Join conditions with and or or. For students whose name starts with J and who scored 80 or more:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col1, Col2, Col3, Col4 WHERE Col1 STARTS WITH 'J' AND Col2 >= 80")
8. Filter by text
contains matches part of a text. Text values go in single quotes:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col1, Col2, Col3, Col4 WHERE Col3 CONTAINS 'Mathematics'")
The comparison is case-sensitive: ‘mathematics’ would return nothing.
9. Sort the result
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col1, Col2, Col3, Col4 WHERE Col2 >= 80 ORDER BY Col4 DESC")
desc sorts from the latest date to the earliest; leave it out, or write asc, for the opposite.
10. Calculate with columns
Columns can be added, subtracted, multiplied and divided inside select. Another tab holds two scores per student, in columns D and F:
To add the two scores:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List all scores!B:F"), "SELECT Col1, Col3+Col5 LABEL Col3+Col5 'Scores Total'")
Col3 and Col5 are the third and fifth columns of the imported range B:F, the two score columns.
11. Aggregate by group
The aggregation functions are sum(), avg(), count(), max() and min(). Every column in select that is not aggregated has to be listed in group by. To total the scores per faculty:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col3, SUM(Col2) WHERE Col3 <> '' GROUP BY Col3 LABEL Col3 'Faculty', SUM(Col2) 'Total Scores'")
The condition Col3 <> '' leaves out the empty rows that come with a whole-column range. The screenshot shows the same query written with column letters; Col1-style names are the form to rely on for imported data.
12. Apply a scalar function
Scalar functions work on each value. year() takes the year from a date:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!B:E"), "SELECT Col1, Col2, Col3, YEAR(Col4) LABEL YEAR(Col4) 'Year of Birth'")
Others are month(), day(), quarter(), upper() and lower(). Note that month() counts from 0, so January is 0. The guide to date filtering with QUERY covers dates in detail.
Errors in QUERY with IMPORTRANGE
Errors in QUERY with IMPORTRANGE
⚠️ Error: #REF! with a prompt to connect the spreadsheets.
✅ Solution: Click the cell and choose Allow access. If the formula combines several source files, first enter a plain IMPORTRANGE for each file in a spare cell and allow access there; inside a larger formula the prompt does not always appear.
⚠️ Error: #VALUE! with “NO_COLUMN”.
✅ Solution: The query uses column letters or a column number that does not exist. With imported data, write Col1, Col2 and so on, with a capital C, and count from the first column of the imported range.
⚠️ Error: #VALUE! with “Unable to parse query string”.
✅ Solution: A clause is misspelled or in the wrong order. The order is select, where, group by, order by, limit, offset, label, format.
⚠️ Error: The header row appears among the data, or the first row is missing.
✅ Solution: Set the third argument of QUERY: 1 when the imported range starts with a header row, 0 when it does not.
⚠️ Error: A text condition returns no rows.
✅ Solution: Text in a query goes in single quotes and is case-sensitive. Use lower(Col3) contains 'math' to ignore case.
⚠️ Error: The formula is slow.
✅ Solution: IMPORTRANGE downloads the whole range before QUERY filters it. Import only the columns the query needs, and a closed range such as B2:E500 where you can.
Related Google Sheets functions
Related Google Sheets functions
Functions that are often used next to QUERY and IMPORTRANGE, each with its own guide:
- QUERY: The query language in detail.
- IMPORTRANGE: Permissions, refresh rules and limits.
- QUERY with CONCATENATE: Joining columns inside and around a query.
- QUERY with UNIQUE: Distinct rows from a query.
- XLOOKUP: Finds a value and returns the matching one.
- ARRAYFORMULA: Applies a formula to a whole range.
- Pivot tables: Summarize a table by category without a query.
- IMPORTXML: Imports data from a web page with an XPath query.
A query over an import is a small data pipeline kept in a cell
A query over an import is a small data pipeline kept in a cell
QUERY around IMPORTRANGE is the spreadsheet version of what a data warehouse does: read from a source, filter, join and aggregate. It works well for a few thousand rows and two or three files. Past that, the formula gets slow, the source file becomes something nobody dares to touch, and the logic of the report lives in a string inside a cell.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. The filtering, joining and aggregating are written there, in SQL that can be reviewed, and the sheet receives the finished table. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
What is the difference between IMPORTRANGE and IMPORTDATA?
IMPORTRANGE is used to import data from one Google Sheets spreadsheet to another, while IMPORTDATA is used to import data from a given URL, such as a CSV or TSV file hosted on the web. IMPORTRANGE requires permission from the source spreadsheet, while IMPORTDATA does not.
How do I use the IMPORTRANGE function in Google Sheets?
To use the IMPORTRANGE function in Google Sheets, you have to include the URL of the source spreadsheet and the range of cells to import. For example: =IMPORTRANGE("source_spreadsheet_url", "sheet_name!range"). You must also grant access to the source spreadsheet for the function to work.
How do I import data into a QUERY in Google Sheets?
Wrap IMPORTRANGE in QUERY: =QUERY(IMPORTRANGE("spreadsheet_url", "Sheet1!A:D"), "select Col1, Col3 where Col2 > 90", 1). Columns of imported data are called Col1, Col2 and so on, counted from the first column of the imported range, and the first use of a source file needs a click on Allow access.
Can you use IMPORTRANGE with QUERY in Google Sheets?
Yes, you can use IMPORTRANGE with QUERY in Google Sheets to import data from another spreadsheet and then apply the QUERY function to filter, sort, and manipulate the imported data.
