Google Sheets Timestamp – How to Add Timestamps in Google Sheets

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

I'm trying to have a timestamp automatically appear in column L (12) in a Google Spreadsheet if a tick box is ticked on the same row (in column A). The app script code that I modified from this source keeps giving me undefined in the cell in column L if I tick the tick box in the same row (column A). I would appreciate any help to fix the problem.

(I know that there are a lot of questions about timestamps here on StackExchange but I'm a newbie and can't get it to work for my situation).

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Chemistry HL" ) { //checks that we're on the correct sheet
   var tickBox = s.getActiveCell();
   if( tickBox.getColumn() == 1 ) { //checks the tick box column
     var timeStamp = tickBox.offset(0, 11); //offset from tick box column to timestamp column
     if( tickBox == true ) //is ticked or not?
     var time = Utilities.formatDate(new Date(), "GMT-7", "yy/MM/dd");
     timeStamp.setValue(time);
   };
 };
}

Best Answer

Some observations

Most programmers benefit greatly from adding some logging as any sort of complexity arises while writing their own code, and at least until confident in their abilities and confident about what's actually happening. Put it everywhere it could help… log functions' starting… log finishing… if an if test passes, log it… sure, add else blocks just for logging why an if's condition evaluated to false… can't hurt. Back in Google Apps Script, in your code for example, adding something like

Logger.log("tests passed. setting timestamp cell to: "+time);

right before you write the timestamp cell would at least make it clear that your time variable truly hasn't been created (hence the "undefined"—more on why it's undefined later). To see the Logger output, edit a Sheet cell so onEdit is called, then back in the script editor, from the menus choose View → Logs.

Your indentation and curly brace usage are not uniform. Staying vigilant about formatting and syntax will make the code easier to read to "future you", "debugger you", and other readers. But it also makes it clearer when writing if there are just actual coding mistakes or structural problems. In your script for example, your if (tickBox == true) has no subsequent { block of conditional code in curly braces }, so only the single next statement is executed. When that if condition is false, the variable never gets created but the script is still then setting the cell value in the line after that (more on why the condition is false later).

Single statements are followed by a semicolon.
Multiple statements are enclosed in curly braces, and each statement within is terminated with a semicolon. Given those rules, there shouldn't be semicolons after function, if, and loop blocks.

Your final condition—to make the if either write or not write the timestamp—it's currently written to do the comparison against the actual "tickBox" object. You need to instead see if tickBox.getValue() is true.

Some Opinions

Your code misses a little opportunity. We're free to ignore it, obviously, but onEdit is passed the actual edit event that occurred. You could check, and operate from, the actual edited cell rather than using getActiveCell. This can have many advantages. One example; testing for whether or not the edit involved multiple cells—what happens if a user cuts three rows (complete with checkboxes) and pastes them into other rows? The script will run—will it behave correctly?

And finally, an unpopular statement: there is one correct date format, 2019-04-18 (yyyy-MM-dd). Your format is dangerously close to other common formats, making it extra ambiguous. That "year-month-date" format is the worldwide standard, ISO 8601 (see: its ISO intro, Apps Script formatDate documentation, obligatory xkcd.) formatDate can also take timezone formatting in stride, e.g. the standard's formatting, yyyy-MM-dd'T'HH:mm:ss'Z'

Some Code

The following implements the above. Comment if there are errors or if you need any explanation.

function onEdit(event) {
  var editSheet = event.range.getSheet(),
    editRow = event.range.getRow(),
    editCol = event.range.getColumn();

  if(event.range.getWidth() == 1 && event.range.getHeight() == 1) {
    Logger.log("edit was a single cell");
    if( editSheet.getName() == "Chemistry HL" ) { 
      Logger.log("we're on the correct sheet");
      if( editCol == 1 ) {
        Logger.log("edited column was 1");
        var editTicked = event.range.getValue();
        if( editTicked == true ) {
          Logger.log("edited cell is now ticked");
          var timeStamp = event.range.offset(0, 11); //offset from tick box column to timestamp column
          var time = Utilities.formatDate(new Date(), "GMT-7", "yyyy-MM-dd");
          Logger.log("tests passed. setting timestamp cell to: "+time);
          timeStamp.setValue(time);
        }
        else {Logger.log("edited cell is not ticked");}
      }
      else {Logger.log("watching column 1. edit was in column: "+editCol);}
    }
    else {Logger.log("wrong sheet: "+editSheet.getName());}
  }
  else {Logger.log("edit included multiple cells. timestamping multiple not implemented.");}
}
Related Topic