Google-sheets – Multiple timestamps in multiple columns on multiple Google Sheet tabs

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I am trying to work out what script is required to have multiple timestamps on several tabs however each one I have tried doesn't work for me. I am new to this.

When text is added to column 2 I need a timestamp in column 1. When text added in 8 I need a timestamp in 7. When 15 which is a checkbox, a timestamp in 16, and when 18, timestamp in 17. Is this possible to have so many?

My current script is as follows and this is giving me the time stamp in column 1 when column 2 is amended and it doesnt overwrite when it is changed a second time.

function onEdit(e) {
  var row = e.range.getRow();
  var col = e.range.getColumn();
  if (col === 2 && row > 1 && e.source.getActiveSheet().getName() === "June 2021") {
    if (e.source.getActiveSheet().getRange(row, 1).getValue() == "") {
      e.source.getActiveSheet().getRange(row, 1).setValue(new Date());
    }
  }
}

Best Answer

A single onEdit(e) function can watch multiple columns on multiple sheets and insert timestamps in multiple columns depending on which column was edited and what the new value in the cell is. See the timestampMultipleColumns_ script for an example.

Short Answer

Replace if( r.getColumn() != 5 ) by if( r.getColumn() != 5 && r.getRow() != 1 ) .

Long answer

&& is the AND operator r.getRow() != 1 compares the row of the edited cell with the row number that you want to be skipped.

Full code minimal modification

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange("E" + row.toString()).setValue(time);
  };
};

Full code improved

Taking advantage that onEdit() event gets an event object that include the range.

function onEdit(e) {
  var r = e.range;
  var s = r.getSheet();  
  if( r.getColumn() != 5 && r.getRow() != 1) { //checks the column & row
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-07:00", "MM-dd-yyyy HH:mm:ss");
    s.getRange("E" + row.toString()).setValue(time);
  };
};

References

Google-sheets – Google Sheets Script Editor action not applying to all sheets

Get the sheet that was edited. Change code to:

var sheet, ss;

ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = ss.getActiveSheet();//Get the active sheet

Now the sheet variable is the active sheet that was just edited. So anything done with sheet will affect that sheet.