What was wrong?
Google Apps Script was flipping the Day/Month from the Form date - so 01/02/2019 (1st February 2019) became 02/01/2019 (2nd January 2019) when creating a 'new Date()' variable. Interestingly the date that went into the Response sheet was perfectly fine and remained intact.
Why?
After plenty of Googling I finally discovered a number of complaints about Googles capability to handle dates (Stack Overflow 1, Stack Overflow 2). So even though I was using a Google Form with Google Apps Script it was ineffective to get a date directly from it.
Workaround
I had a couple of workarounds in mind:
- Get the date directly from the Response spreadsheet instead - as it was correct here Google Apps Script did not try to do anything erroneous with it. My issue with this workaround was that I am already taking other data directly from the Form - I was not happy with mixing and now getting it from the sheet too.
- Parse the date myself and create it by breaking down the date from the Form (so getting the Day, Month, Year separately). This way I was getting everything from the Form and could in effect create a function to perform the task for multiple dates on a Form. This is the option I have explored below ...
var day = favDate[0].substring(0, 2);So with a date of 01/02/2019 for example, we use 'substring' to extract the 3 specific parts (dd/mm/yyyy). The 'substring' method requires a start position (beginning at 0) and an end position - but it does not actually include this end character.
var month = favDate[0].substring(3, 5);
var year = favDate[0].substring(6, 10);
var newFavDate = new Date(year, month - 1, day);
Below is a copy of the full code from a working version and then a link to the Form/Sheet itself.
Form
Response sheet.xlsx
No comments:
Post a Comment