[SOLVED] How to deal with dates from Forms to Spreadsheets using Google Script

How to deal with dates from Forms to Spreadsheets using Google Script

I have a Google Script that takes the input of a date of a Google Form field and I want to save the day, month and year separately. In particular, I want to change the month number to text i.e. 03 means March. That's why I use substring.

Format of Date in Google Forms: dd/mm/yyyy.
Format of Date in Google Spreadsheets: d/mm/yyyy if day<10 and dd/mm/yyyy if day>=10.

The critical part is here:

const cDay = e.namedValues['Date'][0].substring(0,2);
var cMonth = e.namedValues['Date'][0].substring(3,5);
const cYear = e.namedValues['Date'][0].substring(6,10);

If the user submits a day less than 10, then the code is broken.

How can we solve this problem not changing the format on the Google Spreadsheets file?

I couldn't find a way using if or switch statements.

More explanation

I tried with this code:

var cDay;
var cMonth;
var cYear;
if (e.namedValues['Date'][0].substring(1,2) == '/') { // Days are 1,..,9
  cDay = e.namedValues['Date'][0].substring(0,1);
  cMonth = changeMonth(e.namedValues['Date'][0].substring(2,4));
  cYear = e.namedValues['Date'][0].substring(5,9);
} else { // Days are 10,..,31
  cDay = e.namedValues['Date'][0].substring(0,2);
  cMonth = changeMonth(e.namedValues['Date'][0].substring(3,5));
  cYear = e.namedValues['Date'][0].substring(6,10);
}

Function changeMonth:

function changeMonth(month) {
  switch (month) {
    case '01':
    case '1':
      cMonth = 'January';
      break;
    case '02':
    case '2':
      cMonth = 'February';
      break;
    case '03':
    case '3':
      cMonth = 'March';
      break;
    case '04':
    case '4':
      cMonth = 'April';
      break;
    case '05':
    case '5':
      cMonth = 'May';
      break;
    case '06':
    case '6':
      cMonth = 'June';
      break;
    case '07':
    case '7':
      cMonth = 'July';
      break;
    case '08':
    case '8':
      cMonth = 'August';
      break;
    case '09':
    case '9':
      cMonth = 'September';
      break;
    case '10':
      cMonth = 'October';
      break;
    case '11':
      cMonth = 'November';
      break;
    case '12':
      cMonth = 'December';
      break;
  }
}

It seems the error occurs when I use replaceText:

body.replaceText('<<Day>>', cDay);
body.replaceText('<<Month>>', cMonth); // I get "Invalid argument: replacement"
body.replaceText('<<Year>>', cYear);

I found that a solution is to add String(...) to the parameters of replaceText so I have: body.replaceText(String('<<Month>>'), String(cMonth));.

However, when I see the results I get the correct day and year but the month is undefined. What can we do?

Example

If the user submits 03/05/2000 on Forms, the output has to be: You have submitted the form on the 3º day of the month of May of the year 2000. Instead I have You have submitted the form on the 3º day of the month of undefined of the year 2000.

Solution

Instead of parsing the dates by yourself, you could use the JavaScript Date object to get the day, month and year.

Note: this solution is applicable if the format of date is dd/mm/yyyy

Try this:

function formSubmit(e) {
  var dateStr = e.namedValues["Date"][0];
  var dateArr = dateStr.split("/");
  var newDate = dateArr[1]+"/"+dateArr[0]+"/"+dateArr[2];
  var date = new Date(newDate);
  var day = date.getDate();
  var month = date.getMonth();
  var year = date.getFullYear();
  
  const monthNames = ["January", "February", "March", "April", "May", "June",
  "July", "August", "September", "October", "November", "December"
  ];

  Logger.log("You have submitted the form on the "+day+"º day of the month of "+monthNames[month]+" of the year "+year)
}

Output:

Reference:

JavaScript Date