Google sheet script to empty a cell based on an existing onEdit function?

Question

I am trying to do the following on a sheet:

Go over a list in a cell and look for a value from another cell, then mark the position of the value in the list - this seems to be working fine without issue
If the value is not found in the list (hence position will be 0) - clear the value in the index cell - I am having issues with incorporating this, I've tried to look at the cell value but it doesnt seem to trigger, could be the onEdit doesnt trigger when a script does the edit function?

the script I am using is:

    function onEdit() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  const [hA, ...vs] = sh.getDataRange().getValues();
  
  var r = ss.getActiveCell();
  if ( r.getDisplayValue == 0) {
      var prevCell = r.offset(0, -1);
      prevCell.setValue("")
  }
  
  let idx = {};
  hA.forEach((h, i) => { idx[h] = i; });
  let vO1 = vs.map((r, i) => {
    return [r[idx['LIST']].toString().split(',').indexOf(r[idx['ITEM']]) + 1];
  });
  sh.getRange(2, idx['POSITION'] + 1, vO1.length, vO1[0].length).setValues(vO1);

}

test sheet as an example - LIST-test

the outcome I would prefer to have is that the cell under ITEM row with the 'delete' value, would be deleted automatically so it would remain blank

Tom Sharpe Tom Sharpe · Accepted Answer · 2021-07-03T16:01:24

I'm not sure if the onEdit behaviour is exactly what you want, but I can help you a bit on this - the main thing is just missing brackets in the call to getDisplayValue so that section of the program should be:

var r = ss.getActiveCell();

  if ( r.getDisplayValue() == 0) {

      var prevCell = r.offset(0, -1);
      prevCell.setValue("")
  }

Google sheet script to empty a cell based on an existing onEdit function?

2 Answers