0

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?

1

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.

0

Simple and installable on edit triggers are triggered by actions done directly by the user on keyboard not by other functions.

Other functions that are triggered from a menu or other Ui elements or by other triggers like a time driven trigger could call the on edit function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.