[et_pb_section fb_built=”1″ module_class=”sheetgo-post” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Combining QUERY with IMPORTRANGE allows you to pull in data from different spreadsheets and perform advanced queries on it. Whether you’re managing project progress, financial data, or team efforts, mastering these functions can enhance your productivity and data management.<\/span><\/p>\n IMPORTRANGE<\/span><\/a> is one of the most useful Google Sheets functions. As the name suggests it imports a range of cells from one spreadsheet to another. Google Sheets<\/span><\/p>\n On the other hand, <\/span>QUERY<\/span><\/a> is an incredibly versatile formula, essentially allowing you to use your spreadsheet as a database. With SQL-like query language, you can look up, sum, count, average, filter and sort data.<\/span><\/p>\n [\/et_pb_text][et_pb_video_slider _builder_version=”4.23.1″ _module_preset=”default” custom_margin=”15px||15px||true|false” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_video_slider_item src=”https:\/\/www.youtube.com\/watch?v=H4CvUq1vbjM” _builder_version=”4.23.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” show_image_overlay=”off”][\/et_pb_video_slider_item][\/et_pb_video_slider][et_pb_text module_id=”how” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This combination applies QUERY directly to the data transferred with IMPORTRANGE to filter specific columns, rows, and ranges.<\/span><\/p>\n It\u2019s a real time-saver when working with multiple spreadsheets, as you only pull in the exact data you need. It also allows you to format the data within your query instead of afterwards, which saves you an additional step.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY(IMPORTRANGE(spreadsheet_url, range_string), query, [headers])[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_code _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_code][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text module_id=”combination” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Whenever you combine functions, things can get a bit confusing. To avoid that, let\u2019s look at what\u2019s going on in this syntax.<\/span><\/p>\n First, let\u2019s focus on QUERY. This function can manipulate data in many ways, but always needs to start with a data source.<\/span><\/p>\n In many cases this is data from another tab on the same spreadsheet. But as you know, this article deals with importing data from another spreadsheet. As a result, we will use the IMPORTRANGE function to feed data into QUERY.<\/span><\/p>\n IMPORTRANGE is a simple function: just input the link to the spreadsheet and the range you want to import. To <\/span>learn more about IMPORTRANGE <\/span><\/a>check out our in-depth article.<\/span><\/p>\n This imported data can be plugged into the QUERY function. After all you need to input are the query parameters, which dictate how the data will be manipulated and displayed. The examples below illustrate this.<\/span><\/p>\n Just remember, the structure of the syntax remains similar, no matter which function you nest inside Google Sheets\u2019 QUERY function.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”example1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the following examples, I\u2019ll use this geographical data as the source data:<\/span><\/p>\n [\/et_pb_text][et_pb_code _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_code][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1.-Query-Importrange-data.png” alt=”combine query with importrange dataset” title_text=”1. Query Importrange data” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n First, I import country data from 186 countries into a business spreadsheet. I\u2019ve merged items from a separate dataset based on certain criteria.<\/span><\/p>\n Say I only want to import the data from countries in the Americas. To display only those specific cells, you simply need to apply the <\/span>Select <\/b>and <\/span>Where <\/b>clauses inside the QUERY function.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY(IMPORTRANGE(“Spreadsheet_url”, “Population!B1:F187”),\u00a0“Select * Where Col1=’Americas’ “)[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2.-Query-Importrange-ex-1.png” alt=”combine query with importrange example 1″ title_text=”2. Query Importrange ex 1″ align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this formula, QUERY searches data from a predefined range\/array according to specific criteria. Simultaneously, IMPORTRANGE imports the data into the target spreadsheet.<\/span><\/p>\n Placing QUERY before IMPORTRANGE allows you to search (query) specific information from the data that is imported with IMPORTRANGE. This way, you only import the data you want to see in your spreadsheet.<\/span><\/p>\n After IMPORTRANGE, you need to add search criteria. To import data from countries in the Americas, we used this query: <\/span>Select * Where Col1=\u2019Americas\u2019<\/b>.\u00a0 In plain English that translates to \u201cselect all the data that has \u201cAmericas\u201d in column 1\u201d.<\/span><\/p>\n This formula comes in especially\u00a0 handy in situations where you want to merge data. If you work in online marketing for example, you might want traffic from various sources into one spreadsheet. It gives you a better overview of several data sources such as Google Analytics, Ahrefs, SEMrush or Google Search Console.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”example2″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this next example, I added search criteria after IMPORTRANGE. In this case, <\/span>where Col3 > date \u20182019-07-01\u2019 and Col3 < date \u20182019-12-30\u2019<\/b> imports the same data but only for items in the list that were published between those dates.<\/span><\/p>\n Note that when you input a date into your query, you need to type the word date before it. Also don\u2019t forget to put the date in quotation marks. That\u2019s how the function knows you\u2019re referring to a date.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY(IMPORTRANGE(“Spreadsheet_url”, “Population!A1:F187”), “where Col3 > date ‘2019-07-01’ and Col3 < date ‘2019-12-30′”, 0)[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You might apply this formula in logistics or even for a small business cash register. You can add up rows, columns and data ranges with changes in the data over time. If you\u2019re interested in changes to product lines, sales, and other KPIs over the course of the year, this formula can carry out monthly or quarterly comparative analysis.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3.-Query-Importrange-date-range-v2.png” alt=”combine query with importrange example 2″ title_text=”3. Query Importrange date range v2″ _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”example3″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you want to extract certain cells or a range from several spreadsheets, use the following formula. Here\u2019s how to merge data from multiple spreadsheets into one:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY({IMPORTRANGE(“Spreadsheet_url”,”Sheet1!A1:P”);IMPORTRANGE(“Spreadsheet_url”,”Sheet1!A2:P”);IMPORTRANGE(“Spreadsheet_url”,”Sheet1!A2:P”)},”Select * where Col1<>””,1)[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/4.-Query-Importrange-multiple-sheets.png” alt=”combine query with importrange example 3″ title_text=”4. Query Importrange multiple sheets” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You need to use IMPORTRANGE for every spreadsheet you want to merge. Within the first IMPORTRANGE formula, I added the headers in A1:P.<\/span><\/p>\n The other spreadsheets also contain the headers. However, they should not be imported multiple times. To avoid importing the header three times, I\u2019ve changed the range to A2:P on the second and third sheets.<\/span><\/p>\n Lastly, the Where clause (where Col1<>\u2019\u2019) simply states that all cells that are not blank should be imported.<\/span><\/p>\n Here are a few tips for importing multiple sheets:<\/span><\/p>\n This formula is really useful for business surveys (when you collect several spreadsheets with the same format). Likewise for educational purposes. When you hand out similar exercises to students or when you collect grades from various professors.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”example4″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n With this formula, I import the average population of each continent. As you can see, I add \u201cavg\u201d after the \u2018Select\u2019 clause and I put the column in parentheses. Next, add the query (the data you are looking for) after the Where clause. In this case, the query is \u2018Europe\u2019.<\/span><\/p>\n Notice how on the rest of this spreadsheet I use multiple QUERY\/IMPORTRANGE functions to get average populations and total GDPs for different regions. Since QUERY is highly customizable, you can get all sorts of different outputs depending on your objective!<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY(IMPORTRANGE(“Spreadsheet_url”, “Population!A2:P187”), “Select avg(Col4) where Col2 contains ‘Europe’ “)[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n QUERY function allows you to extract all arithmetic functions. This is a very helpful tool for traders and financial analysts. With it, you can look up values from a variety of investment spreadsheets and carry out calculations such as Present Value =PV, Future Value =FV and interest rate functions such as =IRR and =RATE.<\/span><\/p>\n In addition: it\u2019s a more intuitive, natural language version of the complex <\/span>INDEX-MATCH<\/span><\/a> formulas in Excel.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/5.-Query-Importrange-query-and-avg.png” alt=”combine query with importrange example 4″ title_text=”5. Query Importrange query and avg” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”example5″ _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In this final example, the formula filters by continent, sums up each country\u2019s GDP and imports data from a separate spreadsheet.<\/span><\/p>\n This is specifically helpful to create dashboards and reports of all kinds.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]=QUERY(IMPORTRANGE(“Spreadsheet_url”), “Select sum(Col5) where Col2 contains ‘Europe’ “)[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/6.-Query-Importrange-query-and-sum.png” alt=”combine query with importrange example 5″ title_text=”6. Query Importrange query and sum” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”sheetgo” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo offers an intuitive and powerful alternative to the combination of QUERY and IMPORTRANGE in Google Sheets. With Sheetgo, you can seamlessly import and process data to simplify your data management process.<\/span><\/p>\n Coincidentally, one of the key data processing capabilities of Sheetgo offers QUERY filter. This feature enables you to perform advanced data queries similar to the QUERY function in Google Sheets. You can filter, sort, and manipulate data as it flows between your spreadsheets, all without the need for complex formulas.<\/span><\/p>\n Why use Sheetgo over IMPORTRANGE? Here are just three reasons:<\/span><\/p>\n Find out all the reasons <\/span>why Sheetgo is the best IMPORTRANGE alternative<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now you\u2019ve got the lowdown on how to use QUERY with IMPORTRANGE. Now you can merge the power of these two functions, for another great combination check out <\/span>How to use VLOOKUP and IMPORTRANGE<\/a>.<\/span><\/p>\nHow to use QUERY with IMPORTRANGE in Google Sheets<\/h3>\n
Syntax<\/h4>\n
\n
\n
How does this combination work?<\/span><\/h3>\n
Example 1: Import and Query<\/span><\/h4>\n
Example 2: Import data within a date range<\/h4>\n
Example 3: Import and combine multiple spreadsheets with specific criteria<\/h4>\n
\n
Example 4: Import and Query Average Values<\/b><\/h4>\n
Example 5: Import and Query Sum values<\/span><\/h4>\n
Get the same results with Sheetgo connections<\/span><\/h2>\n
\n
Conclusion<\/span><\/h2>\n