Each year in the classroom, my use of student laptops has increased. Three years ago, my first year teaching Texas History, I did not have many lessons based on computers. However, after feeling more secure with my content, it was easy to find lessons that would benefit from technology. The tricky part was scoring enough laptops for my students.
Since I have been coding for about two year, I wanted to see if I could tackle this problem using Google Apps Script. Google allows people to write programs utilizing their excellent online office suite.
First I used Google Forms to collect requests for laptops. All Form information is automatically submitted to a Google Spreadsheet. Knowing this, I wrote a program that takes the information from the spreadsheet, “talks” to Google Calendar, and then creates an event and confirmation email.
Sounds simple enough. Coding it, not so simple. I did enjoy the challenge though and learned a bunch grinding away at it.
Below is posted the code that takes the information from the spreadsheet and creates a calendar event out of it. In a few weeks I’ll post a proper write up of my code and throw it up on github to get some critiques. Hopefully after that, I’ll release it as an add-on for others to tinker with.
[code language=”js”]// Laptop Cart Google Calendar Reservation Code
// By Kurt Kaiser, 2015
// All Rights Reserved ©
//
var sheet = SpreadsheetApp.getActiveSheet();
var numberOfCalendars = 10;
var calendarsObject = {
1: CalendarApp.getCalendarById
(’roundrockisd.org_n3s3kl969vepbpar1s1gqaj6e4@group.calendar.google.com’),
2: CalendarApp.getCalendarById
(’roundrockisd.org_nq8p1bt3ob61fpv99mk7vtnkq0@group.calendar.google.com’),
3: CalendarApp.getCalendarById
(’roundrockisd.org_93b663uftsc7mgrjkecc8o83lk@group.calendar.google.com’),
4: CalendarApp.getCalendarById
(’roundrockisd.org_tbcgpkliuaie013umndlba1ahc@group.calendar.google.com’),
5: CalendarApp.getCalendarById
(’roundrockisd.org_p9nhrujhjh966idgag8kji3gho@group.calendar.google.com’),
6: CalendarApp.getCalendarById
(’roundrockisd.org_nbvpt1v4nfo9cttvldo3j7kb3s@group.calendar.google.com’),
7: CalendarApp.getCalendarById
(’roundrockisd.org_mhv8rjc8754j81f7n8u56qc3c0@group.calendar.google.com’),
8: CalendarApp.getCalendarById
(’roundrockisd.org_sorguq2hn96s98bemjtsucdlmo@group.calendar.google.com’),
9: CalendarApp.getCalendarById
(’roundrockisd.org_60nnoooq4o565ppcut9khhpnv4@group.calendar.google.com’),
10: CalendarApp.getCalendarById
(’roundrockisd.org_he5nk4pm1qqp2rjk1cjstjf3ps@group.calendar.google.com’),
11: CalendarApp.getCalendarById
(’roundrockisd.org_jsi38pgmn4i0a4c5ovjokadnd8@group.calendar.google.com’),
12: CalendarApp.getCalendarById
(’roundrockisd.org_rq9jfg952ac4b1ldbttbfnd604@group.calendar.google.com’),
13: CalendarApp.getCalendarById
(’roundrockisd.org_g1aovfes3f1hl5evuve8tsats8@group.calendar.google.com’),
14: CalendarApp.getCalendarById
(’roundrockisd.org_2tkluchahajgsd8g91uo4hnnrg@group.calendar.google.com’),
313: CalendarApp.getCalendarById
(’roundrockisd.org_ia1o1ejh9jjfu3o6h043osufkk@group.calendar.google.com’),
};
function onFormSubmit(){
Logger.clear();
Logger.log("INSIDE: main()");
request = new Request();
if (checkForIssues(request)){
makeIssuesEmail(request);
Logger.log("this.email.subject: " + request.email.subject);
} else {
getCartsAndCals(request);
checkAvailability(request);
if (request.carts.notAvailable.length > 0){
makeDateConflictEmail(request);
sendEmail(request);
}
if (request.carts.available.length > 0){
createEvent(request);
makeConfirmEmail(request);
sendEmail(request);
}
}
}
//Main Function triggered when form is submitted to the spreadsheet
function Request(){
Logger.log("INSIDE: Request Constructor");
this.lastRow = getLastRowAndFormat();
this.username = sheet.getRange("B" + this.lastRow).getValue();
this.lastName = sheet.getRange(‘D’ + this.lastRow).getValue();
this.start = new Date(sheet.getRange(‘F’ + this.lastRow).getValue());
this.end = new Date(sheet.getRange(‘G’ + this.lastRow).getValue());
this.duration = sheet.getRange(‘H’ + this.lastRow).getValue()+1;
this.recurrence = CalendarApp.newRecurrence().addDailyRule().times(this.duration);
this.carts = {
nums: sheet.getRange(‘E’ + this.lastRow).getValue(),
calendarId: [],
available: [],
notAvailable: []
};
this.email = {
subject: "",
header: "",
message: "",
image: "",
html: "",
htmlMesagge: "",
buttons: ""
};
}
//Formats spreadsheet, calcs duration, returns last row
function getLastRowAndFormat(){
Logger.log("INSIDE: getLastRow()");
var lastRow = sheet.getLastRow();
//Format the spreadsheet
sheet.getRange(‘F’ + lastRow ).setNumberFormat(‘@STRING@’);
sheet.getRange(‘G’ + lastRow).setNumberFormat(‘@STRING@’);
sheet.getRange(‘H’ + lastRow).setFormula(‘=G’ + lastRow + ‘-F’ + lastRow);
return lastRow;
}
//Check to make sure duration is positive, start before end date
//Start day not more then 35 days out and end not more then 45
function checkForIssues(request){
Logger.log("function: checkForIssues()");
var today = new Date();
today = today.getTime();
var startDifference = request.start.getTime() – today;
var endDifference = request.end.getTime() – today;
Logger.log("End Difference: " + endDifference);
if ( startDifference > 3.024E9 || endDifference > 3.888E9 || request.duration < 0){
Logger.log("ISSUE: Date more then 35 days out or END before start date");
return true;
} else {
return false;
}
}
//Creates issues email
function makeIssuesEmail(request){
Logger.log("INSIDE: makeIssuesEmail()");
request.email.subject = "ERROR: Date Reservation Issue";
request.email.header = "<b>Date Issue</b>";
request.email.image = "http://imgur.com/Rpp1ShQ.png";
request.email.htmlMessage = "
There is issue with the requested dates
\
Either the end date occured before \
the start date or the reservation is more then 30 days away.
";
//createEmail(lastRow, username, lastName, start, end, cartNumArray, subject, header,image, htmlMessage);
}
//Select the Calendar
function getCartsAndCals(request){
Logger.log("INSIDE: getCartsAndCals()");
Logger.log("request.carts.nums: " + request.carts.nums);
request.carts.nums = request.carts.nums.toString();
request.carts.nums = request.carts.nums.split(", ");
for (var i = 0; i < request.carts.nums.length; i++){
Logger.log(" For loop in getCartsAndCals() iiii is: " + i);
request.carts.calendarId.push(calendarsObject[request.carts.nums[i]]);
Logger.log(request.carts.calendarId);
}
}
//Make sure no other event already exists on that day
function checkAvailability(request){
Logger.log("INSIDE: checkAvailability()");
for (var i = 0; i < request.carts.calendarId.length; i++){
Logger.log("**END DATE: " + request.end);
request.end.setDate(request.end.getDate() + 1);
var calEvent = request.carts.calendarId[i]
.getEvents(request.start, request.end);
request.end.setDate(request.end.getDate() – 1);
if(String(calEvent) == ""){
Logger.log("Cart " + request.carts.nums[i] + ": Available");
request.carts.available.push([String(request.carts.nums[i]),
request.carts.calendarId[i]]);
Logger.log("request.carts.available: " + request.carts.available[i]);
}else {
Logger.log("Cart " + request.carts.nums[i] + ": NOT available");
request.carts.notAvailable.push([String(request.carts.nums[i]),
request.carts.calendarId[i]]);
}
}
}
//Creates date conflict email
function makeDateConflictEmail(request){
Logger.log("INSIDE: makeDateConflictEmail()");
request.email.subject = "Error: Date Conflict on ";
request.email.header = "<b>Date Conflict</b>";
request.email.image = "http://i.imgur.com/F8Lmjns.png";
request.email.htmlMessage = "
A reservation already exists on all or part of the days
you requested.\
Your request is detailed below.
";
request.email.buttons = ‘
<table width="100%" border="0" cellspacing="0" cellpadding="0" style="margin-bottom:10px;">\
<tr>\
<h4>New Reservation?</h4>
\
<a href="http://bit.ly/1GuIU3L" style="background-color:#003399;border:1px solid\ #EB7035;border-radius:10px;color:#ffffff; display:inline-block;font-family:\ sans-serif;font-size:16px;line-height:44px;text-align:center;text-decoration:none;\ width:110px;-webkit-text-size-adjust:none;mso-hide:all;">Calendar</a>\
</td>
\
</tr>
\
</table>
‘;
createEmail(request, request.carts.notAvailable);
}
// *************************** Create and Confirm Reservation ***************** //
//Create calendar event
function createEvent(request){
Logger.log("INSIDE: createEvent()");
var cal;
for (var i = 0; i < request.carts.available.length; i++){
var titleEvent;
cal = request.carts.available[i];
if (cal[0].length == 1){
Logger.log("cartsAndCals.length: " + cal[0].length);
titleEvent = "0" + cal[0] + ": " + request.lastName;
}else{
titleEvent = cal[0] + ": " + request.lastName;
}
var event = cal[1].createAllDayEventSeries(
titleEvent,
new Date(request.start),
request.recurrence,
{guests: request.username});
}
}
//Creates confirmation email
function makeConfirmEmail(request){
Logger.log("INSIDE: makeDateConflictEmail()");
request.email.subject = "Reserved: ";
request.email.header = "<b>Confirmation</b>";
request.email.image = "https://sites.google.com/a/roundrockisd.org/misterkaiser/_/\
rsrc/1431369505928/home/underconstruction/laptop_clip.png";
request.email.htmlMessage = "
Reservation is as follows:
";
request.email.buttons = ‘
<table width="100%" border="0" cellspacing="0"\ cellpadding="0" style="margin-bottom:10px;">\
<tr>\
<td>\
<a href="http://bit.ly/1GuIU3L" style="background-color:#003399;border:1px\ solid #EB7035;border-radius:10px;color:#ffffff; display:inline-block;\ font-family:sans-serif;font-size:16px;line-height:44px;text-align:center;\ text-decoration:none; width:110px;-webkit-text-size-adjust:none;\ mso-hide:all;">Calendar</a>\
</td>
\
<td>\
<a href="http://bit.ly/1GuISsG" style="background-color: #900000;border:1px \ solid #EB7035;border-radius:10px;color:#ffffff;\ display:inline-block;font-family:sans-serif;font-size:16px;line-height:44px;\ text-align:center;text-decoration:none;\ width:110px;-webkit-text-size-adjust:none;mso-hide:all;">Cancel</a>\
</td>
\
</tr>
\
</table>
‘;
createEmail(request, request.carts.available);
}
// ************************************** Email Creation ****************** //
//Create email then call function to send it
function createEmail(request, requestCarts){
Logger.log("INSIDE: creatEmail()");
var carts = "Cart " + requestCarts[0][0];
if (requestCarts.length > 1){
var carts = "Carts " + requestCarts[0][0];
for ( i = 1; i < (requestCarts.length – 1); i++){
carts = carts + ", " + requestCarts[i][0];
Logger.log("IF EMAIL CARTS: " + carts);
}
carts = carts + " & " + requestCarts[requestCarts.length – 1][0];
}
request.email.subject = request.email.subject + carts
request.email.message = "";
request.email.html = ‘<body style="text-align: center;font-family: Arial;">\
<div id="center" style="width:300px;border: 2px dotted grey;background:#ececec;\ margin:25px;margin-left:auto;\ margin-right:auto;padding:15px;"><img src= "’ + request.email.image + ‘"width="150" style="margin:10px 0px">
\
<div style=" border: 2px dotted grey;background:white;margin-right:auto; \ margin-left:auto;\ padding:10px;">
<h2>’+ request.email.header + ‘</h2>
<h3>’ +request.email.htmlMessage + carts +
‘Start: ‘ + request.start.toString().substr(0, 10) + ", " +
request.start.toString().substr(11, 4) +
‘End: ‘ + request.end.toString().substr(0, 10) + ", " +
request.end.toString().substr(11, 4) +
‘</h3>
‘ + request.email.buttons +
‘Questions? kurt_kaiser@roundrockisd.org</div>
</div>
</html>\
</body>’;
}
// Send the confirmation or conflict email
function sendEmail(request) {
Logger.log("INSIDE: sendEmail()");
MailApp.sendEmail(request.username, request.email.message,
request.email.subject, request.email.html);
sheet.getRange("I" + request.lastRow).setValue("Sent");
SpreadsheetApp.flush();
}
[/code]
No Comments Yet