Understanding the query language
The Query String in Google Sheets actions allows you to perform SQL-like queries to retrieve specific data from a spreadsheet. It is particularly useful for extracting data based on dynamic conditions that change according to the journey's trigger.
Basic components of a query string are:
- Select statement: Determines which columns to retrieve. For example, Select * retrieves all columns, whereas Select A, B retrieves only columns A and B.
- Where clause: Specifies conditions for filtering records. For instance, where A='OrderID' retrieves all rows where the value in column A is OrderID (can be a data pill).
- Order and Limit: Additional clauses can order the results (order by A DESC) or limit the results (limit 10).
Practical examples:
- Retrieving specific details: Query: Select where A = '001' Description: Retrieves all columns for rows where column A has the value '001'. Useful for locating details of a specific order.
- Filtering based on multiple conditions: Query: Select A, B where C > 100 AND D< 200 Description: Retrieves columns A and B for rows where column C is greater than 100 and column D is less than 200. Suitable for scenarios like finding transactions within a specific range.
- Ordering and limiting results: Query: Select * where E = 'Active' order by B DESC LIMIT 5 Description: Retrieves all columns for rows where column E is 'Active', orders them by column B in descending order, and limits the result to 5 rows. This can be used in inventory management to find the top 5 most recent active items.
Below is a table that summarizes various SQL-like query examples to search for users based on different criteria in a Google Sheet, with explanations for each column and query condition:
| Query description | SQL-like query | Explanation |
|---|---|---|
| Find complete information about a specific user by name | Select * where A = 'John Doe' | Retrieves all information for users named John Doe. A is the column header for Name. |
| Search users by email address | Select * Where B = '[email protected]' | Retrieves all information for users with the email [email protected]. B is the column header for Email. |
| Search users over 24 years old opting for M or XL shirts | Select_ Where C > '24' AND (D = 'M' OR D = 'XL') | Retrieves information for users over 24 years who prefer shirt sizes M or XL. C and D are the column headers for Age and Shirt_size, respectively. |
| Get all users and their information | Select * | Retrieves all information for all users. * denotes all columns in the sheet. |
Each query uses * to denote selecting all columns, while column letters A, B, C, D represent specific attributes like Name, Email, Age, and Shirt_size respectively.
Using data pills
In the query string, you can incorporate data pills from previous steps in the journey, allowing dynamic queries based on the journey's context. For example, if a previous step determined an Order ID, it could be inserted into the query to fetch related data.
String values are enclosed in double or single quotes. Numbers and booleans do not require quotes.
📚 Additional resources
Updated 6 days ago