将时间戳添加到Google Spreadsheet onEdit脚本中的指定列 [英] Add timestamp to named column in Google Spreadsheet onEdit script

问题描述

我试图找出如何在Google Spreadsheet脚本中检索具有特定名称的列的索引。

I'm trying to figure out how to retrieve the index of a column with a specific name in a Google Spreadsheet script.

我正在处理一个自定义Google Script在编辑时自动为电子表格添加时间戳。当前版本成功地将时间戳记添加到活动单元格的最后一列。

I'm working on a custom Google Script that automatically adds timestamps to a spreadsheet when it is edited. The current version successfully adds the timestamp to the last column of the active cell's row.

我想创建一个新版本的脚本，它将时间戳添加到特别指定的列使用特定的列名称。例如，我想在电子表格中创建一个名为上次更新的列，我希望脚本检测该列的索引并自动将时间戳添加到该列中，而不是该行中的最后一列。

I want to create a new version of this script that adds the timestamp to a specially designated column by using a specific column name. For example, I want to create a column in my spreadsheet named "Last Updated," and I want the script to detect the index of that column and automatically add the timestamps to it instead of the last column in the row.

这对我来说会更好，因为我可以随时将时间戳列放在任何我想要的位置，而不必担心脚本覆盖任何重要的意外事件。

This would work better for me, because I could then place the timestamp column wherever I wanted and not have to worry about the script overriding anything important by accident.

我的当前脚本如下所示：

My current script looks like this:

function onEdit(event)
{ 
  var timezone = "GMT-4";
  var timestamp_format = "yyyy-MM-dd HH:mm:ss";
  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();
  var cindex = actRng.getColumnIndex();

  // Here is where I want to use a named column's index instead of the active row's last column.
  var dateCol = sheet.getLastColumn();
  //var dateCol = sheet.getColumnIndexByName('Last Updated');

  var lastCell = sheet.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

  lastCell.setValue(date);
}

推荐答案

您的标题行，并使用indexOf（）在这些值中搜索所需的标题。

You could get the values in your header row, and search for the required header in those values using indexOf().

function onEdit(event)
{ 
  var timezone = "GMT-4";
  var timestamp_format = "yyyy-MM-dd HH:mm:ss";
  var sheet = event.source.getActiveSheet();

  // note: actRng = the cell being updated
  var actRng = event.source.getActiveRange();
  var index = actRng.getRowIndex();

  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
  var dateCol = headers[0].indexOf('Last Updated');

  if (dateCol > -1 && index > 1) { // only timestamp if 'Last Updated' header exists, but not in the header row itself!
    var cell = sheet.getRange(index, dateCol + 1);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    cell.setValue(date);
  }
}

这篇关于将时间戳添加到Google Spreadsheet onEdit脚本中的指定列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！