Importing data from one sheet to another using IMPORTRANGE in Google Sheets is something many of us have done. But did you know there's a whole world of possibilities beyond just importing?
With IMPORTRANGE and QUERY functions, you can do much more than that. In this article, we'll explain how to use them, show examples, discuss their benefits, and provide a template that you can use in your work.
The QUERY function in Google Sheets helps you ask questions and get specific answers from your data. Let's explore how it works.
Syntax:
=QUERY(data_range,"query_string")
Here is the breakdown:
Example:
Let's say we have a list of students' names in column B and their scores in column C. If you want to find out who scored higher than 90, you could use the following QUERY formula.
=QUERY(B3:C12, "SELECT B, C WHERE C > 90")
This tells the function to look in cells B3 to C12, find the names and scores where the score is greater than 90, and show them in the result. So, you'd get a list of students' names and scores, but only for those who scored over 90.
The IMPORTRANGE function in Google Sheets helps you bring in data from another sheet or even a different Google Sheets file.
Syntax:
=IMPORTRANGE("spreadsheet_url","range_string")
Let's break it down:
Example:
Let's use our student table from the previous example. Suppose we have a sheet called "Student List" in our Google Sheets file. To import the faculty of each student from that sheet into our current sheet, we will use the IMPORTRANGE function.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!D3:D12")
This function will bring in the faculty information for each student from the specified range in the "Student List" sheet into our current sheet.
What if we need to bring together data from different sources in Google Sheets and then analyze it in specific ways?
Here's how it works:
1. Use IMPORTRANGE to fetch data from other sheets or files.
2. Then, wrap the IMPORTRANGE function inside QUERY. This helps you ask questions about the imported data and filter it based on what you need.
=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string")
Continuing with our example, let's say we have one sheet with student names and scores, and another with their faculties.
By combining QUERY and IMPORTRANGE, you could find out the faculties of students who scored above 90.
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:D"), "SELECT Col1, Col3 WHERE Col2 > 90", 1)
With QUERY and IMPORTRANGE in Google Sheets, you can do a lot. You can import specific data, combine data from different sheets, remove unnecessary rows, search for specific text, and more. Mastering these tricks makes your work faster. And what's more, all these cases are available for you in our template.
If you would like to learn more about finding data with specific criteria in Google Sheets, you can read our guide on VLOOKUP functions.
Now, you're ready to explore the versatility of QUERY and IMPORTRANGE functions further. So let's dive into each use case and uncover the full potential of combining QUERY with IMPORTRANGE to optimize your data management tasks and Google Sheets experience.
The most basic scenario is to import a specific data range from another table. We have a Google Sheets file with student information, but only want to import the scores and faculties of the students (range B3:C12) into another sheet.
Here's how you can combine IMPORTRANGE with the SELECT clause in QUERY to import specific columns from the "Student List" sheet, and then choose exactly which columns you want to pull.
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:D"), "SELECT Col1, Col2, Col3")
If you have data scattered across different sheets and want to merge it into 1, QUERY and IMPORTRANGE can help. There are two different methods of merging your data.
For merging horizontally, separate IMPORTRANGE functions by commas:
=QUERY({IMPORTRANGE("spreadsheet1_url", "data_range"), IMPORTRANGE("spreadsheet2_url", "data_range"),..}, "query_string")
For merging vertically, separate IMPORTRANGE functions by semicolons:
=QUERY({IMPORTRANGE("spreadsheet1_url"; "data_range"), IMPORTRANGE("spreadsheet2_url"; "data_range"),..}, "query_string")
Remember to import data separately from each sheet before using QUERY and IMPORTRANGE to avoid errors.
We have scores in another sheet named "Scores" and faculties in named "Faculty". To merge this information into 1 sheet using QUERY and IMPORTRANGE, use the formula below.
Formula:
=QUERY({IMPORTRANGE("https://docs.google.com/spreadsheets/d/1oad79FkDOx2iGdeiJDEcKeBcVZGDHDl9MDiK0WAa13g/edit?usp=sharing", "Scores!B2:C12"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1oad79FkDOx2iGdeiJDEcKeBcVZGDHDl9MDiK0WAa13g/edit?usp=sharing", "Faculty!B2:C12")}, "SELECT Col1, Col2, Col4")
Note that when you import data using IMPORTRANGE from a specific spreadsheet for the first time, the formula prompts you to grant access. Simply click on the small button that appears in the cell to allow access.
You can use the "limit" and "offset" QUERY clauses to control the number of rows imported and remove unnecessary ones.
The "limit" clause sets the maximum number of rows to import, excluding the header. For instance, "limit 5" would import only 5 rows of data.
Meanwhile, the "offset" clause determines how many rows to skip from the top of the imported data range. For example, "offset 2" would skip the first 2 rows of data.
So, if you use "limit 5" and "offset 2" together, you'll import 5 rows of data starting from the third row. This helps streamline your data analysis by focusing only on the relevant information.
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:D"), "SELECT Col1, Col2, Col3 LIMIT 5 OFFSET 2")
You can customize the imported column names using the "label" QUERY clause.
The "label" clause allows you to rename the headers of the imported columns. For instance, we can change "Score" to "Grade".
To achieve this task:
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:D"), "SELECT Col1, Col2, Col3 LIMIT 5 LABEL Col2 'Grade'")
This formula imports the first 5 rows of data from columns B, C, and D of the "Student List" sheet and renames the header accordingly.
In case you need to apply specific formats to the values in imported columns, you can adjust the formatting of imported columns using the "format" QUERY clause.
For instance, you can format dates in the "E" column to display only the month and year.
For this:
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing","Student List!B:E"), "SELECT Col1,Col2,Col3,Col4 FORMAT Col4 'mmm-yyyy'")
With the QUERY clause in Google Sheets, you can selectively filter rows within imported columns by applying specific conditions.
Let's say, we need to apply a filter to the imported data, specifying that only rows where the value in column C is greater than or equal to 80 should be included.
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing","Student List!B:E"), "SELECT Col1,Col2,Col3,Col4 WHERE Col2>=80")
When using the WHERE statement with QUERY and IMPORTRANGE, you're not limited to just 1 rule, and can actually add a few rules using AND or OR.
So, let's change our previous formula a bit. Along with getting students who scored 80 or more in column C, let's also get students whose names start with the letter "J".
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing","Student List!B:E"), "SELECT Col1,Col2,Col3,Col4 WHERE Col1 STARTS WITH 'J' AND Col2>=80")
We can specify criteria for what a particular column should contain to meet our needs. This can be done using the WHERE clause with a contains operator.
Let's use the formula below to filter our student data only to retrieve those in the "Mathematics" faculty.
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing","Student List!B:E"), "SELECT Col1,Col2,Col3,Col4 WHERE Col3 CONTAINS 'Mathematics'")
When we're using the ORDER BY clause with QUERY, we can sort our data based on a specific column, either in ascending or descending order.
In this case, we want to:
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing","Student List!B:E"), "SELECT Col1,Col2,Col3,Col4 WHERE Col2>=80 ORDER BY Col4 DESC")
We can perform basic arithmetic operations like add (+), subtract (-), multiply (*), and divide (/) columns imported from a spreadsheet and display the result as a separate column.
Our task is to:
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:E"), "SELECT D, SUM(C) WHERE D <> '' GROUP BY D LABEL D 'Faculty', SUM(C) 'Total Scores'")
In Google Sheets, you can use aggregation functions within select, order by, label, and format QUERY clauses to perform calculations on imported columns:
Our task:
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:E"), "SELECT D, SUM(C) WHERE D <> '' GROUP BY D LABEL D 'Faculty', SUM(C) 'Total Scores'")
When using scalar functions, we can transform imported data into a single value.
Consider the table with columns B to E containing student names, scores, faculty, and date of birth. If you apply the year() function to the date of birth values, it will extract the year. So, if you have dates like 26/01/1994, year() will return 1994.
Formula:
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:E"), "SELECT Col1, Col2, Col3, YEAR(Col4) LABEL YEAR(Col4) 'Year of Birth'")
If you want to improve your Google Sheets skills, learn more about mastering more advanced functions like ARRAY, XLOOKUP, and UNIQUE:
Combining QUERY and IMPORTRANGE in Google Sheets can be tricky. Here's what to keep in mind:
For detailed answers, check out our articles on IMPORTRANGE and QUERY functions error solutions.
While it's great to be skilled in spreadsheets, not everyone needs to be an expert. Luckily, there are tools that can handle the complex calculations for you, so you can concentrate on analyzing your data. These tools offer easier, faster, and automated approaches to data analysis.
OWOX: Reports, Charts & Pivots Extension offers a simpler way to work with data, even if you're not a spreadsheet expert. With its easy-to-use interface, you can work with complex data and create quality reports. Whether you're analyzing sales, customer behavior, or marketing data, the OWOX Reports Extension for Google Sheets helps you extract useful data without the need to master complex spreadsheet techniques.
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.
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.
To import data into a QUERY in Google Sheets, first use the IMPORTRANGE function to bring data from another spreadsheet. Then, wrap the IMPORTRANGE function with the QUERY function. Use the following formula: =QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string")
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.