Connected Sheets for Looker lets users interactively explore data from LookML models through the familiar Google Sheets spreadsheet interface. Sheets users will have access to governed data from 50+ databases supported by Looker. Exploration is intuitive since Connected Sheets presents an organized view of the model with views, fields, and measures just as they were defined in Looker. Looker data through Connected Sheets can be used in standard Sheets constructs, like pivot tables, charts, and formulas, allowing for flexibility of analysis through familiar constructs.
Enabling Connected Sheets
To use Connected Sheets:
- Your Looker admin must enable either the Looker BI Connectors or the Connected Sheets toggle on the BI Connectors panel in the Platform section of Looker's Admin menu.
- Your Looker instance must be Looker-hosted on Google Cloud.
- Your Looker instance must be running Looker 22.20 or later.
- Each user who wants to connect to your Looker instance from Google Sheets must use the same email address in their Looker user account as they use for their Google Workspace account. If their Workspace and Looker account email addresses are different, they will receive an error when they try to connect to Looker from Sheets.
- Access to services that are not controlled individually must be turned on in your Google Workspace admin console. This is typically enabled by default. For more information, see the Manage access to services that aren't controlled individually help article.
Connecting to Looker from Google Sheets
From the Google Sheets Data menu, select Data Connectors and then select Connect to Looker.
In the Connect to Looker dialog, enter the name of your Looker instance, or select the name of an instance to which you recently connected, and click Continue.
When Sheets connects to your Looker instance, Sheets will open a new sheet that shows the structure of the selected Explore, including views, dimensions, measures, and any field descriptions added by your Looker developer:
Building Looker queries using Google Sheets
Looker queries are built using a Sheets pivot table. To add Looker data to your sheet:
From the sheet that shows your Explore, click Pivot Table.
Select whether to insert a new pivot table in either a new sheet or an existing sheet, and click Create.
A new, empty pivot table is created, and the pivot table editor window opens. Here you can add rows, columns, values, and filters to build your query based on the dimensions and measures in your Looker Explore:
The rows, columns, values, and filters in the pivot editor are equivalent to your Looker dimensions and measures in the following ways:
- Rows are equivalent to Looker dimensions.
- Columns are equivalent to Looker pivoted dimensions.
- Values are equivalent to Looker measures. You can create a custom measure by selecting a Looker dimension and a Sheets Summarize by function.
- Filters are equivalent to Looker filters.
There is also a list of the views that are part of your Explore, which you can click on to display the dimensions and measures included in that view.
Click Add to add a row, column, value, or filter. Connected Sheets displays a list of the views that are available in your Explore. Click on a view name to show the list of the dimensions and measures in the view, and then select the field that supplies the data for your row, column, value, or filter. In the case of filters, you'll be able to specify how you want to filter the data on that field:
Once you've added your Looker fields and filters to build your query, click Apply. This will populate your sheet with data from your Looker instance:
Once you have added your Looker data to a pivot table, you can use regular Google Sheets functionality to format your data or build new Sheets formulas based on your Looker data.
Refreshing your data in Google Sheets
The data in your pivot table is static but can be refreshed any time. Refreshing your data will cause Looker to query your database and update your pivot table with your most current data.
Both your pivot table and the sheet showing your Explore display the time they were most recently refreshed:
Click Refresh to update the data in your pivot table.
Scheduling regular refresh times
You can also schedule automated updates of your data using the Scheduled Refresh dialog:
You can access the Scheduled Refresh dialog either by clicking the three-dot menu in your pivot table and selecting Refresh Options or by clicking Refresh Options in your Explore sheet.
Editing your pivot table settings
To modify which rows, columns, values, or filters are included in your pivot table, click the pencil icon next to the three-dot menu to open the pivot table editor panel.
Monitoring Connected Sheets
A Looker admin can view Connected Sheets usage using the Query API Client Properties group of fields in the System Activity History Explore. An entry is created in the History Explore every time a new query is run.
In the Query API Client Properties group of fields, there are three new fields for Connected Sheets:
- API Client Name — This field will always show
Connected Sheetsto identify Connected Sheets entries.
- Connected Sheets Spreadsheet ID — This field shows the sheet ID, which is included in the Google Sheets URL for that sheet.
- Connected Sheets Trigger — This field shows whether the query was triggered by a user clicking the Refresh button in Sheets or by a scheduled refresh.
Following is an example of a System Activity URL that shows Connected Sheets usage. Replace
<instance_name.looker.com> with your instance URL.
Things to know
- Case-sensitivity for the data shown in the pivot table is determined by the case-sensitivity setting for the model. Filters on the pivot table, however, force case-insensitivity in situations where the filter is case-insensitive for native Sheets data.
- Anyone who has Viewer access to the sheet will be able to view the cached data in a pivot table.
- In addition to Editor access to the sheet, any users who need to edit or refresh the pivot table will need the correct permissions in Looker to access the data model. This includes access to the model on which the pivot table is based, permissions to view the data, and proper access to the data through any access filters or access grants.
- If the user is authenticating with OAuth to a BigQuery database, they may need permissions to be granted on the underlying database.
Troubleshooting Connected Sheets
You may encounter some issues when you attempt to connect to Looker from Google Sheets. This section describes these issues and how to correct them.
Error: There was an error connecting to your Looker instance. Please try again. If the issue persists, please send feedback.
This error can occur for several reasons:
- The Looker instance name was entered incorrectly. In this case, verify your Looker instance name and try to connect again.
- You are trying to connect to a Looker instance that is not Looker-hosted on Google Cloud. Connect to a different Looker instance that is Looker-hosted on Google Cloud.
- Sheets cannot connect to the Looker API. This can occur if your Looker instance is not using the default API Host URL. To correct this, leave the API Host URL field in the API admin panel blank.
No models appear after connecting to a Looker instance
This indicates that you do not have the required permissions to view models on your instance.
A Looker user with the Viewer role does not have sufficient permissions to create connections in Sheets, just as they do not have sufficient permissions to use the Explore UI in Looker. If the user wants to use Connected Sheets for Looker, they need to have the
explore permission. The Admin, Developer, and User roles include the
explore permission. A Looker admin can grant the user the
explore permission, and that should enable the user to see models on the instance.
Error: Your linked Looker account email address is different than your Google account email address.
This indicates that your Looker account email address does not match the email address used with your Google account. Ensure that you are signed in to a Google account that has an email address that matches your Looker account. Alternatively, a Looker admin can create a new Looker user account that uses your current Google email address.
Error: Bad Request: no enable client found.
This error indicates that the Connected Sheets connector has not been enabled. A Looker admin must enable either the Looker BI Connectors or the Connected Sheets toggle on the BI Connectors panel in the Platform section of Looker's Admin menu.