Dynamically remove Google Form options

The following Google Apps Script code was developed as I wanted to learn about removing Google Form option choices as part of exploring its capabilities for use with appointment slots. I am aware there are a number of Google add-ons that achieve this but they come with potential data protection issues when accessing your Google Account, hence an in-house approach.

The principle is simply that a user would select their desired appointment slot on the Form and then that option would be unavailable for the next user accessing the Form. This is achieved via apps script running in the Form response sheet to recreate the multiple choice list on the Form after a submission, with only the available options left - based on their quota allowance.

Google Form appointment slot

The Form response sheet has a couple of extra tabs added to it - one of those being Options - which contains:

• Options column - a list of the time slots that is used to recreate the multiple choice list on the Form after submission. Note: these cells are formatted as Plain text to prevent the apps script from trying to convert them into a fully defined date string.
• Quota column -  to define how many times an option can be selected before it should disappear. In this example we are using '1' but you could tweak it so that 2 or 3 people could share an appointment slot for instance.
• Left column - contains a 'countif' formula to deduct the quota value from any instance of the option appearing in the Form responses tab (where Form submissions are saved by default). So if somebody selected 11:00 this formula would deduct 1 from the quota (which is set to 1 in this example) and the result would be a value of 0 (which the apps script can then evaluate to determine that options should no longer appear on the Form).

Options tab with quota and calculation

The Config tab simply contains the Url of the associated Form - put here so it is easier to access without going into the apps script code.

How the code works
The beginning of the code performs the usual getting of the spreadsheet data so we have access to it during the rest of the script. We then use the 'FormApp' and 'openByUrl' to get the actual Google Form. Next we create an empty array that will be used to push only the available options into when recreating the multiple choice list after a Form submission.

So now we loop through the options in a for loop and check that option (slot time) is not blank and that the value in the Left column is greater than 0 (otherwise the option has already been taken):

if ((choice != '') && (left > 0)) {
      options.push(choice);
}

Now that we have an array of new values for the available multiple choice list we need to update the Form. First we identify the type of item we want to find in the Form - so a multiple choice list - then we get all instances of this item type from the Form - of which there is only one here:

var formList = FormApp.ItemType.MULTIPLE_CHOICE;
var formItems = form.getItems(formList);

The final step involves accessing the first list on the Form ([0]) as a multiple choice item and setting its values with those from our above array:

formItems[0].asMultipleChoiceItem().setChoiceValues(options);

An onFormSubmit trigger will need to be created in order to initiate the script to run.

Limitations
During testing of the Form I noticed there were a few nuisance limitations however:

1. If a user selects to submit another response straight after completing the Form the apps script seems to be a few seconds behind in updating the list, hence they would see the same option available again.
2. If multiple users open the Form at the same time it will not live update and remove options as they are selected (unless they refresh the page) - so they could all pick the same option.

Dynamically remove Google Form folder (please use 'File' > 'Make a copy' for your own version).