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

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!!

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 🙂

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

I’m getting this error:


Any ideias on how to solve it?

Thanks izzy! It works perfectly.

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!

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.

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?


yes, here it is

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?

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


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.

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

Userlevel 7
Badge +1

They always look like cartoon swear words to me!


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

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

Oh, I just actually gave the script more thought and realized that my suggestion was exactly what the number val.match does already haha.


I messed around with some regex to try and match negatives and think I got it, although someone who actually knows what they’re doing might say I’m a doofus.


Check this out:


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]/)) {
if(val.match(/^-?\d*\.?\d+$/))
return Number(parseInt(val));
else
return String(val);

};

val = val.replace(/[^\d.]/g, '');

if (val.match(/^-?\d*\.?\d+$/))
return Number(val);

return Number(parseInt(val));
});
});
return dataOut;
}

Basically, I just added one more nested if in the initial string matching regex to catch any numbers and treat them as numbers, else returning the String(val).


if(val.match(/^-?\d*\.?\d+$/))
return Number(parseInt(val));
else
return String(val);

I gave it some testing and it seems to work fine.


Give it a shot, chime in with improvements if you’ve got em.

Userlevel 7
Badge +1

I wonder if you could just remove the String()?


if (val.match(/[-a-zA-Z]/)) {
return val
};

I feel like google sheets ought to be able to handle a negative number coming in… Maybe if that doesn’t work


you could allow it to run further and then wrap the value in a parseInt() function. I’m not the best at google apps language but I’ll try and give it a whirl later if I’ve got a sec!

Hi – we’ve been successfully using this script for a few months but have noticed that negative numbers are being exported as strings not integers which means they can’t be interacted with in GSheets. This is because of the leading hypen in this line:



if (val.match(/[-a-zA-Z]/)) {

return String(val)

};



So I’m able to diagnose the problem, but can’t figure out the re-write required to export negative numbers as integers. Any help appreciated.

Userlevel 7
Badge +1

Just spitballing off that little not-so-descriptive error it spat out, I’m wondering if this is related to something about the specific query/look that the script can’t handle-- specifically a subquery/CTE of some kind.


Does the Look you’re pulling have pivots, totals/row totals, or rely on a non-persistent derived table? Any of those could help narrow the scope of the issue with the script.


@jon.allen might be able to provide more wisdom too!

Hi Jon,


I’m also getting this error 😦


Would you be able to post a solution on here or perhaps PM me aswell?


Thanks,

Jevan

Hi Tushar,


Thanks for reaching out! I have emailed you directly to look into a couple things, so we can help troubleshoot this issue for you.


Cheers,

Jon

Hi Everyone,


I tried running this function. However, I am getting the following error:


SQL Error: SQL Syntax Error: SQL Compilation Error:

syntax error line 2 at position 0 unexpected ‘SELECT’.

syntax error line 2 at position 20 unexpected ‘(’.

syntax error line 2 at position 50 unexpected ‘)’.


Could someone please help me out with this? Thanks!

Userlevel 3
Badge

@Miranda_Short yeah I’d say that’s the best path forward. It doesn’t appear to be related to the same issue and it’d be best to get you some individualized attention. That way we can log in to the instance and see what is going on for ourselves

From what I can see, none of the measures are defined as type: list. With that said, do you suggest still reaching out to support?

Userlevel 3
Badge

Hey @Miranda_Short! I’m wondering if this is related to an issue that we are seeing with measures of type list. In these Looks, do you have any measures that are list aggregates and show up in the LookML with type: list defined?


This may be an issue best resolved by emailing us at help.looker.com, or by opening up the in-app chat, if you’re a developer.

Reply