Looker built Google Sheets import script


(Zachary Michel) #1

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.


Is IMPORTXML frustrating you? Here is a better method to refresh public looks in Google Sheets
Google Sheets - Index of topics
Run Looks from Google Spreadsheets using Looker API
Looker 4.16 Release Notes
(Justin Michaels) #2

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


(Justin Michaels) #3

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.


(Zachary Michel) #4

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


(Gianluca Uberti) #5

Hi Justin,

could you please share your revised script?

Thanks in advance,
GL


(Justin Michaels) #6

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

(Gianluca Uberti) #7

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


(Zachary Michel) #8

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


(Daniel Lee) #9

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


(Zachary Michel) #10

@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 :slight_smile:

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


(Jonathan Kaczynski) #11

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


(Zachary Michel) #12

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


New Error in Looker Fetch Code when importing to GSheet
(Zachary Michel) #13

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


(Virx) #14

Hello Zachary,

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

virx


(Zachary Michel) #15

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.


(Miranda Short) #16

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.


(Max Corbin) #17

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 support@looker.com, or by opening up the in-app chat, if you’re a developer.


(Miranda Short) #18

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?


(Max Corbin) #19

@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


(Tushar Agarwal) #20

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!