拍摄日期和时间编辑单元格的时间(Google电子表格) [英] Capture date & time when cell is edited (google spreadsheet)
本文介绍了拍摄日期和时间编辑单元格的时间(Google电子表格)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找一种捕获时间的方法单元格更新/编辑的日期(或仅日期).
I'm looking for a way to capture time & date (or maybe date only) when a cell is updated/edited.
我在网上搜索时发现了一些教程,但我猜它有些过时了,它不能100%运行.这是我找到的代码
I found some tutorials while searching through the web, but I guess its a little outdated that it doesn't run 100%. here is the code I found
function capdatetime(event)
{
var timezone = "GMT+8";
var timestamp_format = "MM-dd-yyyy"; //Timestamp format
var updateColName = "Date Sent";
var sheet = SpreadsheetApp.getActiveSpreadsheet(); //Name of the sheet where you want to run the script
var actRng = SpreadsheetApp.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 of '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);
}
}
我对此进行了修改,以解决遇到的一些错误,但是有一个我不太了解的特定错误.
I modified this to address some errors I encountered but there's one particular error that I can't understand that much.
这是错误:
有人可以解释并帮助我吗?谢谢,我还将继续寻找一些想法.
can someone explain and help me with this? thanks, I'll also continue searching for some ideas.
推荐答案
如何?
function capdatetime(event)
{
var timezone = "GMT+8";
var timestamp_format = "MM-dd-yyyy"; //Timestamp format
var updateColName = "Name";
var timeStampColName = "LastUpdated";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sample'); //Name of the sheet where you want to run the script
var actRng = sheet.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 of '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电子表格)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文