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

0
votes

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.

2
google-apps-scriptgoogle-sheetsgoogle-forms
Jon, I don't think onOpen runs when you think it does. Read the documentation carefully. It says "This event does not occur when a user opens a form to respond, but rather when an editor opens the form to modify it." It seems like you want the form to "update itself" each time someone opens it to respond? I would recommend creating your own custom form via a web app if that's what you want. – dwmorrin
Another aspect of onOpen as a trigger is that simple triggers cannot access services that require authorization. That's not to say that you couldn't install it as an "installable" trigger (if that were appropriate). However, this is why i believe you are getting the error message. – Tedinoz
You said an error with the openByUrl() call (an online search shows that this was deprecated for security reasons). openbyURL is included in the documentation. Please quote your source that it has been deprecated; these things have a tendency to snowball even thought the statement may not be accurate. – Tedinoz
@dwmorrin I was considering a web app, but need to have a file selector (file upload) for both a Google doc and a file on the system. It didn't look like it be able to do this easily from a web app, so I started here. – Jon
@Tedinoz Sorry, I somewhat misspoke. It seems the function was deprecated for this kind of use: issuetracker.google.com/issues/36762646 – Jon

2 Answers

0
votes

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>");
}
0
votes

Here is my suggestion for this. It works for me; takes values from a range for Rows and Columns from a Google sheet and places them in Google Form Multiple Choice Grid questions:

var form = FormApp.openById('MyForm');
var PtjGridList = form.getItemById(MyFormItemId).asGridItem();
var ss = SpreadsheetApp.openById("MyGoogleSheet");
var PtjNombre = ss.getSheetByName("MyDataSheet");
var RowValues = PtjNombre.getRange(2, 1, PtjNombre.getLastRow() - 1).getValues();
var ValuesRow = [];
for(var i = 0; i < RowValues.length; i++)
if(RowValues[i][0] != "")
ValuesRow[i] = RowValues[i][0];
PtjGridList.setRows(ValuesRow)
var ColumnValues = PtjNombre.getRange(2, 2, PtjNombre.getLastRow() - 1).getValues();
var ValuesColumn = [];
for(var i = 0; i < ColumnValues.length; i++)
if(ColumnValues[i][0] != "")
ValuesColumn[i] = ColumnValues[i][0];
PtjGridList.setColumns(ValuesColumn)