Google Spreadsheet Form, populate form options based on a spreadsheet

Question

I need to find a way to have the multiple choice options in a Google form change based on a set of cells in a Google Spreadsheet that is constantly changing. Any ideas?

Histoplasmosis

Answer 1

The functionality you are requesting doesn't currently exist. A feature request around linking Google's forms to spreadsheets would be your best bet to keep the two in sync.

There is always the option to create a form using URL parameters as outlined here: https://docs.google.com/support/bin/answer.py?hl=en&answer=160000

Affair

Answer 2

It's possible. Write your owns script in the spreadsheet to update your form. If you're not acquainted with writing script you may find someone's in Google script gallery. Let's see the sample of my script. The script is to update two list box.

function updateForm(){
  // a way to get the items from the form
  var form = FormApp.openById("<your form id>");
  var agentList = form.getItemById(<your item id>).asListItem();
  var hotelList = form.getItemById(<your item id>).asListItem();


  var ss = SpreadsheetApp.getActive();
  var agents = ss.getSheetByName("Agents");
  var hotels = ss.getSheetByName("Hotels");

  // get the values in the first column accept header row 1
  var agentValues = agents.getRange(2, 1, agents.getMaxRows() - 1).getValues();
  var hotelValues = hotels.getRange(2, 1, hotels.getMaxRows() - 1).getValues();

  var agentNames = [];
  var hotelNames = [];

  // convert 2D to 1D array and ignore empty cells
  for(var i = 0; i < agentValues.length; i++) {  
    if(agentValues[i][0] != "") {
      agentNames[i] = agentValues[i][0];
    }
  }

  for(var i = 0; i < hotelValues.length; i++) {
    if(hotelValues[i][0] != "") {
      hotelNames[i] = hotelValues[i][0];
    }
  }

  // populate the list
  agentList.setChoiceValues(agentNames);
  hotelList.setChoiceValues(hotelNames);
}

You can also link this function to the spreadsheet edit/change events to make the list update automatically.

Grayce

So I'm not really sure to understand where I should be copying this script to, in order to populate my Google Form. And how about the sheet ID? Thanks. – Eam

@Grayce how do I get the item id from a form? instead of <your item id> – Pronate

Doing some research I think @Grayce was reffering to the app-scripts developers.google.com/apps-script/quickstart/docs – Tryout

Answer 3

Try the below code. You can invoke it by a button or menu item.

//
//
const populateGoogleForms = () => {
  const GOOGLE_SHEET_NAME = "Setup";
  var GOOGLE_FORM_ID = "xxx"; //
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [header, ...data] = ss
    .getSheetByName(GOOGLE_SHEET_NAME)
    .getDataRange()
    .getDisplayValues();

  var choices = {};
  header.forEach((title, i) => {
    choices[title] = data.map((d) => d[i]).filter((e) => e);
  });``
  FormApp.openById(GOOGLE_FORM_ID)
    .getItems()
    .map((item) => ({
      item,
      values: choices[item.getTitle()],
    }))
    .filter(({ values }) => values)
    .forEach(({ item, values }) => {
      switch (item.getType()) {
        case FormApp.ItemType.CHECKBOX:
          item.asCheckboxItem().setChoiceValues(values);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues(values);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues(values);
          break;
        default:
        // ignore item
      }
    });
};

Dyeline