Exporting Data to Google Sheets

  • 22 June 2022
  • 0 replies
  • 3600 views

Userlevel 4
Badge

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.


0 replies

Be the first to reply!

Reply