Exporting Data to Google Sheets

[Zam]

Looker will not be updating this content, nor guarantees that everything is up-to-date. 

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 post, 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 an ampersand (&). For example: 

=ImportXML("https://demo.looker.com/looks/ab1cd2.htmlapply_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.

[MichaelTomar]

You can use the Skyvia cloud platform to connect Looker to Google Sheets. It is a freemium app with no code.

Read more here
 

[Carl_Arnold]

To refresh your data, you can either manually update the ImportXML function or use a custom import function.

To manually update the ImportXML function, you can add certain URL parameters to the URL part of the function. For example, appending cachebust=123 to the URL creates a unique URL that effectively forces a request to the Looker server to retrieve fresh data. Other parameters can also be added, but these may affect the Look settings.

To automatically update the data, you can use a custom import function. The Looker Google Sheets Import Community post provides a workflow that can be used in lieu of ImportXML to refresh the crm data enrichment more reliably. However, it's important to note that this method is not officially supported by Looker.