Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Exporting Data to Google Sheets

Avatar
Zachary Michel
  • Updated
Follow

 

You can export data directly to Google Sheets using the Google Sheets action from the Looker Action Hub rather than implementing the method described in this article. See the Looker Actions — Google Sheets Help Center article for more information.

Looker supports exporting a public Look to Google Sheets. In this article, we present some functions that make the process smoother.

Basic setup

Looker admins must enable the Public URLs setting.

To obtain the function that enables you to import data from a Look into a Google Sheet:

  1. Navigate to the Look that you want to export to a Google Sheet. Click the gear menu and select Edit Settings.
  2. Copy the function under Google Spreadsheet. This function includes the ImportXML function that is built into Google Sheets.
  3. Paste the function into the upper-left cell of the Google spreadsheet where you would like your data to appear, and then press Enter.

Refreshing your data

The Google spreadsheet ImportXML function uses a caching mechanism that can make it difficult to predict when data will be refreshed in the Sheet.

Manual update: Google's import function

You can force a data update by adding certain URL parameters to the URL part of the ImportXML function. For example, appending cachebust=123 to the URL creates a unique URL — without changing the settings in the original Look — that effectively forces a request to the Looker server to retrieve fresh data, rather than retrieving from the browser cache. If there are no URL parameters yet, you'll need to add a question mark to the end of the URL first, like this:

  =ImportXML("https://demo.looker.com/looks/ab1cd2.html?cachebust=123", "//tr")
                                                       ^^^^^^^^^^^^^^^

Other parameters can also be added, but these may affect the Look settings. Separate any additional parameters with a &. For example:

  =ImportXML("https://demo.looker.com/looks/ab1cd2.html?apply_formatting=true&cachebust=123", "//tr")
                                                                             ^^^^^^^^^^^^^^^

Automatic update: custom import function

The Google spreadsheet ImportXML function uses a caching mechanism that can make it difficult to predict when data will be refreshed in the Sheet. See the Looker Google Sheets Import Community post for a workflow that can be used in lieu of ImportXML to refresh the data more reliably. Please note that this method is not officially supported by Looker.