Coding level: Beginner
Duration: 5 minutes
Project type: Automation with a custom menu
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.
How it works
Using an activity schedule in Google Sheets, the script creates a Google 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:
- Spreadsheet Service: Holds the activity schedule and form responses, and assigns activities to the employees.
- Forms Service: Creates a form for employees to enter their activity preferences.
- Utilities Service: Formats strings and dates.
Before you begin
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
- Click the button below to make a copy of the Offsite Activity Sign-up
Make a copy
- Click Activities > Create form. You might need to refresh the page for this custom menu to appear.
- When prompted, click Review permissions and authorize the script.
- Click Activities > Create form again.
- To generate test responses, click Activities > Generate test data.
- To test the form yourself, click Tools > Manage form > Go to live form.
- Fill out the form and submit it.
- In the spreadsheet, click Activities > Assign activities.
- Review the two new sheets, Activities by person and Activity rosters.
Open the Apps Script project
The sample script's code includes in-line comments to help you understand how it works. To access the code, in the spreadsheet, click Extensions > Apps Script.
This sample is maintained by Google with the help of Google Developer Experts.