Google连续记录时间戳不同单元格的范围 [英] Google sheets timestamp different cell ranges on a row

问题描述

我一直试图在对应于该行上两个不同单元格范围的行上放置两个时间戳。我已成功使用此脚本为行上的任何更改添加时间戳（在第5列之后）。

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).

如果在单元格范围F到L中发生任何更改，我想在E列中放置一个时间戳，然后在M列中放置另一个时间戳（如果有其他更改）从N列更改为Z：

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"));
}

推荐答案

我看不到rowStart和范围类文档中的columnStart属性；似乎最好使用记录的方法getRow和getColumn。像这样：

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;

可用于获取与列N对应的数字。

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

这篇关于Google连续记录时间戳不同单元格的范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！