Google Sheets – onEdit Stops Working with Other Functions

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I'm working on a script to help with a spreadsheet we use at work, but I'm having an annoying bug with the onEdit() function. After running the script from the script editor, it operates as intended (automatically sets all cells in range A3:F200 to a desired format). However, whenever I run the other function in the script called by a custom menu, called newDay(), the onEdit() function no longer does anything. Editing the spreadsheet does not reformat the range.

Here is the code:

  var logSheet = SpreadsheetApp.getActiveSpreadsheet();        
  var sheet = logSheet.getSheetByName('Repair');               
  var logRange = sheet.getRange("A3:F200");   
  var historySheet = SpreadsheetApp.openById('spreadsheetID')
  var interface = SpreadsheetApp.getUi();

function onOpen() {
  interface.createMenu("Tunnel")
  .addItem("New Day","newDay")
  .addToUi();
}

function onEdit() { 
  logRange = sheet.getRange("A3:F200");
  logRange
  .setFontSize(10)
  .setFontWeight("normal")
  .setFontColor("black")
  .setFontLine("none")
  .setFontFamily("Helvetica")
  .setBackground("#ffffff")
  .setHorizontalAlignment("center")
  .setBorder(true, true, true, true, true, true, "#cccccc", SpreadsheetApp.BorderStyle.SOLID); //formats cells into desired format
}

function newDay() {
  var shiftDate = sheet.getRange('B1').getDisplayValue();
  var result = interface.alert(
    "Reset Log",
    "This will clear the current log and reset forms for today's date.\n\nWould you like to record the current log?",
    interface.ButtonSet.YES_NO_CANCEL);

  if (result == interface.Button.YES) {
    sheet.copyTo(historySheet);
    var copySheet = historySheet.getSheetByName('Copy of Repair');
    copySheet.setName(shiftDate);
  }

  if (result == interface.Button.YES || result == interface.Button.NO) {
    logRange.clearContent();
    sheet.getRange('B1').setValue(Utilities.formatDate(new Date(), "GMT-8", "MM/dd/yy"));
  }
}

What is causing the onEdit() function to stop doing anything when newDay() is run?

Best Answer

It was the line

var historySheet = SpreadsheetApp.openById('spreadsheetID');

This requires authentication and therefore cannot be run by simple triggers such as onOpen() or onEdit(). moving the function into newDay() and setting up manual triggers through the edit menu resolved my issue.

Related Topic