--- title: "QUERY with IMPORTRANGE in Google Sheets: formula, examples" canonical: "https://www.owox.com/blog/articles/query-with-importrange-google-sheets" updated: "2026-10-05" --- # 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. [Google Sheets Tips](/blog/topics/google-sheets-tips) · Updated October 2, 2026 · [Vadym Kramarenko](/team/vadym-kramarenko) · 10 min read ![QUERY with IMPORTRANGE in Google Sheets: formula and 12 examples](https://cdn.owox.ai/www/webflow/689ceb6067cb2332f7dca11f_-2025-08-13-224459.png/public) 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](https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit), 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](/blog/articles/query-function-google-sheets) uses column letters: > `=QUERY(B3:C12, "SELECT B, C WHERE C > 90")` ![Google Sheets with student names and scores in B3:C12 and the formula =QUERY(B3:C12, “SELECT B, C WHERE C > 90”) returning the two students with a score above 90](https://cdn.owox.ai/www/webflow/6789939bff82b7e6458ab1ef_49391.png/public) > `=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](/blog/articles/importrange-google-sheets) on its own imports the range as it is: > `=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit", "Student List!D3:D12")` ![Google Sheets with the formula =IMPORTRANGE for the range Student List!D3:D12 returning the faculty of each student from another spreadsheet](https://cdn.owox.ai/www/webflow/6789939c279fcb7237bc8055_49401.png/public) ## 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: ![Google Sheets tab Student List with student names in column B, scores in column C and faculties in column D](https://cdn.owox.ai/www/webflow/6789939c8151a1ce19ff7a2d_49407.png/public) 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. ![Google Sheets with a QUERY around IMPORTRANGE selecting Col1 and Col3 where Col2 is greater than 90, returning two students and their faculties](https://cdn.owox.ai/www/webflow/6789939ca7c890bac1b89d57_49419.png/public) ## The Turning Point OWOX Data Marts See your first report built in real time. _15 minutes._ 1. Connect your data warehouse 2. Pick your metrics 3. Get a live Google Sheets report In the time it takes to write a ticket. Then imagine never writing that ticket again. [Book a Demo](/book-a-call) We'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")` ![Google Sheets with a QUERY around IMPORTRANGE selecting Col1, Col2 and Col3 and returning student name, score and faculty](https://cdn.owox.ai/www/webflow/6789939c696314f6a04c8eaf_49429.png/public) ### 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") ``` ![Google Sheets with a QUERY around two IMPORTRANGE functions in curly braces, returning student name, score and faculty from two tabs of another spreadsheet](https://cdn.owox.ai/www/webflow/6789939cd486f7287640d0ab_49434.png/public) 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: ![Google Sheets cell with #REF! and the message You need to connect these spreadsheets, with the Allow access button](https://cdn.owox.ai/www/webflow/6789939ca0b2e60389f70be9_49436.png/public) ### 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")` ![Google Sheets with a QUERY around IMPORTRANGE using LIMIT 5 OFFSET 2, returning five students starting from the third row of the source](https://cdn.owox.ai/www/webflow/6789939cc0cc1a33557e5e6e_49438.png/public) 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'")` ![Google Sheets with a QUERY around IMPORTRANGE using LABEL Col2 ‘Grade’, returning five rows with the score column headed Grade](https://cdn.owox.ai/www/webflow/6789939cd952753b5bd61c71_49440.png/public) ### 5\. Format a column `format` sets how a column is displayed. The source tab also has a date of birth in column E: ![Google Sheets tab Student List with a DOB column of dates of birth in column E](https://cdn.owox.ai/www/webflow/6789939c31bd6241e3ff27b7_49442.png/public) 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'")` ![Google Sheets with a QUERY around IMPORTRANGE using FORMAT Col4 ‘mmm-yyyy’, returning the dates of birth as Jan-1994, Nov-1993 and so on](https://cdn.owox.ai/www/webflow/6789939d60ee1d776c03633f_49444.png/public) 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")` ![Google Sheets with a QUERY around IMPORTRANGE with WHERE Col2>=80, returning the students with a score of 80 or more](https://cdn.owox.ai/www/webflow/6789939ca1cbf05c6d5f18ac_49446.png/public) ### 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")` ![Google Sheets with a QUERY around IMPORTRANGE with two conditions joined by AND, returning John Smith and Jessica Taylor](https://cdn.owox.ai/www/webflow/6789939cc8513796b6f8ba9d_49448.png/public) ### 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'")` ![Google Sheets with a QUERY around IMPORTRANGE with WHERE Col3 CONTAINS ‘Mathematics’, returning the three students of the Mathematics faculty](https://cdn.owox.ai/www/webflow/6789939c6e1b89a464dbddbd_49450.png/public) 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")` ![Google Sheets with a QUERY around IMPORTRANGE with ORDER BY Col4 DESC, returning students with a score of 80 or more sorted by date of birth from latest to earliest](https://cdn.owox.ai/www/webflow/6789939c87235e8f0f31adfa_49452.png/public) `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: ![Google Sheets tab Student List all scores with two disciplines and two score columns per student](https://cdn.owox.ai/www/webflow/6789939cc0cc1a33557e5e69_49454.png/public) 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'")` ![Google Sheets with a QUERY around IMPORTRANGE selecting Col1 and Col3+Col5 with the label Scores Total, returning the sum of the two scores for each student](https://cdn.owox.ai/www/webflow/6789939c8151a1ce19ff7a32_49456.png/public) 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'")` ![Google Sheets with a QUERY around IMPORTRANGE grouping by faculty and summing the scores, returning Art 233, Computer Science 357 and Mathematics 262](https://cdn.owox.ai/www/webflow/6789939c1537913d2b2a8d40_49458.png/public) 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'")` ![Google Sheets with a QUERY around IMPORTRANGE using YEAR(Col4) with the label Year of Birth, returning the year of birth for each student](https://cdn.owox.ai/www/webflow/6789939e736f94e27036f693_49460.png/public) 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](/blog/articles/date-filtering-with-query-google-sheets) covers dates in detail. ## 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 Functions that are often used next to QUERY and IMPORTRANGE, each with its own guide: * [QUERY](/blog/articles/query-function-google-sheets): The query language in detail. * [IMPORTRANGE](/blog/articles/importrange-google-sheets): Permissions, refresh rules and limits. * [QUERY with CONCATENATE](/blog/articles/query-with-concatenate-google-sheets): Joining columns inside and around a query. * [QUERY with UNIQUE](/blog/articles/query-with-unique-google-sheets): Distinct rows from a query. * [XLOOKUP](/blog/articles/key-differences-of-xlookup-vs-vlookup): Finds a value and returns the matching one. * [ARRAYFORMULA](/blog/articles/array-formulas-google-sheets): Applies a formula to a whole range. * [Pivot tables](/blog/articles/pivot-tables-google-sheets): Summarize a table by category without a query. * [IMPORTXML](/blog/articles/importxml-function-google-sheets): Imports data from a web page with an XPath query. ## 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](https://workspace.google.com/marketplace/app/owox_data_marts/94902851409) 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. ![Google Sheets with the OWOX Data Marts extension open beside an ad spend table: the Unified Ad Spend data mart, nine of fourteen columns ticked and a Save & Run button – the rows in the sheet come from a governed data mart, not a pasted export](https://cdn.owox.ai/www/content-marketing-article/query-with-importrange-google-sheets/screens/1.png/public) 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](https://www.owox.com/app-signup); [spreadsheet reporting](/features/spreadsheet-reporting) describes both ways data reaches a sheet. ## Your New Normal 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 [Get started free](https://www.owox.com/app-signup) ## FAQ ## 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. ## Who wrote this ![Vadym Kramarenko](https://cdn.owox.ai/www/webflow/6798e703afdf787334941cde_Vadym-Kramarenko.png/public) [Vadym Kramarenko](/team/vadym-kramarenko) · Growth Marketing Manager Vadym Kramarenko is a Growth Marketing Manager at OWOX, where he drives user acquisition and product-led growth strategies. He hosts the OWOX podcast, interviewing analytics professionals about data-driven marketing, attribution, and reporting best practices. Vadym specializes in turning complex analytics concepts into practical, actionable marketing frameworks. [LinkedIn](https://www.linkedin.com/in/vadim-kramarenko/) · [All articles](/team/vadym-kramarenko) [Google Sheets Tips](/blog/topics/google-sheets-tips) ## Related articles [Google Sheets Tips · Pivot tables in Google Sheets: how to create and use them · October 2, 2026](/blog/articles/pivot-tables-google-sheets) [Google Sheets Tips · QUERY with CONCATENATE in Google Sheets: join two columns · October 2, 2026](/blog/articles/query-with-concatenate-google-sheets) [Google Sheets Tips · QUERY function in Google Sheets: syntax, clauses, examples · October 2, 2026](/blog/articles/query-function-google-sheets) [See all articles →](/blog/articles) ## Book a call You are booked. The invitation is on its way to , with the calendar invite and the meeting link. [Reschedule](#) [Cancel](#) ## Links Pages this page links to, on this site and on docs.owox.com. Where the page has a Markdown twin, its address follows the link. - [Google Sheets Tips](https://www.owox.com/blog/topics/google-sheets-tips) — /blog/topics/google-sheets-tips.md - [Vadym Kramarenko](https://www.owox.com/team/vadym-kramarenko) — /team/vadym-kramarenko.md - [QUERY](https://www.owox.com/blog/articles/query-function-google-sheets) — /blog/articles/query-function-google-sheets.md - [IMPORTRANGE](https://www.owox.com/blog/articles/importrange-google-sheets) — /blog/articles/importrange-google-sheets.md - [Book a Demo](https://www.owox.com/book-a-call) — /book-a-call.md - [date filtering with QUERY](https://www.owox.com/blog/articles/date-filtering-with-query-google-sheets) — /blog/articles/date-filtering-with-query-google-sheets.md - [QUERY with CONCATENATE](https://www.owox.com/blog/articles/query-with-concatenate-google-sheets) — /blog/articles/query-with-concatenate-google-sheets.md - [QUERY with UNIQUE](https://www.owox.com/blog/articles/query-with-unique-google-sheets) — /blog/articles/query-with-unique-google-sheets.md - [XLOOKUP](https://www.owox.com/blog/articles/key-differences-of-xlookup-vs-vlookup) — /blog/articles/key-differences-of-xlookup-vs-vlookup.md - [ARRAYFORMULA](https://www.owox.com/blog/articles/array-formulas-google-sheets) — /blog/articles/array-formulas-google-sheets.md - [Pivot tables](https://www.owox.com/blog/articles/pivot-tables-google-sheets) — /blog/articles/pivot-tables-google-sheets.md - [IMPORTXML](https://www.owox.com/blog/articles/importxml-function-google-sheets) — /blog/articles/importxml-function-google-sheets.md - [OWOX Data Marts](https://www.owox.com/app-signup) - [spreadsheet reporting](https://www.owox.com/features/spreadsheet-reporting) — /features/spreadsheet-reporting.md - [See all articles →](https://www.owox.com/blog/articles) — /blog/articles.md