Create google form - display drop down - from an excel sheet.

[Manish Manish]

Hi,
Disclaimer: I'm not a coder. Was using chatgpt for solving the below problem.


Want to create a google form which displays drop down taken from an excel sheet.

The problem is:

Create Google Form with name: 'Idea1form'
Description: Testing drop down of tea names

Question 1: Do you like tea?
Multiple choice options

"Yes"
"No"


If Question 1 is "Yes",
Then

Question 2: Which tea do you like?
A drop down list should be the option.

The drop down list should select values from Column A1:A10 of a Google spreadsheet - lets say GS1.xlsx

If Question 1 is "No"
Then go to submit form option.


As per ChatGpt:

function onFormSubmit(e) {
  var formId = 'I've written the form ID here';
  var form = FormApp.openById(formId);
  var response = e.response;
  var itemResponses = response.getItemResponses();
 
  var isVoterResponse = itemResponses[0].getResponse();
 
  if (isVoterResponse === 'Yes') {
    var acNameQuestion = form.addListItem();
    acNameQuestion.setTitle('Which tea do you like?');
    acNameQuestion.setChoiceValues(getACNameChoices());
   
    var formUrl = form.getPublishedUrl();
    Logger.log('Form URL: ' + formUrl);
  }
}

function getACNameChoices() {
  var spreadsheetId = 'the ID of the spreadsheet with tea choices';
  var sheetName = 'Sheet1';
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var acNames = sheet.getRange('A1:A118').getValues();
 
  var choices = [];
  for (var i = 0; i < acNames.length; i++) {
    var acName = acNames[i][0];
    choices.push(acName);
  }
 
  return choices;
}


I'm getting errors:

TypeError: Cannot read properties of undefined (reading 'response')
onFormSubmit @ Code.gs:4

HELP