0
votes

I would like to fire a trigger with onEdit if a cell is empty in range A2:A100 happens and replace it with a value from formula result in cell B1 I coded as below, but it replaces only with a value set on the script 'HELLO' I need to change 'HELLO' with the value in cell B1

function onEdit(e) {
 var spreadsheet = SpreadsheetApp.getActive();
var ss = SpreadsheetApp.getActive()
    .getSheetByName('Feuille 1');
var range = ss.getRange("A2:A15")
    .offset(0, 0, ss.getDataRange()
         .getNumRows());
range.setValues(range.getValues()
    .map(function (row) {
        return row.map(function (cell) {
            return cell === '' ? 'HELLO'   : cell;
        });
    }));
}

https://i.stack.imgur.com/k4Lku.jpg

1

1 Answers

0
votes

Change your code to:

function onEdit(e) {
  if (e.range.getColumn() == 1)  { //Column A was edited
    if (e.value == '' || e.value == null) { //And the cell was set to BLANK
      //Then I should set this cell to 'B1' 
      var B1Value = SpreadsheetApp.getActiveSheet().getRange("B1").getValue();
      e.range.setValue(B1Value);
    }
  }
}