Triggers not working

44 views
Skip to first unread message

Jordan Lambert

unread,
Feb 27, 2024, 4:19:54 AMFeb 27
to Google Apps Script Community
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

unread,
Feb 27, 2024, 9:13:52 AMFeb 27
to google-apps-sc...@googlegroups.com

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.

Reply all
Reply to author
Forward
0 new messages