Question

Looker built Google Sheets import script

  • 26 January 2016
  • 40 replies
  • 1991 views


Show first post

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