The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever

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 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; }

183 thoughts on “The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever

  1. Pingback:

    Tweets that mention The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever « JISC RSC MASHe -- Topsy.com

  2. 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

    Reply
    1. Martin Hawksey

      Post author

      @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

      Reply
  3. 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.

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  4. 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.

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  5. 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.

    Reply
  6. 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

    Reply
  7. Martin Hawksey

    Post author

    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

    Reply
  8. 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?

    Reply
    1. Martin Hawksey

      Post author

      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))

      Reply
  9. 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.

    Reply
  10. 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.

    Reply
  11. 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?

    Reply
    1. Martin Hawksey

      Post author

      @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

      Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  12. 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?

    Reply
  13. 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.

    Reply
  14. 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!

    Reply
  15. 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!

    Reply
  16. 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.

    Reply
  17. 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.

    Reply
  18. 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?

    Reply
  19. 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.

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  20. 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.

    Reply
  21. 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

    Reply
  22. 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?

    Reply
  23. 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

    Reply
  24. 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

    Reply
  25. 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?

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  26. 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

    Reply
  27. 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?

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  28. 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.

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  29. 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!

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  30. 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

    Reply
  31. 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

    Reply
  32. 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.

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
  33. 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

    Reply
  34. 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.

    Reply
  35. 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

    Reply
    1. Martin Hawksey

      Post author

      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

      Reply
      1. 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

        Reply
        1. 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 :)

          Reply
        2. Martin Hawksey

          Post author

          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 ;)

          Reply
  36. 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?

    Reply
  37. 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

    Reply
  38. 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

    Reply
  39. 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

    Reply
  40. Martin Hawksey

    Post author

    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

    Reply
  41. 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.

    Reply
    1. 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 :)

      Reply
  42. 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.

    Reply
    1. 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.

      Reply
        1. 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?

          Reply
      1. 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.

        Reply
  43. 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!

    Reply
    1. Martin Hawksey

      Post author

      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>"&E4

      If you are still left scratching your head get in touch,

      Martin

      Reply
  44. 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,

    Reply
  45. 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

    Reply
      1. 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

        Reply
        1. 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

          Reply
        2. 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.

          Reply
          1. Vishnu Lakdawala

            Richard:

            Can you tell which Cell in the template are you referring to?

            I have in cell E3 : =”Event ${“”Event Title””}”

            Vishnu

          2. 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

          3. 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

          4. 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.

  46. 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

    Reply
  47. 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.

    Reply
  48. 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

    Reply
    1. 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”);
      }
      }

      Reply
      1. 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 ?

        Reply
  49. 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?

    Reply
      1. 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.

        Reply
  50. 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

    Reply
  51. 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

    Reply
    1. Martin Hawksey

      Post author

      > 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

      Reply
  52. 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

    Reply
  53. 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

    Reply
  54. 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

    Reply
  55. 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!

    Reply
  56. Pingback:

    How it was made: eAssessment Scotland Google Spreadsheet booking form with website and Twitter integration JISC CETIS MASHe

  57. 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

    Reply
  58. 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.

    Reply
  59. Pingback:

    Google Apps Script: Using a Google Spreadsheet to populate a Twitter list [Hashtag Communities] JISC CETIS MASHe

  60. 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.

    Reply
      1. 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

        Reply
  61. 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

    Reply
  62. 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”)

    Reply
  63. 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.

    Reply
  64. 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!

    Reply
  65. 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

    Reply
    1. 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.

      Reply
  66. 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!

    Reply
  67. 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”);
    }
    }

    Reply
  68. 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.

    Reply
  69. 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

    Reply
  70. 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.

    Reply
  71. 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!

    Reply
  72. 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.

    Reply
  73. 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.

    Reply
    1. 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!

      Reply
  74. 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

    Reply
  75. 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!

    Reply
  76. 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.

    Reply
  77. 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

    Reply
      1. 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)

        Reply
  78. 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

    Reply
    1. 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])];

      Reply
  79. 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?

    Reply
    1. 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

      Reply
  80. 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!

    Reply
  81. 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…..

    Reply
  82. 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?

    Reply
  83. 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

    Reply
  84. 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

    Reply
  85. 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

    Reply
  86. 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

    Reply
    1. 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

      Reply
        1. 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);

          Reply
  87. 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?

    Reply
  88. 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.

    Reply
  89. 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

    Reply

Leave a Reply