Google Sheets Workaround Internal Error

done
api
low_priority
reply

(Charles Benkendorf) #1

Hi,

I have made a look public, created a blank google sheet, and used following code

=lookerFetchHtmlTable("mypublicurl")

However, Google sheet says “Internal error executing custom function”. Real helpful. Any ideas what this could mean?

the code for the function is (got from looker discourse)

function lookerFetchHtmlTable(url) {
      url = url.replace(/(\/\w+\.)txt(\?)?/, "$1html$2");
      var xmlText = UrlFetchApp.fetch(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-Z0-9]/);
        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;
}

(cvonk) #2

The API has a default quota of 100 reads/day. After that it throws a meaningless error message such as Internal Error Encountered. I suddenly encountered these errors, even though I didn’t change any of my code [github].


(Izzy) #3

Just to clarify for any onLookers: The Google Sheets API has max quotas (listed here: https://developers.google.com/sheets/api/limits), not the Looker API. You can Look to your hearts content, as many times a day as ya want :slight_smile:

Looks like the limits, per that page, are “500 requests per 100 seconds per project, and 100 requests per 100 seconds per user” with the note that “There is no daily usage limit,” so you might be hitting that instead.