How can I populate multiple choice questions in a Google Form from data within a Google Sheet

Question

In short: I'm new to Google App Script and as part of a larger project I want to be able to populate the options for a multiple choice item in a Google Form from information stored in a Google Sheet. The sheet is storing the name and contact information for several staff members where I work. I want the form to list the names of the individuals from the sheet and, based on that selection, access the other contact information to do additional work later on.

An example entry for the sheet could be (though the sheet should be able to contain any number of entries (obviously starting count on row 2, to ignore the header)):

LNAME    FNAME    ADDRESS             PHONE
Smith    John    123 Sesame Street    (123) 456-7890
Piper    Peter   12 Shore Lane        (098) 765-4321

As for the form, I've populated it with initial items, the first of which is a multiple choice item where I want a different entry for each name in the Sheet (in this case I would want 2 entries that list "John Smith" and "Peter Piper").

It seems logical to put the code to load these names in the onload() function of the Form, as then the form will update each time it is used. As for the code, I initially tried the following:

function onOpen() {
  // Get a handle for the form itself.
  var form = FormApp.getActiveForm();

  // Who is completing the form?
  var swSheet = SpreadsheetApp.openByUrl("SHEET URL"));
  var sheet = swSheet.getSheetByName("Staff");
  var staff= form.getItems()[0];
  var mcitem = staff.asMultipleChoiceItem();
  mcitem.setChoices([
    mcitem.createChoice(
      mcitem.createChoice(sheet.getRange(2, 2) + " " + sheet.getRange(2, 3)),
    mcitem.createChoice(sheet.getRange(3, 2) + " " + sheet.getRange(3, 3)),
    mcitem.createChoice(sheet.getRange(4, 2) + " " + sheet.getRange(4, 3))
    ]);
}

The problem with this is that I get an error with the openByUrl() call (an online search shows that this was deprecated for security reasons). There is an IMPORTRANGE() function that could pull the correct information for me, but this appears to only be accessible from WITHIN a Sheet, not in Google App Script. I looked around online as well and can't seem to find any other ideas that I could consider.

Note: I realize that this is hard coded for the first 3 entries, and not an unlimited number, but that is the end goal. At present I'm just trying to figure out how to pull information from a Sheet into Google App Script.

Answer 1

You can deploy your script as a Web App and send to the form recipients a Web App URL instead of the form link. The form will be automatically updated every time a user opens the Web App URL.

// will be automatically run every time the user opens the URL of the web deployment
function doGet(){
var form = FormApp.openById("FORM ID");
var swSheet = SpreadsheetApp.openByUrl("SHEET URL"));
var sheet = swSheet.getSheetByName("Staff");
var staff= form.getItems()[0];
var mcitem = staff.asMultipleChoiceItem();
mcitem.setChoices([
    mcitem.createChoice(sheet.getRange(2, 2) + " " + sheet.getRange(2, 3)),
    mcitem.createChoice(sheet.getRange(3, 2) + " " + sheet.getRange(3, 3)),
    mcitem.createChoice(sheet.getRange(4, 2) + " " + sheet.getRange(4, 3))
    ]);
// get the link to the form 
var URL=form.getPublishedUrl();
// redirect to the prefilled form URL dynamically created above 
return HtmlService.createHtmlOutput("<script>window.top.location.href=\"" + URL + "\"</script>");
}