CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user7254740
user7254740

Reputation: 111

Capture date & time when cell is edited (google spreadsheet)

I'm looking for a way to capture time & date (or maybe date only) when a cell is updated/edited.

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.

here is the error: enter image description here

can someone explain and help me with this? thanks, I'll also continue searching for some ideas.

Upvotes: 1

Views: 2957

Answers (1)

Tanaike
Tanaike

Reputation: 201338

How about this?

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

Upvotes: 2

Related Questions