New Error in Looker Fetch Code when importing to GSheet


(Gianluca Uberti) #1

Hi,
I am heavy user of this script, that I use to import data from Looker to GSheet. The script was working fine until this morning when I got the following errror: “Error on line 5: The markup in the document following the root element must be well-formed. (line 4).” Anyone getting the same error? How can I fix it?

function lookerFetchHtmlTable(url) {
  url = url.replace(/(\/\w+\.)txt(\?)?/, "$1html$2");
  var xmlText = UrlFetchApp.fetch`Preformatted text`(url).getContentText();
  var document = XmlService.parse(xmlText);
  var root = document.getRootElement();
  
  var rows = root.getChildren("tr");
  var header = rows[0];
  var headercells = header.getChildren("th").map(
    function(t) { return t.getValue(); }
  );

  var bodycells = rows.slice(1, rows.length).map(function(el) {
    return el.getChildren("td").map(
      function(t) { 
        var val = t.getValue();
        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);
        };
        var strMatch = val.match(/[-a-zA-Z]/);
        if (strMatch) {
          return String(val);
        };
    
        val = val.replace(/[^\d]/g, '');
        return Number(parseInt(val));
      }
    );
  });

  var finalArray = [headercells];
  Array.prototype.push.apply(finalArray, bodycells);
  return finalArray;
}

(Aleksandrs Vedernikovs) #2

Hi @gianluca.uberti,

Thanks for bringing this up. Someone ran into this before. We have tweaked the script slightly. You would be able to find all the answers on this discourse article written by my colleague here:

https://discourse.looker.com/t/looker-built-google-sheets-import-script/2068/12


(Gianluca Uberti) #3

Hey Aleksandrs,

I updated my Gsheet with the new script, and all is working fine. Thanks for the help.

Gianluca