Google Form & Spreadsheet Script Project


Job Description


(UPDATE April 5) ---- IT APPEARS AS THOUGHT THE APPROACH DESCRIBED BELOW IS NOT PRACTICAL. WE ARE OPENED TO OTHER APPROACHES THAT MAY INCLUDE A PROGRAM (DATABASE?) THAT WORKS ON OUR WEB PAGE. One key aspect of the program is that it must be flexible enough to allow us to add/delete positions and the number of people required (if we were to delete, we would contact any already signed up volunteers manually - that is not part of the program).

(Original Description:)
This project is to develop scripts that will perform a number of functions within a Google Sheet that is filled from a Google Form.

We have set up a Google Form which will be used to recruit volunteers. The volunteers will fill out the Google form providing name and contact information and select which work positions they want to volunteer for. This information is on Sheet 1

Here are links to:
Google Form (updated 4/5)

The related Google Sheet

The Positions Limits sheet contains the open positions and the number of volunteers needed for each position.

Here are the scripts:

Script 1 must hide the position on the form when enough volunteers have selected that position (i.e. after each submission, the script must compare the number of positions needed with number filled. Then, hid the position so other volunteers don't select it if we have filled all open positions). Note, if a script can not do this, we will not be able to continue with the project.

One other aspect to this script. Occasionally a volunteer may tell us they must withdraw. We will delete volunteers from Sheet 1. The Script must count the commitments every day.

Script 2 will send a list of the new volunteers, their positions and contact information to the recipients shown on Report Distribution sheet on a daily basis.

Script 3 will send a summary of Positions, number of open positions, number filled and unfilled to the recipients shown on Report Distribution sheet on a daily basis. This report will basically be columns A, B & C from the Positions Limits Sheet.

Script 4 will send a list of the new volunteers, their positions and contact information to the responsible Coordinators shown on Position Limits sheet on a daily basis. For example, if three new volunteers sign up for Course Marshals today, then, information on those three volunteers would be sent to Lorraine at

Script 5 will send a reminder email to the volunteers the day before their assigned date reminding them of their volunteer commitment. For example on August 2, the script would send an email to Bob Biker at saying, " Bob, This is just a reminder that your Course Marshal assignment is tomorrow. Thanks for supporting the Marin Century!"

You may test the form by adding new volunteers. Or you may make a copy of the form for yourself. Do not remove data in the Google Sheet.

A couple requirement:
-We must be able to add positions (what is shown is not a compete list and descriptions will be changed)
-You must have experience writing scripts for Google.
-You need to provide examples
-Do NOT reply summarizing the above - you are wasting your time
-Do ask questions...if you ask very good questions, you're more likely to be hired.
-FYI, there is a chance this project will not be awarded because there are programs that provide similar features.

Skills: gmail, test