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:
- Spreadsheet page for event entry
- Calendar (a sample event is on the 10th Dec)
- Google Sites Event Page
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 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;
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 r2 = 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;
}
Last updated by at .
Loading...
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.
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.
Hi Bonnie – can you share the spreadsheet with me and I’ll have a look
Martin
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?
Bonnie:
I managed to get the “Event Manager” menu item back by running “onOpen” script from “script manager” menu item in “Tools” menu.
Thanks Vishnu. That did the trick.
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
Hi martin is the latest template ‘The best Google Spreadsheet EventManager (Make a copy when opened)’ at the top of the page?
Thanks
Richard
yes the link is to the latest version with all the recent code fixes
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!
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
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,
Menu appears to occasionally disappear – see Vishnu’s comment on how to fix (in this thread ;-)
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
If you look on the ‘templates’ sheet you should be able to spot where this is included
Martin
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
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
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.
Richard:
Can you tell which Cell in the template are you referring to?
I have in cell E3 : =”Event ${“”Event Title”"}”
Vishnu
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
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
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.
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.
Hi Ross,
Have you found a solution for inserting the correct date into the emails?
Regards,
Ben
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
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
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.
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
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”);
}
}
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 ?
Hi, running into this error message before I can even get through setup…
TypeError: Cannot call method “getTimeZone” of undefined. (line 32)
Any suggestions?
I am having the same issue..Oops
TypeError: Cannot call method “getTimeZone” of null.
Thoughts?
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.
Dear Martin,
This exactly I am looking for. One question how to collect registration fee? and store in the spread sheet when submitting?
regards
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
> 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
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
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
PS: I tried the solutions provided elsewhere on this page, but they didn’t work
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
When I ran the “Event Manager”–>”Process Event”, I got this error message:
Unexpected Error – function getEvents
Any idea to help?
Thanks very much!
What would be a reason why the Event Manager script button (next to the Help menu) would disappear?
More than likely a temporary Google server issue.
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
Hi Craig – do you have a link to an example calendar or site announcement?
How do I fix this error?
TypeError: Cannot call method “getRange” of null. (line 63)
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.
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.
Hi Guido – do you mean the page on Google Sites?
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
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
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”)
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.
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!
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
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.
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!
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.
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”);
}
}
Hi,
I am trying to integrate this for a client, does anyone have a working version ?
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.
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
You might need to refresh the spreadsheet once you’ve clicked the start setup button and authenticated access
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.
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!
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.
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.
And I figured it out. The person using the form had edited the event ID by accident. Thanks again anyways for the great tool!
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
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!
**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.
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
Sorry, I mean to add that the transferred data just says “undefined”
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)
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
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
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?
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
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!