How to Use QUERY with IMPORTRANGE in Google Sheets

This tutorial includes formula examples on how to use the QUERY function with the IMPORTRANGE function in Google Sheets.

As you may know, the IMPORTRANGE function in Google Sheets imports a range of cells from one spreadsheet in one file to another spreadsheet in another file. It does not have any other data manipulation capabilities.

However, you can get tremendous benefits when you combine the IMPORTRANGE function with the QUERY function in Google Sheets.

Here are a few of them:

Constrain Columns: It helps you select the columns you want.
Criteria-based import: It helps you import the data you want by filtering out unwanted records by applying criteria.
Summarizing data: It can help you import and aggregate data in one go.

If you are not using the IMPORTRANGE and QUERY functions together, then you might usually be adopting the following approach:

Import the data into a sheet.
Then, in another sheet in the same workbook, process the data using VLOOKUP, FILTER, SUMIF, or QUERY.

However, this may not be necessary if you use the IMPORTRANGE and QUERY functions together.

In this QUERY + IMPORTRANGE tutorial, I am going to include some of the most useful QUERY data manipulation techniques with the IMPORTRANGE imported data.

Must Check: Google Sheets Function Guide.

Introduction to IMPORTRANGE Function in Google Sheets

First, let me introduce my Google Sheets file, which is named “File1_Info_Inspired”.

Then, I will give you some formula examples.

The “Sheet1” tab in that file looks like the following example. It has four columns containing items, their quantities, order numbers, and order dates.

Visualization of the IMPORTRANGE formula in Google Sheets

I am importing the data in the range A1:D7 from the “Sheet1” tab of the above file into another Google Sheets file.

Here is the IMPORTRANGE formula for that:

Syntax:

IMPORTRANGE(spreadsheet_url, range_string)

Formula:

=IMPORTRANGE("URL", "Sheet1!A1:D7")

Note: When you import, please use the proper spreadsheet URL, not the string in my formula. You can find the spreadsheet URL by opening the file and looking at the address bar in your browser.

The above IMPORTRANGE formula will probably cause a #REF! error.

How to solve the REF error in IMPORTRANGE?

You can hover your mouse pointer over the cell containing the IMPORTRANGE formula to see a blue button labeled “Allow Access”.

Click that button to import the data immediately.

Allow access prompt when importing data from another Google Sheets

Now, let’s combine the QUERY and IMPORTRANGE functions to perform different data manipulations in Google Sheets. You can see the examples below.

Constrain Columns When Importing Data with QUERY and IMPORTRANGE

One of the drawbacks of the IMPORTRANGE function is that it can only import contiguous columns. For example, you can import the columns A to Z (Sheet1!A1:Z1000), but not A and Z (Sheet1!A1:A1000 and Sheet1!Z1:Z1000).

To constrain columns when using IMPORTRANGE, you can use the QUERY function. The QUERY function allows you to select specific columns from a range of data.

Syntax:

QUERY(data, query, [headers])

1. How to Limit the Number of Columns When Importing Data in Google Sheets

We have four columns in our demo data.

While importing, you can limit the number of columns or reorder the column positions using QUERY.

To do that, combine the QUERY with the IMPORTRANGE and use the SELECT clause as follows:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select Col1,Col4 ")

The above formula will only import columns 1 and 4.

To import all the columns, you may use the following formula:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select *")

2. How to Rearrange Columns in Imported Data in Google Sheets

Here is another copy of the above formula. However, there are some changes in the SELECT clause.

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select Col4,Col3,Col1,Col2")

See how I have reordered the column positions by combining the QUERY function with the IMPORTRANGE function.

The columns were in the order “Item”, “Qty.”, “Order No.”, and “Order Date”.

The above formula will rearrange the columns in the order “Order Date”, “Order No.”, “Item”, and “Qty.”.

3. QUERY with IMPORTRANGE for Soring Imported Data in Google Sheets

Here is the generic QUERY and IMPORTRANGE formula combo for sorting imported data in Google Sheets:

=QUERY({Importrange Formula},"Select * order by Sort Column # asc")

Formula:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * order by Col2 asc")

This formula sorts the imported data based on column number 2 in ascending or A-Z order.

To sort the data in descending or Z-A order, just change asc to desc in the last part of the formula.

What about using A-Z sorting in one column and Z-A sorting in another column together? Is that possible in combination?

Yes! That’s possible. Here is the related formula:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * order by Col1 asc, Col3 desc")

This formula sorts the items in ascending order and the numbers in descending order.

QUERY and IMPORTRANGE: How to Import Data with Criteria in Google Sheets

We can use QUERY with IMPORTRANGE for filtering imported data in Google Sheets. Filtering helps us to apply conditions in one or more columns and thus import the required data.

It’s a broad topic, so I’m giving you some entry-level filtering tips.

Further, I have included some links in this post to help you proceed with advanced-level filtering in IMPORTRANGE.

We can use the WHERE clause in QUERY to filter data. The comparison operators also play a huge role in the filtering of datasets. Here are some of the comparison operators that you can use in the WHERE clause:

1. Equal to = Comparison Operator in QUERY + IMPORTRANGE

See this example of how to use the QUERY function to filter data imported with the IMPORTRANGE function.

The formula below filters the imported data for the values in column 1 that are equal to ‘A’:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col1='A' ")

The criterion is a string, so it needs to be enclosed in single quotes in the QUERY function.

To filter a date column using QUERY and IMPORTRANGE, enter the date criterion within single quotes.

For example, the following formula filters the imported data for the values in column 4 that are equal to the date 29/11/2018:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col4=Date '2018-11-29' ")

Here is the formula to filter a column of numeric data using QUERY and IMPORTRANGE:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col2=50 ")

In all the formulas above, the criterion is hardcoded in the formula. If the criterion is stored in a cell, you can use the corresponding cell reference in the formula.

Here are a few examples (relevant parts only):

Number Criterion: Where Col2="&F2&"
Date Criterion: Where Col4 = date '"&TEXT(F2,"yyyy-mm-dd")&"'
Text Criterion: Where Col1='"&F2&"'

Examples of using the QUERY with IMPORTRANGE in Google Sheets

You can find more about this in my tutorial: Examples of the Use of Literals in Query in Google Sheets.

2. Use the Greater Than “>” Operator to Filter Data in QUERY IMPORTRANGE

Greater than Comparison Operator with Number Criterion in QUERY IMPORTRANGE:

When the criterion is in cell F2;

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col2>"&F2&"")

When the criterion is hard-coded in the formula;

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col2>50")

Greater than Comparison Operator with Date Criterion in QUERY IMPORTRANGE:

When the criterion is in cell F2;

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col4>date '"&TEXT(F2,"yyyy-mm-dd")&"'")

When the criterion is hard-coded in the formula;

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col4>Date '2018-11-29' ")

3. Less Than, Greater Than or Equal To, and Less Than or Equal To

Want to learn how to use the less-than operator in QUERY with IMPORTRANGE in Google Sheets? Here is an example:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col4<Date '2018-11-29' ")

To filter the rows where column 4 dates are greater than or equal to 29/11/2018, replace < with >= in the formula:

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col4>=Date'2018-11-29' ")

Similarly, you can use <= for less than or equal to.

In the following example, I will use the < and > operators together.

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select * Where Col2>10 and Col2<100 ")

4. Not Equal To

Actually, you can specify not equal to in 3 ways when using QUERY with IMPORTRANGE in Google Sheets:

Using <> Comparison Operator. For example, the formula =QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")}, "SELECT * WHERE Col2 <> 10") will return all rows where the value in column 2 is not equal to 10.
The NOT logical operator: For example, the formula =QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")}, "SELECT * WHERE NOT(Col2) = 10") will return all rows where the value in column 2 is not equal to 10.
The IS NOT NULL condition: It is used to check if a value is not empty. For example, the formula =QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")}, "SELECT * WHERE Col2 IS NOT NULL") will return all rows where the value in column 2 is not empty.

Here is a detailed tutorial on this: How to Use Not Equal to in Query in Google Sheets.

5. Substring Match and Data Importing

Other than basic comparison operators, you can also use complex string comparison operators in the QUERY + IMPORTRANGE combination in Google Sheets.

Please see the relevant tutorials below.

To incorporate the IMPORTRANGE function in the formulas that you may find in the above tutorials, do the following.

Replace the QUERY function’s data argument with the IMPORTRANGE function. For example, in the formula =QUERY(A1:D, "Select", replace A1:D with the IMPORTRANGE function. The IMPORTRANGE function must be wrapped within curly brackets ({ }).

Change column IDs in the alphabet (capital letters) with the corresponding column index numbers. For example, the formula =QUERY(A1:D, "Select A, B") should be replaced as =QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")}, "Select Col1, Col2").

How to Use QUERY with IMPORTRANGE to Summarize Data in Google Sheets

The QUERY GROUP BY clause is a powerful tool for aggregating data in Google Sheets. By using QUERY with IMPORTRANGE in Google Sheets, you can summarize your imported data efficiently.

Here are a few examples of how to aggregate imported data using the QUERY function in Google Sheets. I mean the proper combination of QUERY and IMPORTRANGE to summarize data.

1. Count IF IMPORTRANGE Data Using QUERY in Google Sheets

Here is one example of how to count if or count import range data using QUERY in Google Sheets. Here is our sample data for your quick reference.

Demo data for using the IMPORTRANGE and QUERY functions together in Google Sheets

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select Col1,count(Col1) group by Col1 ")

Item	Count Item
A	2
B	2
C	2

Here is the conditional COUNT where the formula summarizes the selected item in column 1, which is “A”.

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select Col1,count(Col1) Where Col1='A' group by Col1 ")

Item	count Item
A	2

2. Sum IF IMPORTRANGE Data Using QUERY in Google Sheets

The following formula groups data by column A and sums the column B.

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select Col1,Sum(Col2) group by Col1")

In the following QUERY and IMPORTRANGE formula, I have included an additional condition.

=QUERY({IMPORTRANGE("URL","Sheet1!A1:D7")},"Select Col1,Sum(Col2) where Col1='B' group by Col1")

Item	sum Qty.
B	90

3. Average IF, Max IF, Min IF IMPORTRANGE Data in Sheets

You can use the functions AVG, MIN, or MAX instead of SUM in the above formula to find the average, minimum, or maximum, respectively.

Must Read: How to Sum, Avg, Count, Max, and Min in Google Sheets Query.

4. Arithmetic Operations in Imported Data

When you want to use arithmetic operators in QUERY and IMPORTRANGE, you can follow the tips detailed in the following tutorial: How to Use Arithmetic Operators in Query in Google Sheets.

Here is one formula that I have adapted from that tutorial for your reference. However, the sample data is different here.

=QUERY({IMPORTRANGE("URL","range_string")}, "SELECT A, B*C")

I hope you can now easily use the QUERY function with IMPORTRANGE in Google Sheets.

Thanks for the stay. Enjoy!

74 COMMENTS

Erin Kimmel September 13, 2023 At 8:55 pm

Hi,

This is amazing, but I’m having one major problem. I was able to use Query and ImportRange with select and when, but my main data source is from Google Form Responses and the data will not automatically update when a new response is entered. It will only refresh when I cut the formula out and then re-paste it into the cell. Any ideas what I need to do differently?

Many thanks!

- Prashanth September 15, 2023 At 12:03 pm
  
  Hi Erin Kimmel,
  
  Try the recalculation settings within the File menu > Settings > Calculation.
  
Manuel March 7, 2022 At 2:10 pm

Hi Prashanth

Maybe you can help me with this problem.

Right now, my formula places a blank row for each query if query+importrange doesn’t give data back.

(there are 28 ‘query+importrange’)

I want that blank row, but with text like ‘NO DATA IN AL.LAZENIA’ or ‘NO DATA IN AL.MURCIA.’

Can you help me, please? Thank you in advance!

Regards,

Manuel

- Prashanth March 9, 2022 At 10:39 am
  
  Hi, Manuel,
  
  You can follow the below syntax and try your luck with that many formulas combined.
  
  ={ ifna( query(IMPORTRANGE("URL1","AL.ZENIA!A10:P70"), "select Col1,Col6,Col7,Col8,Col9,Col10,Col13 where Col6 is not null or Col7 is not null or Col8 is not null",0), {"NO DATA IN AL.LAZENIA "," "," "," "," "," "," "}); ifna( query(IMPORTRANGE("URL2","AL.MURCIA!A10:P70"), "select Col1,Col6,Col7,Col8,Col9,Col10,Col13 where Col6 is not null or Col7 is not null or Col8 is not null",0), {"NO DATA IN AL.MURCIA "," "," "," "," "," "," "}) }
  
Whit May 15, 2021 At 1:38 am

Sure thing and thank you!

— link removed by admin —

Tab one is the example national workbook sheet and tab 2 is the example state sheet.

The difference between the two is that the state sheet is only displaying the rows that had “FL” contained in the State cell, so row 4 from national is missing from the state sheet.

- Prashanth May 15, 2021 At 10:33 am
  
  Hi, Whit,
  
  Please find the following Query in cell F1.
  
  =query({NATIONAL!A1:D},"Select * where Col2 contains 'FL'")
  
  Is this what you are looking for?
  
  - Whit Kathner May 18, 2021 At 3:29 am
    
    First, thank you so much for your time. I wasn’t able to translate what you did, so I made copies of the destination docs.
    
    — URLs removed by admin —
    
    - Prashanth May 18, 2021 At 9:36 am
      
      Hi, Whit Kathner,
      
      There were syntax errors. You were not following the formula given in my tutorial. I have edited the formula in your sheet.
      
Whit Kathner May 12, 2021 At 10:42 pm

Just found your site- it’s so valuable! First-time poster, let me know if I should modify or clarify my question.

I’m creating state-based workbooks off of a national overview workbook. The national overview workbook has a column for States, with multiple states often contained in one cell.

I need to build a state-based workbook (i.e., just shows what’s happening in FL) and am trying to build a formula that will crawl the National workbook and import all the pertinent FL cells. IE the data in a row that has FL contained in the State column populates on that state-based workbook as-is.

Trying a formula like this, and striking out:

=query(importrange("WORKBOOKURL","sheet1!A:Z"),"select * where Col2 contains 'FL'")

- Prashanth May 13, 2021 At 9:06 am
  
  Hi, Whit Kathner,
  
  Can you show me a sample of your national overview workbook and the (hand-entered) expected result you want in state-based workbooks?
  
- Prashanth May 14, 2021 At 9:28 am
  
  You can share the editable URL of your sample sheet via “Reply” below. I will keep the comment unpublished.
  Thanks
  
Judith Goh April 22, 2021 At 7:12 am

Hi Prashanth,

I need to move data from sheet A to sheet B (numbers) & C (text).

Col 6 in sheet A is the filtered column. It consists of numbers and random text.

I have no problem importing data to sheet B using the function below:

=QUERY(IMPORTRANGE("URL", "PAST!A19168:I"),"Select Col1,Col2,Col3,Col5,Col6 where Col6 >1")

But I’m having problem when importing data to sheet C using the function below:

=QUERY(IMPORTRANGE("URL", "PAST!A19168:I"),"Select Col1,Col2,Col3,Col5,Col6 where Col6 <1")

Col 6 will be blank. The text/words won't be imported.

Can you suggest how to amend the function? Note that I can't use "Col 6 = xxx" because the text is completely random and different types of text will be added in the future hence I can't be editing the function all the time…

Your help is very much appreciated.

- Prashanth April 22, 2021 At 2:58 pm
  
  Hi, Judith Goh,
  
  You have two Query formulas, and you think the first formula works correctly, whereas the second one is not.
  
  My humble suggestion is, please don’t rely on your above two formulas as you will see unwanted results because you are using Query in a mixed data type column.
  
  Here are my suggestions.
  
  In Sheet B:
  
  Use Query to import the required columns.
  
  =QUERY(IMPORTRANGE("URL", "PAST!A19168:I"),"Select Col1,Col2,Col3,Col5,Col6")
  
  Assume the above imported and queried data is in Sheet1!A1:E and E contain the mixed type data.
  
  In Sheet2, try the below Query.
  
  =filter(Sheet1!A1:E,isnumber(Sheet1!E1:E))
  
  In Sheet C:
  
  In Sheet1, use the above Query Importrange formula.
  
  In Sheet2 use the below filter.
  
  =filter(Sheet1!A1:E,ISTEXT(Sheet1!E1:E))
  
  - Judith Goh April 23, 2021 At 7:00 am
    
    Thank you for the quick reply!
    
Mike Lancaster November 9, 2020 At 9:40 pm

hey Prashanth,

I left you a comment/question on Friday and a link to my spreadsheet. It’s since disappeared and I’m not sure whether you’ve deleted it deliberately or if it’s simply gone missing. If you cannot help with my question – or more importantly if my question is not possible – can you please let me know?

I’m desperate to find an answer but I’m not sure where to look anymore!

Thank you kindly
Mike

- Prashanth November 10, 2020 At 5:32 pm
  
  Hi, Mike Lancaster,
  
  That comment is still under ‘pending approval’ because it has a link to your sample sheet (to protect your sheet’s privacy).
  
  I have opened your sheet. I could not understand what you really want to do. Please leave some notes on the sheet so that I can understand your requirement.
  
- Prashanth November 13, 2020 At 6:08 am
  
  Hi, Mike Lancaster,
  
  I have gone through your previous comment once again. The IMPORTRANGE in your formula confused me. Actually, you can simply use a Filter to import data from one tab to another.
  
  =filter(DATA!13:1000,DATA!13:13=40)
  
  Change 40 with the value you want. If it’s a text, then the value should be entered within double-quotes.
  
Adit NB October 15, 2020 At 3:40 pm

Hi, Prasanth Thanks for sharing, glad to found this articles, we already did;

=QUERY(IMPORTRANGE("https://docs.google.com/xxxxxxxxxxxxxxx/edit#gid=0", "namesheet!A2:I"),"Select * order by Col1 desc")

In our case result of the import in column A – I ( with the green Color )
And we make column J – N ( with the yellow Color ) to Customer Service fill their note who Follow up the data and what the status.

The Problem is When The New Data from the source comes in, the notes for the client before just stay in the position is supposed to go down following the old client.

- Prashanth October 16, 2020 At 12:51 pm
  
  Hi, Adit NB,
  
  You may try this.
  
  Align Imported Data with Manually Entered Data in Google Sheets.
  
Michael Croft August 18, 2020 At 3:33 pm

That is wonderful, thanks very much. You are a generous helpful person.

Sorted my data just as I needed. Now I’m on a quest to make it easier and neater. Hopefully, I can find my own answers.

Michael Croft August 16, 2020 At 5:44 am

Hi Prashanth

Thanks so much for sharing your expertise, it is much appreciated. However, I have an odd problem using Query Importrange.
Here is my formula

=QUERY(IMPORTRANGE("URL", "Work!A1:O1000" ),"Select * Where Col12='Americas - Sundowners' ")

Now, this returns the expected data except that the cells in row 1 fill up with all of the column contents from the original sheet.

Can you advise, please?

- Prashanth August 16, 2020 At 10:12 am
  
  Hi, Michael Croft,
  
  Normally the Query is able to identify the header row. But sometimes it fails.
  
  So, you may please try including the header clause as below.
  
  =QUERY(IMPORTRANGE("URL", "Work!A1:O1000" ),"Select * Where Col12='Americas - Sundowners'",1)
  
  I don’t see any other issue in your formula.
  
  - Michael Croft August 17, 2020 At 1:46 pm
    
    Hi Prashanth, that worked a treat, thanks very much. I now need to sort the output by a column and nothing I try works.
    
    - Prashanth August 18, 2020 At 11:28 am
      
      Hi, Michael Croft,
      
      For that use ORDER BY clause which is already explained in the above tutorial.
      
      If you use the said clause and still seeing the issue, then you aren’t using the clause in the correct order.
      
      Example for Query Importrange and Sorting A-Z:
      
      =QUERY(IMPORTRANGE("URL", "Work!A1:O1000" ),"Select * Where Col12='Americas - Sundowners' order by Col12 asc",1)
      
Lauren April 12, 2020 At 8:38 am

Hi Prashanth, I have data in columns A:G of Sheet 1. I am trying to get the content only from B where column C = named range value, D = named range value, E = named range value. I am then wanting to join these into a concatenated cell limited to 30 entries based on the highest value in column F. This is to make a dashboard of Instagram hashtags based on category, type, and location. It’s been disastrous!

=QUERY(IMPORTRANGE("URL","InstaRawData!B2:G"),"Select where Col3 contains 'Select_category' ")

Where am I going completely wrong?! I get Value, Ref, and N/A errors, and the closest is returning the header name only, not the record of the row with the matching criterion.

- Prashanth April 12, 2020 At 10:44 am
  
  Hi, Lauren,
  
  If your named range has multiple values, the above formula won’t work. Search “multiple contains” in this post (comment section) to find a relevant tutorial.
  
  If you want my free assistance on this, feel free to share an example (mockup data with your result) sheet.
  
Holly Soltys March 24, 2020 At 10:50 pm

Hi Prashanth,

Learning a lot from these knowledge shares. Thank you very much! I’m struggling to extend this formula even more and include more than one CONTAINS Criteria.

=QUERY(IMPORTRANGE("url","Invoice detailed!A:Z"),"SELECT Col2, Col3, Col4, Col11, Col5, Col7 WHERE Col11 CONTAINS 'Client Budget'",1)

This formula works, and imports the data if column 11 contains the text ‘Client budget’, but I would also like it to import data if Col11 contains ‘advertising budget’.

So a Query Importrange function, which looks up multiple text strings and returns all that hit.

Please help!

- Prashanth March 25, 2020 At 9:51 am
  
  Use the MATCH complex substring match in Query instead of CONTAINS as below.
  
  =QUERY(IMPORTRANGE("URL","Invoice detailed!A1:Z"),"SELECT Col2, Col3, Col4, Col11, Col5, Col7 WHERE Col11 matches 'Client Budget|advertising budget'",1)
  
  Please read Multiple CONTAINS in WHERE Clause in Google Sheets Query for more advanced use.
  
  Note: The above Query is case sensitive. You can make the above Query formula case-insensitive by modifying the WHERE clause as below.
  
  WHERE lower(Col11) matches 'client budget|advertising budget'
  
  - Holly March 25, 2020 At 4:44 pm
    
    Thanks so much! Really helpful.
    
Tcho January 30, 2020 At 2:37 am

I tried with the filter function, but nothing is displayed error, according to google filter manages hypertext links.

- Prashanth January 30, 2020 At 5:51 pm
  
  Hi, Tcho,
  
  I am totally in the dark (:
  
  I don’t think Filter will work correctly with IMPORTRANGE. You must stick with QUERY.
  
  Without seeing your dataset, it’s almost difficult for me to write a correct formula. So please make an example sheet and share with me.
  
  - Tcho January 30, 2020 At 7:15 pm
    
    Hello, Prashanth,
    
    Thank you for your help. The Query function associated with Importrange does not take into account the hyperlinks which are in Sheet 1, which I want to transfer into Sheet 2.
    
    It advises using the Filter function with Importrange because Filter takes into account hyperlinks.
    
    I found this formula:
    
    =FILTER(IMPORTRANGE("URL"; "Poi!A4:L1000"),INDEX(IMPORTRANGE("URL"; "Poi!A4:L1000"),0,12)="Report")
    
    But must have an error, because it does not work on my sheet. The hyperlinks can be found in column I.
    
    Thank you.
    
    - Prashanth January 30, 2020 At 9:39 pm
      
      Hi, Tcho,
      
      Now I could understand the problem. Since I don’t have the data to test, I am giving you a similar example formula.
      
      =filter(importrange("URL of Sheet 1","Income!E1:G"),importrange("URL of Sheet 1","Income!G1:G")="apple")
      
      This formula returns the values from columns E to G if G1:G contains “apple” (tab name is “Income”). It imports the data with hyperlinks if any.
      
      But, I would recommend you to import the data in one tab and use the filter in another tab.
      
      Note: The above formula may be converted to your locale. See the settings here – How to Change a Non-Regional Google Sheets Formula.
      
Tcho January 28, 2020 At 11:13 pm

Hello, The Import command works to display all the columns of my table in another sheet, but I cannot find the query command to display only the rows that contain the word “Report” in column L.

=QUERY(IMPORTRANGE("URL Here"; "Poi!A4:O1000"),"Select * Where Col12='Report' ")

- Prashanth January 29, 2020 At 9:13 am
  
  Hi, Techo,
  
  It should be like;
  
  =QUERY(IMPORTRANGE("URL Here", "Poi!A4:O1000"),"Select * Where Col12 contains 'Report' ")
  
  Use CONTAINS for a substring match in Query. I’ve included a tutorial link to the use of CONTAINS within the post.
  
ksanch5 January 7, 2020 At 4:37 am

With the aggregate functions is there a way to prevent the result from being output with a header?

For example:

query(importrange("URL","Range"), select sum(Col10) where Col5='filter value'") produces output:

row 1 – SUM
row 2 – value of sum

Is there a way to stop row 1 from being output? It makes it difficult to pair this function with an if statement in sheets because then the values you want the formula to output ends up on different rows.

Example: =if(A2="cheese",query(importrange("URL","Range"), select sum(Col10) where Col5='Cheese'),0) will return something like this instead of the intended result which is having 10 next to cheese in the value section. Is there a workaround?

Column A Value
Cheese Sum 10

- Prashanth January 7, 2020 At 1:14 pm
  
  Try this.
  
  =query(importrange("URL","range"),"select sum(Col10) where Col5='condition' label sum(Col10)''")
  
- Prashanth January 7, 2020 At 5:46 pm
  
  Understand the Label Clause in Google Sheets Query
  
Bindu November 13, 2019 At 6:06 pm

Hey, I tried using but getting value error.

=QUERY(IMPORTRANGE("URL","sheet1!I:I"),"select Col9,Count(Col9) where lower(Col9) like '%metro station%' group by Col9",0)

Please help.

- Prashanth November 13, 2019 At 9:44 pm
  
  Please change Col9 to Col1 because your imported range only contains one column.
  
  Best,
  
RDM November 6, 2019 At 2:33 pm

Hey Prashanth,

I have a sheet (let’s call it Sheet1) with names in Col1 and their respective values in Col6.

I need to import the median of values of each name to Sheet2.

How can I do that?

Please help.

Thank you in advance.

- Prashanth November 7, 2019 At 9:29 am
  
  Please check this guide.
  
  Median Function in Google Sheets [Advanced Tips and Tricks]
  
  There read the specific section ‘Group Wise Median’.
  
  Best,
  
  - RDM November 7, 2019 At 11:29 am
    
    Hey, thanks for the reply, Prashanth.
    
    However, I need help with importing a group-wise median to a separate sheet.
    
    Is this possible?
    
    - Prashanth November 7, 2019 At 11:34 am
      
      Into a separate Spreadsheet or a new tab within the Sheet?
      
      - RDM November 7, 2019 At 12:07 pm
        
        A completely separate Spreadsheet.
        
        
        Prashanth November 7, 2019 At 12:47 pm
        
        Hi, RDM,
        
        Import that data from ‘Sheet1’ to ‘Sheet2’. Then use the Data menu Pivot Table in ‘Sheet2’.
        
        Best,
        
        
        Prashanth November 7, 2019 At 5:12 pm
        
        https://infoinspired.com/google-docs/spreadsheet/item-wise-median-using-pivot-table-google-sheets/
        
Scott Brennan October 31, 2019 At 11:48 pm

Hi Prashanth,

Great examples.

Do you know if I can do an ImportRange when the Google sheets are in different Google drives?

For example, could I do an ImportRange from a sheet in your google drive to a sheet in my google drive if the appropriate permissions were granted?

Thinking about a school application for this, where the teacher could do an ImportRange to summerize data from Sheets that exist in student google drives to a summary sheet in the teachers google drive.

- Prashanth November 1, 2019 At 1:03 pm
  
  Hi, Scott Brennan,
  
  If you have permission to pull data, then you won’t face any issue. Read this help center article for more info.
  
  https://support.google.com/docs/answer/3093340?hl=en-GB
  
  Best,
  
Troy October 17, 2019 At 7:01 am

Hi,

I want to know if how to include 3 conditions in a query?

For example:

=QUERY(IMPORTANGE("URL","Sheet1!A:Z), "Select Col1 where Col2 = 'Fast' AND Col3 = 'Good'")

I tried adding another condition but it didn’t work. How do you resolve this?

- Prashanth October 17, 2019 At 7:38 am
  
  Hi, Troy,
  
  I don’t find any issue in adding one more criterion.
  
  =QUERY(IMPORTANGE("URL","Sheet1!A:Z"), "Select Col1 where Col2 = 'Fast' and Col3 = 'Good' and Col4='Yes'")
  
  Please note that text, number, and date criteria must be entered differently. Find the details below.
  
  Examples to the Use of Literals in Query in Google Sheets
  
  If this not helps, please share the formula that includes three criteria/conditions and the value types in the corresponding columns.
  
  Best,
  
Noelle Llorente October 9, 2019 At 10:16 am

Hi- I need help with a formula, please!!!

I can get this to work:

=query(importrange("url","Current Month!A:J"), "select Col8, sum(Col9) group by Col8")

But I need to add a contingency where Col3 contains “(referenced cell)” and can’t figure it out!

I tried

=query(importrange("url","Current Month!A:J"), "select Col8, sum(Col9) group by Col8 where Col3 contains "A2"")

This does not work.

- Prashanth October 9, 2019 At 12:49 pm
  
  Hi, Noelle,
  
  You were so close! Try this Query.
  
  =query(importrange("url","Current Month!A:J"), "select Col8, Sum(Col9) where Col3 contains'"&A2&"' group by Col8",0)
  
  Assuming the criterion in A2 is a string.
  
  Also please do check this guide to know the use of criteria from cell references in Query Contains, Matches, etc.
  
  How to Use Cell Reference in Google Sheets Query
  
  Best,
  
Bindu October 4, 2019 At 11:29 am

Hi,

I tried with below formula but triggering an error

=query(IMPORTRANGE("sheetlinkurl","sheet1!A1:G"),"select col2,col3,col4 where col6>=date '2019-10-01' and where col6<=date '2019-10-01'")

Thank you in Advance.

- Prashanth October 4, 2019 At 12:11 pm
  
  Hi, Bindu,
  
  It should be Col, not col.
  
  Best,
  
arul selvan August 18, 2019 At 6:04 pm

I want the formula in a cell range to be imported instead of value. Is it possible?

- Prashanth August 18, 2019 At 7:44 pm
  
  Hi, Arul Selvan,
  
  It’s not possible 🙁
  
Nessus August 14, 2019 At 10:38 am

My last question just… disappear!!!

I guess no more questions for me!!!

In any case… thanks for your help so far.

Cheers

Nessus

- Prashanth August 14, 2019 At 10:51 am
  
  Hi, Nessus,
  
  Please share an editable Sheet (demo content only) with your question. So that I can save lots of time.
  
  Best,
  
Nessus August 5, 2019 At 8:47 pm

Again… absolutely perfect!!!

Thank you very much, man. You make my life so easy!!!

One last (I hope!) question not so important but helpful too… is it possible besides the dates, also to set the time of the day that the formula will update with the new data?.

I mean, instead of every midnight, update the data every lets say… every day in 7:00 in the morning?.

Thanks

Nessus

- Prashanth August 6, 2019 At 5:48 am
  
  Hi, Nessus,
  
  Welcome 🙂
  
  Regarding your new question please do make the below changes in the formulas in cell A2 and D2:
  
  Replace Col4=date '"&TEXT(today(),"yyyy-mm-dd")&"'",0) with Col4= datetime '"&TEXT(today()+time(7,0,0),"yyyy-mm-dd HH:mm:ss")&"'",0)
  
  In cell E2, make the following changes:
  
  Replace Col3=date '"&TEXT(today()+1,"yyyy-mm-dd")&"'",0) with Col3= datetime '"&TEXT(today()+1+time(7,0,0),"yyyy-mm-dd HH:mm:ss")&"'",0)
  
  Hope that works?
  
  Best,
  
Nessus August 4, 2019 At 12:21 pm

Hi again

Sorry, I didn’t explain well question 1. My mistake.

So, I want to copy the data from column CHECK “F” (Col6) from the TEST 1 file to column CHECK “D” (Col4) to the TEST 2 file and leave the COMMENT column in TEST 2 file empty for additional last-minute editing.

Is possible to do that?

Thank you in advance

Nessus

- Prashanth August 5, 2019 At 10:02 am
  
  Hi, Nessus,
  
  For that use two Query formulas instead of 1.
  
  Formula # 1 in cell A2.
  
  =query(importrange("Enter URL Here","'Sheet1'!A2:F"),"Select Col2, Col1 where Col4=date '"&TEXT(today(),"yyyy-mm-dd")&"'",0)
  
  Formula # 2 in cell D2:
  
  =query(importrange("Enter URL Here","'Sheet1'!A2:F"),"Select Col6 where Col4=date '"&TEXT(today(),"yyyy-mm-dd")&"'",0)
  
  If any issue, please let me know.
  
  Best,
  
Nessus August 2, 2019 At 10:07 pm

Hi there and thank you very much for sharing your knowledge in these very useful google sheets functions.

I have the following use case and I wonder if you can help me how to achieve this.

I have two different spreadsheets files. Let’s say TEST1 & TEST2. In TEST1 a numerous of users they add data all day in the following form:

The second spreadsheet file, TEST2, it has two tables with the following columns:

Now, what I want to do is… check the dates in the DATE RETURN column in TEST1 file and copy all the cells values of ITEM, NUMBER, COMMENTS & CHECK columns with the today’s date to the second spreadsheet TEST2 to the Table 1…

and also…. check the dates in the DATE DELIVERED column in TEST1 file and copy the cell values of the ITEM & COMMENTS columns with the tomorrow’s date to the second spreadsheet TEST2 to the Table 2…

That’s it. Sorry for the long post but I’ve tried to elaborate as much as possible. Can you give me some help on how can I accomplish these tasks?. I’ll appreciate any help/hints!!!

Thank you in advance

Nessus

- Prashanth August 3, 2019 At 11:54 am
  
  Hi, Nessus,
  
  The formula in cell A2 in the file “TEST 2” to check the dates in the DATE RETURN column in TEST1 file for today’s date.
  
  Note: Replace the text URL Here with the URL of your “TEST 1” file. Also, replace Sheet1 with the tab name in your “TEST 1” file.
  
  =query(importrange("URL here","'Sheet1'!A2:F"),"Select Col2, Col1, Col5, Col6 where Col4=date '"&TEXT(today(),"yyyy-mm-dd")&"'",0)
  
  The formula in cell E2 in the “TEST 2” file to check the dates in the DATE DELIVERED column in TEST1 file for tomorrow’s date.
  
  =query(importrange("URL Here","'Sheet1'!A2:F"),"Select Col2, Col5 where Col3=date '"&TEXT(today()+1,"yyyy-mm-dd")&"'",0)
  
  Best,
  
  Prashanth KV
  
  - Nessus August 3, 2019 At 10:31 pm
    
    Excellent!!!…. Perfect!!!!…. Works like a charm!!!
    
    Thanks a bunch, Prashanth!!!… I can thank you enough!!!… you saved me from a lot of daily work.
    
    I may have two questions:
    
    1. What if I want to skip a column but get the data from the next columns in the same row?. For example… skip the COMMENTS column but get the data from column CHECK. Is this can be done?
    
    2. What happens when the dates will change?. Are the data going to be deleted from the TEST 2 spreadsheet automatically or do I have to do it manually ?.
    
    Again, thank you very much for your help!!!
    
    Cheers
    Nessus
    
    - Prashanth August 4, 2019 At 10:12 am
      
      Hi, Nessus,
      
      Welcome and thanks for your feedback!
      
      Regarding your questions;
      
      1. You can return any columns that you want in the range A2:F from your TEST 1 Sheet.
      
      A2:F means there are 6 columns (A, B, C, D, E, and F). In Query, these columns are identified as Col1, Col2, Col3, Col4, Col5, and Col6. That means if you want to return the CHECK column instead of the COMMENT column, replace Col5 in the formula with Col6.
      
      2. The formula will update automatically as I have used today() function in it. which returns the current day. Today()+1 returns tomorrow’s date.
      
      Best,
      
kishore babu p May 21, 2019 At 11:01 am

Nice, good one.

Anup Rai April 11, 2019 At 11:27 am

I encountered an error, stating that “Result too large” – after I used the ‘Query With Importrange in Google Sheets”. Please advise how to resolve this problem?

- Prashanth April 11, 2019 At 5:07 pm
  
  Hi, Anup Rai,
  
  I haven’t face any such issue so far. I guess there is some limitation in the volume of data importing in one single formula. If your data is too large, import the data part by part. Combine them in Query.
  
  I have seen a similar discussion on the StackExchange HERE.
  
  Best,
  
Ren January 26, 2019 At 1:23 am

How to add importranged data.
1 2 3 4
283,091.00 244,844.00 303,244.00 277,948.00
159,806.00 129,948.00 160,600.00 121,166.00
0.00 0.00 0.00 0.00
11,665.00 10,693.00 43,600.00 28,053.00
137,640.00 108,690.00 123,610.00 72,507.00

How to sum importrange this per column, not whole data.

- Prashanth January 26, 2019 At 10:13 am
  
  Try this formula after making the necessary changes.
  
  =query(importrange("key","Sheet1!A1:D"),"Select Sum(Col1), sum(Col2),sum(Col3),sum(Col4)")
  
Craig Cervenka November 30, 2018 At 8:25 pm

Wow Prashanth, well done!

Thank you so much for sharing an easy-to-understand tutorial on one of the most useful (and most difficult to
master) Sheets functionalities.

- Prashanth December 1, 2018 At 10:15 am
  
  Hi, Craig,
  
  Thanks for the positive feedback. It motivates.