Google apps script for GSheet - Find today's date on column B and write current time on column C of the same row

Question

I am trying to write an apps script and assign it to a button. When the button is pressed, it will activate a function I named clockin(). What this function does is to look for today's date on column B and write the current time on column C. The problem is this code is not writing any value on the defined cell which kinda sucks. I'm new to Javascript, hence requiring your assistance. My code is below:

function todayDateNowTime () {
  const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  var today = new Date()
  var month = monthNames[today.getMonth()]; //months from 1-12
  var day = today.getDate();
  var year = today.getFullYear();
  var seconds = today.getSeconds();
  var minutes = today.getMinutes();
  var hour = today.getHours();

  var todayDate = day+"-"+month+"-"+year;
  var nowTime = hour+":"+minutes+":"+seconds;

  console.log(todayDate);
  console.log(nowTime);

  return todayDate, nowTime;
}


function clockin(todayDate, nowTime) {
  todayDate, nowTime = todayDateNowTime();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for(var i = 0; i<data.length;i++){
    if(data[i][1] == todayDate) { //[1] because column B
      var range = SpreadsheetApp.getActiveSpreadsheet().getActiveCell("C"+i)
      range.setValue(nowTime);
    }
  }
}

I have made my gsheet publically available to view right here.

I have also included a screenshot here if it helps:

Does this answer your question? [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) — TheMaster, Mar 04 '20 at 07:23
In addition, you can't return like this :`return todayDate, nowTime`. Return as a array instead: `return [todayDate, nowTime]` and receive it like this: ``const [todayDate, nowTime]= todayDateNowTime();`` — TheMaster, Mar 04 '20 at 07:26
@TheMaster Thank you for your insight. I have read the thread you recommended, I'm afraid that does not solve my question as my program isn't exactly about date comparison. :) I have also implemented your suggestion about returning it as an array and define it as an array in the `clockin()` function. I'm afraid my code is still not working. — Cadell Teng, Mar 04 '20 at 07:32
No it is about date comparison. You see `data[i][1] == todayDate` is your problem. `data[i][1]` is a date object and `todayDate` is a string created by you. You could try `const todayDate=new Date();todayDate.setHours(0,0,0,0);if(todayDate-data[i][1]===0)` (if there's no timezone difference between script and spreadsheet). What jpv did (but didn't explain) is he used `getDisplayValues()`, which returns a string: so `data[i][1]` is a string rather than a date object,which can be compared to your string `todayDate`(IMHO, it's a bandaid than a solution). — TheMaster, Mar 04 '20 at 10:57

score 1 · Accepted Answer · answered Mar 04 '20 at 07:42

I made some small adjustments to your code. See if it now works?

function todayDateNowTime () {
const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", 
"Aug", "Sep", "Oct", "Nov", "Dec"];
var today = new Date()
var month = monthNames[today.getMonth()]; //months from 1-12
var day = today.getDate();
day = day < 10 ? "0" + day : day;
var year = today.getFullYear();
var seconds = today.getSeconds();
var minutes = today.getMinutes();
var hour = today.getHours();  
var todayDate = day+"-"+month+"-"+year;
var nowTime = hour+":"+minutes+":"+seconds;

return [todayDate, nowTime];
}

function clockin() {
var dateAndTime = todayDateNowTime();
var todayDate = dateAndTime[0];
var nowTime = dateAndTime[1];
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var data = sheet.getDataRange().getDisplayValues();
for(var i = 0; i<data.length;i++){
  if(data[i][1] == todayDate) { //[1] because column B
  var range = sheet.getRange(i+1, 3)
  range.setValue(nowTime);
  }
}
}

Google apps script for GSheet - Find today's date on column B and write current time on column C of the same row

1 Answers1