-
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
For
Trial 1
I’d check the type of youraDate
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:is equivalent to
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 thesetDate()
function, you change the value of the date in place. So the value/state of dated
is updated with every call tosetDate()
.Try this: