Filter by query lets you use SQL-like language to filter information transferred to your destinations. Use this to transfer data from specific columns, or if you want to apply a complex set of criteria.
Set up Filter by query
First you have to add a data processor to your automation.
Under Select a data processor, select Query.
The query editor will open, here you will write the query that you want to use to filter the data. This filter option uses SQL syntax and any function available in your source spreadsheet. Once you are done writing your query, click Done.
If you're unfamiliar with writing queries, look at the advanced query examples below and also check out Google's Query Language Reference.
Key clauses:
SELECT - Select specific columns.
WHERE - Set conditions to evaluate which rows to transfer.
LIMIT - Transfer only a fixed number of rows.
OFFSET - Skip a set number of rows from the top.
As an example, to select only columns A, K, and L, and also only where column A has the name "Jonatan", use the statement:
SELECT A, K, L
WHERE A = 'Jonatan'
To select column AS from a spreadsheet, apply single quotation marks ('AS'). Otherwise, it will be interpreted as a statement, not a column.
Advanced query examples
Select all rows and columns where column A contains the name, Jonathan:
SELECT * WHERE
A = 'Jonathan'
Select all rows but only columns B and C where column A contains the name Carl:
SELECT B, C
WHERE A = 'Carl'
Select all rows but only columns B and C where column A either contains the name Ted or Chad:
SELECT B, C
WHERE A = 'Ted' OR A = 'Chad'
Select all rows but only columns B and C where column A contains the name George and column B the name Chad:
SELECT B, C
WHERE A = 'George' AND B = 'Chad'
Select all rows but only columns B and C where column A contains the name George or Chad and column B a value that is bigger than 1000:
SELECT B, C
WHERE (A = 'George' OR A = 'Chad") AND (B > 1000)
Select the first 10 rows and columns B and C where column A contains the name George and column B the name Chad:
SELECT B, C
WHERE A = 'George' AND B = 'Chad'
LIMIT 10
Select a range (A10:B20):
SELECT A, B
LIMIT 10
OFFSET 10
Select all rows but only columns B and sort all rows by values found in column C in descending order:
SELECT B, C
ORDER BY C DESC
Select all rows but only columns B and sort all rows by values found in column C in ascending order:
SELECT B, C
ORDER BY C ASC
Select all rows but only columns B and the sum of C and D, and sort all rows by values found in column C in ascending order:
SELECT B, C+D
ORDER BY C ASC
Select the first 10 rows but only columns B and C and sort those by values found in column C in ascending order:
SELECT B, C
ORDER BY C ASC
LIMIT 10
Group all values in column A and for each of the values, build the sum of column B, count how many entries found in B, build the average of column B, find the maximum value of column B, and the minimum value of B:
SELECT A, SUM(B), COUNT(B), AVG(B), MAX(B), MIN(B)
GROUP BY A
Select all values in column A that contain gmail.com:
SELECT A
WHERE A CONTAINS 'gmail.com'
Select all values in column A that don't contain gmail.com:
SELECT A
WHERE NOT A CONTAINS 'gmail.com'
Select all rows from columns G, I, M and P that have a year greater than 2016 in column M:
SELECT G, I, M, P
WHERE YEAR(M) > 2016
Need more help?
Contact us via chat, on our email ([email protected]), or send us a ticket!
Visit our Community for help, tips, tricks, and feedback for Sheetgo.