skip to Main Content
  • Background:
    I am pretty new to apps script, i have some beginner knowledge in other languages. I am trying to do some dates math, this happens in a loop when a user enters two dates (from and to) in cells. I am trying to iterate each date and enter some values in some cells.

  • Issue:
    The user enters the value as "MM/DD/YYYY". However, when using 1/1/2023, this becomes as "Sat Dec 31 16:00:00 GMT-05:00 2022". Also, subtraction works fine (by the number of milli seconds in a day multiplied by the number of days to be subtracted), but the addition operation fails and returns the same value, which is "Sat Dec 31 16:00:00 GMT-05:00 2022".

  • Other StackOverflow topics:
    I checked similar topics, but they had which got me to add trial 2 and 3, but my problem is specific to the 1/1/yyyy becoming 12/31/yyyy-1.

  • The code:

function PlayGround() {  
    // setting variables
    var spreadsheet = SpreadsheetApp.getActive();
    const MILLIS_PER_DAY = 1000 * 60 * 60 * 24 * 5; // multiplied by 5 (5 days)
    const now = new Date();

    // trial 1
    const aDate = spreadsheet.getRange('J4').getValue()
    // J4 data type is set to date from the interface, and its value is equal to "1/1/2023"
    Logger.log(new Date(aDate));
    Logger.log(new Date(aDate - MILLIS_PER_DAY));
    Logger.log(new Date(aDate + MILLIS_PER_DAY));
    // function returns
    // 2:56:12 PM   Info    Sat Dec 31 16:00:00 GMT-05:00 2022
    // 2:56:12 PM   Info    Mon Dec 26 16:00:00 GMT-05:00 2022
    // 2:56:12 PM   Info    Sat Dec 31 16:00:00 GMT-05:00 2022

    // trial 2
    var new_d = Utilities.formatDate(new Date(aDate), "EET", "MM/dd/yyyy")
    Logger.log(new Date(new_d));
    Logger.log(new Date(new_d - MILLIS_PER_DAY));
    Logger.log(new Date(new_d + MILLIS_PER_DAY));
    // function returns
    // 3:14:35 PM   Info    Sat Dec 31 00:00:00 GMT-05:00 2022
    // 3:14:35 PM   Info    Wed Dec 31 19:00:00 GMT-05:00 1969
    // 3:14:35 PM   Info    Wed Dec 31 19:00:00 GMT-05:00 1969

    // trial 3
    const d = new Date(aDate);
    Logger.log(d);
    Logger.log(new Date(d.setDate(d.getDate() + 5)));
    Logger.log(new Date(d.setDate(d.getDate() - 5)));
    Logger.log(new Date(d.setDate(d.getDate() - 20)));
    // function returns
    // 3:14:35 PM   Info    Sat Dec 31 16:00:00 GMT-05:00 2022
    // 3:14:35 PM   Info    Thu Jan 05 16:00:00 GMT-05:00 2023
    // 3:14:35 PM   Info    Sat Dec 31 16:00:00 GMT-05:00 2022
    // 3:14:35 PM   Info    Sun Dec 11 16:00:00 GMT-05:00 2022
    // the -5 does not work, but - 20 works!!

    }

2

Answers


  1. For Trial 1 I’d check the type of your aDate variable. It could be that you are retrieving a string instead of a Date instance from your Google Sheet. Check the formatting of the cell you’re pulling your date from.


    Trail 2 doesn’t make any sense. You are essentially subtracting two disparate types. In terms of Javascript, the following statement in your code:

    new_d - MILLIS_PER_DAY
    

    is equivalent to

    "12/31/2022" - 86400000
    

    Apps Script/Javascript does not infer Date types from strings.


    The code in Trial 3 is actually executing as expected. Apps Script uses a Javascript runtime under the hood (ie V8) so you inherit all the idiosyncrasies of Javascript’s Date class. That means that when you set a date using the setDate() function, you change the value of the date in place. So the value/state of date d is updated with every call to setDate().

    Login or Signup to reply.
  2. Try this:

    function PlayGround() {
      const ss = SpreadsheetApp.getActive();
      const day = 1000 * 60 * 60 * 24;
      const dt = new Date();
      ss.getRange("J4").setValue(new Date()).setNumberFormat("MM dd, yyyy HH:mm:ss");
      const aDate = new Date(ss.getRange('J4').getValue());
      Logger.log(new Date(aDate));
      Logger.log(new Date(aDate.valueOf() - day));
      Logger.log(new Date(aDate.valueOf() + day));
      var newds = Utilities.formatDate(new Date(aDate), "GMT-7", "MM/dd/yyyy")
      Logger.log(new Date(newds));
      let newd = new Date(newds);
      Logger.log(new Date(newd.valueOf() - day));
      Logger.log(new Date(newd.valueOf() + day));
      const d = new Date(aDate);
      Logger.log(d);
      Logger.log(new Date(d.setDate(d.getDate() + 1)));
      Logger.log(new Date(d.setDate(d.getDate() - 1)));
      Logger.log(new Date(d.setDate(d.getDate() - 20)))
    }
    
    Execution log
    4:51:57 PM  Notice  Execution started
    4:51:57 PM  Info    Tue Sep 26 16:51:57 GMT-06:00 2023
    4:51:57 PM  Info    Mon Sep 25 16:51:57 GMT-06:00 2023
    4:51:57 PM  Info    Wed Sep 27 16:51:57 GMT-06:00 2023
    4:51:57 PM  Info    Tue Sep 26 00:00:00 GMT-06:00 2023
    4:51:57 PM  Info    Mon Sep 25 00:00:00 GMT-06:00 2023
    4:51:57 PM  Info    Wed Sep 27 00:00:00 GMT-06:00 2023
    4:51:57 PM  Info    Tue Sep 26 16:51:57 GMT-06:00 2023
    4:51:57 PM  Info    Wed Sep 27 16:51:57 GMT-06:00 2023
    4:51:57 PM  Info    Tue Sep 26 16:51:57 GMT-06:00 2023
    4:51:57 PM  Info    Wed Sep 06 16:51:57 GMT-06:00 2023
    4:51:59 PM  Notice  Execution completed
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search