I'm trying to extract data from a list of >1000 URLs using a script that uses UrlFetchApp.fetch(url) and regex based on this article.

This is the code I'm using.

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 = {
  cent:  '¢',
  pound: '£',
  yen:   '¥',
  euro:  '€',
  copy:  '©',
  reg:   '®',
  lt:    '<',
  gt:    '>',
  mdash: '–',
  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;

and the importregex function formula I'm using is

=importRegex(A4, "<h1 class=""ch-title"".*?>(.*)<\/h1>")

It gives the following error

TypeError: Cannot read property '1' of null (line 9).

enter image description here

I'm not sure how to fix it.


1 Answer 1


First, custom functions can't be used on >1000 of formulas in a single spreadsheet because Google Apps Script limits. You might improve the custom function to make it able to process a range (an array of values) but the execution time should not exceed 30 secs.

Second, regular expressions have several limitations to parse HTML. For details see Using regular expressions to parse HTML: why not?

Regarding the error message, it's very likely that the site that you trying to scrape has measures to prevent that.

  • Thank you very much for clarifying that, I sort of figured out that regex has it's limits trying to scrap and that the site was blocking visits using UrlFetchApp.fetch(url), so I have a possible alternate solution using importxml, I've posted a different question in that regard at stackoverflow.com/questions/66293249/… Commented Feb 20, 2021 at 17:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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