729days since
Apps Script Hackathon

Resources‎ > ‎

Code Walkthrough: Event Registration Template

Goal
This tutorial will take you through the process of building a script that will manage event registration, including sending confirmation or waitlist emails to attendees, as well as sending reminder emails before the event.

Section 1: Creating a form and email templates
Open this spreadsheet and choose File > Make a copy.

In your copied spreadsheet, you should see two sheets: Registration and Email Templates. On the Registration sheet, you should see seven columns: TimestampFirst NameLast NameEmail AddressCompany or OrganizationMay we contact you about other events in the future?, and Status. The first six columns are populated when a user submits a form associated with the spreadsheet. The last column, Status, will be populated by our script.

Let's look at the form. Go to Form (0) > Edit Form. This is where you can edit the name of the form or content of the questions. For now, leave it as-is and close the Edit Form window.

Now switch over to the Email Templates sheet. Here you see email templates in cells A1:A4, with descriptions of the templates in cells B1:B4. These will be the basis of the emails that are automatically sent to attendees when they sign up, when we update the waiting list, or when we send event reminders. Notice that there are placeholders in the text in several locations. The placeholders formatted like ${"Event Name"} and ${"Event Location"} and in the next section, we'll be setting up our script so that it can fill in the appropriate values for those placeholders.

Section 2: Configuring event-specific values
There are some things our script needs to know about the event in order to personalize the emails that it sends. Those things are:
  • The name of the event
  • The general location of the event
  • The address of the event
  • The date of the event
  • The time range of the event
  • The event organizer's name
  • The event organizer's team (optional)
  • A URL where attendees can learn more about the event (optional)
There are a couple of other things the script needs to know as well:
  • What is the maximum number of attendees we can allow (so we can add people to the waiting list once we hit capacity)?
  • How many questions are in the spreadsheet form (so we can know where to expect our Status column to be)?
To configure these values, we could just hard-code them as constants in the script, but instead we will use Script Properties to store them, and create a custom user interface, like you see below, to manage them.

Open the script editor from the spreadsheet by going to Tools > Script editor. You should see a project named Event Registration with three files: Registration FunctionsHelpers, and Initial Setup. 

Copy and paste the code below into the Initial Setup file, below the onOpen() function that is already there:
/**
 * Constants for status values 
 */
var YES = "Yes";
var NO = "No";
var WAITLIST = "Waitlist";

/**
 * Key of ScriptProperties for event name.
 * @type {String}
 * @const
 */
var EVENT_NAME_PROPERTY_NAME = "eventName";

/**
 * Key of ScriptProperties for maximum number of event attendees.
 * @type {String}
 * @const
 */
var MAX_ATTENDEES_PROPERTY_NAME = "maxAttendees";

/**
 * Key of ScriptProperties for the general event location.
 * @type {String}
 * @const
 */
var EVENT_LOCATION_PROPERTY_NAME = "eventLocation";

/**
 * Key of ScriptProperties for event address.
 * @type {String}
 * @const
 */
var EVENT_ADDRESS_PROPERTY_NAME = "eventAddress";

/**
 * Key of ScriptProperties for the event date.
 * @type {String}
 * @const
 */
var EVENT_DATE_PROPERTY_NAME = "eventDate";

/**
 * Key of ScriptProperties for event time range.
 * @type {String}
 * @const
 */
var EVENT_TIMES_PROPERTY_NAME = "eventTimes";

/**
 * Key of ScriptProperties for optional more info URL.
 * @type {String}
 * @const
 */
var MORE_INFO_PROPERTY_NAME = "moreInfo";

/**
 * Key of ScriptProperties for event organizer name.
 * @type {String}
 * @const
 */
var EVENT_ORGANIZER_NAME_PROPERTY_NAME = "eventOrganizerName";

/**
 * Key of ScriptProperties for optional event organizer team.
 * @type {String}
 * @const
 */
var EVENT_ORGANIZER_TEAM_PROPERTY_NAME = "eventOrganizerTeam";

/**
 * Key of ScriptProperties for the number of questions in the spreadsheet form.
 * @type {String}
 * @const
 */
var NUM_FORM_QUESTIONS_PROPERTY_NAME = "numFormQuestions";

// getters and setters

/**
 * @return String The name of this event, primariy used in emails to attendees.
 */
function getEventName() {
  var name = ScriptProperties.getProperty(EVENT_NAME_PROPERTY_NAME);
  if (name == null) {
    name = "";
  }
  return name;
}
 
/**
 * @param String The name of this event, primariy used in emails to attendees.
 */
function setEventName(name) {
  ScriptProperties.setProperty(EVENT_NAME_PROPERTY_NAME, name);
}

/**
 * @return int The maximum number of attendees allowed for the event.
 */
function getMaxAttendees() {
  var maxAttendees = ScriptProperties.getProperty(MAX_ATTENDEES_PROPERTY_NAME);
  if (maxAttendees == null) {
    maxAttendees = 0;
  }
  return parseInt(maxAttendees);
}
 
/**
 * @param String The maximum number of attendees allowed for this event.
 */
function setMaxAttendees(maxAttendees) {
  ScriptProperties.setProperty(MAX_ATTENDEES_PROPERTY_NAME, maxAttendees);
}

/**
 * @return String The general location of this event.
 */
function getEventLocation() {
  var location = ScriptProperties.getProperty(EVENT_LOCATION_PROPERTY_NAME);
  if (location == null) {
    location = "";
  }
  return location;
}
 
/**
 * @param String The general location of this event.
 */
function setEventLocation(location) {
  ScriptProperties.setProperty(EVENT_LOCATION_PROPERTY_NAME, location);
}

/**
 * @return String The address of this event.
 */
function getEventAddress() {
  var address = ScriptProperties.getProperty(EVENT_ADDRESS_PROPERTY_NAME);
  if (address == null) {
    address = "";
  }
  return address;
}

/**
 * @param String The address of this event.
 */
function setEventAddress(address) {
  ScriptProperties.setProperty(EVENT_ADDRESS_PROPERTY_NAME, address);
}
 
/**
 * @return String The date of this event.
 */
function getEventDate() {
  var dt = ScriptProperties.getProperty(EVENT_DATE_PROPERTY_NAME);
  if (dt == null) {
    dt = "";
  }
  return dt;
}

/**
 * @param String The date of this event.
 */
function setEventDate(dt) {
  ScriptProperties.setProperty(EVENT_DATE_PROPERTY_NAME, dt);

/**
 * @return String The time range of this event.
 */
function getEventTimes() {
  var times = ScriptProperties.getProperty(EVENT_TIMES_PROPERTY_NAME);
  if (times == null) {
    times = "";
  }
  return times;
}

/**
 * @param String The time range of this event.
 */
function setEventTimes(times) {
  ScriptProperties.setProperty(EVENT_TIMES_PROPERTY_NAME, times);

/**
 * @return String The optional "more info" URL for this event.
 */
function getMoreInfo() {
  var info = ScriptProperties.getProperty(MORE_INFO_PROPERTY_NAME);
  if (info == null) {
    info = "";
  }
  return info;
}

/**
 * @param String The optional "more info" URL for this event.
 */
function setMoreInfo(info) {
  ScriptProperties.setProperty(MORE_INFO_PROPERTY_NAME, info);

/**
 * @return String The event organizer's name.
 */
function getEventOrganizerName() {
  var name = ScriptProperties.getProperty(EVENT_ORGANIZER_NAME_PROPERTY_NAME);
  if (name == null) {
    name = "";
  }
  return name;
}

/**
 * @param String The event organizer's name.
 */
function setEventOrganizerName(name) {
  ScriptProperties.setProperty(EVENT_ORGANIZER_NAME_PROPERTY_NAME, name);

/**
 * @return String The event organizer's team (optional).
 */
function getEventOrganizerTeam() {
  var team = ScriptProperties.getProperty(EVENT_ORGANIZER_TEAM_PROPERTY_NAME);
  if (team == null) {
    team = "";
  }
  return team;
}

/**
 * @param String The event organizer's team (optional).
 */
function setEventOrganizerTeam(team) {
  ScriptProperties.setProperty(EVENT_ORGANIZER_TEAM_PROPERTY_NAME, team);
}

/**
 * @return int The number of questions in the spreadsheet form.
 */
function getNumFormQuestions() {
  var num = ScriptProperties.getProperty(NUM_FORM_QUESTIONS_PROPERTY_NAME);
  if (num == null) {
    num = 0;
  }
  return parseInt(num);
}

/**
 * @param String The number of questions in the spreadsheet form.
 */
function setNumFormQuestions(num) {
  ScriptProperties.setProperty(NUM_FORM_QUESTIONS_PROPERTY_NAME, num);
}

The code we just pasted references a Script Properties key for each event-specific value we need to store, as well as a getter and setter for each. These will be used throughout the script when we need to access those values.

Next, we will create a user interface panel so the event organizer can save and modify these event-specific values. For this we will use the Ui Service. Copy and paste the two functions below into your Initial Setup file, below the existing code. 

/**
 * Configure all UI components and display a dialog to allow the user to 
 * configure the script.
 */
function renderConfigurationDialog() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle(
      "Set Up the Event Registration Script");
  app.setStyleAttribute("padding", "10px");
  app.setHeight("500");
  app.setWidth("600");
  
  var helpLabel = app.createLabel(
      "From here, you can configure the values that the script needs to "
      + "personalize your event. You only need to enter this information "
      + "once, after setting up the spreadsheet form, however you can come "
      + "back and edit values later.");
  helpLabel.setWidth("100%");
  
  var eventNameLabel = app.createLabel(
      "Event Name:");
  eventNameLabel.setWidth("70%");
  var eventName = app.createTextBox();
  eventName.setName("eventName");
  eventName.setWidth("95%");
  eventName.setText(getEventName());
  
  var maxAttendeesLabel = app.createLabel(
      "Maximum Number of Attendees:");
  maxAttendeesLabel.setWidth("70%");
  var maxAttendees = app.createTextBox();
  maxAttendees.setName("maxAttendees");
  maxAttendees.setWidth("95%");
  maxAttendees.setText(getMaxAttendees());
  
  var eventLocationLabel = app.createLabel(
      "General event location, e.g. Mountain View, CA:");
  eventLocationLabel.setWidth("70%");
  var eventLocation = app.createTextBox();
  eventLocation.setName("eventLocation");
  eventLocation.setWidth("95%");
  eventLocation.setText(getEventLocation());
  
  var eventAddressLabel = app.createLabel(
      "Event Address");
  eventAddressLabel.setWidth("70%");
  var eventAddress = app.createTextArea();
  eventAddress.setName("eventAddress");
  eventAddress.setWidth("95%");
  eventAddress.setText(getEventAddress());
  
  var eventDateLabel = app.createLabel(
      "Event Date:");
  eventDateLabel.setWidth("70%");
  var eventDate = app.createTextBox();
  eventDate.setName("eventDate");
  eventDate.setWidth("95%");
  eventDate.setText(getEventDate());
  
  var eventTimesLabel = app.createLabel(
      "Event Time Range, e.g. 2:00pm - 8:00pm PST:");
  eventTimesLabel.setWidth("70%");
  var eventTimes = app.createTextBox();
  eventTimes.setName("eventTimes");
  eventTimes.setWidth("95%");
  eventTimes.setText(getEventTimes());
  
  var moreInfoLabel = app.createLabel(
      "URL for More Info (optional):");
  moreInfoLabel.setWidth("70%");
  var moreInfo = app.createTextBox();
  moreInfo.setName("moreInfo");
  moreInfo.setWidth("95%");
  moreInfo.setText(getMoreInfo());
  
  var eventOrganizerNameLabel = app.createLabel(
      "Event Organizer's Name:");
  eventOrganizerNameLabel.setWidth("70%");
  var eventOrganizerName = app.createTextBox();
  eventOrganizerName.setName("eventOrganizerName");
  eventOrganizerName.setWidth("95%");
  eventOrganizerName.setText(getEventOrganizerName());
  
  var eventOrganizerTeamLabel = app.createLabel(
      "Event Organizer's Team (optional, displayed in email closing):");
  eventOrganizerTeamLabel.setWidth("70%");
  var eventOrganizerTeam = app.createTextBox();
  eventOrganizerTeam.setName("eventOrganizerTeam");
  eventOrganizerTeam.setWidth("95%");
  eventOrganizerTeam.setText(getEventOrganizerTeam());
  
  var numFormQuestionsLabel = app.createLabel(
      "Number of Questions in the Spreadsheet Form:");
  numFormQuestionsLabel.setWidth("70%");
  var numFormQuestions = app.createTextBox();
  numFormQuestions.setName("numFormQuestions");
  numFormQuestions.setWidth("95%");
  numFormQuestions.setText(getNumFormQuestions());
  
  var saveHandler = app.createServerClickHandler("saveConfiguration");
  var saveButton = app.createButton("Save Configuration", saveHandler);
  
  var listPanel = app.createGrid(10, 2);
  listPanel.setStyleAttribute("margin-top", "10px")
  listPanel.setWidth("100%");
  listPanel.setWidget(0, 0, eventNameLabel);
  listPanel.setWidget(0, 1, eventName);
  listPanel.setWidget(1, 0, maxAttendeesLabel);
  listPanel.setWidget(1, 1, maxAttendees);
  listPanel.setWidget(2, 0, eventLocationLabel);
  listPanel.setWidget(2, 1, eventLocation);
  listPanel.setWidget(3, 0, eventAddressLabel);
  listPanel.setWidget(3, 1, eventAddress);
  listPanel.setWidget(4, 0, eventDateLabel);
  listPanel.setWidget(4, 1, eventDate);
  listPanel.setWidget(5, 0, eventTimesLabel);
  listPanel.setWidget(5, 1, eventTimes);
  listPanel.setWidget(6, 0, moreInfoLabel);
  listPanel.setWidget(6, 1, moreInfo);
  listPanel.setWidget(7, 0, eventOrganizerNameLabel);
  listPanel.setWidget(7, 1, eventOrganizerName);
  listPanel.setWidget(8, 0, eventOrganizerTeamLabel);
  listPanel.setWidget(8, 1, eventOrganizerTeam);
  listPanel.setWidget(9, 0, numFormQuestionsLabel);
  listPanel.setWidget(9, 1, numFormQuestions);
  
  // Ensure that all form fields get sent along to the handler
  saveHandler.addCallbackElement(listPanel);
  
  var dialogPanel = app.createFlowPanel();
  dialogPanel.add(helpLabel);
  dialogPanel.add(listPanel);
  dialogPanel.add(saveButton);
  app.add(dialogPanel);
  doc.show(app);
}

/** Retrieve config params from the UI and store them. */
function saveConfiguration(e) {
  setEventName(e.parameter.eventName);
  setMaxAttendees(e.parameter.maxAttendees);
  setEventLocation(e.parameter.eventLocation);
  setEventAddress(e.parameter.eventAddress);
  setEventDate(e.parameter.eventDate);
  setEventTimes(e.parameter.eventTimes);
  setMoreInfo(e.parameter.moreInfo);
  setEventOrganizerName(e.parameter.eventOrganizerName);
  setEventOrganizerTeam(e.parameter.eventOrganizerTeam);
  setNumFormQuestions(e.parameter.numFormQuestions);
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

The renderConfigurationDialog() function is where we build the user interface. First, we get a reference to the active spreadsheet, and then we create an instance of a UI application, with the title "Set Up the Event Registration Script."

Next, we create a helpLabel to explain to the user what this panel is for. A label is just a widget that can display text.

After that we create several pairs of widgets, where each pair has a label and a text box. We create one pair for each piece of data that we store in Script Properties. For each text box, it's important to note that we use the .setName() method. By setting a name for a widget, it can be referenced in a handler, such as a button click handler later.

That's what we're setting up with the saveHandler and saveButton. The when the saveButton is clicked, the script will call a function named saveConfiguration(), which we'll look at in a minute.

Next we create a grid called listPanel, and we add all the label and text box pairs to it, then add that listPanel as a callback element to the saveHandler. This is important; if you skip this step, the values from the text boxes will not be available to the saveConfiguration function.

Finally, we create a FlowPanel widget called dialogPanel and add all our other widgets to it, then we call the show() method on our spreadsheet to display the UI we've built.

The saveConfiguration function is pretty simple. Notice that it takes a parameter, e.  Each of the text boxes that we named in renderConfigurationDialog() are available as e.parameter.<name>. So in this function, we're calling the relevant setter for each text box. Then we get a reference to the active UI application and close it, then return.

Now the values are saved in Script Properties for the script to use later. If you run the renderConfigurationDialog() function from the script editor, you can try this out.

At the beginning of this section, we briefly mentioned the onOpen() function that was already in this file. onOpen is something called a simple event handler. It's a specially named function that's built into Apps Script. The code in an onOpen function gets run any time a spreadsheet is opened or reloaded. In our onOpen function, we're creating a custom spreadsheet menu named "Manage Event" and adding several menu entries to it. Configure calls our renderConfigurationDialog() function. Try it from your spreadsheet: click Manage Event > Configure, and your UI panel should show up.

Section 3: Sending a confirmation email when a form is submitted

Next we need to set up our script so that when a user registers for our event, we can determine if there is space available, and if so send a confirmation. If space is not available, we should add them to the waitlist, and send them an email to let them know they've been waitlisted. The code to do this is in the Registration Functions file and is called sendInitialConfirmationEmail(). This code uses several of the helper functions that are in the Helpers file as well.

A recent addition to Apps Script is the Script service, which allows you to programmatically set up triggers. We need to set the script up so that it will automatically run sendInitialConfirmationEmail() whenever a form is submitted. 

Add the following code to the saveConfiguration() function, right before the line that says return app;

// Check if the onFormSubmit trigger has been set
  var triggers = ScriptApp.getScriptTriggers();
  var isSet = false;
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getEventType() == ScriptApp.EventType.ON_FORM_SUBMIT) {
      isSet = true;
    }
  }
  if (!isSet) {
    ScriptApp.newTrigger('sendInitialConfirmationEmail')
             .forSpreadsheet(sheet)
             .onFormSubmit()
             .create();
  }

Now, when a form is submitted, an appropriate email will be sent to the person who submitted the registration entry. Please use your own email addresses for testing, since the script does actually send emails!

Section 4: Updating the waiting list

Also in the Registration Functions file, we have a function called updateWaitlist(). If you run this function by going to Manage Event > Update Waitlist, it will see if there is any space available for the event, and also check if anyone is waitlisted. If so, it will invite the appropriate number of waitlisted people by sending them an update email and marking their status as Yes.

Section 5: Sending reminder emails before the event

Finally, again in the  Registration Functions file, we have a function called sendReminderEmail(). If you run this function by going to Manage Event > Send Reminder Emails, it will send your reminder email template to all attendees whose status is Yes. This can be helpful to send additional details before the event and to remind people that they've signed up.

Wrap-up

Now you have a script that you can make copies of and can be configured for different events. Since the event-specific information is configured in Script Properties, you don't have to change any values in the code in order to use it.

If you want to extend this script during the hackathon, here are a few ideas:
  • Use the Calendar Service to create a calendar event for attendees if they are confirmed for the event.
  • Use filters and labels in Gmail in conjuction with the GmailApp service to automatically look for replies to the confirmation emails. Maybe you could come up with some strings to look for in replies to indicate people were no longer attending and mark their status as "No" in  the spreadsheet.
  • There's an Export Range As CSV option in the script. Take a look and see what it does and see if you can find other similarly useful things to do with the data. Maybe you could create name badges for attendees from the data in the spreadsheet.
Comments