Thursday 4 April 2019

Fixing dates from a Google Form

Following on from my 'Check if a date is more than a month ago' post I proceeded to place the Google Apps Script code within a Form to achieve the original task that was asked. To my surprise it did not work anymore!

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.

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.

I had a couple of workarounds in mind:
  1. 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.
  2. 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 ...
The below script works very much in principle to the previous blog post but I want to draw attention to the 4 lines of code that rebuild the date for Apps Script to handle:
var day = favDate[0].substring(0, 2);
var month = favDate[0].substring(3, 5);
var year = favDate[0].substring(6, 10);
var newFavDate = new Date(year, month - 1, day);
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.

Below is a copy of the full code from a working version and then a link to the Form/Sheet itself.

Response sheet.xlsx

No comments:

Post a Comment