The Ultimate Guide to Using QUERY and IMPORTRANGE in Google Sheets
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.
Decoding the QUERY Function: Optimized Data Retrieval
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:
- data: This is where you tell the function where to find the information you want to analyze. It could be a range of cells or even an entire sheet.
- query: This is the question you're asking your data. You write it using special words that the function understands, sort of like giving it instructions.
- [headers]: This part is optional. If your data has headers (like titles at the top of your columns), you can tell the function how many rows to skip so it knows where your data starts.
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.
Understanding IMPORTRANGE Function: Seamless Data Import
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:
- spreadsheet_url: this is the web address (URL) of the Google Sheets file you want to import data from.
- range_string: it shows which part of the other sheet you want to import. You can think of it as the specific cells or range of cells.
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.
Synergizing QUERY and IMPORTRANGE for Advanced Data Manipulation
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 Syntax Explained
=QUERY(IMPORTRANGE("spreadsheet_url", "data_range"), "query_string")
- IMPORTRANGE("spreadsheet_url", "data_range") fetches data from another Google Sheets file.
- "spreadsheet_url" is the web address (URL) of the Google Sheets file you want to import data from.
- "data_range" specifies the range of cells from the other sheet that you want to import.
- QUERY() function is wrapped around IMPORTRANGE to filter and manipulate the imported data.
- "query_string" is where you write your query to filter the imported data. You use special keywords and commands to specify what information you want to extract.
QUERY+IMPORTRANGE Example Application
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)
- IMPORTRANGE("https://docs.google.com/spreadsheets/d/ 1W9jTjw3XH0mI1rLVeHXej_vqn1L7dVC1djisb4gJtW4/edit?usp=sharing", "Student List!B:D"): This part imports data from our Google Sheets file, specified by its URL and the range "Student List!B:D". This range covers columns B to D, where column B contains student names, column C contains scores, and column D contains faculties.
- "SELECT Col1, Col3 WHERE Col2 > 90": This is the query string, where:
- SELECT Col1, Col3: This selects columns 1 (student names) and 3 (faculties) from the imported data.
- WHERE Col2 > 90: This filters the data to only include rows where the score (in column 2) is greater than 90.
- 1: This specifies that the first row of the imported data contains headers (student names, scores, faculties), so the QUERY function should skip it.
Common Use Cases of Combining QUERY and IMPORTRANGE
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.
Use Case #1: Importing Specific Data Range
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")
Use Case #2: Merging Multiple Sheets Data
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.
Use Case #3: Eliminating Extra Rows in Imported Data
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")
Use Case #4: Renaming Imported Columns
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:
- Import columns B, C, and D from the "Student List" spreadsheet.
- Limit the imported rows to 5, excluding the header row.
- Rename the headers of the imported columns, replacing "Score" with "Grade".
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.
Use Case #5: Adjusting Imported Columns Values
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:
- Import columns B, C, D, and E from the "Student List" spreadsheet.
- Format the values in the "E" column to display only the month and year.
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'")
Use Case #6: Filtering Imported Data
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")
Use Case #7: Applying Multiple Criteria for QUERY and IMPORTRANGE
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")
Use Case #8: Searching for Specific Text Within Imported Data
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'")
Use Case #9: Sorting Imported Data
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:
- Import columns B, C, D, and E from the spreadsheet.
- Filter the imported data so that only values in column B greater than or equal to 80 are included.
- Sort the imported data by the date of birth (column E) in descending order.
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")
Use Case #10: Arithmetic Calculations on Imported Data
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:
- Get the student names along with their scores from 2 specific columns (D and F) in the 'Student List all scores' spreadsheet.
- Add up these scores for each student and show the total as a new column named 'Scores Total'.
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'")
Use Case #11: Aggregating Imported Data
In Google Sheets, you can use aggregation functions within select, order by, label, and format QUERY clauses to perform calculations on imported columns:
- avg() calculates the average of all numbers in a column.
- sum() calculates the total sum of all numbers in a column.
- count() calculates the total quantity of items in a column (excluding rows with empty cells).
- max() identifies the maximum value in a column.
- min() identifies the minimum value in a column.
Our task:
- Summarize the scores (column C) for each faculty (column D), specifically Mathematics, Computer Science, and Art.
- Display the total scores for each faculty in a new column named 'Total Scores'.
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'")
Use Case #12: Executing Scalar Functions on Imported Data
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'")
Sharpen Your Knowledge with These Google Sheets Guides
If you want to improve your Google Sheets skills, learn more about mastering more advanced functions like ARRAY, XLOOKUP, and UNIQUE:
- XLOOKUP: Helps find and retrieve information from your spreadsheet quickly and easily.
- ARRAY: Conducts calculations across multiple cells or ranges, delivering multiple results simultaneously.
- UNIQUE: Extracts values from a range and removes any duplicates.
- Pivot Table: This adaptable tool simplifies the summarization, organization, and examination of extensive data sets, allowing for easy extraction of insights and identification of trends.
- CONCATENATE Function: This function combines multiple text items into a single continuous string, providing a seamless way to merge text from different cells.
- IMPORT Function: Fetches and parses data from structured XML files via URLs, enabling direct display in your sheets.
Navigating Challenges in Combining QUERY and IMPORTRANGE
Combining QUERY and IMPORTRANGE in Google Sheets can be tricky. Here's what to keep in mind:
- Check for typos or incorrect syntax in your QUERY formula.
- Ensure all referenced columns exist in the imported data.
- Double-check the IMPORTRANGE function for the correct spreadsheet URL and range.
- Verify permissions for accessing the source spreadsheet.
For detailed answers, check out our articles on IMPORTRANGE and QUERY functions error solutions.
Simplifying Complex Data Analysis without Formulas
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.
Advancing Your Analytics Journey with OWOX: Reports, Charts & Pivots Extension
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.
FAQ
