I am setting up dynamic dropdown fields in a Google Form, where one dropdown’s options depend on the selection made in another dropdown, using Google Sheets and Apps Script. My church choir has 8 Choir Pastors (Choir Governors) and each Governor has a number of Shepherds under them. I want create a dynamic dropdown that shows only the Shepherds under a Governor when you click on that particular Governor in my Google Form.

I have written the code in the Apps Script of my Google Sheets and everything executes completely when i run it but it does not work in my Google Form.

function populateDropdowns() {
  var form = FormApp.openById('1o3tbZiSWVMpyRNSf0KXJAQU9MCYnn5fsQygIIZGWGFU'); // Replace with your Form ID
  Logger.log('Form opened successfully'); // Log statement

  var sheet = SpreadsheetApp.openById('19NhQ0DWGZPLIyKISWcUJmwFEDc_yoj3ZqikcNFAiljE').getSheetByName('ChoirGovernors'); // Replace with your Spreadsheet ID
  Logger.log('Sheet opened successfully'); // Log statement

  var data = sheet.getDataRange().getValues();
  Logger.log('Data retrieved from sheet: ' + JSON.stringify(data)); // Log statement

  var governors = {};
  
  // Create a dictionary of governors and their shepherds
  for (var i = 1; i < data.length; i++) {
    var governor = data[i][0];
    var shepherd = data[i][1];
    if (!governors[governor]) {
      governors[governor] = [];
    }
    governors[governor].push(shepherd);
  }
  Logger.log('Governors and their shepherds: ' + JSON.stringify(governors)); // Log statement

  var items = form.getItems();
  Logger.log('Form items retrieved: ' + items.length); // Log statement

  var governorDropdown = null;
  var shepherdDropdown = null;

  // Identify the dropdown items in the form
  for (var i = 0; i < items.length; i++) {
    var item = items[i];
    if (item.getTitle() === 'Choir Governors') {
      governorDropdown = item.asListItem();
      Logger.log('Governor dropdown found'); // Log statement
    } else if (item.getTitle() === 'Name of Choir Shepherd') {
      shepherdDropdown = item.asListItem();
      Logger.log('Shepherd dropdown found'); // Log statement
    }
  }

  if (governorDropdown && shepherdDropdown) {
    var governorChoices = [];
    for (var governor in governors) {
      governorChoices.push(governor);
    }
    governorDropdown.setChoiceValues(governorChoices);
    Logger.log('Governor choices set'); // Log statement

    // Set up form submission trigger
    ScriptApp.newTrigger('onFormSubmit')
             .forForm(form)
             .onFormSubmit()
             .create();
    Logger.log('Form submission trigger created'); // Log statement
  } else {
    Logger.log('Form items not found'); // Log statement
  }
}

function onFormSubmit(e) {
  var form = FormApp.openById('YOUR_FORM_ID'); // Replace with your Form ID
  var sheet = SpreadsheetApp.openById('YOUR_SPREADSHEET_ID').getSheetByName('ChoirGovernors'); // Replace with your Spreadsheet ID

  var data = sheet.getDataRange().getValues();
  var governors = {};
  
  // Create a dictionary of governors and their shepherds
  for (var i = 1; i < data.length; i++) {
    var governor = data[i][0];
    var shepherd = data[i][1];
    if (!governors[governor]) {
      governors[governor] = [];
    }
    governors[governor].push(shepherd);
  }

  var items = form.getItems();
  var shepherdDropdown = null;

  // Identify the dropdown items in the form
  for (var i = 0; i < items.length; i++) {
    var item = items[i];
    if (item.getTitle() === 'Name of Choir Shepherd') {
      shepherdDropdown = item.asListItem();
    }
  }

  if (shepherdDropdown) {
    var responses = e.response.getItemResponses();
    var selectedGovernor = '';
    for (var i = 0; i < responses.length; i++) {
      var response = responses[i];
      if (response.getItem().getTitle() === 'Choir Governor') {
        selectedGovernor = response.getResponse();
      }
    }

    if (selectedGovernor) {
      var shepherds = governors[selectedGovernor];
      shepherdDropdown.setChoiceValues(shepherds);
    }
  } else {
    Logger.log('Shepherd dropdown not found'); // Log statement
  }
}