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?

CodePudding user response：

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)

CodePudding user response：

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.