I have issues with onEdit() functions

Question

I'm a beginner in Google sheet app script as I'm needing it for the first time now in my internship. I have looked for answers to my questions on many other topics but i have not find the holy answer I'm looking for.

Basically what i want to do is that i have a spreadsheet to manage the stocks and orders of the company I'm working in. So I'm slowly trying to improve it and I want to use several function that are supposed to be triggered with an onEdit(). My issue is that, individually I've tried the functions and they are working, but when i put them in the same onEdit() function there is one that doesn't work

function addRow(lRow) {
  if ( lRow != null ) {
    var ss = SpreadsheetApp.getActive()
    var sh = ss.getActiveSheet(); 
    var lCol = sh.getLastColumn(); 
    var range = sh.getRange(lRow,1, 1, lCol);
    sh.insertRowsAfter(lRow, 1);
    range.copyTo(sh.getRange(lRow+1, 1, 1, lCol), {formatOnly:true});
  }  
}
function lastValue(column) {
  var lastRow = SpreadsheetApp.getActiveSheet().getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange("K1:K"+lastRow).getValues();

  for (; values[lastRow - 1] == "" && lastRow > 0; lastRow--) {}
  return lastRow;
}
function onEdit(){
  myOnEdit()
}
function myOnEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Commandes V3" ) { //checks that we're on Commandes V3 or not
    var r = s.getActiveCell();
    if( r.getColumn() == 23 ) { //checks that the cell being edited is in column W
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //checks if the adjacent cell is empty or not?
        nextCell.setValue(new Date());
      } 
    }
  }
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getActiveSheet();
  var r = sheet.getRange("J1:J")
  for(i = 2 ; i <= 500 ; i++) {
    if (sheet.getRange("J"+ i).getValue() == true ) {
      var derLigne = SpreadsheetApp.getActiveSheet().getRange("AR54").getValue()
      var l = derLigne-1
      var ai = SpreadsheetApp.getActiveSheet().getRange("A" + i).getValue()
      var bi = SpreadsheetApp.getActiveSheet().getRange("B" + i).getValue()
      var ci = SpreadsheetApp.getActiveSheet().getRange("C" + i).getValue()
      var di = SpreadsheetApp.getActiveSheet().getRange("D" + i).getValue()
      var ei = SpreadsheetApp.getActiveSheet().getRange("E" + i).getValue()
      var fi = SpreadsheetApp.getActiveSheet().getRange("F" + i).getValue()
      var gi = SpreadsheetApp.getActiveSheet().getRange("G" + i).getValue()
      addRow(l)
      SpreadsheetApp.getActiveSheet().getRange("A" + derLigne).setValue(ai)
      SpreadsheetApp.getActiveSheet().getRange("B" + derLigne).setValue(bi)
      SpreadsheetApp.getActiveSheet().getRange("K" + derLigne).setValue(ci)
      SpreadsheetApp.getActiveSheet().getRange("L" + derLigne).setValue(di)
      SpreadsheetApp.getActiveSheet().getRange("M" + derLigne).setValue(ei)
      SpreadsheetApp.getActiveSheet().getRange("N" + derLigne).setValue(fi)
      SpreadsheetApp.getActiveSheet().getRange("O" + derLigne).setValue(gi)
      SpreadsheetApp.getActiveSheet().deleteRow(i+1)
    }
  }
  SpreadsheetApp.getActiveSheet().getRange("AR54").setValue(lastValue("K")+1);

So, to summarize a little bit, the function addRow work with the second part of myOnEdit() which is for transferring some cells from a place to another when a tickbox is checked. LastValue() works with the last line of myOnEdit(), to fill a cell with the number of the last row of a column. The first part of myOnEdit() is for adding in the next cell the date when a cell is checked (without the auto update of now() ).

My problem is that the function supposed to fill a cell with a date doesn't work here, but when I use it in another gsheet to test it, it works perfectly. I'm really struggling to know why.

(I'm sorry for the several grammatical mistakes i must have done I'm not a native English speaker)

How does the part that is meant to put the date in a cell not work? Are you getting errors? Incorrect values? Value going in wrong cell? — norie, Apr 01 '21 at 12:42
When i put something in the cell that is supposed to activate it (in the W column), nothing happen. So i don't what is the problem with the code. Because if i use the same code in an other gsheet (and by replacing the name of the sheet too) it's works well, the date comes out in the cell nearby — Thomas Girault, Apr 01 '21 at 12:53
I found on a forum the tips to name a function to trigger with onEdit() rather than just using the line directly in the onEdit(), i don't really know if it is effective. With or without this tips, the function does't activate itself — Thomas Girault, Apr 01 '21 at 16:08
Thomas I finally had a chance to look at your code and as far as I can see what you've posted isn't complete. Specifically the code after the myOnEdit function doesn't appear to belong to any function. — norie, Apr 01 '21 at 16:14
Can you try making a very simple script that only writes some values to the cells you want? Maybe there is some kind of protection on them? — iansedano, Apr 05 '21 at 08:27

Rubén · Answer 1 · 2021-04-01T23:37:18.750

When working with an on edit trigger it's better to use the event object instead of "get active" methods like getActive(), getActiveCell() but this might require to use a function to provide an emulated event object.

Also it might be more convenient to use const instead of var to prevent redeclaring variables that should not be redeclared. I mention this because the code assign different objects to r which could confuse you and others about the purpose of this variable.

function debugOnEdit(){
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const name = 'Commandes V3';
  const e = {
    source: spreadsheet,
    range: spreadsheet.getSheetByName(name).getRange(1,23)
  }
  onEdit(e);
}

function onEdit(e){
  myOnEdit(e);
}

function myOnEdit(e){
  const s = e.range.getSheet();
  const r = e.range;
  ...
}

By the other hand, onEdit might not be triggered always i.e. a user does multiple edits before the previous onEdit execution finish, it should be as fast as possible, and to achieve this the number of calls to SpreadsheetApp methods should be minimized and optimized. I.E.

instead of using getMaxRows() use getLastRow()
instead of using multiple getValue() / setValue() use getValues() / setValues()

How can I test a trigger function in GAS?

I have issues with onEdit() functions

1 Answers1