Question

Looker built Google Sheets import script

  • 26 January 2016
  • 40 replies
  • 2028 views


Show first post

40 replies

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

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

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

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.

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

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

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

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,



could you please share your revised script?



Thanks in advance,


GL

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!

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.

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

Reply