Google sheets timestamp different cell ranges on a

问题:

I have been trying to place two timestamps on a row corresponding to two different cell ranges on that row. I have been successfully using this script to timestamp any changes on the row (after the 5th column).

What I would like is to place a timestamp in column E if any change happen in cell ranges F to L, and then, another timestamp in column M, if any other changes are made from column N to Z:

function onEdit(e) {
  if (e.range.columnStart < 6 || e.range.rowStart < 2) return;
  e.source.getActiveSheet().getRange(e.range.rowStart, 5)
  .setValue(Utilities.formatDate(new Date(), "GMT-4", "MM/dd/yyyy HH:mm:ss"));
}

回答1:

I don't see rowStart and columnStart properties in the Range class documentation; it seems preferable to use the documented methods getRow and getColumn. Like this:

function onEdit(e) {
  var sheet = e.range.getSheet();
  var row = e.range.getRow(); 
  if (row < 2) {
    return;
  }
  var col = e.range.getColumn();
  if (col >= 6 && col <= 12) {
    sheet.getRange(row, 5).setValue(new Date());
  }
  if (col >= 14 && col <= 26) {
    sheet.getRange(row, 13).setValue(new Date());
  }
}

The timestamp columns can be formatted to show both date and time, if desired.

As you can see, the conditional statements are similar: leftmost column, rightmost column, timestamp column. More could be added to the same effect. If you don't feel like counting letters, expressions like

"N".charCodeAt(0) - "A".charCodeAt(0) + 1;

can be used to get the number corresponding to column N.