Question

Looker built Google Sheets import script

  • 26 January 2016
  • 40 replies
  • 1375 views

Userlevel 4
Badge
  • Looker Staff
  • 75 replies

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, Zachary - this was really helpful and helped to quickly answer a request from our customer service team.


I’ve got an odd problem, though. There is a data column that is a text string, but the only part that the import is pulling through is -XX where XX is the first numeric text within the string. I’ve got a similar problem on another column as well, now that I take a closer look.


Can you think of any reasons why the format wouldn’t come through reliably? Any things I can test or check in the script or in sheets?


Cheers,

Justin

Got one of our devs to take a quick look. Was just a matter of changing around some of the script so that it wouldn’t prioritise strings last. I’ll go ahead and leave the comments up just in case others benefit from being aware of this. I appreciate you guys have to write scripts that are generic enough for most applications.

Userlevel 4
Badge

@Justin_DICE Apologies for the delayed response, was in the midst of a move to London. This script was really a first draft, and definitely requires some refinement. One of the things we’ve not tackled entirely is formatting. I’d be curious in your revision of the script if you’d be willing to pass it along!

Hi Justin,


could you please share your revised script?


Thanks in advance,

GL

Hi Gian Luca,


Here is what we changed as it relates to the body text (i.e., rest of the script remains the same). I’ve left the original script at the bottom for reference. As a reminder, our specific problem was that we had text strings that contained numbers, so we had to make sure that the import pulled the whole text string. I can imagine situations where the way we’ve done it isn’t appropriate for others.


var bodycells = rows.slice(1, rows.length).map(function(el) {
return el.getChildren("td").map(
function(t) {
var val = t.getValue();
var dateMatch = /(\d{4})\-(\d{2})\-(\d{2})/.exec(val);
if (dateMatch) {
// first index is full match
return new Date(dateMatch[1], dateMatch[2] - 1, dateMatch[3]);
}

var strMatch = val.match(/[a-zA-Z]+/);
if (strMatch) {
return String(val)
};

val = val.replace(/[^\d]/g, '');
return Number(parseInt(val));
// var numMatch = val.match(/\-?(?:\d+,?)+(?:\.\d+)?/);
// if (numMatch) {
// return Number(numMatch[0].replace(/,/g, ''));
//}
//return String(val);

Hi Justin,


thanks for sharing the script. This is really useful, especially in cases where I have date in the format 2015-01, where the old script showed only 2015.


Unfortunately though the script causes some values to show 0 values or NULL as errors in the format --> #NUM!


Did you guys find a solution to this problem?


Thanks a lot in advance,

Gianluca

Userlevel 4
Badge

@gianluca.uberti @Justin_DICE


I’ve updated the script in the OP, would you be open to testing it for me? Curious of your feedback.

Hi @Zam


I’ve been using this script recently to refresh our Google Sheets data from Looker. We have have found it to be much more reliable than the standard google spreadsheet =ImportXML function.


I wanted to shout out this script still works well and can be used as an option to export data from Looker.


Thanks for providing this to the community.

Daniel

Userlevel 4
Badge

@Daniel_L Thanks for the feedback, and glad it’s working reliably! It was a joint Looker effort to build the script, I was just lucky enough to post and own it 🙂


Don’t hesitate to let me know if there are any possible improvements you notice while using it!

Hi, I’m seeing the following error in Google Sheets:


Error on line 5: The markup in the document following the root element must be well-formed. (line 5).

My cell looks like this:


=LookerFetchHTMLTable("https://AAA.looker.com/looks/ZZZ.html?apply_formatting=true&apply_vis=true")

When I curl the above url, the structure looks like this:


<head>
<base href='https://generalassembly.looker.com'/>
<style>...</style>
</head>
<table>
...
</table>

There is no root element, which I believe to be the issue. Also note, the “<table>” tag appears on Line 5.


Has the generation of the public URLs payload changed in the most recent release?


Thanks,

Jon

Userlevel 4
Badge

@jon-ga


We’re working on this issue now. Thanks for pointing it out! It’s particularly tied to an adjustment we made to the .html returned by public looks. Will update this post asap.

Userlevel 4
Badge

@jon-ga and to anyone else following, we’ve fixed the script and I’ve adjusted the original post.


The good thing is the new script is backwards compatible. We’ll be sending out a note in our release notes (and adjust existing release notes) to warn of the breakage. Huge thanks for catching this @jon-ga !

Hello Zachary,


is your script lookerFetchData workng with the website content generated by a script (data loaded dynamically e.g. by javascript)?


virx

Userlevel 4
Badge

Heya @VIRX - not entirely sure I follow your question, but I read it as whether or not lookerFetchData will work with other data sets. If so, it could technically work with any url that returns values formatted as csv.


Whether or not the values will format properly is another question, as we’ve built functions in our script that formats data relevant to how Looker returns it.

I have this script successfully in two other spreadsheets using two different time-driven triggers for the refresh. However, I am using this script once more to import data every hour from two looks (two sheets obviously), and they keep erroring out! The only thing different about this scenario than the other two is that it’s one spreadsheet with two sheets, while the others are just one.


When trying to manually run my script, I get this error:

TypeError: Cannot call method "replace" of undefined. (line 2, file "fetchdata")

Timeout is also in my logs


Again, I’ve copy/pasted directly from both this site, as well as my original scripts to make sure there are absolutely no differences. I’ve tried busting cache as well. Here they are currently (the currently work, but if I check back in 20 minutes, they won’t work):

=lookerFetchData("https://barkandco.looker.com/looks/[redacted].txt?apply_formatting=true&apply_vis=true&refresh=1508430768885")


=lookerFetchData("https://barkandco.looker.com/looks/[redacted].txt?apply_formatting=true&apply_vis=true&refresh=1508430718598")


I have a feeling the problem is not within my script, but with the looks themselves, I just don’t know what that is.

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.

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

@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

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!

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 Jon,


I’m also getting this error 😦


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


Thanks,

Jevan

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

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!

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.

Reply