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

Update 21/12/2015: As many may have gathered I haven’t maintained this solution and some of the code may be broken. You are free to re-use the code shared in this post. You might also be interested in this Event Manager Google Add-on  by Romain Vialard made to administer events using Google Sheets to manage event publication and bookings.

Back in March 2010 I looked into using Google Apps (Spreadsheet, Calendar and Sites) as an event management system utilising the power of Google App Scripts. The solution I came up with was okay, but wasn’t very user friendly and bits of the code were, well, a bit messy. Shortly after posting the final version I also came across Romain Vialard’s – Manage your events: Calendar Importer and Registration Form.

Romain had some really nice features including streamlining the setup and passing information to a booking form and for a long time I’ve vowed to return to my original solution and incorporate some of Romain’s ideas. So here it is:

The best Google Spreadsheet EventManager (Make a copy when opened)

The main features of the new Spreadsheet are:

  • Entering events in a Google Spreadsheet which are then pushed to Google Calendar and optionally Sites.
  • Improved registration with an autofill for an event ID populated using the the booking form link
  • Automatic email notification to admin for new bookings
  • Delegate email addresses automatically added to guest list in Google Calendar and added to Google Contacts
  • Option for the delegate to receive personalised travel directions in joining instructions email
  • General tidy up of the code

The video below describes these features and how to setup the EventManager Spreadsheet (I’ve I’m sounding a little lacklustre it was really late when I recorded this and I just want to get this homework out):

Gotchas (Google Apps Script Map Service)

There were a couple of gotchas along the way, mostly around the maps/direction service. I’m still not convinced that this feature is entirely worthwhile, but I decided to preserver with it just as an opportunity to familiarise myself with this service. I really shouldn’t gripe as most of the hard work was done in the ‘Send customized driving directions in a mail merge’ template. My main problem was setting the mode of transport which isn’t covered in the template. I wasn’t helped by the lack of documentation on the Google App Script site, but a friendly Google Employee was able to answer a question I posed on the forum.

If anyone else is looking at using the Map service is Google App Script I would recommend looking at the documentation for the main Google Maps API to let you take a punt at what some of the methods are (as highlighted in this post I made to someone else’s problem with getting a route duration and distance).

Another reason I might give up on the Google Apps Script Map service is you probably can pull most of this functionality straight from the Google Maps API as JSON, allowing you to use something like:

var url = "http://maps.googleapis.com/maps/api/directions/json?origin=Chicago,IL&destination=Los+Angeles,CA&sensor=false";
var response = UrlFetchApp.fetch(url);

PS I was also left scratching my head when Session.getUser().getUserLoginId() suddenly stopped working but I’m guessing this is Google patching a possible security problem.

Here are some links to an example event so you can see what the results look like:

Below is a copy of the code (mainly for SEO purposes):

// EventManagerV3 glued together by mhawksey http://www.google.com/profiles/m.hawksey
// Related blog post https://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; }

191 Comments



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


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


  2. i’m from italy but google doc is in english

    so i could digit for example ’29/11/2010 hh:9:00′ ?


    1. 29/11/2010 09:00:00 (Date/times should be validated in the sheet. Also if you enter a date/time then click on the cell a small calendar should popup


  3. I have always this error

    Oops
    Cannot find method formatDate(number,string,string).


    1. Make sure your event ID isn’t numeric. Either letters or letters and numbers are allowed


  4. great it was the mistake
    the reservation is kept by any email arrived in my box (i try with different email)


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


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


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


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


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


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


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


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


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


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


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


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


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


  14. That appears to work. Thanks

    The script is very good. Bravo.

    Please explain the change or the var.

    Trying to understand.


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


  15. 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?


    1. Never looked at Google Checkout so don’t know what you can do. Really interesting idea tho. Have you every looked at eventbrite.com?


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


  17. Hi Martin,

    I lose the extra menu entries after I try to send out the joining instructions emails. Any ideas?


  18. 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!


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


  20. Hi ,
    I can’t seem to copy the form. The copy function is grayed out.
    GREAT FORM though!!


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


    1. @Thomas is it failing for all contacts or just for those who have registered for an event before?


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


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


    1. Google App Script rewrote their api access to Contacts, I think this has broke my script. I need to rewrite :(


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


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


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


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


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


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


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


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


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


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


    1. Hi – the TypeError on getTimeZone usually indicates that the calendar name is wrong or doesn’t exist
      Martin


    2. I know this was years ago but did you ever figure this out? I’m getting the same error.


      1. For anyone else having this issue, I figured it out. I had logged into two google accounts so the spreadsheet copy was in one and I authorized the script for the other.


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


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


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


    1. Hi Shawn – You can certainly add a guest to a calendar event via apps script, but I’m not entirely sure how this is handled
      Martin


  34. awesome script. thank you!!!

    the event manager menu no longer appears in toolbar. any ideas?


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


  35. It’s great to see that google apps is now a ‘quick mashup’ dev environment like MS Office but for online apps


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


  36. 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!


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


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


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


    1. Hi Marc – this might be because the start and stop date/time is not formatted correctly
      Martin


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


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


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


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


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


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


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


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


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


  43. 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?


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


    1. @Jon a vague recollection that and/or spreadsheet, script, calender properties need to have same locale


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


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


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


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


    1. not sure why that would be but if you share the spreadsheet with me I can have a look


      1. I just mailed a zip file with spreadsheet images. Thanks for a quick response.


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


      1. The registration button is by default rendered with html markup if going for plain text “& B3 &”&entry_1=${“”Event ID””}”


    3. No fix appears to be forthcoming regarding the templating issue so I’ve come up with a workaround. The template has been updated, to modify and existing copy open Tools > Script editor.. and replace the code with the one here


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


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


      1. Hi Bonnie – can you share the spreadsheet with me and I’ll have a look
        Martin


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