谷歌表格在一行中为不同的单元格范围添加时间戳 [英] 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 中放置一个时间戳，然后，如果从列 N 到 Z 进行任何其他更改，则在列 M 中放置另一个时间戳:

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

推荐答案

我在 范围类文档;似乎最好使用记录在案的方法 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.

这篇关于谷歌表格在一行中为不同的单元格范围添加时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！