Skip to main content

How to search cell data using query?

Quickwork
  • Updated

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

Prerequisites

Let's create a sample Google Sheet that contains some information about the users. Name the sheet as Ecommerce customer list:

unnamed.png

To execute a query in any of the tools, there are some key points to remember to avoid unwanted errors:

  1. 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.
  2. Always pass the string and integer values in single quotes 'XYZ' while writing a query. E.g., select * Where A = 'Sam'. 
  3. 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

  1. 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.
  2. Select the trigger event, New scheduled event, from the drop-down menu in the Triggers field.
  3. Set the Interval as per your choice based on how many times you want the journey to run.
  4. Set the date and time of your choice in the Start at field and keep the Custom payload field empty:

mceclip0.png

Configuring the Google Sheets-Search cell using query action

  1. 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.
  2. Select the action, Search cell using query, from the drop-down menu in the Actions field.
  3. 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.
  4. 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.
  5. In the Sheet name field, select the sheet that contains the data of the spreadsheet. It is Sheet 1 in our case:

    mceclip0.png
  6. 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).
  7. Specify this query in the text format in the Query String field:

    mceclip12.png

    Save the changes done in the journey and click the Start journey button. Check the Output block of the Google Sheets action:
    mceclip11.png

    Operation  Query Meaning
    To search users by email address Select * Where B = 'jenny@gmail.com'

    Select users whose email is jenny@gmail.com and return all their information.

    Here, * denotes All and B denotes the name of the column header (which is Email in our sheet).

    mceclip2.png

    Output:

    mceclip9.png

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

    mceclip4.png
    Output:
    mceclip8.png


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


    mceclip3.png

    Output:
    mceclip7.png


  10. Operation  Query Meaning
    Get all the users and their information Select *

    Select and show all records

    Here, * denotes All

    mceclip5.png

    Output:

    mceclip6.png

Note: To study the clauses, conditions, syntax, and operators used in a query that Quickwork supports, click here.

Related articles

Comments

0 comments

Please sign in to leave a comment.