Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – convert google form timestamp to day-only date

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Difference in days

    function compareDates01() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
  const dt = new Date();
  const dv = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate()).valueOf();//current date value 1/17/2024
  const day = 1000 * 60 * 60 * 24;//milliseconds in a day
  const col4 = vs.map(r => {
    let d = new Date(r[0]);
    return [(new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf() - dv) / day];//convert timestamp to time at 0:00 hours the beginning of  a day
  });
  col4.unshift(['Diff in Days']);
  Logger.log(JSON.stringify(col4).replace(/],/g, '],n'));//the replace just formats the output to have each row on a different line
  sh.getRange(1, 4, col4.length, col4[0].length).setValues(col4);
}

    Sheet0 before:

    COL1 COL2 COL3
    1/12/2024 B2 C2
    1/13/2024 B3 C3
    1/14/2024 B4 C4
    1/15/2024 B5 C5
    1/16/2024 B6 C6
    1/17/2024 B7 C7
    1/18/2024 B8 C8
    1/19/2024 B9 C9
    1/20/2024 B10 C10
    1/21/2024 B11 C11
    1/22/2024 B12 C12

    Sheet0 after:

    COL1 COL2 COL3 Diff in Days
    1/12/2024 B2 C2 -5
    1/13/2024 B3 C3 -4
    1/14/2024 B4 C4 -3
    1/15/2024 B5 C5 -2
    1/16/2024 B6 C6 -1
    1/17/2024 B7 C7 0
    1/18/2024 B8 C8 1
    1/19/2024 B9 C9 2
    1/20/2024 B10 C10 3
    1/21/2024 B11 C11 4
    1/22/2024 B12 C12 5 
    Execution log
5:56:45 PM  Notice  Execution started
5:56:46 PM  Info    [["Diff in Days"],
[-5],
[-4],
[-3],
[-2],
[-1],
[0],
[1],
[2],
[3],
[4],
[5]]
5:56:47 PM  Notice  Execution completed
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search