Skip to content

Instantly share code, notes, and snippets.

@hakarune

hakarune/gist:4985606

Forked from mhawksey/gist:1643207
Last active December 13, 2015 22:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save hakarune/4985606 to your computer and use it in GitHub Desktop.
Save hakarune/4985606 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.js
// EventManagerV3 glued together by mhawksey http://www.google.com/profiles/m.hawksey
// Related blog post http://mashe.hawksey.info/eventmanagerv3/
// With some code (settings, importIntoCalendar, sendEmails) from
// Romain Vialard's http://www.google.com/profiles/romain.vialard
// Manage your events: Calendar Importer and Registration Form
// https://spreadsheets.google.com/ccc?key=tCHuQkkKh_r69bQGt4yJmNQ
var ss = SpreadsheetApp.getActiveSpreadsheet();
var BOOKING_ACTION_COL = 10;
if (ss.getSheetByName("Templates").getRange("E1").getValue()!=""){
var TZ = CalendarApp.openByName(ss.getSheetByName("Templates").getRange("E1").getValue()).getTimeZone();
}
function onOpen() {
var conf = ss.getSheetByName("Templates").getRange("B3").getValue();
if (conf == "") {
ss.toast("Click on setup to start", "Welcome", 10);
}
var menuEntries = [ {name: "Process Events", functionName: "importIntoCalendar"}, {name: "Process Bookings", functionName: "sendBookingConf"}, {name: "Email joining instructions", functionName: "sendJoinEmails"} ];
ss.addMenu("Event Manager", menuEntries);
}
function settings(){
var calendarName = Browser.inputBox("First create a calendar in Google Calendar and enter its name here:");
if (calendarName != "cancel" && calendarName != ""){
var templateSheet = ss.getSheetByName("Templates");
templateSheet.getRange("E1").setValue(calendarName);
var formURL = ss.getFormUrl();
templateSheet.getRange("B3").setValue(formURL);
var calTimeZone = CalendarApp.getCalendarsByName(calendarName)[0].getTimeZone();
ss.setSpreadsheetTimeZone(calTimeZone);
var timeZone = ss.getSpreadsheetTimeZone();
var siteUrl = Browser.inputBox("If you would like to update events to a Sites page enter your announcement page url (otherwise just leave blank and click OK)");
if (siteUrl != "cancel" && siteUrl != ""){
templateSheet.getRange("E2").setValue(siteUrl);
}
var adminEmail = Browser.inputBox("Please enter your administrator email address");
if (adminEmail != "cancel" && adminEmail != ""){
templateSheet.getRange("B4").setValue(adminEmail);
}
ss.toast("You can now import events in your calendar. Time Zone is currently set to: "+timeZone+".", "Set up completed!", -1);
SpreadsheetApp.flush();
}
}
// http://code.google.com/p/google-apps-script-issues/issues/detail?id=264#c35
function getEvent(events,eventID){
for( var i in events )
{
if( events[i].getId() == eventID )
{
var event = events[i];
return event;
}// end if
}// end for var i in events
Browser.msgBox("Unexpected Error - function getEvents");
return event;
}
function importIntoCalendar(){
var dataSheet = ss.getSheetByName("Put your events here");
var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
var templateSheet = ss.getSheetByName("Templates");
var calendarName = templateSheet.getRange("E1").getValue();
var siteUrl = templateSheet.getRange("E2").getValue();
if (calendarName !=""){
var cal = CalendarApp.getCalendarsByName(calendarName)[0];
var eventTitleTemplate = templateSheet.getRange("E3").getValue();
var descriptionTemplate = templateSheet.getRange("E4").getValue();
var siteTemplate = templateSheet.getRange("E5").getValue();
// Create one JavaScript object per row of data.
objects = getRowsData(dataSheet, dataRange);
// For every row object, create a personalized email from a template and send
// it to the appropriate person.
for (var i = 0; i < objects.length; ++i) {
// Get a row object
var rowData = objects[i];
if (rowData.eventId && rowData.eventTitle && rowData.action == "Y" ){
var eventTitle = fillInTemplateFromObject(eventTitleTemplate, rowData);
var description = fillInTemplateFromObject(descriptionTemplate, rowData);
var siteText = fillInTemplateFromObject(siteTemplate, rowData);
// add to calendar bit
if(rowData.endDate == "All-day"){
var eventid = cal.createAllDayEvent(eventTitle, rowData.startDate, rowData.endDate, {location:rowData.location, description: description}).getId();
}
else{
var eventid = cal.createEvent(eventTitle, rowData.startDate, rowData.endDate, {location:rowData.location, description: description}).getId();
}
var events = cal.getEvents(rowData.startDate, rowData.endDate);
var event = getEvent(events, eventid);
event.addEmailReminder(15).setTag("Event ID", rowData.eventId);
// add to site bit
if (siteUrl != ""){
var page = SitesApp.getPageByUrl(siteUrl);
var announcement = page.createAnnouncement(rowData.eventTitle, siteText);
}
// create event sheet
var temp = ss.getSheetByName("EventTMP");
//var eventSheet = ss.insertSheet("Event "+rowData.eventId, {template:temp});
// copy format due to issue http://code.google.com/p/google-apps-script-issues/issues/detail?id=1050
var sh = ss.getSheetByName("EventTMP");
sh.getRange(1, 2, 1, 1).setValue(rowData.numberOfPlaces);
sh.getRange(1, 3, 1, 1).setValue(rowData.eventTitle);
sh.getRange(2, 3, 1, 1).setValue(rowData.location);
sh.getRange(3, 6, 1, 1).setValue(rowData.startDate);
sh.getRange(3, 8, 1, 1).setValue(rowData.endDate);
ss.insertSheet("Event "+rowData.eventId);
sh.getDataRange().copyTo(ss.getRange("Event "+rowData.eventId+"!A1"));
var nusheet = ss.getSheetByName("Event "+rowData.eventId);
nusheet.setFrozenRows(4);
nusheet.setFrozenColumns(2);
nusheet.setColumnWidth(1, 30)
nusheet.setColumnWidth(2, 50);
sh.getRange(1, 2, 1, 1).setValue("0");
sh.getRange(1, 3, 1, 1).setValue("Event Title");
sh.getRange(2, 3, 1, 1).setValue("Location");
sh.getRange(3, 6, 1, 1).setValue("start time");
sh.getRange(3, 8, 1, 1).setValue("end time");
/*
eventSheet.getRange(1, 2, 1, 1).setValue(rowData.numberOfPlaces);
eventSheet.getRange(1, 3, 1, 1).setValue(rowData.eventTitle);
eventSheet.getRange(2, 3, 1, 1).setValue(rowData.location);
eventSheet.getRange(3, 6, 1, 1).setValue(rowData.startDate);
eventSheet.getRange(3, 8, 1, 1).setValue(rowData.endDate);
*/
dataSheet.getRange(i+2, 1, 1, 1).setValue("");
dataSheet.getRange(i+2, 2, 1, 1).setValue("Added "+ Utilities.formatDate(new Date(), TZ, "dd MMM yy HH:mm")).setBackgroundRGB(221, 221, 221);
dataSheet.getRange(i+2,1,1,dataSheet.getMaxColumns()).setBackgroundRGB(221, 221, 221);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
}
}
ss.toast("People can now register to those events", "Events imported");
}
}
function onFormSubmit() {
var dataSheet = ss.getSheetByName("Bookings");
var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
var templateSheet = ss.getSheetByName("Templates");
var emailTemplate = templateSheet.getRange("E6").getValue();
var adminEmail = templateSheet.getRange("B4").getValue()
// Create one JavaScript object per row of data.
data = getRowsData(dataSheet, dataRange);
for (var i = 0; i < data.length; ++i) {
// Get a row object
var row = data[i];
row.rowNumber = i + 2;
if (!row.action) { // if no state notify admin of booking
var emailText = fillInTemplateFromObject(emailTemplate, row);
var emailSubject = "Booking Approval Request ID: "+ row.rowNumber;
MailApp.sendEmail(adminEmail, emailSubject, emailText);
dataSheet.getRange(row.rowNumber,BOOKING_ACTION_COL).setValue("TBC"); //9 is the column number for 'Action'
}
}
}
function sendBookingConf(){
var dataSheet = ss.getSheetByName("Bookings");
var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
var templateSheet = ss.getSheetByName("Templates");
var emailSubjectTemplate = templateSheet.getRange("B1").getValue();
var emailTemplate = templateSheet.getRange("B2").getValue();
var emailSentColumn = BOOKING_ACTION_COL;
// To add guests into Calendar
var calendarDataSheet = ss.getSheetByName("Put your events here");
var calendarDataRange = calendarDataSheet.getRange(2, 1, calendarDataSheet.getMaxRows(), calendarDataSheet.getMaxColumns());
var calendarName = templateSheet.getRange("E1").getValue();
// Create one JavaScript object per row of data.
calendarObjects = getRowsData(calendarDataSheet, calendarDataRange);
// Create one JavaScript object per row of data.
objects = getRowsData(dataSheet, dataRange);
// For every row object, create a personalized email from a template and send
// it to the appropriate person.
for (var i = 0; i < objects.length; ++i) {
// Get a row object
var rowData = objects[i];
if (rowData.action == "Y") { // Prevents sending duplicates
// add guest in calendar
for (var j = 0; j < calendarObjects.length; ++j) {
// Get a row object
var calendarRowData = calendarObjects[j];
if (calendarRowData.eventId == rowData.eventId){
var cal = CalendarApp.getCalendarsByName(calendarName)[0];
if(calendarRowData.endDate == "All-day"){
var getDate = new Date(calendarRowData.startDate).getTime();
var endDate = new Date().setTime(getDate + 86400000);
var events = cal.getEvents(new Date(calendarRowData.startDate), new Date(endDate));
}
else{
var events = cal.getEvents(new Date(calendarRowData.startDate), new Date(calendarRowData.endDate));
}
for (var k in events){
if (events[k].getTag("Event ID") == rowData.eventId){
events[k].addGuest(rowData.email);
j = calendarObjects.length;
}
}
}
}
// Generate a personalized email.
// Given a template string, replace markers (for instance ${"First Name"}) with
// the corresponding value in a row object (for instance rowData.firstName).
calendarRowData.bookingId = rowData.eventId+"-B"+(i + 2);
calendarRowData.firstName = rowData.firstName;
var emailSubject = fillInTemplateFromObject(emailSubjectTemplate, calendarRowData);
var emailText = fillInTemplateFromObject(emailTemplate, calendarRowData);
var emailAddress = rowData.email;
MailApp.sendEmail(emailAddress, emailSubject, emailText,{htmlBody: emailText});
// add booking to right event sheet
dataSheet.getRange(i + 2,emailSentColumn).setValue(calendarRowData.bookingId).setBackgroundRGB(221, 221, 221);
var eventSheet = ss.getSheetByName("Event " + rowData.eventId);
var rowNum = eventSheet.getLastRow()+1;
eventSheet.getRange(rowNum, 3, 1, 1).setValue(calendarRowData.bookingId);
eventSheet.getRange(rowNum, 4, 1, 1).setValue(rowData.timestamp);
eventSheet.getRange(rowNum, 5, 1, 1).setValue(rowData.totalNumber);
eventSheet.getRange(rowNum, 6, 1, 1).setValue(rowData.organisationName);
eventSheet.getRange(rowNum, 7, 1, 1).setValue(rowData.gradeLevel);
eventSheet.getRange(rowNum, 8, 1, 1).setValue(rowData.students);
eventSheet.getRange(rowNum, 9, 1, 1).setValue(rowData.adults);
eventSheet.getRange(rowNum, 10, 1, 1).setValue(rowData.firstName);
eventSheet.getRange(rowNum, 11, 1, 1).setValue(rowData.lastName);
eventSheet.getRange(rowNum, 12, 1, 1).setValue(rowData.phone);
eventSheet.getRange(rowNum, 13, 1, 1).setValue(rowData.email);
eventSheet.getRange(rowNum, 14, 1, 1).setValue(rowData.address);
eventSheet.getRange(rowNum, 15, 1, 1).setValue(rowData.teachers);
eventSheet.getRange(rowNum, 16, 1, 1).setValue(rowData.buses);
eventSheet.getRange(rowNum, 17, 1, 1).setValue(rowData.cars);
eventSheet.getRange(rowNum, 18, 1, 1).setValue(rowData.startPostcode);
eventSheet.getRange(rowNum, 19, 1, 1).setValue(rowData.preferredMode);
eventSheet.getRange(rowNum, 20, 1, 1).setValue(rowData.otherInfo);
eventSheet.getRange(rowNum, 21, 1, 1).setValue(rowData.invoice);
eventSheet.getRange(rowNum, 22, 1, 1).setValue(rowData.amount);
eventSheet.getRange(rowNum, 23, 1, 1).setValue(rowData.paid);
eventSheet.getRange(rowNum, 24, 1, 1).setValue(rowData.packet);
// Make sure the cell is updated right away in case the script is interrupted
// Add delegate to Contacts
var curDate = Utilities.formatDate(new Date(), TZ, "dd MMM yy HH:mm");
// var c = ContactsApp.findByEmailAddress(rowData.email);
var c = ContactsApp.getContact(rowData.email);
if (!c){
var c = ContactsApp.createContact(rowData.firstName, rowData.lastName, rowData.email);
var prop = {};
prop.Organisation = rowData.organisationName;
prop.Added = curDate;
c.setUserDefinedFields(prop);
//var group = ContactsApp.findContactGroup(rowData.organisationName);
var group = ContactsApp.getContactGroup(rowData.organisationName);
if (!group){
var group = ContactsApp.createContactGroup(rowData.organisationName);
}
c.addToGroup(group);
} else {
c.setUserDefinedField("Last activity", curDate);
}
SpreadsheetApp.flush();
}
}
ss.toast("", "Emails sent", -1);
}
// Code to send joining instructions - based on simple mail merge code from
// Tutorial: Simple Mail Merge
// Hugo Fierro, Google Spreadsheet Scripts Team
// March 2009
function sendJoinEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getActiveSheet();
var eventName = ss.getRange("C1").getValue();// pull event name from sheet
var location = ss.getRange("C2").getValue();// pull event location
var emailCount = 0;
var dataRange = dataSheet.getRange(5, 2, dataSheet.getMaxRows(), dataSheet.getMaxColumns());
var templateSheet = ss.getSheetByName("Templates");
var emailTemplate = templateSheet.getRange("B6").getValue();
var emailSubject = templateSheet.getRange("B5").getValue();
emailSubject = emailSubject.replace('${"Event Name"}', eventName);
// Create one JavaScript object per row of data.
objects = getRowsData(dataSheet,dataRange,4);
// For every row object, create a personalized email from a template and send
// it to the appropriate person.
for (var i = 0; i < objects.length; ++i) {
// Get a row object
var rowData = objects[i];
rowData.eventName = eventName;
rowData.rowNumber = i + 5;
// Generate a personalized email.
if (!rowData.emailed) {
if (rowData.startPostcode && (location != "Online" || location)){
rowData.directions = getMapDirections_(rowData.startPostcode, location, rowData.mode);
}
var emailText = fillInTemplateFromObject(emailTemplate, rowData);
try {
MailApp.sendEmail(rowData.emailAddress, emailSubject, 'Please view in HTML capable email client.', {htmlBody: emailText});
emailCount++;
dataSheet.getRange(rowData.rowNumber, 2).setValue(Utilities.formatDate(new Date(sDate), TZ, "dd MMM yy HH:mm"));
} catch(e) {
Browser.msgBox("There was a problem sending to "+rowData.emailAddress);
}
}
}
ss.toast("Joining instructions have been sent to "+emailCount+" delegates", "Joining instructions sent", 5);
}
// Modified from Send customized driving directions in a mail merge template
// http://code.google.com/googleapps/appsscript/templates.html
function getMapDirections_(start, end, mode) {
// Generate personalized static map with directions.
switch(mode)
{
case "Cycling":
var directions = Maps.newDirectionFinder()
.setOrigin(start)
.setDestination(end)
.setMode(Maps.DirectionFinder.Mode.BICYCLING)
.getDirections();
break;
case "Walking":
var directions = Maps.newDirectionFinder()
.setOrigin(start)
.setDestination(end)
.setMode(Maps.DirectionFinder.Mode.WALKING)
.getDirections();
break;
default:
var directions = Maps.newDirectionFinder()
.setOrigin(start)
.setDestination(end)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();
}
var currentLabel = 0;
var directionsHtml = "";
var map = Maps.newStaticMap().setSize(500, 350);
map.setMarkerStyle(Maps.StaticMap.MarkerSize.SMALL, "red", null);
map.addMarker(start);
map.addMarker(end);
var r1 = new RegExp('<div style="font-size:0.9em">', 'g');
var r2 = new RegExp('</div>', 'g');
var points = [];
var distance = 0;
var time = 0;
for (var i in directions.routes) {
for (var j in directions.routes[i].legs) {
for (var k in directions.routes[i].legs[j].steps) {
var step = directions.routes[i].legs[j].steps[k];
distance += directions.routes[i].legs[j].steps[k].distance.value;
time += directions.routes[i].legs[j].steps[k].duration.value;
var path = Maps.decodePolyline(step.polyline.points);
points = points.concat(path);
var text = step.html_instructions;
text = text.replace(r1, '<br>');
text = text.replace(r2, '<br>');
directionsHtml += "<br>" + (++currentLabel) + " - " + text;
}
}
}
// be conservative, and only sample 100 times...
var lpoints=[];
if (points.length < 200)
lpoints = points;
else {
var pCount = (points.length/2);
var step = parseInt(pCount/100);
for (var i=0; i<100; ++i) {
lpoints.push(points[i*step*2]);
lpoints.push(points[(i*step*2)+1]);
}
}
// make the polyline
if (lpoints.length>0) {
var pline = Maps.encodePolyline(lpoints);
map.addPath(pline);
}
var mapLink = "<a href='http://maps.google.com/maps?saddr="+encodeURIComponent(start)+"&daddr="+encodeURIComponent(end)+"'>Click here for complete map and directions</a>";
var dir = "<p><strong>Travel Directions</strong></p><p align=center><img src=\"" + map.getMapUrl() + "\" /><br>"+mapLink+"</p><p><strong>Summary of Route</strong>"+
"<br><strong>Distance: </strong>" +Math.round(0.00621371192*distance)/10+" miles"+
"<br><strong>Time: </strong>"+Math.floor(time/60)+" minutes</p>" + directionsHtml;
return dir;
}
// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
// - template: string containing markers, for instance ${"Column name"}
// - data: JavaScript object with values to that will replace markers. For instance
// data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
var email = template;
// Search for all the variables to be replaced, for instance ${"Column name"}
var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);
// Replace variables from the template with the actual values from the data object.
// If no value is available, replace with the empty string.
for (var i = 0; i < templateVars.length; ++i) {
// normalizeHeader ignores ${"} so we can call it directly here.
var variableData = isDate(data[normalizeHeader(templateVars[i])]);
email = email.replace(templateVars[i], variableData || "");
}
return email;
}
// Test if value is a date and if so format
// From http://stackoverflow.com/questions/1353684
// Returns 'true' if variable d is a date object.
function isValidDate(d) {
if ( Object.prototype.toString.call(d) !== "[object Date]" )
return false;
return !isNaN(d.getTime());
}
// Test if value is a date and if so format
// otherwise, reflect input variable back as-is.
function isDate(sDate) {
if (isValidDate(sDate)) {
sDate = Utilities.formatDate(new Date(sDate), TZ, "dd MMM yy HH:mm");
}
return sDate;
}
//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
//////////////////////////////////////////////////////////////////////////////////////////
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
var numColumns = range.getEndColumn() - range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
var headers = headersRange.getValues()[0];
return getObjects(range.getValues(), normalizeHeaders(headers));
}
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
if (isCellEmpty(cellData)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
var key = normalizeHeader(headers[i]);
if (key.length > 0) {
keys.push(key);
}
}
return keys;
}
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
var key = "";
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == " " && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) {
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
isDigit(char);
}
// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
return char >= '0' && char <= '9';
}
@mogsdad
Copy link

mogsdad commented Feb 27, 2013

The isDate change looks good.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment