Question About the App Script of Dynamic Dropdown list with Section Navigation in Google Form

Question

I am trying to make a dynamic dropdown list and Section Navigation in Google Form. However, my script can auto delete the choice when the quota has been met, the choice can’t navigate to the related page for other selections.

I am planning a health check event for my hospital. It needs to reserve by timeslot and date due to the crow control policy. The links below are my Google Spreadsheet for the form and my daft Google Form of the function.

https://forms.gle/ZV9Djni8hyQGdAd86

https://docs.google.com/spreadsheets/d/1F1dpGCTSlpEOUMh5txsZouhx784JmJvh66IsiGfDTtg/edit?usp=sharing

Reference:

1. How to set the go to sections on a Google Forms question using app script
2. https://www.pbainbridge.co.uk/2019/04/dynamically-remove-google-form-options.html

    function appointmentSlots() {
    
    var form = FormApp.openById("1VqFBKBD_-iKYk_3Ze40j2tvRIi093-alaoCDsXpFi8k");
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    
    var date1timelist = form.getItemById("2101588132").asListItem();
    
    var optionsSheet = ss.getSheetByName('Date Options');
    var dateoptions = optionsSheet.getRange('A2:A3').getValues();
    var dateleft = optionsSheet.getRange('C2:C3').getValues();
    
    var day1sheet = ss.getSheetByName('9/3');
    var day1timeoptions = day1sheet.getRange('A2:A4').getValues();
    var day1left = day1sheet.getRange('C2:C4').getValues();
    
    var formFieldsArray = [
                         ["9/3", 2061926149],
                         ["10/3", 1632977105]
                        ];
                        
    for(var h = 2; h < formFieldsArray.length; h++) {
    
      var datelist = form.getItemById(formFieldsArray[h][2]).asListItem();
      var avaibledateoptions = [];  
      
      var sectionday1timeslots = form.getItemById("2101588132").asPageBreakItem();
      var sectionday2timeslots = form.getItemById("1630116063").asPageBreakItem();
      var datechoice = datelist.getChoices();
      var optionsDataLength = dateoptions.length;
      
      for (var i=0; i<optionsDataLength; i++) {
        var choice = dateoptions[i][0];
        var left = dateleft[i][0];
        if ((choice != '') && (left > 0) == formFieldsArray[h][2]) {
            if (formFieldsArray[h]= "9/3") {
          datechoice.push(datelist.createChoice(avaibledateoptions,sectionday1timeslots));
        }
        else {
          datechoice.push(datelist.createChoice(avaibledateoptions,sectionday2timeslots));
    datelist.setChoices(avaibledateoptions);
        }
      } 
    } 
     }  
     var day1avaibledateoptions = []; 
     var optionsday1Length = day1timeoptions.length;
     for (var i=0; i<optionsday1Length; i++) {
     var day1timechoice = day1timeoptions[i][0];
     var day1timeleft = day1left[i][0];
     if ((day1timechoice != '') && (day1timeleft > 0)) {
      day1avaibledateoptions.push(day1timechoice);
     }
      }
           date1timelist.setChoiceValues(day1avaibledateoptions)
         }
          //etc for day2 timeslots choice and day3 timeslots
         }
        }
        }

Answer 1

In order to modify your form depending on the changing cell values in your Spreadsheet (caused by new form submissions) you will need to set up an installable onChange trigger that will basically run your function when a change on your Spreadsheet is done (like one coming from a form submission). To create such a trigger, please access your trigger pannel and then click on Create trigger and select as the event type onChange assigning it to the function you will be using to create/delete the form items.

Once a user submits a new form and you do certain calculations on your Spreadsheet to determine how many slots are free for that time slot, you can take the value of the cell that tells you how many free appointments are free for that time and if that number is 0 you can proceed to delete that question element using the method deleteItem().

If you eventually end up resetting the form (because your time slot is free again or someone cancels the meeting), you can undo this by creating back the element.

The following piece of code is a basic example on how to delete and create form items based on the changes of a Spreadsheet cell. It has self explanatory comments:

function onChange() {
  // Get the different sheets where you have all your left places in your time slots
  var sheet = SpreadsheetApp.getActive().getSheetByName('A');

  // Get your form
  var form = FormApp.openById('FORMID');

  // Here you would get each element that might depend on whether there are any
  // appointments left or not
  var element = form.getItems()[2];

  // Get the cell value that tells you if the time slot is already full (full=0)
  var value = sheet.getRange('C2').getValue();

  // If the value is 0 it means that this time slot is all completed and nobody
  // should be able to select it again
  if (value == 0) {
    // delete this item
    form.deleteItem(element);
    // if it is not full yet, it might be because your reset the time slot and      therefore
    // the element does not exist any more
  } else {
    // if the element exists dont do anything but if it doesnt and there are available
    // apointments create it again 
    if (!element) {
      form.addMultipleChoiceItem().setTitle('B').setChoiceValues(['Cats', 'Dogs']);
    }
  }
}

If you want to remove a choice option rather than an Item, you can look for the item, get all the choices as an array and then remove the choice you don't want any more from this array. Finally, you can update the item with your updated options with setChoices(). Here is a code example on how to achieve this:

function myFunction() {

// This is an example where I only have a single multiple choice item
  var choiceItem = FormApp.getActiveForm().getItems(FormApp.ItemType.MULTIPLE_CHOICE)[0].asMultipleChoiceItem();

  // Get current choices array
  var choices = choiceItem.getChoices();
  // Get choice you want to delete, this would be your times or dates obtained from
  // the cell values
  var choiceToBeRemoved = "A";
  // remove choice from array  
  choices = choices.filter(function(choice){return choice.getValue() !== choiceToBeRemoved});
  // Set updated choices
  choiceItem.setChoices(choices);
}

References

• setChoices
• Javascript filter()