CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Cadell Teng
Cadell Teng

Reputation: 244

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

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: enter image description here

Upvotes: 0

Views: 476

Answers (1)

JPV
JPV

Reputation: 27242

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);
  }
}
}

Upvotes: 1

Related Questions