Looker actions – Google Sheets

Note: Starting in Looker 23.4, you can connect Google Sheets to Looker directly using the Connected Sheets for Looker feature. This feature lets users interactively explore data from LookML models through the familiar Google Sheets spreadsheet interface. Sheets users will have access to governed data from the databases that are 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 presents Looker data in standard Sheets constructs, such as pivot tables, charts, and formulas, allowing for flexibility of analysis through familiar constructs. For information, see the Using Connected Sheets for Looker documentation page. For an overview of all the ways that Looker integrates with Google Sheets, see the How Looker integrates with Google documentation page.

Customer-hosted instances may be unable to enable actions from the Looker Action Hub, especially actions that support streamed results or that use OAuth, if the customer-hosted Looker instance does not fulfill the Looker Action Hub requirements.

See the Sharing data through an action hub documentation page for suggested solutions to this potential issue.

Does your work require you to spend a lot of time in Google Sheets? You can use Looker's secure, OAuth-based action to send CSVs directly from Looker to Google Sheets on a one-off or recurring basis!

The Google Sheets action is integrated with Looker through the Looker Action Hub. Once the Looker admin has enabled the Google Sheets action in the Action Hub, users can select Google Sheets as a possible destination when sending or scheduling Looks or Explores.

This page walks admins through enabling the Google Sheets action in Looker. It also describes how any user with the appropriate permissions can send or schedule deliveries in CSV format to a Google Sheet.

Enabling the Google Sheets action in Looker

Looker admins can enable the Google Sheets action in Looker with the following steps:

1. Go the Admin panel and, under Platform, go to the Actions page.
2. On the list of Action Hub actions, scroll to Google Sheets and click the Enable button.
3. On the Google Sheets action page, toggle on Enabled and click Save.
4. When you return to the list of Action Hub actions, your Google Sheets action should be enabled!

You and your users — if they have send_to_integration permissions — can now send or schedule Looks or Explores in CSV format to a Google Sheet.

Delivering data in CSV format to a Google Sheet

Any Looker user with send_to_integration permissions can send or schedule Looks or Explores in CSV format to a Google Sheet.

Google Sheets are limited to 10 million cells for the entire Sheet. If your data table has columns, rows, or tabs that exceed this limit, any deliveries to the Google Sheet integration will fail.

To prep your data before sending or scheduling:

• Horizontal scaling: If your data table contains more than 26 columns and you plan to import the data to an existing Google Sheet, you'll need to manually expand the Google Sheet before you can import your data into it.
• Vertical scaling: If your data table contains more than 1,000 rows, Looker will add those rows to your Google Sheet by default.
• Overwriting: If you're going to import your data into an existing Google Sheet, write any formulas on a separate tab of your Google Sheet to preserve the logic in your formulas. If you utilize the Google Sheets action's overwrite function, the action dynamically updates your Google Sheet and your data will automatically be loaded into the first or leftmost tab of your Google Sheet. If you'd prefer to create a new spreadsheet with each data delivery so that you have a full history of your data and can track changes over time, do not use the Google Sheets action's overwrite function.

To send or schedule your content:

1. From the Scheduler, next to Where should this data go?, select Google Sheets as your delivery destination.
2. If you're delivering to Google Drive for the first time, you'll need to authenticate with your Google credentials. Click Login, specify your Google account, and then click Allow to connect your Looker account to your Google OAuth credentials. You can connect only one set of Google Oauth credentials to this integration.
3. In the Scheduler, click Verify credentials to load your Google Drive.
4. From the Select Drive to save file drop-down, choose the Google Drive where your CSV file will be saved.
5. From the Select folder to save file drop-down, choose the Google Drive folder where your CSV file will be saved.
6. Specify the name of your Google Sheet file in the Enter a name field. You do not need to include the file extension — the Google Sheets action will automatically append it during delivery.
7. In the Overwrite Existing Files drop-down, select Yes or No. Selecting No will generate a new Google Sheet with a snapshot of your data with every data delivery. Selecting Yes will load your data in the first or leftmost tab of your existing Google Sheet.

Due to Google Sheets cell limits, if you choose the overwrite option, keep in mind that the tabs added during each delivery cannot cause the Sheet to exceed the 10 million cell limit. Once the cell limit is exceeded, subsequent schedules will fail.

8. Advanced options for CSVs include customizable limit and format. Click Send or Schedule.
9. Refresh your Google Drive folder to see your file delivery.