Google-sheets – Put Date in Specific Column

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I found the following code in another post here to add a date after an edit but it places it in an offset column, I would like the date in Column 8 if column 3 or 4 has been edited.
What do I change?

function onEdit(e) {
  if ([3,4].indexOf(e.range.columnStart) != -1) {
    e.range.offset(0, 1).setValue(new Date());
  }
}

Best Answer

  • then try e.range.offset(0, 7)

    function onEdit(e) {
 if ([3,4].indexOf(e.range.columnStart) != -1) {
 e.range.offset(0, 7).setValue(new Date());
 }
}

  • to have dates only in one column try like this:

    function onEdit(e) {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet1" ) { 
var r = s.getActiveCell();
if( r.getColumn() == 3 ) { 
  var nextCell = r.offset(0, 7);
  var newDate = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy");
  nextCell.setValue(newDate);
}
if( r.getColumn() == 4 ) { 
  var nextCell = r.offset(0, 6);
  var newDate1 = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy");
  nextCell.setValue(newDate1);
}}}

    • "Sheet1" = sheet name
    • r.getColumn() == 3 = column C / 3rd column
    • r.offset(0, 7) = offset timestamp 7 columns to the right on the same row eg column H
    • "GMT+8" = timezone
    • "MM/dd/yyyy hh:mm:ss" = date and time format

  • alternative (untested):

    function onEdit(e) { 
 if ([3].indexOf(e.range.columnStart) != -1) { 
 e.range.offset(0, 5).setValue(new Date()); } 
 if ([4].indexOf(e.range.columnStart) != -1) { 
 e.range.offset(0, 4).setValue(new Date()); } }

Related Topic