0

The code below insert timestamp in google sheet by editing another column. However, there an issue which I have no idea how to solve. When I add new row manually, it successfully adds timestamp, BUT I have an html form in my website which sends data to this sheet and even-though new row is added, google don't create a timestamp !!! It's a bit weird since conceptually when a new row is added, means an edit in cell, and google must add timestamp. I would be so grateful if someone help me to solve this issue. Thanks

function onEdit(event)
{ 
  var timezone = "GMT+8:30";
  var timestamp_format = "yyyy-MM-dd HH:mm:ss"; // Timestamp Format. 
  var updateColName = "date";
  var timeStampColName = "Date Sent";
  var sheet = event.source.getSheetByName('Attendance'); //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);
  }
}
  • perhaps try onChange – user0 Jun 25 at 16:49
0

Simple and installable triggers for open, edit and change events are triggered only when a user using the Google Sheets UI do the corresponding action.

Reference

Related

  • I already said the same thing to similar questions but those answer didn't get any upvote yet. – Rubén Jun 25 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.