Apps Script custom function working in script editor but not in Google Sheet custom function

Question

I have built a simple custom function in Apps Script using URLFetchApp to get the follower count for TikTok accounts.

function tiktok_fans() {
  var raw_data = new RegExp(/("followerCount":)([0-9]+)/g);
  var handle = '@charlidamelio';
  var web_content = UrlFetchApp.fetch('https://www.tiktok.com/'+ handle + '?lang=en').getContentText();
  var match_text = raw_data.exec(web_content);
  var result = (match_text[2]);
  Logger.log(result)
  return result
}

The Log comes back with the correct number for followers.

However, when I change the code to;

function tiktok_fans(handle) {
  var raw_data = new RegExp(/("followerCount":)([0-9]+)/g);
  //var handle = '@charlidamelio';
  var web_content = UrlFetchApp.fetch('https://www.tiktok.com/'+ handle + '?lang=en').getContentText();
  var match_text = raw_data.exec(web_content);
  var result = (match_text[2]);
  Logger.log(result)
  return result
}

and use it in a spreadsheet for example =tiktok_fans(A1), where A1 has @charlidamelio I get an #ERROR response in the cell

TypeError: Cannot read property '2' of null (line 6).

Why does it work in the logs but not in the spreadsheet?

--additional info--

Still getting the same error after testing @Tanaike answer below, "TypeError: Cannot read property '2' of null (line 6)."

Have mapped out manually to see the error, each time the below runs, a different log returns "null". I believe this is to do with the ContentText size/in the cache. I have tried utilising Utilities.sleep() in between functions with no luck, I still get null's.

code

  var raw_data = new RegExp(/("followerCount":)([0-9]+)/g);

  //tiktok urls
  var qld = UrlFetchApp.fetch('https://www.tiktok.com/@thisisqueensland?lang=en').getContentText();
  var nsw = UrlFetchApp.fetch('https://www.tiktok.com/@visitnsw?lang=en').getContentText();
  var syd = UrlFetchApp.fetch('https://www.tiktok.com/@sydney?lang=en').getContentText();
  var tas = UrlFetchApp.fetch('https://www.tiktok.com/@tasmania?lang=en').getContentText();
  var nt = UrlFetchApp.fetch('https://www.tiktok.com/@ntaustralia?lang=en').getContentText();
  var nz = UrlFetchApp.fetch('https://www.tiktok.com/@purenz?lang=en').getContentText();
  var aus = UrlFetchApp.fetch('https://www.tiktok.com/@australia?lang=en').getContentText();
  var vic = UrlFetchApp.fetch('https://www.tiktok.com/@visitmelbourne?lang=en').getContentText();

  //find folowers with regex
  var match_qld = raw_data.exec(qld);
  var match_nsw = raw_data.exec(nsw);
  var match_syd = raw_data.exec(syd);
  var match_tas = raw_data.exec(tas);
  var match_nt = raw_data.exec(nt);
  var match_nz = raw_data.exec(nz);
  var match_aus = raw_data.exec(aus);
  var match_vic = raw_data.exec(vic);

  Logger.log(match_qld);
  Logger.log(match_nsw);
  Logger.log(match_syd);
  Logger.log(match_tas);
  Logger.log(match_nt);
  Logger.log(match_nz);
  Logger.log(match_aus);
  Logger.log(match_vic);

Tanaike · Accepted Answer · 2021-11-30T05:36:23.213

Issue:

From your situation, I remembered that the request of UrlFetchApp with the custom function is different from the request of UrlFetchApp with the script editor. So I thought that the reason for your issue might be related to this thread. https://stackoverflow.com/a/63024816 In your situation, your situation seems to be the opposite of this thread. But, it is considered that this issue is due to the specification of the site.

In order to check this difference, I checked the file size of the retrieved HTML data.

The file size of HTML data retrieved by UrlFetchApp executing with the script editor is 518k bytes.
The file size of HTML data retrieved by UrlFetchApp executing with the custom function is 9k bytes.
- It seems that the request of UrlFetchApp executing with the custom function is the same as that of UrlFetchApp executing withWeb Apps. The data of 9k bytes are retrieved by using this.

From the above result, it is found that the retrieved HTML is different between the script editor and the custom function. Namely, the HTML data retrieved by the custom function doesn't include the regex of ("followerCount":)([0-9]+). By this, such an error occurs. I thought that this might be the reason for your issue.

Workaround:

When I tested your situation with Web Apps and triggers, the same issue occurs. By this, in the current stage, I thought that the method for automatically executing the script might not be able to be used. So, as a workaround, how about using a button and the custom menu? When the script is run by the button and the custom menu, the script works. It seems that this method is the same as that of the script editor.

The sample script is as follows.

Sample script:

Before you run the script, please set range. For example, please assign this function to a button on Spreadsheet. When you click the button, the script is run. In this sample, it supposes that the values like @charlidamelio are put to the column "A".

function sample() {
  var range = "A2:A10"; // Please set the range of "handle".
  var raw_data = new RegExp(/("followerCount":)([0-9]+)/g);
  var sheet = SpreadsheetApp.getActiveSheet();
  var r = sheet.getRange(range);
  var values = r.getValues();
  var res = values.map(([handle]) => {
    if (handle != "") {
      var web_content = UrlFetchApp.fetch('https://www.tiktok.com/'+ handle + '?lang=en').getContentText();
      var match_text = raw_data.exec(web_content);
      return [match_text[2]];
    }
    return [""];
  });
  r.offset(0, 1).setValues(res);
}

When this script is run, the values are retrieved from the URL and put to the column "B".

Note:

This is a simple script. So please modify it for your actual situation.

Reference:

Related thread.
- UrlFetchApp request fails in Menu Functions but not in Custom Functions (connecting to external REST API)

Added:

About the following additional question,

whilst this works for 1 TikTok handle, when trying to run a list of multiple it fails each time, with the error TypeError: Cannot read property '2' of null. After doing some investigating and manually mapping out 8 handles, I can see that each time it runs, it returns "null" for one or more of the web_content variables. Is there a way to slow the script down/run each UrlFetchApp one at a time to ensure each returns content?

i've tried this and still getting an error. Have tried up to 10000ms. I've added some more detail to the original question, hope this makes sense as to the error. It is always in a different log that I get nulls, hence why I think it's a timing or cache issue.

In this case, how about the following sample script?

Sample script:

In this sample script, when the value cannot be retrieved from the URL, the value is tried to retrieve again as the retry. This sample script uses the 2 times as the retry. So when the value cannot be retrieved by 2 retries, the empty value is returned.

function sample() {
  var range = "A2:A10"; // Please set the range of "handle".
  var raw_data = new RegExp(/("followerCount":)([0-9]+)/g);
  var sheet = SpreadsheetApp.getActiveSheet();
  var r = sheet.getRange(range);
  var values = r.getValues();
  var res = values.map(([handle]) => {
    if (handle != "") {
      var web_content = UrlFetchApp.fetch('https://www.tiktok.com/'+ handle + '?lang=en').getContentText();
      var match_text = raw_data.exec(web_content);
      if (!match_text || match_text.length != 3) {
        var retry = 2; // Number of retry.
        for (var i = 0; i < retry; i++) {
          Utilities.sleep(3000);
          web_content = UrlFetchApp.fetch('https://www.tiktok.com/'+ handle + '?lang=en').getContentText();
          match_text = raw_data.exec(web_content);
          if (match_text || match_text.length == 3) break;
        }
      }
      return [match_text && match_text.length == 3 ? match_text[2] : ""];
    }
    return [""];
  });
  r.offset(0, 1).setValues(res);
}

Please adjust the value of retry and Utilities.sleep(3000).

Thanks @Tanaike whilst this works for 1 TikTok handle, when trying to run a list of multiple it fails each time, with the error TypeError: Cannot read property '2' of null. After doing some investigating and manually mapping out 8 handles, I can see that each time it runs, it returns "null" for one or more of the `web_content` variables. Is there a way to slow the script down/run each UrlFetchApp one at a time to ensure each returns content? — racker, Nov 30 '21 at 04:03
@racker Thank you for replying. About your additional question of `Is there a way to slow the script down/run each UrlFetchApp one at a time to ensure each returns content?`, in this case, for example, how about modifying `var range = "A2:A10"` to `var range = "A2"`? By this, the script uses only the value of "A2". If I misunderstood your new question, I apologize. — Tanaike, Nov 30 '21 at 04:32
@Tanaike yes that works, however how would you achieve it for a range of more than 1 cell? — racker, Nov 30 '21 at 04:51
@racker Thank you for replying. When you want to use the above script for the multiple script, you can use it with `var range = "A2:A10"`. At that time, when you want to add the wait time, how about putting `Utilities.sleep(3000)` just after the line of `var match_text = raw_data.exec(web_content);`? By this, 3 seconds is waiting. When you want to change the 3 seconds, please modify `3000` of `Utilities.sleep(3000)`. The unit is ms. — Tanaike, Nov 30 '21 at 04:56
Thanks @Tanaike, i've tried this and still getting an error. Have tried up to 10000ms. I've added some more detail to the original question, hope this makes sense as to the error. It is always in a different log that I get nulls, hence why I think it's a timing or cache issue. — racker, Nov 30 '21 at 05:12
@racker Thank you for replying. In that case, I thought that the correct HTML data might not be able to be retrieved by the continuous requests. So for example, when you add `Utilities.sleep(3000)` and also modify `return [match_text[2]];` to `return [match_text && match_text.length == 3 ? match_text[2] : ""];` in my script, the error can be removed. And, how about running with the specific range by checking the empty cells? If this was not useful, I apologize again. By the way, about your 1st question of `Why does it work in the logs but not in the spreadsheet?`, how about this? — Tanaike, Nov 30 '21 at 05:21
@racker Now, I added one more sample script. In the additional sample script to your additional question, when the value couldn't be retrieved, the retry request is run. By this, the value is tried to be retrieved. In this sample, when the value cannot be retrieved by 2 retries, the empty value is returned. Could you please confirm it? If this was not useful, the reason for this issue is due to my poor skill. I apologize again. — Tanaike, Nov 30 '21 at 05:39
Thanks @Tanaike this is perfect, I am still learning Apps Script, self taught! This worked for me, I will keep testing but it looks to work every time now. Thanks again. — racker, Dec 02 '21 at 06:09
@racker Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, Dec 02 '21 at 07:32

score 0 · Answer 2 · answered Nov 26 '21 at 04:02

This works for me as a Custom Function:

function MYFUNK(n=2) {
  const url = 'my website url'
  const re = new RegExp(`<p id="un${n}.*\/p>`,'g')
  const r = UrlFetchApp.fetch(url).getContentText();
  const v = r.match(re);
  Logger.log(v);
  return v;
}

I used my own website and I have several paragraphs with ids from un1 to un7 and I'm taking the value of A1 for the only parameter. It returns the correct string each time I change it.

Apps Script custom function working in script editor but not in Google Sheet custom function

2 Answers2

Issue:

Workaround:

Sample script:

Note:

Reference:

Added:

Sample script:

Linked