Question

New Error in Looker Fetch Code when importing to GSheet

  • 22 June 2017
  • 2 replies
  • 98 views

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

2 replies

Userlevel 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

Hey Aleksandrs,


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


Gianluca

Reply