This article demonstrates how to use queries to fetch cell data in Google Sheets using Quickwork.
What you'll need:
Skill level: Intermediate
Time required: 10 minutes
- Scheduler by Quickwork: Trigger
- Google Sheets: Action
- Query writing and understanding skills to search for data
Let's create a sample Google Sheet that contains some information about the users. Name the sheet as Ecommerce customer list:
To execute a query in any of the tools, there are some key points to remember to avoid unwanted errors:
- It is necessary to have data stored in a spreadsheet in plain text format, especially dates. To do this, press CTRL + A and navigate to Format > Number > Plain text.
- Always pass the string and integer values in single quotes 'XYZ' while writing a query. E.g., select * Where A = 'Sam'.
- The column headers data (the title of columns) must not contain a space. Use underscore '_' to separate two words. E.g., Shirt_size as shown in the previous screenshot.
Configuring the trigger
- Under the Event section, choose the Scheduler by Quickwork app from the drop-down menu in the Apps field present right below the New Trigger button.
- Select the trigger event, New scheduled event, from the drop-down menu in the Triggers field.
- Set the Interval as per your choice based on how many times you want the journey to run.
- Set the date and time of your choice in the Start at field and keep the Custom payload field empty:
Configuring the Google Sheets-Search cell using query action
- Under the Steps section, click on the Simple Action button and choose the Google Sheets app from the drop-down menu present in the Apps field.
- Select the action, Search cell using query, from the drop-down menu in the Actions field.
- Authorize your Google Sheets account by clicking the Link an account button, selecting the Gmail account that contains the above spreadsheet, and allowing the set of permissions.
- A set of input fields will open. In the Spreadsheet field, select the Ecommerce customer list spreadsheet, which we had created earlier, from the drop-down menu.
- In the Sheet name field, select the sheet that contains the data of the spreadsheet. It is Sheet 1 in our case:
- In the Query string field, let's write a query that will help to search for data in the cell(s):
Operation Query Meaning To find complete information of a specific user by name Select * Where A = 'Jon Doe'
Select users whose Name is Jon Doe and return all the information.
Here, * denotes All and A denotes the name of the column header (which is Name in our sheet).
- Specify this query in the text format in the Query String field:
Save the changes done in the journey and click the Start journey button. Check the Output block of the Google Sheets action:
Operation Query Meaning To search users by email address Select * Where B = 'firstname.lastname@example.org'
Select users whose email is email@example.com and return all their information.
Here, * denotes All and B denotes the name of the column header (which is Email in our sheet).
Operation Query Meaning To search users whose age is equal to 25 years Select * Where C = '25'
Get the information of users whose age is equal to 25 years.
Here, * denotes All and C denotes the name of the column header (which is Age in our sheet).
Operation Query Meaning To search users whose age is greater than 24 years and opts for a shirt of M or XL size Select * Where C > '24' AND ( D = 'M' OR D = 'XL' )
Select users whose age is greater than 24 years and has opted for a shirt of M or XL size.
Here, * denotes All, and C and D denote the name of the column header (which is Age and Shirt_size in our sheet).
Operation Query Meaning Get all the users and their information Select *
Select and show all records
Here, * denotes All
Note: To study the clauses, conditions, syntax, and operators used in a query that Quickwork supports, click here.