自动为Google表格中的多列添加时间戳 [英] Auto-timestamp multiple columns in Google Sheets

问题描述

我正在尝试使用从

I'm trying to use this code I adapted from http://www.internetgeeks.org/tech/add-timestamp-time-stamp-google-docs-spreadsheet/ to automatically timestamp cells upon entering question and answer text for a Google Sheets file. When I only use the code for one column it works, but when I duplicate and try to get it to work on two columns, it doesn't work.

我想做什么.

function onEdit(event)
{ 
   var timezone = "GMT-4";
   var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp Format. 
   var updateColName = "Question/Comment";
   var timeStampColName = "Date/Time Sent";
   var sheet = event.source.getSheetByName('ArtsVision Questions/Comments'); //Name of the sheet where you want to run this script.

   var actRng = event.source.getActiveRange();
   var editColumn = actRng.getColumn();
   var index = actRng.getRowIndex();
   var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
   var dateCol = headers[0].indexOf(timeStampColName);
   var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
   if (dateCol > -1 && index > 1 && editColumn == updateCol) { // 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);
   }
}

function onEdit(event)
{ 
   var timezone = "GMT-4";
   var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp Format. 
   var updateColName = "Response";
   var timeStampColName = "Date/Time Answered";
   var sheet = event.source.getSheetByName('ArtsVision Questions/Comments'); //Name of the sheet where you want to run this script.

   var actRng = event.source.getActiveRange();
   var editColumn = actRng.getColumn();
   var index = actRng.getRowIndex();
   var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();
   var dateCol = headers[0].indexOf(timeStampColName);
   var updateCol = headers[0].indexOf(updateColName); updateCol = updateCol+1;
   if (dateCol > -1 && index > 1 && editColumn == updateCol) { // 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);
   }
}

推荐答案

在这种情况下，我们可以确信event.range将仅包含最后编辑的单元格(请参见

Given that in this case we can be pretty confident that the the event.range will contain just the last cell edited (see https://developers.google.com/apps-script/guides/triggers/events) this should work:

function onEdit(event) { 
  var timezone = "GMT-4";
  var timestamp_format = "MM-dd-yyyy HH:mm:ss"; // Timestamp Format
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

  var range = event.range; // this will be the last cell edited
  var column = range.getColumn(); // use this to check the cell edited was in a relevant column
  var rangeValue = range.getValue(); // use this to check something was actually entered into the last cell edited
  var newRange  = range.offset(0,1); // this is one cell to the right of the last cell edited

  if ((column == 2 || column == 4) && rangeValue) { //if the last cell edited was in a relevant column, and there is a value in it
    newRange.setValue(date);
  } else if ((column == 2 || column == 4) && !rangeValue) { // there was no question or no response, so make sure there is no timestamp
    newRange.setValue("");
  }
}

如果您保护工作表中的范围，那么您不希望人们能够进行编辑(标题和两个timestamp列)，就可以了.

If you protect the ranges in the sheet you don't want people to be able to edit (the headers, and the two timestamp columns) this should work ok.

这篇关于自动为Google表格中的多列添加时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！