LinkedIn
Twitter
Important note: As of Looker 4.16 we’ve changed the below script. It was previously based on parsing HTML, which broke when we adjusted how we formatted html tables in Looker 4.16.
We’ve since switched to a csv parsing method, and the current script below should work for all versions of Looker.
Why we built this
Looker users have the ability to share results of Looks publicly. Amongst the public sharing options is the ability to import the data into a Google Sheet using the =ImportXML function. We’ve recently noticed a major hiccup in the process causing a majority of links to take up to 5 minutes even though the queries return in seconds or less.
After extensive research, we’ve been able to validate that there is a bug in Google’s spreadsheet functionality. We don’t yet have an estimated resolution time from Google, but we are working with them to add a solution to our product. In the meantime, we’ve developed this workaround.
The function and how to use it
In order to use the function you’ll need to add the script at the end of this post to the desired sheets’ scripts. Here are the steps required to get the script installed:
-
Make sure the look is public.
-
Head to Tools > Script Editor from the top section of the sheet.
-
Paste the code from the script at the end of this post into the script editor and save - you may give it whatever name you’d like. Close the script editor and refresh the Google Sheet.
-
Use the function
=lookerFetchData(url)within the cell where you’d like the table to start replacingurlwithin the parenthesis with either of the highlighted public url options below, wrapped in double quotes.
The Script
Quick Note: We previously named the function
lookerFetchHtmlTable, and have since changed it tolookerFetchData. If you’re 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;
}
An Example
Below is an example of our function on a Google Sheet, using a public url from learn.looker.com:
=lookerFetchData("https://learn.looker.com/looks/VpZ7XXfyxGmFCqbbXmsW58XC3PGNXYxc.txt?apply_formatting=true")
How to alter existing ImportXML links
In order to change your existing sheets all you need to do is change the function, for example:
=ImportXML("https://learn.looker.com/looks/VpZ7XXfyxGmFCqbbXmsW58XC3PGNXYxc.html?apply_formatting=true", "//tr")
Becomes
=lookerFetchData("https://learn.looker.com/looks/VpZ7XXfyxGmFCqbbXmsW58XC3PGNXYxc.html?apply_formatting=true")
Refresh AddOn
We’ve also built a Google AddOn to refresh both lookerFetchData and ImportXML links which can be found here.
