How to delete a cell's value previously set by

2019-08-20 07:19发布

Following my previous post additional problem of deleting values previously preset by the (e) event object here How to automatically add the ordinal number of an entry based on timstamp to a row when non-contiguous new data is added to it in Google Sheets?

How to reach the result of the code below using a variable or a simpler method in the context of the current situation?

With the great help of @TheMaster and some more from @Tanaike here google script detect empty cell after edit I've found a workaround to delete values previously preset by the (e) event object.

Here's what I found:

function onEdit(e) {
  var rg = e.range,
    row = rg.rowStart,
    col = rg.columnStart,
    sht = rg.getSheet();

  //exit code
  if (col !== 2 || sht.getName() !== 'Sheet1' || row === 1) return;

  // When the empty cell is edited, this becomes true.
  if ((e.value != null) && (e.oldValue == null)){ 
  //Calculate max value and add 1
  rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
    rg
      .offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
      .getValues()
      .reduce(function(acc, curr) {//get max of colA
        return Math.max(acc, Number(curr[0]));
      }, 0) + 1
  );
  }

  // When the value of cell with a value is removed, this becomes true.
  else if ((e.value == null) && (e.oldValue == null)) { 
  rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
    rg
      .offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
      .getValues()
      .reduce(function(acc, curr) {//get max of colA
        return Math.max(acc, Number(curr[0]));
      }, 0)
  ).clearContent(); // If we delete cell
  }

}

Here the live result:

https://i.imgur.com/jWd4Erz.gif

What clearer and simpler way to reach the result would you recommend?

The result does what is expected. But is there a way to use a variable to reach the same result in the context the original code by @TheMaster below ?

The original code by @TheMaster link:

function onEdit(e) {
  var rg = e.range,
    row = rg.rowStart,
    col = rg.columnStart,
    sht = rg.getSheet();

  //exit code
  if (col !== 2 || sht.getName() !== 'Sheet1' || row === 1) return;

  //Calculate max value and add 1
  rg.offset(0, -1, 1, 1).setValue(//setvalue in colA
    rg
      .offset(2 - row, -1, sht.getLastRow() - 1, 1) //get all of colA
      .getValues()
      .reduce(function(acc, curr) {//get max of colA
        return Math.max(acc, Number(curr[0]));
      }, 0) + 1
  );
}

Thanks a lot for your help and ideas!

0条回答
登录 后发表回答