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.
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:
- Navigate to the Look that you want to export to a Google Sheet. Click the gear menu and select Edit Settings.
- Copy the function under Google Spreadsheet. This function includes the
ImportXMLfunction that is built into Google Sheets.
- 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.