-1

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?

  • A previous version in Revision History shows a lot of sheets. Maybe your spreadsheet was too complex and/or it hit some limit. – Rubén Aug 17 '16 at 3:48
  • I somewhat doubt that as it wasn't all that crazy, and many of those sheets had little to nothing on them (they were placeholders for future info). Also if I deleted them all, wouldn't the limit issue go away? It seems to be some sort of latency issue... even on a brand new sheet, your formula didn't calculate for about a minute. – Jonathan van Clute Aug 17 '16 at 3:49
  • The linked sheet is in the trash of the owner. – Rubén Aug 26 '16 at 17:48
  • Sorry, forgot it was linked on this question... fixed now. Check out the "ImportMethods" sheet to see all the various problems & failures. – Jonathan van Clute Aug 26 '16 at 17:51
  • Looks that your spreadsheet/account reach some limit that could be on the Google side or on the data source side. – Rubén Aug 26 '16 at 17:59
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.

  • 1
    Yeah unfortunately it's just too unpredictable to definitively conclude what the problem is. Seems that using these functions is just subject to unreliability, and that's just the way it goes. Thanks for the answer though. – Jonathan van Clute Aug 26 '16 at 19:12
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;
        }
    });
};
  • Wow very interesting, thanks for sharing! I will have to play with this at some point. =) – Jonathan van Clute Jan 28 '18 at 17:58
  • No problem. I'm planning on building a keyword rank tracker in Excel with it, it's potentially very powerful. – Josh Bradley Jan 28 '18 at 23:13
  • Very cool, how do you handle nested tags here? I have a child tag which is in the root but also in some of it's children. – Christoph Feb 12 '18 at 14:24
  • @Christoph You can update your regex to include parent tags too, something like <div id="important-element".*?<h3>(.*)</h3> Or, if it's not that simple, you can also customize the function to be for a single, specific use. Such as hard coding your match pattern, searching globally ("ig" instead of "i"), or passing the match index (html.match(regex)[ INDEX ];) as a parameter so you can match the specific instance of the element if you know it's always going to be the second appearance on the page. – Josh Bradley Feb 12 '18 at 19:00
  • @JoshuaBradley Thanks I will check it out! – Christoph Feb 13 '18 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.