Looker built Google Sheets import script

done
normal_priority
reply

(jon.allen) #21

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


(Jevan Nagarajah) #22

Hi Jon,

I’m also getting this error :frowning:

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

Thanks,
Jevan


(Izzy) #23

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!


(Ben Roberts) #24

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.


(Izzy) #25

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!


(Izzy) #26

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.


(Ben Roberts) #27

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 :slight_smile: ). Very grateful for you taking a look at this, as regex’s aren’t something I’ve worked with before.


(Izzy) #28

They always look like cartoon swear words to me!

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