Filters and Limits

In this article, we'll explain how to manage the data loaded from Google BigQuery into your Google Sheets using field filters and limit settings without altering the SQL code in the Query. The filter functionality is similar to the native filters in Sheets, while the limit functionality allows you to display only the specified number of top rows from the Query result.

Let's explore how to use filters and limits in the extension interface.

Filters

Important The Filter functionality is labeled as "Preview," indicating that the feature is ready for customer testing and has been publicly announced, but is not covered by the SLA.

Usage Scenarios

Imagine that data analysts can now prepare a comprehensive SQL Query that provides a wide dataset from BigQuery. Meanwhile, their colleagues without SQL knowledge, such as managers, can create dozens of reports in Sheets by specifying the Query and adding the necessary filters and limits through the interface, without waiting for a data analyst to load specific data.

Example 1: A sales lead wants to review the pages visited by qualified leads before filling out a form. An analyst has prepared the Query, but loading all the data into Google Sheets is not feasible. They need the ability to select data for individual users, which is easy to accomplish with Filters in the extension.

Example 2: A category manager wants to analyze the popularity of specific products in the list. It is necessary to explore them from different perspectives: ordered versus sold, most popular this month, most popular last month, etc. For this, they can create reports on separate sheets based on slightly different configurations of columns within the same Query and apply Filters by fields.

How It Works

The Filter functionality becomes available after loading data schema as a result of a successful Run of Query.

To select a filter for any field, follow these steps:

  1. Create a new Sheet document or open an existing one.
  2. Open the extension panel through the menu: Extensions → OWOX BI BigQuery Reports → Edit current report (or Create new report).
  3. Select the BigQuery project and Query, click the Run button, and wait for the Query to finish executing.

Now, you can add filters to any field in the table.

Available filter conditions by field types

BigQuery field type Filter conditions Description Example value
STRING Contains LIKE '%value%' value
Doesn’t contain NOT LIKE '%value%' unwanted_value
Equals to = 'value' value
Not equal to != 'value' different_value
Starts with LIKE 'value%' prefix_value
Ends with LIKE '%value' suffix_value
Is empty IS NULL  
Is not empty IS NOT NULL  
INTEGER, FLOAT64, and other Numeric types Greater than > value 15
-0.15
-23,45
Less than < value 15
-0.15
-23,45
Between BETWEEN value1 AND value2

value1: 10.
value2: 20.

Equals to = value 30,
23.5,
-4
Not equal to != value 40
Is empty IS NULL  
Is not empty IS NOT NULL  
BOOLEAN True = TRUE TRUE
False = FALSE FALSE
Is empty IS NULL  
Is not empty IS NOT NULL  
DATE, DATETIME,
TIME, TIMESTAMPT
Between BETWEEN value1 AND value2 value1: 2023-01-01 12:00:00.
value2: 2023-12-31 23:59:59.
Equals to = value 2023-01-01 12:00:00
Before < value 2023-01-01 12:00:00
After > value 2023-01-01 12:00:00
Not equal to != value 2023-01-01 12:00:00
Is empty IS NULL  
Is not empty IS NOT NULL  
Other types Is empty IS NULL  
Is not empty IS NOT NULL  

 

Adding a Filter

Watch the demo video below to see how to add filters without changing the SQL code. You can add multiple filters and then click the Run button, saving your Report Runs.

 

And now, let's go through the step-by-step instructions for adding filters.

Step 1. Click the “+ New filter” button.

filters-01.png

Step 2. Select a field from the list.

filters-02.png

Step 3. Select the Filter condition.

filters-03.png

Step 4. Depending on the selected Filter condition, fill in any additional fields that appear. For example, for “Equals to…”, you need to fill in the Value field.

filters-04.png

Step 5. All done, the filter has been added. Now you can add more filters if needed, and then click the Run button to execute the Query and load the data from Google BigQuery into Google Sheets.

filters-05.png

Removing a Filter

Just as simple as possible – click the cross icon next to the “Filter condition” field to remove a filter. Remember to update the data in the table by rerunning the Query using the Run button.

filters-06.png

 

Limits

The Limit functionality lets you specify the number of rows (starting from the first) you want to receive in the Sheets table.

 

For example, if your Query returns 1 million rows from BigQuery but only needs the first 100,000 rows, you can enter “100000” in the “Rows count” field and click the Run button.

limit-01.png

If you want to execute the Query without limits, leave the “Rows count” field empty.

limit-02.png

 

Scheduling Auto-Runs with Filters and Limits

The scheduling auto-runs feature works seamlessly with Filters and Limits. This allows you to automate data extraction processes, applying the necessary filters and limits without any additional setup. Whether you need filtered subsets of data or specific row limits, the auto-run functionality ensures your reports are always up-to-date with the exact data you require.

 

Note: The filter and limit functionalities modify the Query executed in BigQuery. Technically, your Query code is wrapped with additional code to add filters and limits to the Query result. It is important to note that complex SQL Queries (e.g. the WITH clause) may be incompatible with the filter and limit functionalities. In such cases, contact our support team or rewrite the SQL Query.

If you have any questions about the filter and limit functionalities, please write to us at bi@owox.com, and we will be happy to assist you.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.