How to combine QUERY with IMPORTRANGE in Excel and Google Sheets

Heads up: Excel and Google Sheets do this differently.

Step by step

1. 1Obtain the source spreadsheet URL — it looks like https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/.
2. 2First, grant access by typing =IMPORTRANGE("URL","Sheet1!A1:D") in an empty cell and clicking 'Allow access' in the prompt that appears. You only need to do this once per source file.
3. 3Once access is granted, wrap the IMPORTRANGE in QUERY: =QUERY(IMPORTRANGE("URL","Sheet1!A1:D"),"SELECT Col1, Col2, SUM(Col4) WHERE Col3 = 'Active' GROUP BY Col1, Col2 LABEL SUM(Col4) 'Total'",1).
4. 4Note that when data comes from IMPORTRANGE the columns are named Col1, Col2, Col3, … (not A, B, C) inside the QUERY string.
5. 5Set the headers argument (the last parameter of QUERY) to 1 if the imported range has a header row.

Tips

• Always use Col1, Col2, Col3 notation in the QUERY string when the data source is IMPORTRANGE — using A, B, C will return an error because IMPORTRANGE returns an array, not a named sheet range.
• The IMPORTRANGE connection needs to be authorised once per destination file. If collaborators open the file they will not need to re-authorise, but if the source file is moved or deleted the QUERY will break.
• For very large cross-file datasets, consider importing into a staging tab first (=IMPORTRANGE(...) alone) and querying that tab with standard A/B/C column references — it performs better and is easier to debug.

Frequently asked