Create a sign-up for an offsite

  • This solution is a beginner-level automation project that creates an end-to-end offsite activity sign-up system using Google Sheets, Google Forms, and Apps Script.

  • The script uses the Spreadsheet service to manage activity schedules and responses, the Forms service to create and collect preferences, and the Utilities service for formatting data.

  • Users need a Google Account and internet access to set up and run the script by making a copy of the sample spreadsheet.

  • The script can create a form, generate test data, and assign activities based on employee preferences and activity capacity, with results presented in new sheets.

Coding level: Beginner
Duration: 5 minutes
Project type: Automation with a custom menu

Objectives

  • Understand what the solution does.
  • Understand what the Apps Script services do within the solution.
  • Set up the script.
  • Run the script.

About this solution

Create an end-to-end offsite activity sign-up system. The solution creates a form for employees to express their activity preferences, and matches employee preferences to the activity schedule.

Google Form for employees to sign up for offsite activities

How it works

Using an activity schedule in Google Sheets, the script creates a Google Forms form for employees to select their activity preferences. Once the responses are in, the script matches employee preferences with the schedule and capacity of each activity. The matches are provided in two new sheets, one organized by employee and the other by activity.

Apps Script services

This solution uses the following services:

Prerequisites

To use this sample, you need the following prerequisites:

  • A Google Account (Google Workspace accounts might require administrator approval).
  • A web browser with access to the internet.

Set up the script

To copy the spreadsheet and its attached script, click the following button:

Make a copy

Run the script

  1. In your copied spreadsheet, click Activities > Create form. You might need to refresh the page for this custom menu to appear.
  2. When prompted, authorize the script. <<../_snippets/oauth.md>>
  3. Click Activities > Create form again.
  4. To generate test responses, click Activities > Generate test data.
  5. To test the form yourself, click Tools > Manage form > Go to live form.
  6. Fill out the form and submit it.
  7. In the spreadsheet, click Activities > Assign activities.
  8. Review the two new sheets: Activities by person and Activity rosters.

Review the code

To review the Apps Script code for this solution, click View source code:

View source code

Code.gs

solutions/automations/offsite-activity-signup/Code.js
// To learn how to use this script, refer to the documentation:
// https://developers.google.com/apps-script/samples/automations/offsite-activity-signup

/*
Copyright 2022 Google LLC

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

    https://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/

const NUM_ITEMS_TO_RANK = 5;
const ACTIVITIES_PER_PERSON = 2;
const NUM_TEST_USERS = 150;

/**
 * Adds custom menu items when opening the sheet.
 */
function onOpen() {
  const menu = SpreadsheetApp.getUi()
    .createMenu("Activities")
    .addItem("Create form", "buildForm_")
    .addItem("Generate test data", "generateTestData_")
    .addItem("Assign activities", "assignActivities_")
    .addToUi();
}

/**
 * Builds a form based on the "Activity Schedule" sheet. The form asks attendees to rank their top
 * N choices of activities, where N is defined by NUM_ITEMS_TO_RANK.
 */
function buildForm_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  if (ss.getFormUrl()) {
    const msg = "Form already exists. Unlink the form and try again.";
    SpreadsheetApp.getUi().alert(msg);
    return;
  }
  const form = FormApp.create("Activity Signup")
    .setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId())
    .setAllowResponseEdits(true)
    .setLimitOneResponsePerUser(true)
    .setCollectEmail(true);
  const sectionHelpText = Utilities.formatString(
    "Please choose your top %d activities",
    NUM_ITEMS_TO_RANK,
  );
  form
    .addSectionHeaderItem()
    .setTitle("Activity choices")
    .setHelpText(sectionHelpText);

  // Presents activity ranking as a form grid with each activity as a row and rank as a column.
  const rows = loadActivitySchedule_(ss).map(
    (activity) => activity.description,
  );
  const columns = range_(1, NUM_ITEMS_TO_RANK).map((value) =>
    Utilities.formatString("%s", toOrdinal_(value)),
  );
  const gridValidation = FormApp.createGridValidation()
    .setHelpText("Select one item per column.")
    .requireLimitOneResponsePerColumn()
    .build();
  form
    .addGridItem()
    .setColumns(columns)
    .setRows(rows)
    .setValidation(gridValidation);

  form
    .addListItem()
    .setTitle("Assign other activities if choices are not available?")
    .setChoiceValues(["Yes", "No"]);
}

/**
 * Assigns activities using a random priority/random serial dictatorship approach. The results
 * are then populated into two new sheets, one listing activities per person, the other listing
 * the rosters for each activity.
 *
 * See https://en.wikipedia.org/wiki/Random_serial_dictatorship for additional information.
 */
function assignActivities_() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const activities = loadActivitySchedule_(ss);
  const activityIds = activities.map((activity) => activity.id);
  const attendees = loadAttendeeResponses_(ss, activityIds);
  assignWithRandomPriority_(attendees, activities, 2);
  writeAttendeeAssignments_(ss, attendees);
  writeActivityRosters_(ss, activities);
}

/**
 * Selects activities via random priority.
 *
 * @param {object[]} attendees - Array of attendees to assign activities to
 * @param {object[]} activities - Array of all available activities
 * @param {number} numActivitiesPerPerson - Maximum number of activities to assign
 */
function assignWithRandomPriority_(
  attendees,
  activities,
  numActivitiesPerPerson,
) {
  const activitiesById = activities.reduce((obj, activity) => {
    obj[activity.id] = activity;
    return obj;
  }, {});
  for (let i = 0; i < numActivitiesPerPerson; ++i) {
    const randomizedAttendees = shuffleArray_(attendees);
    for (const attendee of randomizedAttendees) {
      makeChoice_(attendee, activitiesById);
    }
  }
}

/**
 * Attempts to assign an activity for an attendee based on their preferences and current schedule.
 *
 * @param {object} attendee - Attendee looking to join an activity
 * @param {object} activitiesById - Map of all available activities
 */
function makeChoice_(attendee, activitiesById) {
  for (let i = 0; i < attendee.preferences.length; ++i) {
    const activity = activitiesById[attendee.preferences[i]];
    if (!activity) {
      continue;
    }
    const canJoin = checkAvailability_(attendee, activity);
    if (canJoin) {
      attendee.assigned.push(activity);
      activity.roster.push(attendee);
      break;
    }
  }
}

/**
 * Checks that an activity has capacity and doesn't conflict with previously assigned
 * activities.
 *
 * @param {object} attendee - Attendee looking to join the activity
 * @param {object} activity - Proposed activity
 * @return {boolean} - True if attendee can join the activity
 */
function checkAvailability_(attendee, activity) {
  if (activity.capacity <= activity.roster.length) {
    return false;
  }
  const timesConflict = attendee.assigned.some(
    (assignedActivity) =>
      !(
        assignedActivity.startAt.getTime() > activity.endAt.getTime() ||
        activity.startAt.getTime() > assignedActivity.endAt.getTime()
      ),
  );
  return !timesConflict;
}

/**
 * Populates a sheet with the assigned activities for each attendee.
 *
 * @param {Spreadsheet} ss - Spreadsheet to write to.
 * @param {object[]} attendees - Array of attendees with their activity assignments
 */
function writeAttendeeAssignments_(ss, attendees) {
  const sheet = findOrCreateSheetByName_(ss, "Activities by person");
  sheet.clear();
  sheet.appendRow(["Email address", "Activities"]);
  sheet.getRange("B1:1").merge();
  const rows = attendees.map((attendee) => {
    // Prefill row to ensure consistent length otherwise
    // can't bulk update the sheet with range.setValues()
    const row = fillArray_([], ACTIVITIES_PER_PERSON + 1, "");
    row[0] = attendee.email;
    attendee.assigned.forEach((activity, index) => {
      row[index + 1] = activity.description;
    });
    return row;
  });
  bulkAppendRows_(sheet, rows);
  sheet.setFrozenRows(1);
  sheet.getRange("1:1").setFontWeight("bold");
  sheet.autoResizeColumns(1, sheet.getLastColumn());
}

/**
 * Populates a sheet with the rosters for each activity.
 *
 * @param {Spreadsheet} ss - Spreadsheet to write to.
 * @param {object[]} activities - Array of activities with their rosters
 */
function writeActivityRosters_(ss, activities) {
  const sheet = findOrCreateSheetByName_(ss,