Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

Looker-Built Google Sheets Import Script

Avatar
Zachary Michel
  • Updated
Follow
Starting in Looker 7.4, you can export data directly to Google Sheets using the Google Sheets action from the Looker Action Hub. See the Looker Actions — Google Sheets Help Center article for more information.

In Looker, we have developed an import script to build your Looker Google Sheets. This article walks you through the steps for using it.

The Function and How to Use It

To use the function, you will need to add the script included below to the desired Sheets' scripts. Here are the steps required to get the script installed:

  1. Ensure that the Look is public.
  2. From the top section of the Google Sheet, select Tools > Script Editor.
  3. Copy the code from The Script section below; paste it into the Google Script Editor; and then save the script. You can give the script any name you'd like.
  4. Close the script editor and refresh the Google Sheet.
  5. Use the =lookerFetchData(url) function within the cell where you'd like the table to start replacing url within the parentheses, with either of the public URL options highlighted below, wrapped in double quotes. (Be sure to copy the full URL.)

The Script

Quick Note: We previously named the function lookerFetchHtmlTable and have since changed it to lookerFetchData. If you are a user of the legacy function name, you may still use it if you wish as long as you replace the rest of the method.

function lookerFetchData(url) {
 url = url.replace(/(\/\w+\.)txt|html|csv|gsxml(\?.*)?/, "$1csv$2");
 var csvString = UrlFetchApp.fetch(url).getContentText();
 var dataIn = Utilities.parseCsv(csvString);
 var dataOut = dataIn.map(function(row) {
 return row.map(function(val) {
 if (val == '') return '';
 var dateMatch = /(\d{4})\-(\d{2})-*(\d{0,})/.exec(val);
 while (dateMatch != null) {
 // first index is full match
 return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3] || 1);
 };
 if (val.match(/[-a-zA-Z]/)) {
 return String(val)
 };
 
 val = val.replace(/[^\d.]/g, '');
 
 if (val.match(/[0-9.]+/))
 return Number(val);
 
 return Number(parseInt(val));
 });
 });
 return dataOut;
}

Example

Here is an example of our function on a Google Sheet, using a public URL from learn.looker.com:

Note: For users who do not currently have login access to learn.looker.com, please reach out to Support to obtain credentials.

=lookerFetchData("https://learn.looker.com/looks/VpZ7XXfyxGmFCqbbXmsW58XC3PGNXYxc.txt?apply_formatting=true")

How to Alter Existing ImportXML Links

To change your existing Sheets, you only need to change the function.

For example, the following code:

=ImportXML("https://learn.looker.com/looks/VpZ7XXfyxGmFCqbbXmsW58XC3PGNXYxc.html?apply_formatting=true", "//tr")

Changes to this:

=lookerFetchData("https://learn.looker.com/looks/VpZ7XXfyxGmFCqbbXmsW58XC3PGNXYxc.html?apply_formatting=true")

Related articles