3

As a teacher, I am trying to create a Google sheet for the students to use as they sign in and out of class. I have successfully used code to auto insert a time stamp into column A if Column C is edited. However, I can't get a new time to be inserted into column B after after column E has been edited.

The code I am using is below. Can you please help me?

/*Updated and maintain by internetgeeks.org*/ 

function onEdit(event) 
{ 
 var timezone = "GMT-5"; 
 var timestamp_format = "MMMM-dd HH:mm"; // Timestamp Format. 

 var updateColName = "Student First, Last Name"; 
 var timeStampColName = "Sign Out"; 

 var sheet = event.source.getSheetByName('Feb 16'); //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);  {
        var cell = sheet.getRange(index, dateCol + 1); 
        var date = Utilities.formatDate(new Date(), timezone, timestamp_format); cell.setValue(date); }

}
2

I modified the script so that it accepts arrays of column headers:

  • updateColNames lists the columns that you want to keep track of
  • timeStampColNames lists the columns where timestamps should appear, in the same order.

There is now a for loop that goes over the tracked columns one by one, comparing them to the edit location, and inserting timestamp when needed.

I also changed the handling of sheet name, because the original version appeared to edit the sheet named Feb 16 regardless of where the edit was.

function onEdit(e) { 
  var timezone = "GMT-5"; 
  var timestamp_format = "MMMM-dd HH:mm"; // Timestamp Format. 

  var updateColNames = ["Student First, Last Name", "Another Column to Watch"]; 
  var timeStampColNames = ["Name Timestamp", "Another Timestamp"]; 

  var sheet = e.range.getSheet();
  if (sheet.getSheetName() == 'Feb 16') {    // Name of the sheet to track

    var editColumn = e.range.getColumn(); 
    var row = e.range.getRow(); 
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 

    if (row > 1) {
      for (var i = 0; i < updateColNames.length; i++) {
        var dateCol = headers[0].indexOf(timeStampColNames[i]); 
        var updateCol = headers[0].indexOf(updateColNames[i]) + 1;  
        if (dateCol > -1 && editColumn == updateCol) {
          var cell = sheet.getRange(row, dateCol + 1); 
          var date = Utilities.formatDate(new Date(), timezone, timestamp_format); 
          cell.setValue(date); 
        }
      }
    }
  }
}

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.