The system is a Google Sheets solution, containing scripts which updates a Google calendar, sends emails, and collects registrations. Here are some of the tweaks made to this template made by delving into the code and reworking some elements to fit
Making this work with Outlook
I noticed that the .ics invitation file which is sent out when attendees register did not work in Outlook, though works fine in a Google calendar. The problem appeared to be that the ics file did not specify an Organiser, so Outlook assumed that the recipient was the organiser and the event was already in the calendar. The solution to this was to add an organiser/trainer column (organiser) + email address column (orgmail) to the Google sheet, and bring this information into the .ics file
ics += "ORGANIZER;CN=" + eventRowData.organiser + ":mailto:" + eventRowData.orgmail + "\r\n";
These new columns also gave me the data required to send out feedback forms to the Trainer when the course was complete.
Getting Attendee Feedback
IMPORTRANGE("<spreadsheet key>","<sheet>!<range>")
e.g.
=ImportRange("0AisVSZMkrof5dEw5M01Kb1RMb2cybjB2aGlLX00tNXc","Form Responses!d:e")
The information from the separate feedback response sheets are now in a sheet the master template, and automatically refreshed with new data as forms are submitted.
Adding new menu items to the Google Sheet
Two new scripts to enable feedback surveys to be sent out were created - via New -> Script file from the coding screen.
AttendFeedback and InstructFeedback
A nice trick from the Template was the ability to add triggers for these to the Sheets menu, allowing you to activate or disable the functionality with only a couple of clicks. I therefore used this trick for the new scripts.
Similar to when you add a Private Sub Workbook Open() event in excel, Google sheets allows you to use the function onOpen() which is automatically executed every time a Spreadsheet is loaded. With the code below, the new menu items are added for the toggle triggers. (NB. Code has been trimmed here to exclude the other menu items for ease of viewing, though in the spreadsheet, the other menu triggers have been kept)
function onOpen() {
var menuEntries = [{
name: "Instructor Feedback Surveys (sent after event)",
functionName: "InstructFeedbackSetup"
},{
name: "Attendee Feedback Surveys (sent after event)",
functionName: "FeedbackSetup"
}];
ss.addMenu("Event booking", menuEntries);
}
Emailing links to feedback forms, prefilling course ID and Title
Google forms allow you to prefill certain information into fields, using a query string in the URL. I wanted attendees to be able to provide feedback through a new button on the calendar event and also to get sent an email when the course finished with a link to that form - with the course ID and Title prefilled.
The button was easy, by adapting the existing script which pushes event details to the calendar,
On the templates sheet, you can enter the required HTML in a cell, and get the script to push this to the calendar. I therefore created HTML code for the button
<div id="feedback_button" style="text-align:right; padding:5px"><a href="https://docs.google.com/*******/forms/d/14snn4VMc0rtsy6mSPsl_sGJaC3eclU7JbU2Q2VYaWOw/viewform?entry.13782825=${"Event Id"}&entry.1644682605=${"Event Title"}" style="-moz-box-shadow:inset 0px 1px 0px 0px #cae3fc; -webkit-box-shadow:inset 0px 1px 0px 0px #cae3fc; box-shadow:inset 0px 1px 0px 0px #cae3fc; background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #79bbff), color-stop(1, #4197ee) ); background:-moz-linear-gradient( center top, #79bbff 5%, #4197ee 100% ); filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#79bbff', endColorstr='#4197ee'); background-color:#79bbff; -webkit-border-top-left-radius:0px; -moz-border-radius-topleft:0px; border-top-left-radius:0px; -webkit-border-top-right-radius:0px; -moz-border-radius-topright:0px; border-top-right-radius:0px; -webkit-border-bottom-right-radius:0px; -moz-border-radius-bottomright:0px; border-bottom-right-radius:0px; -webkit-border-bottom-left-radius:0px; -moz-border-radius-bottomleft:0px; border-bottom-left-radius:0px; text-indent:0; border:1px solid #469df5; display:inline-block; color:#ffffff; font-family:Arial; font-size:11px; font-weight:bold; font-style:normal; height:28px; line-height:30px; width:90px; text-decoration:none; text-align:center; text-shadow:1px 1px 0px #287ace" target="_blank">FEEDBACK</a></div>
Extracting the${"Event Title"}
for example is made possible by a utility already included on the template which grabs applicable row data for an event.NB The html renders in a published or embedded view of the calendar, so embedding this in a Google site works fine. As you can see there is now a new feedback button which passes the prefill values to the form
Google needs a plus symbol to replace spaces.... this caused a challenge when coding a link to be sent by email to the same form, as simply grabbing the value ignores any words after the first space.
The solution to this was a a combination of a cell value in the sheet translated with a regular expression
On the templates sheet, I put in a cell which grabs the appropriate event title ${"Event Title"}
Here's the (abridged) code
// Let's grab the Event title for use later
var reminderSubjectTemplate = templateSheet.getRange("E7").getValue();
..............
var TitletoEncode = fillInTemplateFromObject_(FeedbackFormTitleFixed, rowData);
// Lets replace the spaces with pluses
var EncodeTitle = encodeURIComponent(TitletoEncode).replace(/%20/g, '+');
This can then be used in an email (sample here for the instructor feedback)
var emailText = "As an instructor, please could you provide your feedback on " + rowData.eventTitle + " by completing the survey <a href=" + feedbackURL + "?entry_355127635=" + event.getId() + "&entry_1189378848=" + EncodeTitle + ">on this link</a>";
NB. To get the IDs of the form fields (the &entry_********) you can either do a test prefill from the normal form interface and grab those from there, or use Firebug or (F12)IE Dev tools or a Chrome inspect element to expose these instead.
Scheduling emails
When a script has fired, a cell on the spreadsheet updates to confirm this. This also prevents duplicate emails being sent out, as in the script you can just tell the script not to fire depending on the value of that cell
if(rowData.status != undefined && rowData.status != "INST_FEEDBACK_SENT"
I wanted the attendee feedback reports to be sent out, and then the instructor feedback to be sent out after a delay, to allow the sheet to update the cell values in the interim and prevent duplicate emails. The solution to this is to tell Google exactly when to send them via the script e.g.
The attendee survey emails will be triggered every hour
ScriptApp.newTrigger('sendAttFeedback').timeBased().everyHours(1).create();
but will only actually be sent if the course has finished
// check to see if event has finished
if(rowData.status != undefined && rowData.status != "ATT_FEEDBACK_SENT" && rowData.status != "INST_FEEDBACK_SENT" && (endDate - now) < 0){
The Instructor survey emails will be triggered in the first hour of the next day
ScriptApp.newTrigger('sendInstructFeedback').timeBased().everyDays(1).atHour(1).create();
but will again only be sent if the course has finished using the same method if(rowData.status != undefined && rowData.status != "INST_FEEDBACK_SENT" && (endDate - now) < 0){
Google Sheets appear to be a really versatile and powerful tool. Hope this has been of interest! Any comments or queries welcomed!
No comments:
Post a Comment