I have a cell counter in (J1) that counts every time cell (J2) has changed, finally got the counter to work, but I noticed that it only "sees" manual edits and does not count any copy-pasted values (I'm pasting values directly into cell J2). Is there anyway to fix this as manually typing in the value would defeat the whole purpose.

This is what I have so far

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

/**
* Increments a counter in a cell when another cell is edited.
*
* @param {Object} e The event object.
*/
function incrementCounter_(e) {
  var sheetToWatch = 'Work Order';
  var cellToWatch = 'J2';
  var cellToIncrement = 'J1';
  if (!e || !e.range) {
    return;
  }
  var sheet = e.range.getSheet();
  if (sheet.getName() === sheetToWatch && e.range.getA1Notation() === cellToWatch) {
    var cell = sheet.getRange(cellToIncrement);
    cell.setValue((Number(cell.getValue()) || 0)   1);
  }
}

CodePudding user response：

If what you mean is pasting into a range where it includes J2 (that doesnt start at J2), then you need to check if J2 is within the edited range.

Script:

function incrementCounter_(e) {
  var sheetToWatch = 'Work Order';
  var cellToIncrement = 'J1';

  var range = e.range;
  // get the dimension of the range
  var rowStart = range.getRow();
  var rowEnd = range.getLastRow();
  var colStart = range.getColumn();
  var colEnd = range.getLastColumn();
  // boolean if range edited does not include J2 (2, 10)
  var invalidRange = rowStart > 2 || rowEnd < 2 || colStart > 10 || colEnd < 10;

  // return if range is invalid
  if (!e || !e.range || invalidRange){
    return;
  }
  var sheet = e.range.getSheet();
  // range should be valid (negated boolean value)
  if (sheet.getName() === sheetToWatch && !invalidRange) {
    var cell = sheet.getRange(cellToIncrement);
    cell.setValue((Number(cell.getValue()) || 0)   1);
  }
}

Output:

Note:

• onEdit is only triggered by user edits.

CodePudding user response：

Try

function onEdit(e){
  var sh = e.source.getActiveSheet()
  for (var i = e.range.rowStart;i<=e.range.rowEnd;i  ){
    for (var j = e.range.columnStart;j<=e.range.columnEnd;j  ){
      incrementCounter_(sh.getRange(i,j),sh)
    }
  }
}
function incrementCounter_(rng,sh) {
  var sheetToWatch = 'Work Order';
  var cellToWatch = 'J2';
  var cellToIncrement = 'J1';
  if (sh.getName() === sheetToWatch && rng.getA1Notation() === cellToWatch) {
    var cell = sh.getRange(cellToIncrement);
    cell.setValue((Number(cell.getValue()) || 0)   1);
  }
}

or

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  var sheetToWatch = 'Work Order';
  if (sh.getName() === sheetToWatch) {
    var cellToWatch = 'J2';
    var cellToIncrement = 'J1';
    for (var i = e.range.rowStart; i <= e.range.rowEnd; i  ) {
      for (var j = e.range.columnStart; j <= e.range.columnEnd; j  ) {
        if (sh.getRange(i, j).getA1Notation() === cellToWatch) {
          sh.getRange(cellToIncrement).setValue((Number(sh.getRange(cellToIncrement).getValue()) || 0)   1)
        }
      }
    }
  }
}