Spreadsheet function does not run when button clicked

Question

I previously asked a two-part question, and only one part was answered and then closed, while the other part was left unanswered. I'm re-asking that part.

I'm in charge of creating an "automatic" project tracking sheet for my team. Automatic in the sense that someone presses a button, it logs a date/time stamp, and the time they spent on said project is automatically calculated for them.

However, when I connect a script to a Google Drawing to act as a button, it doesn't do anything, and I am unable to figure out why.

Here is what my spreadsheet looks like, with a button (drawing) configured to run the timeStamp() function when clicked:

You can also get a copy of the spreadsheet here.

The code I've got so far:

function timeStamp() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  var timezone = ss.getSpreadsheetTimeZone;
  var timeFormat = "hh:mm a";

  var lastCol = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();
  var lastCell = sheet.getRange(lastRow, lastCol);
  var timeStampCell = sheet.getRange(lastRow, lastCol + 1);


  if(timeStampCell === " ") {
    var date = Utilities.formatDate(new Date(), timezone, timeFormat); 
    timeStampCell.setValue(date);
  };
}

Answer 1

var timeStampCell = sheet.getRange(lastRow, lastCol + 1);

...sets timeStampCell to a Range object.

if(timeStampCell === " ") {

...compares that Range object to a String containing a single space character. The comparison is always false, because the types are different. If == was used for the comparison with type coercion allowed, it would still evaluate false because the operands are just different.

You probably want the cell's contents, and to compare them to an empty string:

if(timeStampCell.getValue() === "")

Note: take care using === with the contents of Spreadsheet cells. If the comparison is for Date objects, === will evaluate true only when both operands are the exact same object. See Which equals operator (== vs ===) should be used in JavaScript comparisons?

In this example, you don't really care what data type comes from the spreadsheet, only that it is blank. Therefore, you could use ==.

if(timeStampCell.getValue() == "")

Another problem, here:

var timezone = ss.getSpreadsheetTimeZone;

This will get a reference to the Spreadsheet "getSpreadsheetTimeZone" method, but it will not tell you the timezone. To get the result of the method, you need to call it by including parentheses:

var timezone = ss.getSpreadsheetTimeZone();

With those two changes, the script works for me.