Google Sheets Add-On onEdit(e) not working with Enabled Document

Question

I am trying to to create an Add On where the user has the option to either choose from the menu, or an onEdit command triggers a change to the cell in sheets. When I use the code below, attached to the sheet it works perfectly, however, when i test it as AUTH-LIMITED (Enabled or installed & enabled) the onEdit(e) functionality doesn't work. Everything about the menu button is working great, I can't figure out how to get the onEdit(e) called, what so ever.

I've tried searching all over but to no success with how to solve for this specific issue.

Thank you in advance!

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheeter = ss.getActiveSheet();
  sheeter.getRange("a1").setValue("Test");
  var range = e.range;
  var val = range.getValues();
  if(val.length+val[0].length<=2){
    var val = range.getValue();
    if(range.getFormula()){}
  else{
    var regex2 = new RegExp('[0-5]{0,1}[0-9]:[0-5]{0,1}[0-9].[0-5]{0,1}[0-9]$','g'); 
    var docContent2 =  val.replace(regex2,"00:"+val);
    range.setValue(docContent2);
    range.setNumberFormat("[M]:SS.0");
  }
 }

}



function Mass_Convert(){
  var now = Date.now();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();
  var val = range.getValues();
  var replaceBox = new Array(val.length);
  var replaceFormat =range.getNumberFormats();

    for(var i=0;i<val.length;i++){
    replaceBox[i] = new Array(val[i].length);
    replaceFormat[i] = new Array(val[i].length);

for(var j=0;j<val[0].length;j++){
  var newRange = range.getCell(i+1,j+1);
  var newVal = newRange.getValue();
  var format = newRange

  if(newRange.getFormula()){
    replaceBox[i][j]=newRange.getFormula();
    replaceFormat[i][j] = newRange.getNumberFormat();
  }

  else{
    if(isNaN(newVal)){
      var regex2 = new RegExp('[0-5]{0,1}[0-9]:[0-5]{0,1}[0-9].[0-5]{0,1}[0-9]$','g');           
      var docContent2 =  newVal.replace(regex2,"00:"+newVal);
      replaceBox[i][j] = docContent2;
      replaceFormat[i][j] = "[M]:SS.0"
    }else{
      replaceBox[i][j] = newVal;
      replaceFormat[i][j] = newRange.getNumberFormat();

    }
  }
 }
 }  
  range.setValues(replaceBox);
  range.setNumberFormats(replaceFormat);

}

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Split Conversion')
  .addItem('Mass Convert Selected', 'Mass_Convert')
  .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

utphx utphx · Accepted Answer · 2016-11-19T05:01:20

I tested this with my add-on and .getActiveSpreadsheet() gives the error:

Execution failed: The Add-on attempted an action that is not permitted in Test as Add-on mode. To use this action, you must deploy the Add-on

Also your onEdit() might have permission issues. There should be some condition to run on specific sheets otherwise it will run on all sheets.

I also found this: onEdit(e) not working in Add-on

Google Sheets Add-On onEdit(e) not working with Enabled Document

1 Answers