Good afternoon! This my first time posting and let me acknowledge that I am a professional educator but an amateur at script writing. I used some guidance from this site to write a script that creates a calendar event each time a form is submitted. The form is for "early releases" for teams from our school so I made a dummy sheet as an example here:
https://docs.google.com/spreadsheets/d/1b-8FWWppyNqfycMmc4E3TLT4Fl7W-TJqoG3Zy6XSCkQ/edit?usp=sharing
. The calendar events then go to an activity calendar that is shared with teachers. The issue I am having is the time's submitted always default to am but most are pm. I tried to fix it myself but to no avail. Any help would be appreciated!
function createCalEvent(e) {
Logger.log(e);
// this will return something like this :
/*
{values=[11/9/2014 22:30:00, serge, test descr, 11/7/2014, Before school | 7:30],
namedValues={Your Full Name=[serge], Work to Make Up=[test descr], Date You Will Make Up Assignment=[11/7/2014],
Makeup Time=[Before school | 7:30], Timestamp=[11/9/2014 22:30:00]}, range=Range, source=Spreadsheet, authMode=FULL}
*/
var cal = CalendarApp.getCalendarById("c_2d83fb444341942e6e44b791b97bd5db57611ab24aebfc16ed17a8b4a5ca0ad2@group.calendar.google.com");// replace with the right calendar ID, this one is for test (and is public)
var name = e.namedValues["Coach/Advisor Name"][0];
var team = e.namedValues["Team/Club/Organization"][0];
var descr = e.namedValues["Name of Event"][0];
var location = e.namedValues["Location to be released to"][0];
var date = e.namedValues["Date of Event"][0].split('/');
var time = e.namedValues["Time for Release"][0].split(':');
var hrs = time[0];
var mnts = time[1];
Logger.log(name+' '+descr+' '+date[1]+' '+time[1]); // this will return serge test descr 11,7,2014 7,30
var startTime = new Date(date[2],date[0]-1,date[1]);
var hours = hrs.toString();
var minutes = mnts.toString();
startTime.setHours(hours,minutes,0,0);
endTime = new Date(startTime.getTime()+900000); //assuming event is 1 hour long
Logger.log('start='+startTime+' end='+endTime);
cal.createEvent('Early Release for '+team, startTime, endTime, {'description':descr+' submitted by '+name+ '(please release to '+location+')'});
cal.addEmailReminder(15);
cal.addPopupReminder(15);
}
I tried to create an "if" statement where if hours < 4, then +12 but it ended up moving the events to the wrong days and still not the right time. With the code as it is now, I get the right day, and hour, it's just the events are set to 2 or 3 am instead of pm.
The issue I am having is the time's submitted always default to am but most are pm.
The issue
The "problem" is that time is entered in the Form as a 24 hour clock but is recorded in the Sheet/Event Object as a 12 clock.
| Form | Sheet |
|---|---|
| 08:30 | "8:30:00 AM" |
| 15:30 | "3:30:00 PM" |
In the sheet, the meridian marker is at the end of the time ("AM" or "PM"). This means that the hour value will always fall between 1 and 12 regardless of whether the Release Time is "AM" or "PM".
This line: var time = e.namedValues["Time for Release"][0].split(':'); captures the split for hours and minutes but doesn't return the meridian marker.
Solution
var timeMarker = e.namedValues["Release time"][0].slice(-2)For example:
(12+3)This answer uses slice(-2) to extract the marker (last 2 characters in the time string) and then an IF statement to test for "PM", but there are many ways of doing this. The method in this answer should be regarded as just one approach.
function releaseHours(e) {
//Logger.log(JSON.stringify(e)) // DEBUG
// get the Release time
var time = e.namedValues["Release time"][0].split(':');
// get the time marker (AM or PM)
var timeMarker = e.namedValues["Release time"][0].slice(-2)
Logger.log("DEBUG: RAW DATA: Time = "+time+", time marker = "+timeMarker)
if (timeMarker === "PM"){
var hrs = parseInt(time[0])+12
}
else{
var hrs = parseInt(time[0])
}
Logger.log("DEBUG: Adjusted hours = "+hrs)
}