0

I want a cell in column B to timestamp when a cell column A is edited/updated

I have this function but unfortunately it updates every time the spreadsheet reloads

function getUpdate() {
  return new Date();
}

Is there a way I can timestamp it with the date when the cell is updated, but not when it reloads? Many thanks

cloudycider
  • 37
  • 6
  • Does this answer your question? [Automatic timestamp when a cell is filled out](https://stackoverflow.com/questions/11458470/automatic-timestamp-when-a-cell-is-filled-out) – Diego Apr 22 '21 at 13:03
  • Thanks @Diego that's really helpful. I'm stuck on this line though - instead of inserting the date in the next column, I want it to insert the timestamp on the same row in column C `var nextCell = r.offset(0, 1); ` – cloudycider Apr 22 '21 at 15:51
  • I figured this out `var nextCell = SpreadsheetApp.getActiveSheet().getRange('C' + r.getRow());` – cloudycider Apr 22 '21 at 18:56

3 Answers3

1

This does the job. Notice I've slightly modified it to enter a timestamp in column J, if input was recorded in column 6, 7, 8, 9

Is there a range function in Javascript like in Python?

Instead of using || is there something like range(6, 10)?

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Projects" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 6 || r.getColumn() == 7 || r.getColumn() ==  8 || r.getColumn() == 9 ) { //checks the column

      // var nextCell = r.offset(0,2);

      var nextCell = SpreadsheetApp.getActiveSheet().getRange('J' + r.getRow());


      // if( nextCell.getValue() === '' ) //is empty?
      nextCell.setValue(new Date());
    }
  }
}
cloudycider
  • 37
  • 6
0
function onEdit(e) {
  var s = e.range.getSheet();
  if( s.getName() == "Sheet1" && e.range.columnStart == 1 ) {           
     e.range.offset(0,2).setValue(new Date());
  }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
0

Regarding your proposed solution and what are you asking there, you can use the method includes:

var range = [6,7,8,9]
range.includes(col)
fullfine
  • 1,371
  • 1
  • 4
  • 11