Query Google Spreadsheets: A Complete Guide

The Pareto rule states that 20% of effort brings 80% of results. In the context of Google Tables, this rule is especially relevant: once you master the QUERY function, you can replace dozens of other functions and automate most routine data operations.

QUERY is a powerful data processing tool that uses simplified SQL syntax. Imagine that your table is a database, and QUERY allows you to make almost any kind of selections and transformations in it. You don't need to be a programmer or a database expert - you just need to learn a few basic principles.

The advantages of using QUERY are obvious. First, it is speed: a single QUERY formula can replace dozens of conventional formulas. Second, flexibility: you can easily modify the query by changing the sampling conditions or the way the data is processed. Third is performance: QUERY is much faster than combinations of standard functions.

Basic QUERY syntax

The basic structure of the QUERY function is as follows:

=QUERY(data; "query"; [headers])

Where:

• data - is the range of cells to be processed
• query - text of SQL-like query in quotes
• headers - optional parameter specifying the number of lines with headers

Let's consider a simple example. Suppose we have a sales table with columns: Date, Product, Quantity, Price. To get a list of all sales of a certain product, we can use:

=QUERY(A1:D100; "SELECT A, B, C, D WHERE B = 'Laptop'")

This query will return all rows where "Laptop" is specified in column B (Product).

Beginners often make a few common mistakes:

1. They forget to specify quotation marks around text values in conditions. Correct: "WHERE B = 'Laptop'", incorrect: "WHERE B = Laptop"
2. The syntax for referring to columns is confusing. QUERY can use either alphabetic designations (A, B, C) or Col1, Col2, Col3, but do not mix them in the same query
3. Incorrect handling of dates. To compare dates, you need to use the construct:

"WHERE A >= date '2024-02-19'".

When working with large datasets, it is important to remember that QUERY only processes the explicitly specified range. If you add new rows outside the range, they will not be counted in the results. So it is always better to use dynamic ranges or formulas like:

=QUERY(A1:A; "SELECT A WHERE A is not null").

The QUERY function also allows you to combine multiple conditions using AND and OR operators:

=QUERY(A1:D100; "SELECT A, B, C, D WHERE B = 'Laptop' AND C > 5")

This query will find all laptop sales with more than 5 units. Note the spaces in the query - they are necessary to properly separate the parts of the query.

Basic data operations

The QUERY function provides a wide range of tools for working with data. Let's start with basic data selection via the SELECT statement. This operator allows us to specify which columns we want to get as a result.

The simplest example of selecting all data:

=QUERY(A1:E100; "SELECT A, B, C, D, E")

You can use the "*" character to select all columns:

=QUERY(A1:E100; "SELECT *")

Data filtering is performed using the WHERE operator. For example, to sample sales for a certain period:

=QUERY(A1:E100; "SELECT * WHERE A >= date '2024-01-01' AND A <= date '2024-01-31'")

When working with dates, it is important to use the correct format. QUERY supports several comparison operators:

• = equal to

more

• < less

= greater than or equal to

• <= less than or equal to
• <> not equal

The results are sorted using ORDER BY. For example, sorting by date in reverse order:

=QUERY(A1:E100; "SELECT * ORDER BY A DESC")

Advanced features

QUERY becomes a particularly powerful tool when using aggregate functions. Basic aggregate functions include:

SUM() - summarizing values:

=QUERY(A1:E100; "SELECT B, SUM(C) GROUP BY B")

AVG() - average value:

=QUERY(A1:E100; "SELECT B, AVG(C) GROUP BY B")

COUNT() - counting the number of values:

=QUERY(A1:E100; "SELECT B, COUNT(C) GROUP BY B")

The PIVOT operator is used to create crosstabs. For example, to see sales by month and category:

=QUERY(A1:E100; "SELECT MONTH(A), SUM(D) GROUP BY MONTH(A) PIVOT B")

The calculated fields are created directly in the query. For example, to calculate the total cost:

=QUERY(A1:E100; "SELECT A, B, C, D, (C * D) WHERE C > 0")

When working with multiple ranges, you can use curly braces to combine the data:

=QUERY({Sheet1!A1:D100; Sheet2!A1:D100}; "SELECT Col1, SUM(Col4) GROUP BY Col1")

A particularly useful feature is the ability to create temporary calculations. For example, to calculate the percent of total:

=QUERY(A1:E100; "SELECT B, SUM(C), SUM(C)/SUM(SUM(C)) OVER() GROUP BY B"))

In this case, OVER() indicates that the calculation should be performed against the entire data set. Such calculations are particularly useful when creating analytical reports where comparisons with totals are required.

QUERY provides several useful operators for working with text data:

• CONTAINS - check for the content of a substring
• STARTS WITH - check for the beginning of a string
• ENDS WITH - check the end of the string

Example of using text search:

=QUERY(A1:E100; "SELECT * WHERE B CONTAINS 'iPhone' OR B STARTS WITH 'Mac'")

Formatting and presentation of results

QUERY results can be made more readable and understandable by using special formatting operators. The key tool here is the LABEL operator, which allows you to change column headings to more understandable names.

Let's consider an example of a sales report with column renaming:

=QUERY(A1:E100; "SELECT A, SUM(B), AVG(C)
LABEL A 'Date of Sale',
SUM(B) 'Total Amount',
AVG(C) 'Average Check'")AVG(C) 'Average Check'")

The FORMAT operator is used to format numeric values. It allows you to specify the output format for each column. For example, to display monetary values and percentages:

=QUERY(A1:E100; "SELECT A, B, B/SUM(B)
FORMAT B '#,##0.00 ₴',
B/SUM(B) '#.##%'")B/SUM(B) '#.##%'")

When working with dates, you often need to group by month or quarter. QUERY allows you to do this using built-in functions:

=QUERY(A1:E100; "SELECT MONTH(A), SUM(B)
GROUP BY MONTH(A)
LABEL MONTH(A) 'Month',
SUM(B) 'Sales per month'")SUM(B) 'Sales per month'")

Practical use cases

Let's look at a few real-world scenarios for using QUERY to solve business problems.

Analyze sales by category with growth dynamics:

=QUERY(Sales!A1:E100; "SELECT
B,
SUM(C),
SUM(C) - LAG(SUM(C)) OVER()
GROUP BY B
LABEL
B 'Category',
SUM(C) 'Total Sales',
SUM(C) - LAG(SUM(C)) OVER() 'Change'")

This query creates a report showing total sales by category and their change from the previous period.

Dynamic dates can be used to automate daily reports:

=QUERY(Data!A1:E100; "SELECT A, SUM(B)
WHERE A >= date '"&TEXT(TODAY()-7; "yyyyy-mm-dd")&"'
GROUP BY A
ORDER BY A DESC")

This query automatically shows the data for the last 7 days.

Create a summary table by manager and product:

=QUERY(Sales!A1:F100; "SELECT
C,
SUM(E)
GROUP BY C
PIVOT D
LABEL
C 'Manager'
FORMAT
SUM(E) '#,##0.00 ₴'")

For large datasets, it is useful to create subtotals:

=QUERY(Data!A1:E100; "SELECT
B,
D,
SUM(E),
SUM(E)/SUM(SUM(E))") OVER()
GROUP BY B, D
LABEL
B'Region',
D'Product',
SUM(E) 'Sales',
SUM(E)/SUM(SUM(E)) OVER() 'Percentage of total sales'
FORMAT
SUM(E) '#,##0.00',
SUM(E)/SUM(SUM(E)) OVER() '0.00%'')")

Such a query generates a detailed report broken down by region and product, including each combination's share of total sales.

Optimization and performance

When working with large datasets, QUERY performance becomes a critical factor. Tables containing more than 10000 rows can be processed much slower if the query is not optimized. Let's consider the main optimization techniques.

The first rule is to always limit the data range to only the necessary columns. Instead of sampling the entire range A1:Z1000, select only the columns you need. For example, if you only want sales and date data, use:

=QUERY(A1:B1000; "SELECT A, B WHERE A is not null")

Use pre-filtering to speed up date handling. Instead of:

=QUERY(A1:E1000; "SELECT * WHERE A >= date '2024-01-01'")

It is better to filter the data by date first, and then apply additional conditions:

=QUERY(FILTER(A1:E1000; A1:A1000 >= DATE(2024;1;1)); "SELECT Col1, Col2 WHERE Col2 > 0")

When working with large amounts of data, it is important to avoid redundant calculations. If you need to perform multiple operations on the same data, create an intermediate table using the first QUERY and then work with it:

=QUERY( QUERY(A1:E1000; "SELECT A, B, SUM(C) GROUP BY A, B"; "SELECT Col1, SUM(Col3) GROUP BY Col1" )

Conclusion and tips

QUERY is a powerful tool that can greatly simplify working with data in Google Tables. Based on practical experience, we can highlight several typical usage scenarios where QUERY is particularly effective.

For daily reports, it is recommended to create query templates with dynamic parameters:

=QUERY(Sales!A1:E1000;
"SELECT A, B, SUM(C)
WHERE A >= date '"&TEXT(TODAY()-30; "yyyyy-mm-dd")&"'
GROUP BY A, B
PIVOT B")=QUERY(Sales!A1:E1000; "SELECT A, B, SUM(C) WHERE A >= date '"&TEXT(TODAY()-30; "yyyyy-mm-dd")&"'' GROUP BY A, B PIVOT B"))

QUERY combines nicely with other Google Tables features. For example, you can use a combination of QUERY and SPARKLINE to create dynamic charts:

=SPARKLINE(
QUERY(A1:B100; "SELECT B WHERE A >= date '"&TEXT(TODAY()-7; "yyyyy-mm-dd")&"")
)=SPARKLINE( QUERY(A1:B100; "SELECT B WHERE A WHERE A >= date '"&TEXT(TODAY()-7; "yyyyy-mm-dd")&"")) )

When building complex reports, it is useful to break queries into several steps:

1. First, filter the data:

=QUERY(Data!A1:E1000; "SELECT * WHERE A is not null")

1. Then group the results:

=QUERY(FilteredData; "SELECT A, SUM(B) GROUP BY A")

1. Finally, format the output:

=QUERY(GroupedData; "SELECT * FORMAT Col2 '#,##0.00'")

Further study of QUERY is recommended to start with mastering more complex operations:

• Working with nested queries
• Using window functions
• Creating complex conditional groupings
• Using regular expressions in filters

Remember that QUERY's effectiveness depends not only on the correct syntax, but also on the structure of your data. Well-organized data with a clear structure will allow you to create simpler and more productive queries.