Google Sheets / Google Sheets Power User

Use the Query function

TRY IT OUT

The query function allows you to import data and add a SQL statement to specify exactly which data is imported into the sheet.

The following syntax is used

=QUERY(range, "SQL statement")

To extract all data from another sheet in the current workbook:

=QUERY(sheet2!A:G, "SELECT *")

To extract only specific columns from the data source - the first three columns in this case:

=QUERY(sheet2!A:G, "SELECT A, B, C")

To extract specific data from another sheet of the current workbook:

=QUERY(sheet2!A:G, "SELECT * WHERE E LIKE ‘%GATWICK%’")

In the above formula, the data in all rows of Sheet 2, columns A to G is queried, but only records that contain the word Gatwick in column E are returned.

The query function can be combined with the ImportData function to only return specific records from the data source:

=QUERY(IMPORTRANGE("SpreadsheetKey", range), "SELECT Col1, Col2, Col3 WHERE Col4 = ‘TRUE’")

In the above formula, the specified range in the spreadsheet will be queried, and only the first three columns returned, with all rows returned where the value in the fourth column is TRUE.