How to use the Query filter

Learn how you can filter using SQL language

Eugenia Langen avatar
Written by Eugenia Langen
Updated over a week ago

4. How to use the Query filter?


One of the more powerful tools inside Sheetgo is the ability to use SQL-type language to filter the information from your source spreadsheet. You can do this by using the Query filter which allows you to extract only specific data. Use this option if you only want to transfer data from certain columns, or if you want to apply a complex set of criteria.

To use the filter by Query option you need to go to the Data processor box of the connection and select the Query option.

Now you can see the Query Editor, this is the box where you will write down the query that you want to use to filter the data. This filter option uses SQL syntax and any function that's available in your source spreadsheet. When you are finished writing down the query you want to use click Done.

If you're not familiar with writing queries, take a look at the advanced query examples below and also check Google's reference guide.

Key statements:

  • Select specific columns (SELECT).

  • Select specific rows within the columns (WHERE).

  • Limit the transfer to a fixed number of rows (LIMIT).

  • Skip a set number of rows from the top (OFFSET).

As an example, if you need to select only information in columns A, K, and L, and also only when column A has the name "Jonatan", use the statement: SELECT A, K, L WHERE A = 'Jonatan'.

When selecting column AS from a spreadsheet, apply single quotation marks `` like this: 'AS'. This is because "AS" will be interpreted as a statement, not a column.

Advanced query examples

Select a range (A10:B20):

SELECT A, B LIMIT 10 OFFSET 10

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 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.

Did this answer your question?