Monday, 22 April 2019

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 slots screenshot
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 screenshot
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).

10 comments:

  1. Thank for providing nice example. I am trying to use this for section-wise options in google form, but this script adds all options of renaming section of google form.

    In fact, I am interested to use this concept for sectionwise in google form options.

    Kindly help me.

    ReplyDelete
    Replies
    1. Hi

      Sorry I've not done any further developments with this so I'm unable to assist.

      Kind regards
      Phil

      Delete
  2. Phil,

    Thank you for the explanation, as I've looked at other examples and found them less than straightforward. I downloaded your spreadsheet to look at, but the Form doesn't seem to be available still. Is it possible to get access to be able to walk through and see how I can implement an approach like this?

    Thanks,


    Markie

    ReplyDelete
    Replies
    1. Hi Markie

      Firstly my apologies - it's been difficuly making the Form available without people being able to fill in the template (which then affects everybody else's copy). I've now resolved this and when you make a copy of the Google Sheet in the Drive folder it will also make a copy of the Form with it.

      Secondly - I've been retrospectively adding tutorial videos to posts, so your comment came at the right time as I've now sorted one for this post. It should help with more of the explanation/demo.

      Kind regards
      Phil

      Delete
  3. Phil,

    No apologies necessary, I was able to puzzle out the majority of it, and build a new form. I just couldn't get the FormApp.OpenByURL() to work, and your new video showed me I was using the form URL and not the editable one.

    I made copies of your files from above, and it works a charm.

    Brilliant job, thanks so much for the information!


    Markie

    ReplyDelete
  4. This is great! Thank you!

    Would you happen to know why "limit to 1 response" stops the script from working? I was hoping to be able to use that setting in addition to Edit after submit so people can change their answers, but it doesn't populate the Sheet anymore when I limit responses.

    ReplyDelete
  5. Hi

    You're welcome!

    No 'limit to 1 response' would not prevent this from working - if you're not getting any data in the Response Sheet at this point then something else is going wrong with your Google Form. It may be worth creating a new one from afresh.


    Kind regards
    Phil

    ReplyDelete
  6. Hey! We found that your post is very useful!


    We are new to JavaScript. Now we try to apply your script to our Form.

    Now we try to apply your script to our form with dropdown choice.

    However, we have many multiple choices in our Form.

    As your script accesses first list with multiple choice on Form, do you have any suggestion for accessing another list which is Dropdown choice in the form? Which part of the code that we need to adjust in order to do so?
    Your kind assistant will be very grateful.
    We are looking forward to hearing from you.

    Thank you so much,

    ReplyDelete
    Replies
    1. Hi !

      Line 42 is where the script looks for the items in the Form.
      Line 45 is where it specifically gets the multiple choice items and returns them as an array (https://developers.google.com/apps-script/reference/forms/form#getitemsitemtype). This is the bit you need to target.

      So for instance:
      var dropdown1 = formItems[0].asMultipleChoiceItem();
      var dropdown2 = formItems[1].asMultipleChoiceItem();

      Good luck!
      Phil

      Delete