LinkedIn
Twitter
In the world of data, spreadsheets are king. This is why we partnered with the Google Workspace team to build Connected Sheets for Looker, which lets you explore modeled data from Looker through the familiar Google Sheets spreadsheet interface.
For Looker users, this brings the power and governance of LookML-modeled data to the humble spreadsheet. For Google Sheets users, connecting to a Looker model means opening up access to data from any of the 50+ databases supported by Looker.
Over the last several months, we have continued to enhance this connectivity with five new filtering capabilities that bring more of the power of LookML to the spreadsheet experience. You can now apply filters using more intuitive language and leverage customized filter options from a LookML model right within your pivot table. Let’s take a look at what’s new!
Filter by Value
Applying filters is easiest when you can see a list of values to select from. That’s exactly what this feature brings. Rather than typing in filter values manually, you can now see and select from a list of available values. And like standard Google Sheets filtering, you can search within the list of values, select individual values, select all values, or clear all values.
Filter Expressions
Support for filter expressions means that you can now apply much more sophisticated filters to your pivot table by using any of the filter expressions supported by Looker. This is likely to be most helpful when it comes to relative date filtering. Rather than selecting specific dates or ranges, you can now choose the Filter Expressions option and type in expressions like “last 12 days”.
In addition to relative date filtering, you can also use this to apply filters like:
- -FOO% to filter out any strong that starts with “FOO”
- (12, 20] to to filter down to a numeric range that excludes 12 but includes 20
For a full list of all available filter expressions, check out the Looker filter expressions documentation page.
Filter-only Fields
Filter-only fields are often used in Looker to inject dynamic filters into LookML code. This is often used to incorporate more user-interactivity into a LookML model by enabling a user’s selection to impact which database table gets queried or which dimension is inserted into a chart. Support for this type of field through Connected Sheets means that users can now have that same level of interactivity in their spreadsheets that they are used to having in Looker.
Parameters
Parameters are a type of filter-only field that enable users to inject specific, selected values into the SQL that Looker executes. These are often used to create interactive query results, labels, dynamic URLs, and more. In the example below, you’ll see a parameter value being used to change the granularity of the date field.
Filter-only fields and parameters will appear in the FILTER-ONLY FIELDS section of the field picker in Google Sheets.
Always Filters and Conditional Filters
The always filter and conditionally_filter settings are used within Looker Explores as a way of requiring users to include certain filters or sets of filters in their queries. These are commonly used for performance purposes, such as to ensure that users are including a partitioned date field filter. They can also be used to guide users toward an expected set of filters that they should be using.
In the example above, the Looker Explore always applies a default filter of “last 7 days” on the Order Date field. This filter appears in the new “Looker filter” section of the pivot table in Google Sheets. If a user then applies a new “last 20 days” filter to the Order Date field, this will override the default filter. In this scenario, Order Date must be used as a filter in any query, but the user has the ability to change the date range.
If you are using Looker and Google Sheets but haven’t enabled this connectivity yet, learn more about how to get started by checking out our documentation page. If you are already using Looker with Connected Sheets, check out these new filtering options and let us know what you think in the comments section!
