1

I've created a script that inserts a timestamp based on any update to a specific cell; however I'm trying to insert a timestamp on a specific condition: 'Complete'.

Would anyone know how to modify this script?

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();

    if( r.getColumn() == 1 ) { //checks the column 
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');
    }

    if( r.getColumn() == 2 ) { //checks the column
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');
    }

    if( r.getColumn() == 3 ) { //checks the column - Complete
      var nextCell = r.offset(0, 1);
      //if( nextCell.getValue() !== '' ) //is empty?
      nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');

    }
  }
}
  • Welcome. There are several questions on this site about timestamps on Google Sheets. Did you already review them? If so, why they doesn't work for you? – Rubén May 2 '17 at 18:07
3

This line:

if( nextCell.getValue() !== '' ) //is empty?

Should be:

if (nextCell.getValue() === 'Complete') {
  nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');
}
0

I have tried myself using inspiration from this post and found to have cracked the script for the desired application.

you need to try the following:

 function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Enter_Sheet_Name" ) { //checks that we're on the correct sheet
 var r = s.getActiveCell();

 if( r.getColumn() == 7 ) { //checks the column 
        var nextCell = r.offset(0, 1);
  if( r.getValue() === "Complete")
    nextCell.setValue(new Date()).setNumberFormat('dd-MM-yyyy HH:mm:ss');
  }
  }
  }

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.