Google Apps Script daily schedule trigger failing

Question

I have set up a function to call the Bitly API to track total clicks for my links. It is set up in a column to track clicks for each individual link. It updates perfectly every time I open the spreadsheet.

Here is the code for this

function bitlyStats(bitlink_url) {
  var bitlink_url_parts = bitlink_url.split('://');
  var bitlink = bitlink_url_parts[1];
  var accessToken = 'token';
  var fetchUrl = 'https://api-ssl.bitly.com/v4/bitlinks/' + bitlink + '/clicks/summary?unit=day&units=1';
  var headers = {
    'Authorization': 'Bearer '+ accessToken,
    'Content-Type': 'application/json',
  };
  var params = {
    'method' : 'get',
    'headers' : headers,
    'muteHttpExceptions' : true
  };
  var response = UrlFetchApp.fetch(fetchUrl, params);
  var clickCount = JSON.parse(response).total_clicks;
  return clickCount;
}

I've set up a second function to append these results to a second tab in the sheet each day. I have set up a trigger for BitlyStats to update overnight, and then to run the append function. However I get an error on the trigger for BitlyStats..

TypeError: bitlink_url.split is not a function at bitlyStats(BitlyStats:10:39)

This causes the append function to copy "#NAME?" in every cell on the second tab. I am not sure why there is an error when the function works find on open?

Any advice would be highly appreciated! Thanks.

EDIT After some feedback and a potential answer, it is clear that the time based trigger wont work with a function that references what is input in the cell. I am now trying to create an API call function for "bitlink_url" instead so it creates a list of bitlinks instead of the list already being in the sheet.

However I am running into issues with the new code, any advice would be appreciated. I am still very new to this. Thankyou! This is the reference guide I am using https://dev.bitly.com/api-reference#getBitlinksByGroup

The log shows "null"

function getBitly() {
  var accessToken = 'x'; //access token
  var groupID = 'x' ; //group ID
  var fetchUrl2 = 'https://api-ssl.bitly.com/v4/groups/' + groupID + '/bitlinks?size=10';
  var headers = {
    'Authorization': 'Bearer '+ accessToken,
    'Content-Type': 'application/json',
  };
  var params = {
    'method' : 'get',
    'headers' : headers,
    'muteHttpExceptions' : true
  };
  var response2 = UrlFetchApp.fetch(fetchUrl2, params);
  var bitlinkList = JSON.parse(response2).link;
  Logger.log(bitlinkList);

##EDIT2 - resolved## Thanks to @lamblichus I was able to revise the code and resolve the issue of having to reference each cell that the Bit.ly URL was saved in using the map function. Final code below. I have also incorporated the Append values code into the same function.

function getStats() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("VV Bitly Links");
  var bitlyurls = sheet.getRange(2,12,sheet.getLastRow()-1).getValues().flat();
  var clickCounts = bitlyurls.map(bitlink => [bitlyStats2(bitlink)]);
  sheet.getRange(2,6,clickCounts.length).setValues(clickCounts);
  var sheet2 = SpreadsheetApp.getActive().getSheetByName("Bitly Reporting");
  var data = sheet.getRange(2,6,sheet.getLastRow()-1,6).getValues();
  var lastrow = sheet2.getLastRow();
  sheet2.getRange(lastrow+1,1,data.length,data[0].length).setValues(data);
}

function bitlyStats2(bitlink) {
  var accessToken = 'xx'; //token
  var fetchUrl = 'https://api-ssl.bitly.com/v4/bitlinks/' + bitlink + '/clicks/summary?unit=day&units=1';
  var headers = {
    'Authorization': 'Bearer '+ accessToken,
    'Content-Type': 'application/json',
  };
  var params = {
    'method' : 'get',
    'headers' : headers,
    'muteHttpExceptions' : true
  };
  var response = UrlFetchApp.fetch(fetchUrl, params);
  var clickCount = JSON.parse(response).total_clicks;
  return clickCount;
}

function installTrigger() {
  ScriptApp.newTrigger("getStats")
  .timebased()
  .atHour(3)
  .everyDays(1)
  .create();
}

finally, since this references a lot of Bit.ly links, some of which aren't active any more and return 0 clicks in a day, I have created another function to delete empty cells from the second tab "Bitly Reporting"

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Bitly Reporting");
var range = sh.getDataRange();
var delete_val = ""
var col_to_search = 1;
function deleteEmptys(){
  var rangeVals = range.getValues();
  for (var i = rangeVals.length-1; i >=0; i--){
    if(rangeVals[i][col_to_search] === delete_val){
      sh.deleteRow(i+1);
    };
  };
};

function installTrigger2() {
  ScriptApp.newTrigger("deleteEmptys")
  .timebased()
  .atHour(5)
  .everyDays(1)
  .create();

Answer 1

Issue:

You cannot pass parameters to a function triggered through a time-driven trigger. Because of this, bitlink_url is undefined, so bitlink_url.split('://') gives an error.

Solution:

• Either set up a default parameter for bitlyStats:

function bitlyStats(bitlink_url = "YOUR_URL") {

• Or call a wrapper function instead in your trigger, so that bitlink_url is defined:

function timeTriggeredFunction() {
  var bitlink_url = "YOUR_URL";
  bitlyStats(bitlink_url);
}

Update:

You have a series of URLs in column A, and you want to update column B periodically, based on those URLs (using bitlyStats).

In this case, I'd suggest not using custom functions, which cannot be called via a time-driven trigger, but install a trigger that will call a function (named triggeredFunction in the sample below) that will:

• Retrieve the URLs from column A.
• For each URL, retrieve its clickCounts, using bitlyStats.
• Write the resulting clickCounts to column B.

Code sample:

function triggeredFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  var bitlink_urls = sheet.getRange(2,1,sheet.getLastRow()-1).getValues().flat();
  var clickCounts = bitlink_urls.map(bitlink_url => [bitlyStats(bitlink_url)]);
  sheet.getRange(2,2,clickCounts.length).setValues(clickCounts);
}

function installTrigger() {
  ScriptApp.newTrigger("triggeredFunction")
  .timeBased()
  .atHour(3)
  .everyDays(1)
  .create();
}

Note:

• In the sample above, the time-driven trigger is installed programmatically by running installTrigger once, and by this, triggeredFunction would run every day between 3 and 4 AM. You could also install it manually and modify the trigger settings according to your circumstances.
• In the sample above, it is assumed that the sheet with data is named Sheet1. Change that if it's not the case.