Google Sheets QUERY Function: Write SQL in Sheets

Filtering and aggregating thousands of rows with FILTER and pivot formulas gets messy fast. You’ll learn QUERY syntax, 12 copy-paste patterns, JOIN-style logic with IMPORTRANGE, and safe date and text handling—SQL power without leaving Sheets.

What is the Google Sheets QUERY function?

The Google Sheets QUERY function lets you run SQL-like statements on a range of data. It uses Google’s Visualization API Query Language under the hood, enabling familiar clauses like select, where, group by, pivot, order by, limit, and label. It’s ideal when FILTER and SUMIFS chains get messy or when you want one formula to produce a clean, analysis-ready result.

• Primary use cases: reporting tables, KPI summaries, dynamic dashboards, multi-sheet joins.
• Strengths: compact expressions, readable logic, powerful grouping and pivoting.
• Limitations: strict type inference, column references by letter (A, B, C), and a few SQL differences.

QUERY syntax and parameters (the essentials)

QUERY(data, query, [headers])

• data: Your range, e.g., A1:F1000 (include headers when possible).
• query: A string using the query language, e.g.,

  "select A, sum(C) where B='Paid' group by A order by sum(C) desc"

  .
• headers (optional): Number of header rows. Leave blank to let Sheets detect, or set explicitly for reliability.

Pro tip: Build the query string in parts using TEXTJOIN or ampersands if you need variables (like dates or dropdown values).

Column addressing and data types

• Columns by letter: In the query string, reference columns as A, B, C based on their position in data.
• Types matter: Comparisons differ for numbers vs text vs dates. For text, wrap with single quotes: where B = 'Paid'.
• Dates: Use date 'YYYY-MM-DD' syntax or pass a parameter via TEXT to format correctly.

Quick-start: 12 foundational QUERY patterns

1. Select specific columns
   

   =QUERY(A1:F, "select A, C, F", 1)

2. Filter rows (text match)
   

   =QUERY(A1:F, "select * where B='Paid'", 1)

3. Filter rows (number condition)
   

   =QUERY(A1:F, "select A, C where C > 100", 1)

4. Filter rows (partial text)
   

   =QUERY(A1:F, "select * where lower(B) contains 'pro'", 1)

5. Between two dates
   

   =QUERY(A1:F, "select * where A >= date '-01-01' and A <= date '-12-31'", 1)

6. Sort and limit
   

   =QUERY(A1:F, "select A, C order by C desc limit 10", 1)

7. Group and sum
   

   =QUERY(A1:F, "select B, sum(C) group by B order by sum(C) desc", 1)

8. Average per category
   

   =QUERY(A1:F, "select B, avg(C) group by B", 1)

9. Count by status
   

   =QUERY(A1:F, "select B, count(A) group by B", 1)

10. Pivot a category by month
    

    =QUERY(A1:F, "select sum(C) group by B pivot month(A)", 1)

11. Rename columns
    

    =QUERY(A1:F, "select B, sum(C) group by B label sum(C) 'Revenue'", 1)

12. Distinct values
    

    =QUERY(A1:B, "select distinct B where B is not null", 1)

JOIN-like queries with IMPORTRANGE

QUERY doesn’t have a native SQL JOIN keyword, but you can combine ranges then filter with WHERE to simulate joins.

1. Enable access: First use

   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/...", "Orders!A1:F")

   once and allow access.
2. Combine data: Use { ... , ... } to horizontally combine or { ... ; ... } to stack vertically.

Example: left-join style lookup (Customers in current file; Orders from external sheet):

=ARRAYFORMULA( QUERY( {
Customers!A2:C, VLOOKUP(Customers!A2:A, IMPORTRANGE("https://docs.google.com/...","Orders!A2:D"), 4, FALSE)
}
, "select Col1, Col2, Col3, Col4 where Col1 is not null", 0))

Alternative: use

{
Customers!A2:C, QUERY(IMPORTRANGE(...), "select ... where ...", 0)
}

to pre-filter imported data before combining.

Handling dates, times, and text safely

• Dates: Use ISO format with date 'YYYY-MM-DD'. Build dynamic dates with TEXT:
  

  =QUERY(A1:F, "select * where A >= date '" & TEXT(G1, "yyyy-mm-dd") & "'", 1)

• Case-insensitive search: Wrap lower() or upper() on both sides.
• Null-safe conditions: Guard with is not null to avoid type errors.

Advanced aggregations and pivots

QUERY supports aggregates like sum, avg, count, max, min, and functions like month(), year(), day() on date columns.

=QUERY(A1:F, "select year(A), month(A), B, sum(C) where B is not null group by year(A), month(A), B order by year(A), month(A), sum(C) desc", 1)

To create a pivot table by month with categories as rows and months as columns:

=QUERY(A1:F, "select sum(C) group by B pivot year(A)*100 + month(A)", 1)

Regex filtering + QUERY for rapid cleanup

Combine REGEXMATCH or pre-clean with helper columns. Within QUERY, use matches (RE2 syntax) for pattern filters:

=QUERY(A1:F, "select * where B matches '^(Pro|Business)$'", 1)

For more complex normalization, create a helper column (e.g., =REGEXREPLACE(B2, "[^a-z0-9]", "")) and reference that column in where.

Dynamic dashboards with dropdowns + QUERY

Use Data Validation to build dropdowns for category, date range, or region. Then inject those cell values into your query string.

1. Create dropdowns in, say, G1:G3 (Category, Region, Year).
2. Build the query with concatenation:

=QUERY(A1:F, "select B, sum(C) where B='" & G1 & "' and D='" & G2 & "' and year(A)=" & G3 & " group by B label sum(C) 'Total'", 1)

For step-by-step dependent dropdowns, see our detailed guide: Dependent Dropdowns in Sheets .

Performance tips for large sheets

• Query a bounded range (e.g., A1:F50000) instead of whole columns to reduce scan time.
• Pre-calc helper columns (clean dates, normalized text) to avoid heavy string ops inside QUERY.
• Stack first, then query: For multi-sheet reports, {Sheet1!A:F; Sheet2!A:F; ...} and run a single QUERY.
• Minimize volatile functions (NOW, RAND) near your query ranges.
• Cache imported data in a hidden tab with a timestamped refresh strategy if using many IMPORTRANGE.

Apps Script: schedule email reports from a QUERY

Automate a daily email with a query result rendered as HTML.

function emailDailyReport() {
const ss = SpreadsheetApp.getActive();
const sheet = ss.getSheetByName('Report');
const range = sheet.getRange(1,1, sheet.getLastRow(), sheet.getLastColumn());
const html = HtmlService.createHtmlOutput(range.getDisplayValues() .map(row => '' + row.map(c => `$ {
c
}
`).join('') + '') .getContent()) .getContent();
const body = `$ {
html
}
`;
MailApp.sendEmail( {
to: 'team@example.com', subject: 'Daily KPI Report', htmlBody: body
}
);
}
function createTrigger() {
ScriptApp.newTrigger('emailDailyReport') .timeBased() .atHour(8) .everyDays(1) .create();
}

Tip: Keep the “Report” tab driven by QUERY so the email always reflects the latest filters and aggregations.

QUERY vs FILTER vs Pivot Table: when to use which

• QUERY: Best for one-formula summaries with grouping, sorting, pivots, and labels.
• FILTER: Best for simple row filtering—fast and readable for basic conditions.
• Pivot Table: Best for interactive exploration and quick ad-hoc analysis.

Common errors and how to fix them

• #VALUE! Type mismatch: Ensure numbers vs text comparisons are correct; wrap text in single quotes.
• Unknown column: Column letters refer to positions in data. If your range starts at C, then “C” is actually Col1 inside QUERY. Keep ranges aligned.
• Date parsing: Use date 'YYYY-MM-DD' or build with TEXT. Don’t rely on locale-specific formats.
• IMPORTRANGE permission: Call once to grant access; check the spreadsheet URL and named range.

Implementation guide: build a sales summary in 10 minutes

1. Prepare data: Ensure headers in row 1. Columns: Date (A), Region (B), Product (C), Amount (D).
2. Add dropdowns: Data → Data validation for Region (G1) and Year (G2).
3. Write the QUERY (in H1):
   

   =QUERY(A1:D, "select C, sum(D) where B='" & G1 & "' and year(A)=" & G2 & " group by C order by sum(D) desc label sum(D) 'Revenue'", 1)

4. Add a total: =SUM(INDEX(H2:I, , 2)) if your result is in H:I with amount in I.
5. Format: Currency for the amount column; add a sparkline:
   

   =SPARKLINE(INDEX(H2:I, , 2), {"charttype","column"})

Pro tips you’ll reuse everywhere

• Use labels to rename aggregate columns for clean headers.
• Wrap long queries in LET via Named Functions to keep sheets maintainable. See: Named Functions .
• Build parameterized queries from dropdowns to drive dashboards.
• Prefer bound ranges and avoid whole-column references for speed.

Recommended tools and deals

• AppSumo: Lifetime deals on data tools, reporting templates, and automation add-ons.
• Envato: Dashboard UI kits and icons to make your Sheets exports and slide decks shine.
• Hostinger: Spin up a lightweight webhook/API to feed Sheets or host Apps Script webhooks reliably.

Disclosure: Some links are affiliate links. If you click and purchase, we may earn a commission at no extra cost to you. We recommend tools we’d use ourselves.

From our library (related guides)

• Google Sheets Named Functions
• Dependent Dropdowns in Sheets
• XLOOKUP vs VLOOKUP in Sheets

Trusted sources and official docs

• QUERY function reference (Google): support.google.com/docs/answer/3093343
• Google Visualization API Query Language: developers.google.com/chart/interactive/docs/querylanguage
• Data validation in Sheets: support.google.com/docs/answer/186103
• IMPORTRANGE: support.google.com/docs/answer/3093340
• Apps Script: Triggers & MailApp: developers.google.com/apps-script/guides/triggers, MailApp

Frequently Asked Questions

What is the Google Sheets QUERY function used for?

It runs SQL-like statements on a range to filter, group, aggregate, sort, and pivot data in one formula.

How do I reference columns in a QUERY?

Use letters (A, B, C…) based on the position within the data argument, not the sheet’s absolute columns.

How do I filter by date in QUERY?

Use date 'YYYY-MM-DD' or build it with TEXT, e.g., where A >= date '-01-01'.

Can I do a JOIN in Google Sheets QUERY?

No native JOIN keyword. Combine ranges with { ... }, VLOOKUP, or XLOOKUP, or pre-join via IMPORTRANGE.

How do I make QUERY dynamic with dropdowns?

Insert cell values into the query string using concatenation (&) and data validation for inputs.

What are common errors in QUERY?

Type mismatches (#VALUE!), wrong column letters, and date parsing issues. Explicit headers and helper columns help.

Is QUERY faster than chaining FILTER/SUMIFS?

Often, yes—especially for grouped summaries and pivots. But bounded ranges and helper columns still matter.

Can I pivot in QUERY?

Yes, with pivot and a column function like month(A) or year(A) for columns.

How do I email a QUERY report daily?

Use Apps Script with a time-based trigger to render a QUERY-driven tab into HTML and send via MailApp.

Where do I learn the full query language?

See Google’s Visualization API Query Language docs for the full syntax and supported functions.