Import google spreadsheet data into google forms with app script

Question

I searched the internet and I can't find a response to this nor the documentation for it. I need to dynamically generate Google forms questions with data from a Google spreadsheet using app script, but I don't know how to reference and read a spreadsheet.

Answer 1

In your spreadsheet select Tools > Script Editor and adapt this to your needs:

/**
   After any change in the sheet, update the combobox options in the Form
*/
function onChange(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var range = sheet.getDataRange();
  var values = range.getValues();
  var comboValues = [];  // <-- cheddar will go here

  // in this example we are interested in column 0 and discarding row 1 (the titles)
  for (var i = 1; i <= values.length; i++) {
    var v = values[i] && values[i][0];
    v && comboValues.push(v)
  }

  // Sort the values alphabetically, case-insensitive
  comboValues.sort(
    function(a, b) {
      if (a.toLowerCase() < b.toLowerCase()) return -1;
      if (a.toLowerCase() > b.toLowerCase()) return 1;
      return 0;
    }
  );
  Logger.log(comboValues);

  // Use your form ID here. You can get it from the URL
  var form = FormApp.openById('<my-form-id>');

  /* 
    Uncomment this to display the item IDs
    and pick the one that you want to modify

  var items = form.getItems();
  for (i = 0; i < items.length; i++) {
    Logger.log("ID: " + items[i].getId(), ': ' + items[i].getType());
  }
  */
  form.getItemById(807137578).asListItem().setChoiceValues(comboValues);

};

To debug, select the script in the combobox and click either "play" or "debug". The first time you will have to give it permissions to interact with your spreadsheet and form.

Once you are satisfied with the result, in the editor select Resources > Triggers for the active project and add this method to be triggered with any modification on the spreadsheet (on change, not on edit).

After this, your form options will be changed in real time after any change in your spreadsheet.