Update 21/12/2015: As many may have gathered I haven’t maintained this solution and some of the code may be broken. You are free to re-use the code shared in this post. You might also be interested in this Event Manager Google Add-on by Romain Vialard made to administer events using Google Sheets to manage event publication and bookings.
Back in March 2010 I looked into using Google Apps (Spreadsheet, Calendar and Sites) as an event management system utilising the power of Google App Scripts. The solution I came up with was okay, but wasn’t very user friendly and bits of the code were, well, a bit messy. Shortly after posting the final version I also came across Romain Vialard’s – Manage your events: Calendar Importer and Registration Form.
Romain had some really nice features including streamlining the setup and passing information to a booking form and for a long time I’ve vowed to return to my original solution and incorporate some of Romain’s ideas. So here it is:
The best Google Spreadsheet EventManager (Make a copy when opened)
The main features of the new Spreadsheet are:
- Entering events in a Google Spreadsheet which are then pushed to Google Calendar and optionally Sites.
- Improved registration with an autofill for an event ID populated using the the booking form link
- Automatic email notification to admin for new bookings
- Delegate email addresses automatically added to guest list in Google Calendar and added to Google Contacts
- Option for the delegate to receive personalised travel directions in joining instructions email
- General tidy up of the code
The video below describes these features and how to setup the EventManager Spreadsheet (I’ve I’m sounding a little lacklustre it was really late when I recorded this and I just want to get this homework out):
Gotchas (Google Apps Script Map Service)
There were a couple of gotchas along the way, mostly around the maps/direction service. I’m still not convinced that this feature is entirely worthwhile, but I decided to preserver with it just as an opportunity to familiarise myself with this service. I really shouldn’t gripe as most of the hard work was done in the ‘Send customized driving directions in a mail merge’ template. My main problem was setting the mode of transport which isn’t covered in the template. I wasn’t helped by the lack of documentation on the Google App Script site, but a friendly Google Employee was able to answer a question I posed on the forum.
If anyone else is looking at using the Map service is Google App Script I would recommend looking at the documentation for the main Google Maps API to let you take a punt at what some of the methods are (as highlighted in this post I made to someone else’s problem with getting a route duration and distance).
Another reason I might give up on the Google Apps Script Map service is you probably can pull most of this functionality straight from the Google Maps API as JSON, allowing you to use something like:
var url = "http://maps.googleapis.com/maps/api/directions/json?origin=Chicago,IL&destination=Los+Angeles,CA&sensor=false"; var response = UrlFetchApp.fetch(url);
PS I was also left scratching my head when Session.getUser().getUserLoginId() suddenly stopped working but I’m guessing this is Google patching a possible security problem.
Here are some links to an example event so you can see what the results look like:
Below is a copy of the code (mainly for SEO purposes):
// EventManagerV3 glued together by mhawksey http://www.google.com/profiles/m.hawksey
// Related blog post https://hawksey.info/blog/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;
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.openByName(calendarName).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");
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();
}
}
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();
// 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);
// add to calendar bit
if(rowData.endDate == "All-day"){
cal.createAllDayEvent(eventTitle, rowData.startDate, rowData.endDate, {location:rowData.location, description: description}).addEmailReminder(15).setTag("Event ID", rowData.eventId);
}
else{
cal.createEvent(eventTitle, rowData.startDate, rowData.endDate, {location:rowData.location, description: description}).addEmailReminder(15).setTag("Event ID", rowData.eventId);
}
// add to site bit
if (siteUrl != ""){
var page = SitesApp.getPageByUrl(siteUrl);
var announcement = page.createAnnouncement(rowData.eventTitle, description);
}
// create event sheet
var temp = ss.getSheetByName("EventTMP");
var eventSheet = ss.insertSheet("Event "+rowData.eventId, {template:temp});
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 "+new Date()).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.openByName(calendarName);
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.firstName);
eventSheet.getRange(rowNum, 6, 1, 1).setValue(rowData.lastName);
eventSheet.getRange(rowNum, 7, 1, 1).setValue(rowData.email);
eventSheet.getRange(rowNum, 8, 1, 1).setValue(rowData.organisationName);
eventSheet.getRange(rowNum, 9, 1, 1).setValue(rowData.startPostcode);
eventSheet.getRange(rowNum, 10, 1, 1).setValue(rowData.preferredMode);
eventSheet.getRange(rowNum, 11, 1, 1).setValue(rowData.otherInfo);
eventSheet.getRange(rowNum, 12, 1, 1).setValue(rowData.comments);
// Make sure the cell is updated right away in case the script is interrupted
// Add delegate to Contacts
var curDate = Utilities.formatDate(new Date(), "GMT", "dd/MM/yy HH:mm");
var c = ContactsApp.findByEmailAddress(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);
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, 3, 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(), "GMT", "dd/MM/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('
‘, ‘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, ‘ ‘); text = text.replace(r2, ‘ ‘); directionsHtml += ” ” + (++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 = “Click here for complete map and directions“; var dir = ” Travel Directions “+mapLink+” Summary of Route“+ ” Distance: ” +Math.round(0.00621371192*distance)/10+” miles”+ ” Time: “+Math.floor(time/60)+” minutes ” + directionsHtml; return dir; }
Tweets that mention The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever « JISC RSC MASHe -- Topsy.com
[…] This post was mentioned on Twitter by Karen Blakeman, Martin Hawksey. Martin Hawksey said: By me: The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever http://bit.ly/bXLxZT […]
nidele
interesting but somethings is wrong when i put date in spreadsheet.
what is the format of a date whith hours ?
for example:
11/29/2010 9:00
Martin Hawksey
@Nidele I suspect the date format is dependant on your locale spreadsheet setting which are controlled in File -> Spreadsheet settings…
For me it is set to UK and I enter date/time as dd/mm/yyyy hh:mm
nidele
i’m from italy but google doc is in english
so i could digit for example ’29/11/2010 hh:9:00′ ?
Martin Hawksey
29/11/2010 09:00:00 (Date/times should be validated in the sheet. Also if you enter a date/time then click on the cell a small calendar should popup
nidele
I have always this error
Oops
Cannot find method formatDate(number,string,string).
Martin Hawksey
Make sure your event ID isn’t numeric. Either letters or letters and numbers are allowed
nidele
great it was the mistake
the reservation is kept by any email arrived in my box (i try with different email)
Rocky
Martin, can I include several people on the one event form? And will I then be able to be notified and will all the people on that form be sent an email to notify of details etc.
Martin Hawksey
Hi Rocky, You might be able to change the form appearance using http://www.morningcopy.com.au/tutorials/how-to-style-google-forms/ (haven’t tried this myself otherwise there are a number of google themes to choose from. Don’t think there is a way of including several people. I workaround might be to add a free textarea to take names of additional bookings, then you could fill in the form manually on their behalf. Hope this helps,
Martin
Keith
Hi Martin,
On the “Put your events here” sheet the “added” cell in column B shows Added Sat Jan 15 2011 05:12:21 GMT-0800 (PST).
(The PST timezone is also shown in your video.)
My timezone is set to UK. Can the “added” cell above be fixed to show GMT ?
ps.Your vid is smart.
Martin Hawksey
Hi Keith, I sure this used to work by setting the sheet default time zone but it appears Google have changed this (or I’m loosing my mind).
To format dates to GMT you’ll need to edit some of the script. On line 90 change
dataSheet.getRange(i+2, 2, 1, 1).setValue("Added "+new Date()).setBackgroundRGB(221, 221, 221);to
dataSheet.getRange(i+2, 2, 1, 1).setValue("Added "+ Utilities.formatDate(new Date(), "GMT", "dd-MM-yyyy HH:mm:ss")).setBackgroundRGB(221, 221, 221);[Need to add localisation to the script at some point 😉 The vid was recorded at 1am, take 15. Just happy to hit the publish button]
Martin
James A
Found this very educational. Thank you.
Would love to see you do something with the Google Maps API as JSON you mention.
Maybe something like sending each delegate a second ‘drive time’ event before and after the event itself with driving directions embedded. That would cover just about every angle.
clay harris
getting an error on processing events
Cannot find method formatDate(number,string,string).
I’ve made the change to the script you described above with no luck. Any thoughts?
This is a great idea, btw
Martin Hawksey
Hi Clay – This error message appears if the event ID is a number (you can use number identifiers if they contain at least one letter – really must catch this bug)
Martin
clay harris
thanks! once i created a letters-only code, it worked. the code DCRC123 did NOT work. DCRC did work.
Also, the calendar is not getting updated by my events. Is it required that the calendar be set to ‘public’ editing as well?
Martin Hawksey
Not sure what is happening here – it shouldn’t matter if the calendar is public or not. Could be either: when you setup the script you didn’t grant access to allow it to modify your calendar, mix of american/UK date formats, or a corss domain issue (ie using the spreadsheet on docs.google.com but trying to update a calendar on your Google Apps domain (if you have one))
Thomas
Whenever I try to enter another events, I get the service error calendar
The event is created in the calendar, but there is not an date/time in the Added column.
Also, there is NOT an event sheet (Event Event ID) created.
And the Google Sites events page I created is not updated with the event.
And is there a way to change the Event Title in the calendar entry? I keep getting the HTML tags (comment tag <!– and ) and would like to remove the tags.
Thomas
Two issues:
1. When the site description is created, the Date: start date – end date is not being entered on the site, but the date/time variables are in the Site Description Template.
Also, the email confirmation is displaying GMT and not the time reflected in the calendar, although the spreadsheet locale has been changed and the calendar entry is correct.
Thomas
Whenever I sent joining instructions to the latest delegate, everyone previously on the list is sent an email.
Does the joining instructions send emails to the entire list even if there was a previous email sent?
Is there a way to not resend the joining email?
Martin Hawksey
@Thomas Ah that’ll be a bug.
Around line 233 you need to change
var dataRange = dataSheet.getRange(5, 3, dataSheet.getMaxRows(), dataSheet.getMaxColumns());to
var dataRange = dataSheet.getRange(5, 2, dataSheet.getMaxRows(), dataSheet.getMaxColumns());[I’ve updated the master]
Thanks for letting me know, much appreciated
Martin
Thomas
That appears to work. Thanks
The script is very good. Bravo.
Please explain the change or the var.
Trying to understand.
Martin Hawksey
Hi Thomas – var dataRange collects all the data from the event page required for the join instruction email. Originally the range it was pulling started on the 5th row and 3rd column going to the maximum rows and columns. When the script runs it was supposed to check if an email had already been sent by checking the ‘Emailed’ column, but because this is in column 2 it wasn’t being collected in the dataRange, which was fixed by changing 3 to 2.
More info about the getRange class here
Happy to help,
Martin
Thomas
Thank you.
The script is one of a kind, and will really help me.
After trying to understand it, and break it, I’m just starting to understand the various modules.
If I may suggest a feature: since some events require payment (classes, luncheons, seminars, etc.) any thoughts on Google Checkout integration?
Is it possible?
Martin Hawksey
Never looked at Google Checkout so don’t know what you can do. Really interesting idea tho. Have you every looked at eventbrite.com?
Thomas
The script is working well.
Thought I’d ask instead of taking a risk and breaking the script.
How can I add fields (more contact info, company info) to the registration/rsvp form without breaking it?
Another feature to suggest: once the event is completed, and using the info on the event page, sending out a event feedback form to the attendees.
James
Hi Martin,
I lose the extra menu entries after I try to send out the joining instructions emails. Any ideas?
Michael
Hi Martin,
Thanks for the app. I opened it in gDocs but the option to make a copy is not available. Please help. Thanks again!
Martin Hawksey
@Michael – sometimes there are issues with copying files if you are working in a Google Apps domain. You could try making a copy from the version on Google Docs Templates
Martin
Kelly
How can I go about editing the form that is being used to collect data? Some of the information is decidedly european (postcode) and I want to change it to work the content that we need to collect. I found the place where to set the URL to the form, but wasn’t sure if I could just create my own google form and insert the link there or if there is something specific I need to do. Any help and information would b greatly appreciated! I really want to start using this in the next week for some studies our Church is planning on offering. Thanks so much for this awesome event manager!
Richard Grignon
Hi ,
I can’t seem to copy the form. The copy function is grayed out.
GREAT FORM though!!
Martin Hawksey
@Richard see comment to Michael above. I’m not entirely sure but if you are working from a Google Apps domain you might need to use http://spreadsheets.google.com/a/yourdomainname.com/ccc?key=0AqGkLMU9sHmLdFRSUjEyT0lDbk1TeU02eF9jRnpCNFE (maybe)
Thomas
For some reason I just started getting the error Service Error: Contacts whenever I confirm bookings.
The confirmation email is being sent, but the attendee is not being added to the event sheet or contacts.
Martin Hawksey
@Thomas is it failing for all contacts or just for those who have registered for an event before?
Thomas
It is failing for all contacts. I just processed a new booking to verify the error.
The booking ID is generated and posted on the booking sheet, then the Oops Service error: contacts box pops up.
The booking is not updated on the event sheet.
I am running a copy of the program for a another organization and the same error is occurring.
Thomas
Still trying to solve my Service error: contacts …
When I look at the execution transcript, it stops at:
ContactsApp.findByEmailAddress(rowData.email);
Looks like findByEmailAddress was deprecated recently on 2011-02-18.
I’ve tried replacing with getContact, getContacts and getContactsByEmailAddress but nothing seems to work.
Does this clue help?
Martin Hawksey
Google App Script rewrote their api access to Contacts, I think this has broke my script. I need to rewrite 🙁
Thomas
Looks like the script is not completely broken. I was able to comment out the “Add delegate to Contacts” portion.
Also, on the Bookings sheet, the TBC is not being set, but changing the Action to Y on the Bookings sheet does not appear to adversely affect the rest of the script.
I tried play around with the Class ContactsApp Members, but nothing works.
Hopefully you can fix this quickly; and maybe the new debugger will help.
If I figure it out or learn something, I’ll post it back here.
Martin Hawksey
Google recently changed the way Contacts work. I’ve updated the template but for those already using the script these are the changes you need to make (You need to open the Script Editor in the spreadsheet):
Line 204 replace:
var c = ContactsApp.findByEmailAddress(rowData.email);with
var c = ContactsApp.getContact(rowData.email);and Line 211 replace:
var group = ContactsApp.findContactGroup(rowData.organisationName);with
var group = ContactsApp.getContactGroup(rowData.organisationName);if there are still problems let me know
Martin
Thomas
Changing findByEmailAddress to getContact; and changing findContactGroup to getContactGroup does not appear to work.
Causing the same error.
I actually played around with the first change and it never worked. Not sure if you found out anything different.
Whether or not, if it matters that it is an array, or string, etc.
Were there any necessary changes to the spreadsheets; or just the script?
There were recent changes to Google Contacts within Google Apps for Domains. For example, the contacts have tighter integration with mail.
I was wondering if Class ContactsApp were compatible with the changes in Google Contacts.
Erica
Hi,
I tried to set this up but when I clicked on the “Click me to begin setup” button, I was able to enter the spreadsheet name, then it said
TypeError: Cannot call method “getTimeZone” of null.
I went to the Spreadsheet settings (on the copy of the Event Manager) and I changed the time zone to match where I am, but it still did not work.
Any help would be appreciated!
Erica
Duc Pham
Hi there,
Great work on the apps and I really appreciate the time you put into answering all these questions.
I have a question but didn’t see an answer in the above posts. In my case, the formatting under templates are not being properly pushed through to google calendar. For example, when I look at a newly-created event in google calendar it shows title: “経営革新塾2011年4月度” (my calendar name and event names are in Japanese. The same problem occurs with description.
Am I doing something wrong here? Or should I scrap the formatting together and enter replace the templates with pure texts?
Craig
Hi Martin
This is fantastic, i got it working and tested an event. It will be perfect for us to start managing our golf bookings centrally. I have a problem though. After using the spreadsheet for one event i lose the menu option for ‘Events Manager’ on the ‘put your events here’ tab (the last text menu option after the ‘help’ menu item). What am i doing wrong?
I also have an problem with the time zones (I’m in KL Malaysia so it may take me some time to work out how to synch the time zones between Event Manager v3 and google calendar), but i’m pretty sure i can work that out myself.
Thanks
Craig
Craig
Don’t worry, i solved that problem. I ran the ‘settings’ script again and the Event Manager text appeared again. My event updated to announcements but not to the calendar, but i’ll keep fiddling around with it.
Craig
Martin Hawksey
Glad you got it to work out. Super of my latest stuff might interest you http://mashe.hawksey.info/2011/05/work-in-progress-google-spreadsheetsites-flexible-event-booking-form/ (good be good for skins, for balls port something 😉
Clive Richards
Looks to be an interesting solution to something that many people want. Probably best for people used to dealing with a bit of coding but worth that effort.
One question I have is whether a limit can be set on the number of participants for an event – for example we limit our workshops to a maximum of 10 and they often fill up pretty rapidly when we advertise them or is this just down to manual management when the replies come in. If it has to be managed manually then is there a way to make an event “unavailable” when full but whilst retaining the item for contact with registered participants?
Martin Hawksey
Hi Clive – setting automatic limits with the current solution isn’t possible, but some of my latest work does has this feature. There is this framework I created which uses some of the latest services in Google Spreadsheet which allows you to create custom interfaces where maximum numbers can be set or the outline of a different spreadsheet booking system we use for one of our conferences. Main problem is these solutions are designed for one-off events. Given the interest in the EventManager outlined here it might be worth exploring how some of these ideas could be merged and de-techified even more.
I just need a rainy day to do it 😉
Martin
Arin
I have the same problem as Erica.
Evrytime I run setup, it will give the error “TypeError: Cannot call method “getTimeZone” of null”
Tried to change the time zone also did not work.
Any help will be much appreciated
Martin Hawksey
Hi – the TypeError on getTimeZone usually indicates that the calendar name is wrong or doesn’t exist
Martin
Jean
I know this was years ago but did you ever figure this out? I’m getting the same error.
Jean
For anyone else having this issue, I figured it out. I had logged into two google accounts so the spreadsheet copy was in one and I authorized the script for the other.
Arin
Thanks Martin. That’s a silly mistake of me.
I got another question. I followed step by step based on your guide in youtube. But in the calendar, I still can’t get the look which you did in the title of each calendar. It will give the comment tag (<!–) and paragraph tag ().
Even the calendar example which you provided have the same issues as I did. Is there anything which I need to modify it?
Martin Hawksey
Hi Arin – I’m guessing you can remove this markup from the Templates sheet (Cell E3). If removing it works and doesn’t break anything can you let me know and I’ll remove it from the master.
Thanks!
Martin
Shawn
Could this be used with Google Calendars’ Appointment Blocks and auto update individual people of an appointment that was booked for them?
In other words, I want to integrate a booking system into our website and when a person books an appointment through Google Calendar/Facebook Registration it would send notifications to only appropriate persons as well as update Google Sites, etc…
The events wouldn’t be already set up until the client booked the appointment. It would only display blocks of time where appointments could be scheduled by users/clients of the website.
Martin Hawksey
Hi Shawn – You can certainly add a guest to a calendar event via apps script, but I’m not entirely sure how this is handled
Martin
chad
awesome script. thank you!!!
the event manager menu no longer appears in toolbar. any ideas?
Martin Hawksey
Hi Chad don’t know why it would disappear. You can force it to appear by selecting Tools > Script editor … then Run > onOpen (if it asks you to authorize the script then run again, this should also mean it should start adding the menu automatically again on start up)
Martin
Steve Lee
It’s great to see that google apps is now a ‘quick mashup’ dev environment like MS Office but for online apps
Martin Hawksey
Big plus for me is Apps Script has a lot lower entry than App Engine so that even hobbist programmers like me can do stuff. You should check out the long list of Google Apps services you can interact with plus 3rd party stuff too.
I’m collecting as many Apps Script examples as I can in http://scoop.it/gas
Thanks,
Martin
chad
Hi Martin,
Thank you! I’ve learned a ton by playing with your program. Enough that I was able to develop a system of my own that is a bit simpler/aligned to the needs of my school district. One question… How do I get the registration form’s event code to pre-poluate with the event code from the preceding calendar window (i.e., “Prefilled – editing/deleting will delay your booking).
Thanks again!
Martin Hawksey
Hi Chad – glad you’ve gone off and done your own thing 😉 To prefill the form just add &entry_1=what ever you want to the form url (you might have to play with the entry number ie entry_2 etc to find the right field). In my version I build this url in the description templates in the Template sheet.
Please share your version – its always useful if new discoveries can be put back for the community to build on 😉
Let me know if you are still unclear
Thanks,
Martin
Jan Vandorpe
Hi Martin,
I am trying to adapt your script and I run into some problems:
– as soon as I change something the Event menu disappears… I can run parts of the scripts through the script editor, but the menu would be nicer
– how would you suggest adding fields to the form? sometimes things go wrong in the spreadsheet: other columns are overwritten…I will have to change the script as well, but the link form-spreadsheet is mysterious, to say the least.
– the location seems based on an England. How could I change it for an international setting. any address in ‘location’?
thanks for some tips!
Jan
Marc
Hi Martin
Great tool! I’m trying to rework it for my purposes 😉 But my copy always gives me the following error: Cannot find method createEvent(string,string,string,object). (line 77) when i try to publish events. Do you have a clue what’s wrong? (I didn’t make any changes yet)
Marc
Martin Hawksey
Hi Marc – this might be because the start and stop date/time is not formatted correctly
Martin
Mitch Webster
Hi Martin,
Can each event have its own organizer?
I am looking to see if your solution can be used to schedule our Poker nights.
Each event is hosted at a players home each week and we need to be able to create a long term roster with the ability for the host and all attendees to confirm in advance. Typically need a min of 4 players for their to be a quorum.
Also need the ability to “swop” hosting locations as this needs to adapt to individuals change in circumstance on weekly basis.
Martin Hawksey
Hi Mitch – you can share the spreadsheet with other users who will also be able to use the custom apps script functions. So everyone in your group could add and administer an event when they wanted to – it does mean everyone needs to know what they are doing.
hope that helps,
Martin
Kbrown
Hi i am having a problem with the prefilled box n the registration form. For some reason it is pulling in my last name not the ID for my events. Any idea why or how it is doing that. Also it is showing up on the email to me to confirm booking.
Secondly the Even ID on my google sites page is also acting weird. The ID it gives is the date it was added to the site, not the ID that was created. Here are the ID’s I have used. Help Please! I love this Spreadsheet tool.
AHOUSETR
BHOUSETR
CHOUSETR
DHOUSETR
Beth
This is an amazing spreadsheet, but it doesn’t seem to fully work with the new version of Google Docs. I’ve got everything working except that it won’t update the calendar. I’ve tried in both Safari and Chrome on Mac to get it working. It does update the Google Site, but not the calendar. Suggestions? Think it has anything to do with the fact that I’m doing this on Google Apps for Business rather than a regular Google account?
Thank you so much.
Martin Hawksey
Hi Beth, Looks like there is an open issue for this. I hoping there will be a fix soon as this is part of the core functionality
Martin
Craig
Hi Martin
I don’t know if this is the same problem, but i receive this error message when trying to load a new event.
Oops
Service error: Calendar: Mismatch: etags = ["FUsCRAZBfip7JGA6WxdQ"], version = [63461761584] (line 77)
BTW, we have been using the spreadsheet for the last 6+ months to manage our weekly golf bookings for our group. It’s been fantastic!
Craig
Martin Hawksey
Hi Craig – yes that’s the same bug. It great to know this solution has been working for you for so long before this. I’m giving Google until the weekend to fix otherwise I’ll be trying out some workarounds
Martin
Jon Rickert
Where can I get the extra functions like getRowsData()? Thanks
Martin Hawksey
Loads of useful snippets of code including getRowsData here http://code.google.com/googleapps/appsscript/articles.html
Jon Rickert
Thanks, Good code, easey to follow. BTW I’m a programmer.
I imported your spreadsheet pages into mine and copied you code. Yours ran. Now I’m modifying it for my use and my “run’ and “debug” buttons are disabled (gray) in my script editor. Any idea how I can turn them on I’m using browser & toast popups and it’s a drag. It seems my maxcol & maxrows id returning numbers way to big and so my getrown isn’t working. Thanks
Jon Rickert
Update, my Objects code was not working because of Capitalization! Be warned. My cell title was LName when it was “normilized” it became lname so when I refered to it as rowData.LName it was undefined! rowData.lname was defined properly! I program in VB and ASP .NET so I got bit! Be warned all you good people javascript requires proper use of capitizalition! Now 2 days later off the the rest of my project 🙂
Martin Hawksey
Not sure why greyed out-apps script isn’t bulletproof yet, temporary server conditions can create lot of havoc. I’ve lost many an hour trying to work out what I’m doing wrong in code, more often than not its a typo 😉
Jon Rickert
Thanks a lot, I know, those fat fingers really cost me some times too 🙂
BTW do you know if Documents making and pushing var’s works yet, after I do the calendar part I want to build a doc and mail murge spreadsheet cells into it?
Martin Hawksey
Hi Jon – I’ve done something with this here (links to docs in Update section) also on developer blog mail merge 4 ways
Jon Rickert
Thanks, I hate to bother you, but I do have a data conversion problem. My spreadsheet is formated and valadated as date (I read your other posts) it looks like “1/6/2012 19:16:42” which is great. When I run it thru as rowData.startdate it is “1325851200000” I looked at a lot of posts and it seams as a spreadsheet it is a Date Object. I have tride:
var myStartDate =Utilities.formatDate(rowData.startdate, “EST”, “dd-MM-yyyy HH:mm:ss”);
and
Browser.msgBox(Utilities.jsonStringify(rowData.startdate));
I read I could use toString but I can’t find the syntax.
my goal is to:
cal.createEvent(eventTitle, myStartDate, myEndDate)
Any help is appreciated — thanks Jon
Martin Hawksey
@Jon a vague recollection that and/or spreadsheet, script, calender properties need to have same locale
Ross Halfpenny
Hello Martin, I have just started playing with your EventManager (good work) and have a few questions
1. On the “Booking” sheet the “T.B.C” in the “Action field ” is not being filled in when the form is submitted and also the admin email is not sent out to inform them of the booking request. If I go into the script editor and run the “on form submit” script the sheet up dates with the “T.B.C” and the admin email is sent. Do you know any reason why this is not working when the form is submitted?
2. I also wanted to see if you can point me in the right direction as I am very new to coding and need to have the ability to send a decline email when we enter a “N” into the “Action field”. I understand that I would need to amend the script and also add some new fields and text into the “Templates” sheet.
Thanks again as this is what I have been looking for and any help on the above would be very much appreciated.
Cheers
JK
Hi Martin! THX a lot for you Code!! is there any solution for this problem?
Service error: Calendar: Mismatch: etags = [“FUsCRAZBfip7JGA6WxdQ”], version = [63461761584] (line 77)
Best regards
jk
Martin Hawksey
Google haven’t been rushing to fix the problem with Calendar: Mismatch: etags so I’ve made a workaround which is now included in the existing template.
For existing users of the the Event Manager that don’t want to start from scratch open your copy of the spreadsheet and go to Tool > Script editor and replace all the code with the copy here https://gist.github.com/1643207
[If you have modified your own version of the code the changes are highlighted in this doc (new version on the right) https://docs.google.com/open?id=0B6GkLMU9sHmLZThiMzlkNjItZWZiMy00YzM0LTllMDEtYWVhNGFhODdjODIy ]
Martin
Vishnu Lakdawala
Martin:
I just started using the script and new comer to this. It worked fine during the first few trials. However, now my Event Sheet is totally different then the EventTMP. It is not showing any of the template cells and the formatting. Any clue why is this and what can be done to check to fix.
Thanks
BTW I am trying to set it up for a nonprofit org for event management.
Martin Hawksey
not sure why that would be but if you share the spreadsheet with me I can have a look
Vishnu Lakdawala
I just mailed a zip file with spreadsheet images. Thanks for a quick response.
Martin Hawksey
Looks like it has just been raised as an issue. I’m monitoring the response, hopefully it will be fixed soon.
Catherine Parkinson
I’m also having the same problems with the newly created Event sheet having no content or formatting.
Also having issues with the Calendar showing all the markup. It works if I have just the plain text – but I’m stumped as to how to get the Register button working again.
But I must say that this is just an awesome script 🙂
Martin Hawksey
The registration button is by default rendered with html markup if going for plain text “& B3 &”&entry_1=${“”Event ID””}”
Martin Hawksey
No fix appears to be forthcoming regarding the templating issue so I’ve come up with a workaround. The template has been updated, to modify and existing copy open Tools > Script editor.. and replace the code with the one here
Bonnie
This is exactly what I was looking for. I have one issue… that I cannot seem to figure out. I’d like the event date to be posted to the Announcements page. The ${“Start Date”} in the Site Description Template seems to enter the date/time I created the event, not the Start Date I entered in the spreadsheet for the event. I was only able to see this when I placed ${“Start Date”} – ${“End Date”} in place of the Event ID.
The Date: ${“Start Date”} – ${“End Date”} in the template does not publish to the site.
I tried adding rowData.startDate to var announcement = page.createAnnouncement(rowData.eventTitle, description);
That gives me an error that createAnnouncement cannot be found.
I even tried creating a new field in the spreadsheet for a custom date, then added that to the site template code. Nothing will publish to the site’s announcement’s page except for title and descrition and event ID.
Any guidance is much appreciated.
Bonnie
Hello again. This is just so close to what I need, but I’m having issues. As I mentioned above, for the life of me, I cannot get “Start Date” to appear on the site page. Like in your reply to Pam, I even tried adding a custom field and added a reference to it in the template. It will not appear on the site page. I opened up the event manager this morning, and now the “Event Manager” menu item it gone from the “Put your events here” sheet. I tried running the “settings” script again, like someone previously tried, and it did not bring back the menu item for me.
I’d really like to put this to use on a site, for our content managers to start using. It’s so close. Your assistance is much appreciated.
Martin Hawksey
Hi Bonnie – can you share the spreadsheet with me and I’ll have a look
Martin
Page
I love this tool and I want to use it for our church. I have stepped through your video several times and I am getting the following: if I put a Y in the first column I get a script error. If I leave it blank the events appear to process. BUT – nothing shows on the calendar and nothing shows on the Event TMP spreadsheet. I do not write script but I can follow directions – what am I missing?
Vishnu Lakdawala
Bonnie:
I managed to get the “Event Manager” menu item back by running “onOpen” script from “script manager” menu item in “Tools” menu.
Bonnie
Thanks Vishnu. That did the trick.
Martin Hawksey
Turns out there was a bug meaning it wasn’t reading the site announcement page correctly. Fixed in the template if others want this functionality and don’t want to start over just replace all the code in the Script Editor with the one here https://gist.github.com/1643207
Martin
Richard Deakin
Hi martin is the latest template ‘The best Google Spreadsheet EventManager (Make a copy when opened)’ at the top of the page?
Thanks
Richard
Martin Hawksey
yes the link is to the latest version with all the recent code fixes
Pam Currie
Hey Martin,
I am starting out in the world of Google apps, with a view to replicating something I have been doing in Sharepoint. It is basically what you have done here for the purposes of an online TV schedule for our events. I want users to view a schedule (calendar) with a link to an announcements page in the site to view swanky looking additional content and then sign up for the event, so that we can track attendance in the backend, via a spreadsheet.
This is exactly what you have done here, having the three areas talk to each other and it is truly brilliant. I have spent days looking at ways of doing what I wanted to do using Google’s default gadgets/ tools. When I eventually landed at your site, I thought I had cracked it.
What i would like to do is change the column headers in the spreadsheet and possibly add additional fields. Problem? I know next to nothing about code. Is there any way of getting around the fact that I am clearly already out of my depth?
I was hoping there might be a way of editing the spreadsheet and having the code update for me. And if that is a ridiculous suggestion, maybe you could give me a starter for ten and point me to the bits of code I would need to edit in order to change the columns and not break it.
I realise you probably prefer to deal with proper developers with more informed questions, so I thank you in advance even for taking the time even to read this!
Martin Hawksey
Hi Pam – your in luck the way the code works means there is flexibility in the data pulled to write the calendar and announcement pages. What you need to do is just add more columns after Column I on the ‘Put your events here’ sheet and then reference the new column heading in the ‘Template’ sheet by wrapping the new column heading in {” “} or {“” “”} if it is part of a cell formula. So if you had a new column called ‘Show’ the way to reference it in the Template is {“Show”} or {“”Show””} if it’s part of a formula like
="<p><strong>Date:</strong> ${""Start Date""} - ${""End Date""}</p><p><strong>Show:</strong> {""Show""}</p>"&E4If you are still left scratching your head get in touch,
Martin
Richard Deakin
HI great tutorial i set up a demo and took the first booking fine but a second test booking and the event manager drop down next to help to manage the booking has disappeared do you know how to get it back or what i did wrong. I wondered if i clicked process events instead of process booking might have messed it up?
Thanks,
Martin Hawksey
Menu appears to occasionally disappear – see Vishnu’s comment on how to fix (in this thread 😉
Ross
Hello Martin, Love your work on this and starting to use it for a Charity I help.
I have one small issue that I can not seem to fix. When the event title is shown on the calendar it shows as below (as it is in the templates page) My event title is “Test Event 1” in this example. I have looked back at some of the older post on your site and found one with a similar issue but am not sure if anything was resolved? I have pasted the post at the bottom. Any help on this would be very much appreciated.
Thanks in advance
Current Event Title
Test Event Title 1
Arin
June 20, 2011 at 5:17 pm
Thanks Martin. That’s a silly mistake of me.
I got another question. I followed step by step based on your guide in youtube. But in the calendar, I still can’t get the look which you did in the title of each calendar. It will give the comment tag (<!–) and paragraph tag ().
Even the calendar example which you provided have the same issues as I did. Is there anything which I need to modify it?
Reply
Martin Hawksey
June 21, 2011 at 4:01 pm
Hi Arin – I’m guessing you can remove this markup from the Templates sheet (Cell E3). If removing it works and doesn’t break anything can you let me know and I’ll remove it from the master.
Thanks!
Martin
Martin Hawksey
If you look on the ‘templates’ sheet you should be able to spot where this is included
Martin
Ross
Thanks Martin, I have changed the text on the template sheet in cell E3 to =”Event ${“”Event Title””}” at it now only shows the Event name (perfect!)
I have some other small issues but want to try and deal with them one at a time and just wanted to see if you can again point me in the right direction?
On The “Email Booking Confirmation” the event date is showing the date & time the email was sent and not the actual event date & time? It also has the number 12 after the date? (example: 03 Feb 12 16:28 – 03 Feb 16:28)
I have had a look at both the template sheet and the script and can see where the code is for this part but can not understand why this is happening? Any help on this would be great as I am so close to getting this ready for use.
Thanks again
Vishnu Lakdawala
Ross:
I managed to get the full year “2012” in responses by replacing every instance of “yy” in the script with “yyyy”. Try if this work.
Thanks for the template sheet E3 correction. It worked for me also.
Cheers
Vishnu
richard deakin
Hi tried copying in the =”Event ${“”Event Title””}” into the template and it kept saying:
Oops
Make sure that your formula has matching quotes.
and wont let me do anything, was there something I was supposed to leave in cell e3 thanks.
Vishnu Lakdawala
Richard:
Can you tell which Cell in the template are you referring to?
I have in cell E3 : =”Event ${“”Event Title””}”
Vishnu
richard deakin
Hi it was cell e3 I copied it in again and I didn’t get an error so that’s fine. But when I submitted the event to be processed I got this error.
Oops
TypeError: Cannot read property “length” from null. (line 413)
OK
Do you know what that means?
Thanks
Richard
Ben J
Hi Richard,
Looking closely it would appear your first quote is a 99 not 66. Or it’s something to do with the formatting from when you copied it. I first pasted into note pad and re-typed the quotes. It then worked for me.
=”Event ${“”Event Title””}”
I am also having difficulty with the Start and End dates. I’ve tried re-naming the variable in the spreadsheet template to ‘start time’ and ‘end time’ with no success.
Have you found a solution?
Regards,
Ben
Ben J
I have tried without success changing the variable in the template to start date, startDate and other formats. I’ll ponder for a little longer.
Richard Deakin
HI Ben J thanks i will try that. If this problem solves the google calender issue could the template be updated Martin? Thanks for you help guys.
Ben J
Hi Ross,
Have you found a solution for inserting the correct date into the emails?
Regards,
Ben
Ahamed Fasil
Kindly try to change the following line under function isDate()
return Utilities.formatDate(new Date(), TZ, “dd MMM yy HH:mm”);
with
return Utilities.formatDate(new Date(sDate), TZ, “dd MMM yy HH:mm”);
I hope this resolves the issue everywhere including the announcement on the page and the emails.
Vishnu Lakdawala
Martin:
How can I edit the Form, such that if an email field is submitted, we ask the user to fill a second email field so as to verify the accuracy and/or to catch any typo error before the user submits the response.
Any hint would be greatly appreciated.
Vishnu
Ross
Hi Vishnu, thanks for your comments on my post. With regards to your last post ref email validation you may want to check out the links below. I am yet to do this myself but will be looking into it more at some point this year.
http://www.morningcopy.com.au/tutorials/google-forms-and-jquery-validation/
http://bassistance.de/jquery-plugins/jquery-plugin-validation/
Cheers
Martin
Hi Martin
the spread sheet is giving me some problems again. When trying to process an event It states that ‘another entity already exists the same name- line 99’ although all of my booking ID’s have a date in them so there can’t be any duplicate entries. Is this a known problem?
Thanks
Craig
PS. Would still like to know how to send out email invites when a new event is loaded. I can see the email invite content but not the list that receives it.
Ross
Hello Martin, I am still having issues with the “Email Booking Confirmation” email. When the email is received the event date is showing the date & time the email was sent and not the actual event date & time?
Do you have any ideas to why this is as I have hit a dead end and can not make this live until i fix it.
Any help would be great
Thanks
Enrico
Found a bug with isDate.
If what you find is a date you are returning a new date, woops 🙂
Change is date function to:
function isDate(sDate) {
var scratch = new Date(sDate);
if (scratch.toString(“dd/MM/yyyy HH:mm:ss”) == “NaN” || scratch.toString(“dd/MM/yyyy HH:mm:ss”) == “Invalid Date”) {
return sDate;
}
else {
return Utilities.formatDate(sDate, TZ, “dd MMM yyyy HH:mm”);
}
}
Frank
Enrico, I get showing “invalid character in string” error in line:
if (scratch.toString(“dd/MM/yyyy HH:mm:ss”) == “NaN” || scratch.toString(“dd/MM/yyyy HH:mm:ss”)
any workaround ?
Amy
Hi, running into this error message before I can even get through setup…
TypeError: Cannot call method “getTimeZone” of undefined. (line 32)
Any suggestions?
Hunter Byington
I am having the same issue..Oops
TypeError: Cannot call method “getTimeZone” of null.
Thoughts?
Smeagle
I came across this issue today and figured it out.
I did not follow the instruction EXACTLY 😉
I did not make the calendar public.
First lesson; we follow instructions.
To Fix It;
Make the calendar public.
AND make sure you are using the same calendar (exact spelling) that you used on the first attempt.
( or you can start over by setting cell E1 in the Templates Sheet to blank and start over. It will ask you for the calendar name again. )
Good luck
I’m posting the solution here so the next person can save a few minutes.
Sridhardk
Dear Martin,
This exactly I am looking for. One question how to collect registration fee? and store in the spread sheet when submitting?
regards
Jason R
This is terrific – thank you!
I have three questions:
1) Same as Sridhardk – is it possible to add payment in?
2) Is it possible to have different forms for different events without duplicating the whole spreadsheet?
3) The rich text in the event description isn’t showing up in my google calendar, and all the discussion pages I find seem to think it’s impossible – but you got it to work very nicely here. How did you do that?
Thank you for open-sourcing this great service!
Jason
Martin Hawksey
> 1) Same as Sridhardk – is it possible to add payment in?
Only way I can see to do this is create a custom form. See http://mashe.hawksey.info/2011/05/google-booking-form/#later for an example of how this might be done
> 2) Is it possible to have different forms for different events without duplicating the whole spreadsheet?
Using solution in 1) would get around that
> 3) The rich text in the event description isn’t showing up in my google calendar, and all the discussion pages I find seem to think it’s impossible – but you got it to work very nicely here. How did you do that?
I recall that what you see as the owner of the calendar is different to what you see as everyone else (or it was a feature of the calendar when I recorded the video)
Martin
Dutchie
Martin,
Thnx for this great post & booking form! Exactly what I’ve been looking for.
One question though: apart from the primary email address I have two additional email addresses attached to my google account. As I will be using your form for two different events organizers I would like to assign one of those email addresses and not the primairy email address to each separate event.
In other words I would like to attach not the primairy email address (let’s call it email_address_1), but email_address_2 to event_A and email_address_3 to event_B and so on.
Is this possible?
Kind regards, Dutchie
Dutchie
Martin,
While I am at it, I find I have another question. I’m experiencing the same problem with Bonnie had or is having or one similar to it.
In the booking confirmation email sent to a guest not the starting date & time – ${“Start Date”} – is quoted, but the time of creating the email. Same goes for end date & time – ${“End Date”}.
How do I go about fixing this, so actual starting and end date will be displayed?
Thank you for taking time to help with this!
Kind regards, Dutchie
Dutchie
PS: I tried the solutions provided elsewhere on this page, but they didn’t work
jonesytheteacher
I, too, am having trouble with the event date. It appears on the Google Site announcement as the date I created the event, not the actual date of the event.
Love your work – this is sooo close to what I need exactly its not funny – hopefully this date thing can be sorted out 🙂
Jonesy
Allen
When I ran the “Event Manager”–>”Process Event”, I got this error message:
Unexpected Error – function getEvents
Any idea to help?
Thanks very much!
Bryan
What would be a reason why the Event Manager script button (next to the Help menu) would disappear?
Martin Hawksey
More than likely a temporary Google server issue.
How it was made: eAssessment Scotland Google Spreadsheet booking form with website and Twitter integration JISC CETIS MASHe
[…] v2]Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, eve…Google Apps Script SnippetsWordPress PluginsuTitle PluginMake PDF NewspaperEverPress […]
Craig
Hi Martin, as of a few days ago the link to ‘register to this event’ does not work, nothing happens when we click on it. I downloaded a new version of the spreadsheet today but we have the same problem. Any ideas? Craig
Martin Hawksey
Hi Craig – do you have a link to an example calendar or site announcement?
Kyle Walter
How do I fix this error?
TypeError: Cannot call method “getRange” of null. (line 63)
Brendon P
Hi Martin,
Thanks for the great post and well done.
I’m having two issues, the first is the same I have read that many others have where the date posted to the website announcements page and the date written to the confirmation email are not the date of the event, and for some reason I don’t receive an admin email when someone registers for the event. I feel like the second problem may be something I can figure out on my own, problem something in setup I did incorrectly.
Any hints as to how to fix either problem would be greatly appreciated.
Again, thanks for this great event manager and your work maintaining it.
Cheers,
Brendon.
Google Apps Script: Using a Google Spreadsheet to populate a Twitter list [Hashtag Communities] JISC CETIS MASHe
[…] The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, eve… […]
Guido van der Meer
Dear Martin,
What a wonderful file it is you created. I have to thank you for all the work and effort you have put into it.
There is one issue i would like to point out.
The page for an event after i processed it, isn’t created. It does export the event into the calendar, but is doesn’t make the special page for the event. Do you have any idea what the problem could be?
I sure hope so, because it is a great file/program.
Kind regards,
Guido van der meer.
Martin Hawksey
Hi Guido – do you mean the page on Google Sites?
Guido van der Meer
Hi Martin, i didn’t mean onn the google site, what i meant to say is a worksheet. Fortunately it is working now, a lot of thanks for the brilliant spreadsheet
Frank
Hi Martin,
thks for your great contribution.
I am a teacher and give courses is several locations / dates. I take a look at your script to try to adapt it to my needs. In my case course = event.
I tried out yr script and found out the following:
1) when copying/renaming your spreadsheet the “Event Manager” menu item dissapears (comments 30Jan – manual fix running “onOpen” script from “script manager” menu item in “Tools” menu).
2) spreadsheet seems to work with only one active event. If two or more simultaneous events are active “Event ID” in form should be a list displaying all active events and event descriptions should be available from list.
3) Event descriptions are pushed into calendar / emails with html code (solvable in template)
4) event time in calendar is GMT – I am in another time zone and in order to make it work have to enter GMT time in order to get correct start+end times.
5) event times pushed in emails are event manager script processing times not event times (comments).
Even with these fixable bugs, you have done great work.
Thks a lot
Frank
Frank
Correction to my previous post (see above)
Ref 2) it does work with multiple events – what I meant is the possibility to have a single registration form for all events with list boxes for event title / event dates.
Ref 3) not an isssue at all – correct in Template Sheet
Ref 4) solved – changing time zone in BOTH calendar properties AND worksheet properties
Ref 5) this is still the major issue – Booking confirmation Emails show Booking time repeated twice instead of event start + end times. Function isDate as provided by Enrico is showing “invalid character in string” in line:
if (scratch.toString(“dd/MM/yyyy HH:mm:ss”) == “NaN” || scratch.toString(“dd/MM/yyyy HH:mm:ss”)
Mark Mandeno
Hi Martin.
I am impressed with what you have developed and enjoyed the video tutorial. It looks like this will add some class to a basic booking I have which involves a link on the website to a google form. However, I cant copy the version that I go to from the blog. I just get light grey options that cant be clicked. I dont mean to waste your time as I am not a developer but if I could get started I may find my way around it.
Danni
Hi,
I’m not sure if you’re still accepting questions from this post, but here goes nothing. 🙂
Once the maximum number of places is reached for an event, how do i ensure that future bookings are are sent waitlist messages instead of confirmations?
Thanks!
Brenda
Thank you for this great idea!
I am working for a start up company now and have to organize the training on our product but it take up too much of my time when I could focus on other things…so this is very useful.
I am having problems with the script ImportintoCalendar
One I copy from the link supplied…I follow all the instructions…I get to the point where I can enter the training events on ‘Put your events here’ page and put the start date as 03/12/2012 08:00 and end date as 08/12/2012 09:00. I enter the ‘Y’ in the action field and then I press Process events, but the ImportintoCalendar error shows up.
I should not that the ‘action’, ‘start date’, ‘end date’ and ‘number of places all have upper right hand red corners….and that the event does sometimes get published to the calendar with a bunch of other code in the title, but the event never get imported into the EventTMP.
I tried doing some of the solutions above but no luck so far….Please help someone?
Thank you!
Then I
Emmanuel
I think I’ve found something.
I was a bit upset by this ‘Start Date’, ‘End Date’ problem so I throw a new line of Event and put a number in Event Description :’13’, just because it was faster to write. In the announcement page and the email confirmation I got the date of event creation ??
So there is a problem with number parsing. Maybe someone will fix it.
But there is a get around. I added two columns ‘date1’ and ‘date2’ at the right hand side of th ‘Put your event here’ sheet. On wrote the date and time in text with the formula =”May 4th 2013 at 22:00″
I replace ‘Start Date’ by ‘Date1’ for every occurrences in the ‘Templates’ sheet…
And it worked
Thanks a lot for this project Martin.
Brenda
The problem may be, when running the ImportintoCalendar script this comes up:
Cannot read property “length” from null. (line 413)
but what do I change length to then?
Thank you!
Mark Mandeno
HI Brenda. Ive been trying to make use of this over the last couple of days and have had exactly the same problem. Cant help you sorry.
Tim
There’s a bug. I’ve commented-out the erroneous line and put the correct stuff in.
function isDate(sDate) {
var scratch = new Date(sDate);
if (scratch.toString() == “NaN” || scratch.toString() == “Invalid Date”) {
return sDate;
}
else {
//return Utilities.formatDate(new Date(), TZ, “dd MMM yy HH:mm”);
return Utilities.formatDate(scratch, TZ, “dd MMM yy HH:mm”);
}
}
George Phillip
Hi,
I am trying to integrate this for a client, does anyone have a working version ?
Mark
Thanks Martin for the great template. Extremely useful. A few questions though:
1) I don’t want a new spreadsheet to be created every time I approve an Event, I only want that event to be pushed into the calendar. Is there a way for me to prevent this from happening?
2) How can I stop the message box (outlined in red with the word “validation” in it) from appearing every time I hover my cursor over a cell?
3) Romain’s template that you linked to above has the ability to set a color for an Event. Is it possible to do this on your template? If so, how would a create a column to get it working?
Thanks again.
Bishopp
Hi Martin,
great script and decent tutorial you have put together here. I am hoping to use it to take bookings for a Sailing School. Question: at 3:15 on the youtube tutorial video, you click on Event Manager and Process Events. Where/how did you get this menu option? I don’t have an Event Manager option on the toolbar.
Thanks in advance for any help,
Bishopp
Martin Hawksey
You might need to refresh the spreadsheet once you’ve clicked the start setup button and authenticated access
alex ramirez
in my copy there is no setup button, what can i do?
Bishopp
Martin,
thank you. I managed to get the menu back by running the onOpen function from Script Manager, as suggested by Vishnu in an earlier post.
I’ve got my head around the workings of this system a bit more now but am struggling with an issue that a lot of people appear to have posted on: Where the Start Date and End Date should appear with an event that is pushed into the Google Calendar (as well as generated in confirmation emails and possibly even pushed to a google sites Announcement page), the time and date that the event was processed is appearing instead.
Have you managed to find a solution to this problem?
Many thanks.
Bob B
Hi Martin, I left you a comment on your youtube video but figured this might be better. I love the spreadsheet but I’m a scripting newbie and would like a quick word of advice. Is it possible to modify this so that it PULLS events from a calendar, populates a form from the events so users can select the one they want, and then (when users register) adds users to the calendar entry and sends them a confirmation email? I’d just like to know if this is possible before I do any more work on it. It’s for a class calendar with recurring classes. I’d like for users to be able to select the class, and then pick from upcoming dates/times, then submit and be added. I don’t want to have to modify anything but the Google Calendar after the spreadsheet/scripts are set up. Thanks!
Melissa
Hi Martin,
I love this Google Drive Spreadsheet.
Though I do not seem to have any luck syncing it to my Calendar.
:/
Please let me know what I’m doing wrong.
I refresh…and nothing.
Brendon P
Hey Martin thanks again for your work.
I’ve ran into a typeError when I try to process my bookings: TypeError: Cannot call method “getLastRow” of null.
The getLastRow operation is at: var rowNum = eventSheet.getLastRow()+1;
And I have no idea what it’s supposed to be returning and why it’s not there. I’m stumped.
Have you ran into this before? Any hints on how to fix it would be appreciated.
Cheers,
Brendon.
Brendon P
And I figured it out. The person using the form had edited the event ID by accident. Thanks again anyways for the great tool!
Tiim
Hi Martin,
This script looks perfect for my needs. Sadly I can’t seem to make it work. When I add an event, put a Y in the action box and then click process events it doesn’t generate a new spreadsheet for this event and it doesn’t create an event in the calender.
Any help to resolve would be much appreciated
Many thanks
Jason
I have the same issue as Tiim, above comment. I have copied and replaced the code in the script editor and re-run the entire process from scratch but still can’t seem to get this to function correctly and never get a new spreadsheet to be made for the event I enter. Any help would be appreciated!
Jason
**UPDATE**
I was able to get the event to process and create a new spreadsheet for the event. The ‘Event Manager’ menu item wasn’t on my spreadsheet so I ran the functions from the Script Manager manually and boom it worked. I’m going to test it out some more and see if I can follow some of the other users suggestions for getting the Event Manager menu back.
BTW, thanks for your work on this script and making it available to the public.
Sam
Hi,
I changed the “Organisation Name” form to “License Number”. It shows up on the booking requests correctly, but when I process the bookings- the data is not correctly ported over to the Event spreadsheet.
Any help would be great!
Sam
Sam
Sorry, I mean to add that the transferred data just says “undefined”
Trevor Elliott
Sam, if you’re changing the “Organisation Name” to “License Number” you need to also change the EventTMP location too; also you need to go into the script code and modify it too. (The lines may not be 100% the same since I’ve modified the script a little)
Line 236: eventSheet.getRange(rowNum, 9, 1, 1).setValue(rowData.organisationName);
Line 254: prop.Organisation = rowData.organisationName;
Line 254: prop.Organisation = rowData.organisationName;
Line 258: var group = ContactsApp.getContactGroup(rowData.organisationName);
Line 260: var group = ContactsApp.createContactGroup(rowData.organisationName);
You need to:
#1 makesure that “rowNum, 9, 1, 1” is the correct cell you have License Number in.
#2 Change all organizationName to licenseNumber and see now see if it works. There’s a lot of connections between the form, spreadsheet, and the script (keep an eye out)
Ray
Really love this spreadsheet and all its functions. I just downloaded a copy today but I seem to having a problem with the Start and End date on both the Announcement sites and email confirmation to people that have signed up. I see that others have had this problem too (since early 2012?).
I have inserted the code recommended by Tim above but it doesn’t seem to help. It continues to insert the date that we are inserting as opposed to the date of the actual event. Is there a solution to this yet?
Thanks very much
Ray
L
Hi Ray,
If you’re still looking at this, I found a solution:
change this line from variableData = isDate(…) to the one below:
var variableData = data[normalizeHeader(templateVars[i])];
Martin Hawksey
One day I’ll revisit this and fix it – but unfortunately for now I’m afraid you guys are on your own. You can try asking for help on stackoverflow or in the Google+ Apps Script Community
Lucie delaBruere
Again.. thanks to Martin for making this public.
When I try the form trigger, I get the URL is too large to process.
Anyone get past that point? If so, do you have any idea?
Hakarune
What do you mean “form trigger”? From his base everything technically works! except the writing the correct date inside response emails… Hit me back with what you mean and I’ll try to help you get it working
Georgie
Hi Lucie,
I know what you mean. If you click the setup button once it will ask you to authorize the script. Do that first, THEN add the trigger, then go back and click the set up button again to finish setting up the manager.
This way worked for me anyway!
JH
Hi Martin,
the booking email in the script only pull out the data of First Name, Event Title, Start Date, End Date & Booking ID…..is there any way to pull out more form contain into the replying email? such as Last Name, Email & Organisation field? any script to edit from these
// 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
Please assist, thanks very much…..
Praneet
Hey Martin
Great work with the application!!
I was trying to build a similar system. Only that it requires to calculate distance between two locations. Earlier on, I could do that using google maps api v2 in google spreadsheets using importxml. But now I am finding it difficult to achieve the same in apps script. Can you help with this?
Karim
Hi Martin,
Would really appreciate help with this. I’m not familiar at all with scripts and programming. I managed to get the calendar up and running and the spreadsheet working pretty well. However, I don’t receive an email with I try to test the form.
The event manager menu button is gone, not sure what I did, so I can’t “manage bookings”
Also, I keep getting this…
TypeError: Cannot call method “getTimeZone” of null. (line 12, file “EventManagerV3 – Script”)
Any help would be greatly appreciated!!
Thanks,
-Karim
Jeremy
Same problem here!
Lauren
Hey there –
Love the script – very helpful in scheduling our daily activities. However I need to reassign some bookings … is there a way to cancel the events?
Thanks!
Lauren
Matt OC
Hi, Great tool, thanks.
All ppl registering at our school will be internal. Can it be set to get respondents automatically?
Require logins and collect emails are the form properties but Im not sure where to add this.
thanks,
Matt
tyler
Hi,
Trying to setup your script and after following the video tutorial very closely I am receiving this error:
Cannot find method createEvent(string,string,string,object)
Any advice is appreciated.
Thank you
BSM
Same problem as Tyler, I get the
Cannot find method createEvent(string,string,string,object)
error and cant’ seem to find a way to fix it
steve white
me 3
steve white
ah fixed it, for me i was pointing at the wrong sheet, click debug to check and then var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(‘sheetname);
steve white
actualy no that get rid of createEvent(string,string,string,object) error can your dates be formulas?
Eric
Thanks so much for putting this together. I am having issues with entering a correct date before processing the event. What is the proper way to enter it?
Also I am having difficulty with the “number of places” tab. What do I need to enter here?
Brian Pugh
Mr. Hawksey, just discovered your video this evening. I have one question regarding the following:
We have an online test scheduling system in our school. Teachers schedule tests (e.g., max. 2 tests / day, 5 / week / class) via a google form. From the spreadsheet we are pushing the published data to a web site using Awesome Tables. The data from various columns in the spreadsheet that Awesome Tables publishes to a web site are as follows: date of scheduling, date of test, section (e.g., 8a1) and course (e.g., Mathematics). This works well, but for many, it would be easier to get a ‘big’ picture of what tests/dates are already scheduled if this information was in a Google calendar.
Is it possible to use EventManagerV3 to push this information from a Google spreadsheet to a Google calendar?
Thanks very much,
Brian.
Nan
Hi Martin, I am interested in trying your EventManager but it seems there is no “setup” button in the Google Spreadsheet and I have no idea to do it manually…
Thanks a lot for your help in advance! 🙂
Regards, Nan
Beltechs
Cannot find method createEvent(string,string,string,object)
Date and TIME format fixed it for me!
MM/DD/YYYY 12:00:00
varianceinfotech
We implemented google cal V3 for more details review our URL : http://goo.gl/9kXfhF
Jeremy
Hi,
I live in NZ, have all documents pointing to timezone Auckland. Does anyone have a suggestion for solving this on set up?
TypeError: Cannot call method “getTimeZone” of null. Details Dismiss
Any help would be greatly received