Question

Looker built Google Sheets import script

  • 26 January 2016
  • 40 replies
  • 1991 views

Userlevel 4
Badge

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:




  1. Make sure the look is public.




  2. Head to Tools > Script Editor from the top section of the sheet.





  3. 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.




  4. Use the function =lookerFetchData(url) within the cell where you’d like the table to start replacing url within 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 to lookerFetchData. 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.


40 replies

Thanks Izzy! Your logic of catching any numbers in the string matching regex was exactly what I was after… & I’ve edited the script for this & it is working on negative numbers now. Perfect (at least in terms of results 🙂 ). Very grateful for you taking a look at this, as regex’s aren’t something I’ve worked with before.

Userlevel 7
Badge +1

They always look like cartoon swear words to me!


Actual photo of me trying to get the regex to work:

I am having trouble running any report with the lookerfetchdata - I always get error loading data forever, could anyone advise?

Userlevel 7
Badge +1

I wonder if it’s a problem with your script, or a problem with your Look— The best way to figure that out is to try an example look!


If you try =lookerFetchData("https://play.looker.com/looks/tgtRGqj5nBd3yHSzSd6VxXsSD7rxJMGh.gsxml?apply_formatting=true&apply_vis=true", "//row") does that work, or throw a similar error? That’s an example Look that I just made public and verified works on my end.

Hi Izzy, thanks for your response. I’m still getting the same error (see pic)


Userlevel 7
Badge +1

Hmm well in that case, the problem must lie in the script somewhere. Did you copy and paste from the original post in this thread?

yes, here it is

in fact, the report is now working although is still very slow. In some cases I am now getting a new error message, any idea how to proceed?


Userlevel 7
Badge +1

Ah, interesting— If it’s a super big report, then it makes sense it would be taking a long time to load and look “errored”. Additionally, if the report is too big, there’ll be an internal error thrown by google sheets, usually because of a timeout on their end.


Google Scripts enforce a 30s timeout, I think, so if the query takes longer than that, you may want to add row limits or filter it down to make it smaller.

Userlevel 7
Badge +1

@efeerturk I heard you were having some trouble with a situation where you had year-month-string data being imported into Gsheets as an epoch or something— Basically, being incorrectly date matched. I looked at the date matching regex in the script and made a quick addition to make it a little bit more judicious about what it considers a date/not a date. I might have botched something, but in some quick tests this seems to work well for your situation:


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;
}

The key change being


 var dateMatch = /(\d{4})\-(\d{2})-*(\d{0,})/.exec(val);

to


var dateMatch = /(\d[^a-z]{4})\-(\d{2})-*(\d{0,})$/.exec(val);

Regex is genuinely mystical to me so this might be anything but perfect, but it works!

Thanks izzy! It works perfectly.

I’m getting this error:


Any ideias on how to solve it?

I also get the same error as above! If there could be an update that would be greatly appreciated!

Userlevel 7
Badge +1

Hey gang, I believe this one is not currently supported anymore.


You may want to check out the new Google Sheets action: https://help.looker.com/hc/en-us/articles/360044002214


If that doesn’t fit your use case, drop a note here and we can troubleshoot this appscript code together 🙂

It’s unfortunate that this function is no longer supported. The Looker action for google sheets is nice, but i have not found a way to integrate multiple looks into one sheet this way. Is there a workaround for that? the “ImportXML” feature in GS has been unreliable, which is why I’m looking for alternatives.

 

Thanks!!

Reply