Google-sheets – Time stamp on last row of column for changes in predetermined range

google sheetsgoogle-sheets-timestamp

I am looking for a way to add a time stamp to the last row of a column of figures any time there is a change in the above range. I have been able to find a number of examples where you can timestamp the next or last column but none for monitoring a column range and time stamping below. I am trying to get this working across multiple column with the answer showing in the same row of each.

Best Answer

I've modified the code from my answer to fit your description. In the example below, columns 2-5 (that is, B-E) of sheet named 'Sheet1' are monitored for changes. In case of change, a timestamp is inserted in the last row of that column.

To customize: change the sheetname and column bounds on line 4 of the script. If a non-contiguous range of columns is to be watched (e.g., columns 2,4,7,8), replace the conditions column >= 2 && column <= 5 with, for example, [2, 4, 7, 8].indexOf(column) > -1

function onEdit(e) {
  var sheet = e.range.getSheet();
  var column = e.range.getColumn();
  if (sheet.getSheetName() == 'Sheet1' && column >= 2 && column <= 5) {
    var row = sheet.getMaxRows();
    sheet.getRange(row, column).setValue(new Date());
  }
}

Related Solutions

Google-sheets – Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

Don't use several onEdit-functions. Places place both IF-statements in the same tag.

Also, you shouldn't have semicolons after the IF-statements brackets.

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "General Meeting Attendance Roster" ) { //checks that we're on the correct sheet
       s.getRange('S6').setValue("Roster last updated: " + (new Date()));
  }

 if( s.getName() == "Master Chapter Roster" ) { //checks that we're on the correct sheet
       s.getRange('K1').setValue("Chapter Roster last updated: " + (new Date()));
  }
}

Google-sheets – Timestamp in two columns

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

Best Answer

Related Solutions

Google-sheets – Individual time stamps for individual sheets in a Google Spreadsheet containing multiple sheets

Google-sheets – Timestamp in two columns

Related Topic