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

32 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
  7. Thanks so much for this! I used Choice eliminator, but it didn`t work as planned. Same issues as you stated above. The real life status when the form is opened. Do you happen to know if that is still the case for FormApp?

    ReplyDelete
    Replies
    1. Hi

      Thank you.

      No sorry I'm not aware of a workaround for the Form being open whilst somebody else makes a selection.

      Kind regards
      Phil

      Delete
  8. HI, I used your example to put a form and sheet together for appt times in the past, but the one I did was for just one date. Now I have another one that needs to be done by tomorrow morning and it is for more than 1 day with the options of times every 1/2 hr. Is that possible? Can I use the drop-down option or must it be multiple choice (without reading everything again, you may have went over this in your example and I will review it). So my new one needs 5 different dates and different times. How would you handle this? Currently I have the person selecting the date in the first section and then I have them selecting a time in the next section based on their response to the date. Is that possible?

    ReplyDelete
    Replies
    1. Hi

      I'm afraid this example is just for 1 Google Form question, of the type 'Multiple Choice'. Due to the Limitations mentioned in the blog post I have not developed this any further.

      Kind regards
      Phil

      Delete
  9. Hi! For some reason my numbers under the "Left" column will not go from 1 to 0. Is there something i need to type in the script?

    ReplyDelete
    Replies
    1. Hi

      Please ensure there is a 'COUNTIF' formula in those cells in the first instance as that performs the job.

      Kind regards
      Phil

      Delete
  10. Hi Phil

    Thanks for sharing the script. I just want to seek information, supposedly I've 2 multiple choice questions on the Google Form (Department and Appointments) how do I assign the script to Appointments Slots, as once the script run, it change by Department options to Appointment slots.

    Thanks

    ReplyDelete
    Replies
    1. Hi Gerald

      Thank you. I've not developed this script any further due to the 'Limitations' detailed in the post. The code here assumes only 1 multiple choice question and therefore does not specifically look for 'Appointments' but instead the first item on the Form.

      You would need to modify the loop in the Apps Script code to account for additional multiple choice questions.

      Kind regards
      Phil

      Delete
    2. Hi Phil

      Thanks a lot. I managed to find the solution by modifying the the Loop script and this works well. Just last question, supposedly one of the user canceled the appointments, are there anyways to restore the choice instead waiting the another users to submit the form to restore the choice? Thank you

      Delete
    3. Hi Gerald

      Glad you've been able to get sorted. The last lines of the script do rebuild the Form so I assume there would be a way to recreate a cancelled appointment. There's the question of how does a user inform you of a cancelled appointment however - they would need to use another method of communication.

      I've not explored it here as I stopped developing this when I hit the Limitations.

      Kind regards
      Phil

      Delete
  11. Thanks, Phil.
    Your script and explanation were very helpful.
    They worked perfectly for me.
    Regards,

    ReplyDelete
    Replies
    1. Thank you for your nice comments Ricardo, I'm glad you were able to get sorted.

      Kind regards
      Phil

      Delete
  12. Hi Phil, how to do to reinitialize the timeslot options since there is a formula linked to the form?
    Brian

    ReplyDelete
    Replies
    1. Hi Brian

      I'm a bit unsure what you mean, please can you provide more information? The Google Sheet is linked to the Form via an Apps Script Trigger. You need to set the Form up yourself manually with your timeslots and then manually mirror these within the Google Sheet.

      Kind regards
      Phil

      Delete
  13. Hi Phil,

    Would you please help me modify this script to accommodate a drop down list instead of multiple choice?

    ReplyDelete
    Replies
    1. Hi

      I'm really sorry but given the limitations of this script/tool it's not something I've used since learning about it over 2yrs ago.

      Kind regards
      Phil

      Delete
  14. Our question is on section 4 of the form. This is What we get
    Error
    Exception: Questions cannot have duplicate choice values.
    appointmentSlots @ Code.gs:45

    ReplyDelete
    Replies
    1. Hi

      As the error states you can't have duplicate values in your multiple choice question. So there cannot be two '11:00' options for instance in this example.

      Delete
  15. Hi Phil,

    Thank you so much for the app. This is just what I've been looking for for a long time.
    However, I need it to be a dropdown list and not multiple choice. How do I achieve that?

    Thanks a lot!

    ReplyDelete
    Replies
    1. Hi

      You're welcome.

      I've not actually done any further development of this script. From taking a quick look you would need to use the 'List' method (https://developers.google.com/apps-script/reference/forms/item-type.html) including populating this List with new items (https://developers.google.com/apps-script/reference/forms/item#aslistitem)

      So there are a few areas you would need to tweak the code for.

      Delete
    2. Hey, I actually modified this code to make a drop down. It works perfectly for my use case. I can try to share it here if it lets me. I'll do it when I get home from work.

      Delete
    3. Cool! I'm glad you were able to get something sorted.
      If you're happy for me to I can share it on your behalf? I don't allow others to post links due to rogue spammers altering them later on I'm afraid, very sorry.

      Delete