Why won't importxml work on this sheet?

Question

I have a sheet, upon which I'm trying to use importxml() but it just won't work properly. Until moments ago, not a single one of the formulas on this sheet would load at all, they just hung on "Loading...", but just as I began this question, some of the cells began populating with data.

This is the follow-on from a previous question that turned out to be an issue with the XPath and how importxml() handles it. But that led me to this issue.

Can anyone see what is wrong with this sheet and why the function won't behave properly?

Answer 1

Google Sheets have processing limits. The official documentation doesn't describe them but it's possible to found a lot of user posts claiming that the import functions randomly returns an expected result while others the result is "Loading...", "Error: Loading data".

Something similar happens when the spreadsheet is very complex with other functions.

According to +Samantha, who has the Expert badge of the Google Docs Help Forum, in this post from May 12, 2016, there is no way to get around the loading limitations on import functions. She also mentions that Google Apps for Work accounts and other services like Google Cloud have other limits.

There are other services like Google Apps Script that clearly compares the limits between account types: Quotas for Google Services. In the case of Google Apps Script, the executive limit is around 6 minutes, but I think that the import functions have a comparatively very short processing limit. I didn't found something similar for Google Sheets built-in functions, so it's very likely that they could move at any time without any notice.

By the other hand, it's possible that the services holding the data to be imported have their own limits and could be blocking the IMPORTXML machines doing the import on behalf of the users.

Answer 2

I created a custom import function that overcomes all limits of IMPORTXML I have a sheet using this in about 800 cells and it works great.

It makes use of Google Sheet’s custom scripts (Tools > Script editor…) and searches through content using regex instead of XPath.

function importRegex(url, regex_string) {
  var html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html.length && regex_string.length) {
      var regex = new RegExp( regex_string, "i" );
      content = html.match(regex)[1];
    }
  }
  Utilities.sleep(1000); // avoid call limit by adding a delay
  return content;  
}

You can then use this function like any function.

=importRegex("https://example.com", "<title>(.*)<\/title>")

Of course, you can also reference cells.

=importRegex(A2, "<title>(.*)<\/title>")

If you don’t want to see HTML entities in the output, you can use this function.

var htmlEntities = {
  nbsp:  ' ',
  cent:  '¢',
  pound: '£',
  yen:   '¥',
  euro:  '€',
  copy:  '©',
  reg:   '®',
  lt:    '<',
  gt:    '>',
  mdash: '–',
  ndash: '-',
  quot:  '"',
  amp:   '&',
  apos:  '\''
};

function unescapeHTML(str) {
    return str.replace(/\&([^;]+);/g, function (entity, entityCode) {
        var match;

        if (entityCode in htmlEntities) {
            return htmlEntities[entityCode];
        } else if (match = entityCode.match(/^#x([\da-fA-F]+)$/)) {
            return String.fromCharCode(parseInt(match[1], 16));
        } else if (match = entityCode.match(/^#(\d+)$/)) {
            return String.fromCharCode(~~match[1]);
        } else {
            return entity;
        }
    });
};

All together…

function importRegex(url, regex_string) {
  var html, content = '';
  var response = UrlFetchApp.fetch(url);
  if (response) {
    html = response.getContentText();
    if (html.length && regex_string.length) {
      var regex = new RegExp( regex_string, "i" );
      content = html.match(regex)[1];
    }
  }
  content = unescapeHTML(content);
  Utilities.sleep(1000); // avoid call limit by adding a delay
  return content;  
}

var htmlEntities = {
  nbsp:  ' ',
  cent:  '¢',
  pound: '£',
  yen:   '¥',
  euro:  '€',
  copy:  '©',
  reg:   '®',
  lt:    '<',
  gt:    '>',
  mdash: '–',
  ndash: '-',
  quot:  '"',
  amp:   '&',
  apos:  '\''
};

function unescapeHTML(str) {
    return str.replace(/\&([^;]+);/g, function (entity, entityCode) {
        var match;

        if (entityCode in htmlEntities) {
            return htmlEntities[entityCode];
        } else if (match = entityCode.match(/^#x([\da-fA-F]+)$/)) {
            return String.fromCharCode(parseInt(match[1], 16));
        } else if (match = entityCode.match(/^#(\d+)$/)) {
            return String.fromCharCode(~~match[1]);
        } else {
            return entity;
        }
    });
};