Triggers not working

[Jordan Lambert]

Hey, this is my first message to the community. I wondered if anyone can advise why my triggers, at the bottom of the script, are not executing. At the time they are supposed to execute, I see no errors or anything at all, they simply do not run. They appear in the Trigger section in my Google Apps Script UI, but they don't work. Previously I had all 4 triggers in a single function, though as that didn't work I decided to put them in separate functions but still to no avail. Here is the script, ignore the timings because I have been changing them around to schedule them for a few minutes ahead of the present time.

////////////////////////////////////////////////////////////

function marketClose() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NDI Stock Prices");

  var tickers = sheet.getRange("B1:AO1").getValues()[0];

  tickers.forEach(function(ticker, index) {

    var cell = sheet.getRange(3, index + 2);

    var value = cell.getValue();

    cell.setValue(value);

  });

}

///////////////////////////////////////////////////////////////

function marketOpen() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NDI Stock Prices");

  var returnSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NDI Return Ex Post");

  sheet.insertRowBefore(2);

  var tickers = sheet.getRange("B1:AO1").getValues()[0];

  var dateCell = sheet.getRange(2, 1);

  var returnDateCell = returnSheet.getRange("A3");

  var today = new Date();

  var holidays = [

    "2024-01-01", // New Year's Day

    "2024-01-15", // Martin Luther King Jr. Day

    "2024-02-19", // Presidents' Day

    "2024-03-29", // Good Friday date changes each year so change when next Good Friday is known

    "2024-05-27", // Memorial Day

    "2024-06-19", // Juneteenth National Independence Day

    "2024-07-04", // Independence Day

    "2024-09-02", // Labor Day

    "2024-11-28", // Thanksgiving Day

    "2024-12-25", // Christmas Day

    "2025-01-01", // New Year's Day of the next year for completeness

  ];

  // Adjust for weekends and holidays

  while (isWeekendOrHoliday(today, holidays)) {

    today.setDate(today.getDate() - 1); // Adjusting the date backwards to the last valid day

  }

  // Set the adjusted date after determining the correct day

  dateCell.setValue(today);

  returnDateCell.setValue(today);

  tickers.forEach(function(ticker, index) {

    var priceCell = sheet.getRange(2, index + 2);

    priceCell.setFormula('=GOOGLEFINANCE("' + ticker + '", "price")');

    var closeCell = sheet.getRange(3, index + 2);

    closeCell.setFormula('=GOOGLEFINANCE("' + ticker + '", "closeyest")');

  });

  // Delete last row in sheet, which is NDI Stock Prices worksheet

  var lastRow = sheet.getLastRow();

  if (lastRow > 250) sheet.deleteRow(lastRow);

  // Update NDI Return Ex Post sheet

returnSheet.insertRowBefore(3);

var weightsRange = "Weights";

for (var i = 3; i <= 250; i++) {

  var formulaCell = returnSheet.getRange("B" + i);

  formulaCell.setFormula('=ARRAYFORMULA(MMULT(TRANSPOSE(' + weightsRange + '),TRANSPOSE(C' + i + ':AP' + i + ')))');

}

  // Delete the last row in returnsSheet, which is NDI Return Ex Post worksheet

  var lastRowReturns = returnSheet.getLastRow();

  returnSheet.deleteRow(lastRowReturns);

  }

function isWeekendOrHoliday(date, holidays) {

  var dayOfWeek = date.getDay();

  var dateString = Utilities.formatDate(date, Session.getScriptTimeZone(), "yyyy-MM-dd");

  return dayOfWeek === 0 || dayOfWeek === 6 || holidays.includes(dateString);

}

/////////////////////////////////////////////////////////////

function updateReturnCalculations() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var stockPricesSheet = ss.getSheetByName("NDI Stock Prices");

  var returnsSheet = ss.getSheetByName("NDI Stock Returns");

 

  // Full list of tickers

  var tickers = ['SE', 'MELI', 'GRAB', 'CHWY', 'DASH', 'AMZN', 'TSLA', 'AVGO', 'QCOM', 'TSM', 'CSCO', 'FTNT', 'FIVN', 'MNDY', 'PANW', 'OSCR', 'QLYS', 'TENB', 'JAMF', 'SMCI', 'NU', 'DUOL', 'RMBS', 'TSEM', 'S', 'CPNG', 'GLBE', 'SQ', 'SOFI', 'CVNA', 'ROKU', 'COUR', 'OPEN', 'UPST', 'LMND', 'DLO', 'HIMS', 'INTC', 'LRCX', 'KLAC'];

 

  // The first row of returns starts from row 3 in the returns sheet

  var startRow = 3;

  var numRows = 248; // Number of rows of returns

 

  for (var i = 0; i < tickers.length; i++) {

    var column = i + 2; // Reference to the column number of the first ticker

    for (var j = 0; j < numRows; j++) {

      var row = startRow + j;

      var cell = returnsSheet.getRange(row, column);

      var formula = `=LN('NDI Stock Prices'!${columnToLetter(column)}${2+j}/'NDI Stock Prices'!${columnToLetter(column)}${3+j})`;

      cell.setFormula(formula);

    }

  }

 

  // Helper function to convert column index to Excel letter

  function columnToLetter(column) {

    var temp, letter = '';

    while (column > 0) {

      temp = (column - 1) % 26;

      letter = String.fromCharCode(temp + 65) + letter;

      column = (column - temp - 1) / 26;

    }

    return letter;

  }

}

//////////////////////////////////////////////////////////////

function updateTradingDays() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("NDI Stock Returns"); // Get the sheet

  var range = sheet.getRange("A3:A250"); // Define the range of dates

  var dates = range.getValues(); // Get the current dates in the range

  var holidays = [

    "2023-04-07", "2023-05-29", "2023-06-19", "2023-07-04",

    "2023-09-04", "2023-11-23", "2023-12-25", "2024-01-01",

    "2024-01-15", "2024-02-19"

  ]; // List of holidays

 

  Logger.log("Starting to update dates...");

 

  for (var i = 0; i < dates.length; i++) {

    if (dates[i][0]) { // Check if the date cell is not empty

      var date = new Date(dates[i][0]);

      var dayOfWeek = date.getDay();

      Logger.log("Original date: " + date.toDateString() + " (Day of week: " + dayOfWeek + ")");

     

      // Calculate the next business day, skipping Saturday and Sunday and holidays

      do {

        date = new Date(date.getTime() + (24 * 60 * 60 * 1000)); // Move to the next day

        dayOfWeek = date.getDay();

        var dateString = date.toISOString().split('T')[0];

      } while (dayOfWeek === 6 || dayOfWeek === 0 || holidays.includes(dateString));

     

      dates[i][0] = date; // Update the array with the new date

      Logger.log("Updated date: " + date.toDateString());

    }

  }

 

  range.setValues(dates); // Write the updated dates back to the sheet

  Logger.log("Finished updating dates.");

}

//////////////////////////////////////////////////////////////////

function createMarketCloseTrigger() {

  deleteTriggersByName('marketClose'); // Optional: Deletes existing trigger for this function

  ScriptApp.newTrigger('marketClose')

    .timeBased()

    .everyDays(1)

    .atHour(2) // Set to execute at 02:00

    .inTimezone(Session.getScriptTimeZone()) // Ensure the timezone is correctly set

    .create();

}

function createMarketOpenTrigger() {

  deleteTriggersByName('marketOpen'); // Optional: Deletes existing trigger for this function

  ScriptApp.newTrigger('marketOpen')

    .timeBased()

    .everyDays(1)

    .atHour(11)

    .nearMinute(45) // Set to execute at 18:50

    .inTimezone(Session.getScriptTimeZone())

    .create();

}

function createUpdateReturnCalculationsTrigger() {

  deleteTriggersByName('updateReturnCalculations'); // Optional: Deletes existing trigger for this function

  ScriptApp.newTrigger('updateReturnCalculations')

    .timeBased()

    .everyDays(1)

    .atHour(18)

    .nearMinute(51) // Set to execute at 18:51

    .inTimezone(Session.getScriptTimeZone())

    .create();

}

function createUpdateTradingDaysTrigger() {

  deleteTriggersByName('updateTradingDays'); // Optional: Deletes existing trigger for this function

  ScriptApp.newTrigger('updateTradingDays')

    .timeBased()

    .everyDays(1)

    .atHour(18)

    .nearMinute(52) // Set to execute at 18:52

    .inTimezone(Session.getScriptTimeZone())

    .create();

}

// Helper function to delete existing triggers by function name

function deleteTriggersByName(functionName) {

  var triggers = ScriptApp.getProjectTriggers();

  for (var i = 0; i < triggers.length; i++) {

    if (triggers[i].getHandlerFunction() == functionName) {

      ScriptApp.deleteTrigger(triggers[i]);

    }

  }

}

 

[Marco]

Você pode verificar no menu de execuções do script o por quê de não estar rodando seus acionadores. Outra coisa que você pode fazer é colocar um try-catch e tentar capturar algum erro.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/aedd71fb-c614-461b-af5f-7d3e3f0f9997n%40googlegroups.com.

PRESERVE A NATUREZA, EVITE O DESPERDÍCIO DE PAPEL E PENSE ANTES DE IMPRIMIR.

"Responsabilidade com o MEIO AMBIENTE"

Esta mensagem e quaisquer arquivos em anexo podem conter informações confidenciais e/ou privilegiadas. Se você não for o destinatário ou a pessoa autorizada a receber esta mensagem, por favor não leia, copie, repasse, imprima, guarde, nem tome qualquer ação baseada nestas informações. Por favor notifique o remetente imediatamente por e-mail e apague a mensagem permanentemente. Este ambiente está sendo monitorado para evitar o uso indevido de nossos sistemas.