convert google form timestamp to day-only date

Question

when I save each google form entry in my linkde google sheet, it adds a column with the response's timestamp. What I see in that cell is something like this "17/01/2024 20:55:23". When I print the content of this cell in AppsScript (through console.log()) I get something like this "Wed Jan 17 2024 20:55:22 GMT+0100 (Central European Standard Time)".

What I need is really to be able (in Apps Script) to compare the day in that cell to the current day (new Date()).

So far I haven't managed to do so in Apps Script. Not sure if I should use substrings to manually extract the interesting part from the sheet's cell (i.e. the day), or if there's a built in function to get the day out of that string? In google sheet the TO_DATE(INT(A1)) function works wonderfully so ideally I'm looking for something like this to use in Apps Script Thanks

Answer 1

To compare the day in a cell with the current day in Google Apps Script, you can use JavaScript's Date object. The date in your Google Sheet is automatically converted to a JavaScript Date object when you retrieve it in Apps Script. Here's the code snippet:

function isSameDay(date1, date2) {
  var d1 = new Date(date1);
  var d2 = new Date(date2);

  return d1.getDate() === d2.getDate() &&
         d1.getMonth() === d2.getMonth() &&
         d1.getFullYear() === d2.getFullYear();
}

function checkDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cellDate = sheet.getRange('A1').getValue(); // Assuming the date is in A1
  var currentDate = new Date();

  var isSameDayResult = isSameDay(cellDate, currentDate);

  console.log("Are the two dates the same day? " + isSameDayResult);
}

You need to make sure that both the spreadsheet's (File -> Settings) and Apps Script's (in appscript.json that you can enable in Project Settings) timezones are the same. Usually they are the same.