Populate Options on Google Forms From Google Sheets

In this post, I demonstrate how to take options on a Google Sheet and populate those options onto Google Forms Questions using Apps Script.

How to Video:

Video Notes:

  • Further Information on OnOpen(e) Functions Click Here.

Code in Video:

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.asMultipleChoiceItem().setChoiceValues(choiceArray);
        // If using Dropdown Questions use line below instead of line above.
        //item.asListItem().setChoiceValues(choiceArray);
      }
    });     
  });
}

function getQuestionValues() {
  var ss= SpreadsheetApp.openById('1GeFzNR-UoFl9xbla8E1Ditsdr49UnrCmyGRr-6m9wNw');
  var questionSheet = ss.getSheetByName('Questions');
  var returnData = questionSheet.getDataRange().getValues();
  return returnData;
}

Related Posts

Apps Script Google Forms Google Form – Using Apps Script to Populate Google Sheet - In this post, I take a Google Form and populate a Google Sheet by using Google Apps Script. The Apps Script adds versatility in populating a Google Sheet.