I've been grappling with a formatting issue that I'm hoping someone can assist me with. In my script, there's a point where I need to combine the date value (e.g., 11/20/2020) from one column with the time (3:00 PM) from another column. This combined value is then used to call the createEvent method of the calendar service class in order to create an event. The problem I'm encountering is that unless I manually format the cells in the spreadsheet using Format->Number->Plain Text, the concatenation results in nonsensical output.
concatenated dateStartTime is Wed Nov 20 2020 00:00:00 GMT-0500 (Eastern Standard Time) Sat Dec 30 1899 15:00:00 GMT-0500 (Eastern Standard Time)
and the cell(E4) will have value:
*Wed Nov 20 2020 00:00:00 GMT-0500 (Eastern Standard Time) Sat Dec 30 1899 15:00:00 GMT-0500 (Eastern Standard Time)*
This same issue occurs with concatenatedDateEndTime after running the script.
What I really need is to have the plain text "11/20/2020 3:00 PM" in the F4 cell where the concatenation takes place. Despite consulting the documentation and utilizing setNumberFormat("@"), none of the suggestions in this Post have proven effective.
Below is the relevant code snippet:
// Now we have to convert date,startTime and endTime var's to proper format to send to
// Calendar service
ss.getRange("B" + currentRowNumber).setNumberFormat("@");
ss.getRange("F" + currentRowNumber).setNumberFormat("@");
ss.getRange("G" + currentRowNumber).setNumberFormat("@");
// Let's join date & start time and date & end time columns
var concatenatedDateStartTime = eventDate + " " + startTime;
var concatenatedDateEndTime = eventDate + " " + endTime;
// Now lets create the calendar event with the pertinent data from the SS. See
// https://developers.google.com/apps-script/reference/calendar/calendar-app#geteventsstarttime,-endtime
eventCal.createEvent(
summary,
new Date(concatenatedDateStartTime),
new Date(concatenatedDateEndTime),
event
);
I would like to streamline the process for users by allowing them to simply paste in a row of data without worrying about formatting the appropriate cells, as I aim to handle this programmatically.