I am trying to populate Google Form questions from a Google Sheet workbook using Apps Script. The issue I'm running into is that the Form isn't populating and I'm getting the error TypeError: Cannot read property '0' of undefined specifically on the line googleSheetsQuestions[0]. How can I populate the 2 questions on my Google Form using only column A values from both Sheets?

function openForm(e)
{
  populateQuestions();
}

function populateQuestions() {
  var form = FormApp.getActiveForm();
  var googleSheetsQuestions = getQuestionValues();
  var itemsArray = form.getItems();
  itemsArray.forEach(function(item){
    googleSheetsQuestions[0].forEach(function(header_value, header_index) {
      if(header_value == item.getTitle())
      {
        var choiceArray = [];
        for(j = 1; j < googleSheetsQuestions.length; j  )
        {
          (googleSheetsQuestions[j][header_index] != '') ? choiceArray.push(googleSheetsQuestions[j][header_index]) : null;
        }
        item.asCheckboxItem().setChoiceValues(choiceArray);
      }
    });     
  });
}

function getQuestionValues() {
var ss= SpreadsheetApp.openById('1234567890');
["Sheet1", "Sheet2"].forEach(function (s) {
    var questionSheet = ss.getSheetByName(s);
    var returnData = questionSheet.getDataRange().getValues();
    debugger;
    return returnData;
})
}

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

function populateQuestions() {
  // Retrieve values from Google Spreadsheet.
  var ss = SpreadsheetApp.openById('1234567890');
  var obj = ["Sheet1", "Sheet2"].reduce((o, s) => {
    var sheet = ss.getSheetByName(s);
    var [h, ...values] = sheet.getRange("A1:A"   sheet.getLastRow()).getValues();
    o[h] = values;
    return o;
  }, {});
  
  // Put values to the Google Form.
  var form = FormApp.getActiveForm();
  var itemsArray = form.getItems();
  itemsArray.forEach(e => {
    var item = e.asCheckboxItem();
    var title = item.getTitle();
    if (obj[title]) {
      item.setChoiceValues(obj[title]);
    }
  });
}

• When this script is run, the values are retrieved from 2 sheets of Google Spreadsheet, and the retrieved values are put into the Google Form.

• In your showing script, googleSheetsQuestions of var googleSheetsQuestions = getQuestionValues(); is always undefined. From your goal, in this case, I used an object for searching the title of the question.

Note:

• In this sample script, please confirm whether the title of Google Form is the same with the values of "A1" of each sheet, again.

References:

• reduce()
• forEach()