Google-sheets – way to link a “Choose from a list” style question in a Google Form to a list of data (e.g. in a spreadsheet)

google sheetsgoogle-apps-scriptgoogle-forms

Is there a way to link a "Choose from a list" style question in a form to a list of data (e.g. in a spreadsheet)?

This would mean that as new options were added to the list in the spreadsheet (or wherever else it might be possible to host the list) they would automatically appear as an option to select in the form in the future.

In my case it would mean that the respondents filling in the form would be able to select which volunteers were present at that particular volunteering session from the comprehensive list of volunteers (rather than typing in each name individually), but since volunteers join sporadically, it would be hugely helpful if it were possible to add these to the options on the list automatically rather than adding a new option manually each time.

I have no idea if this is possible but I would really appreciate it if anyone could suggest a way of doing it.

Best Answer

Tom Horwood's answer works great, but only after correcting an important error in his code. All references to LIST_DATA elements should be made using the index j (not i). I don't have sufficient karma to comment on Tom's post so here's the fully monty:

Below is the corrected code. I've also changed the name of the LIST_DATA elements so they are a little more descriptive. It also shows how Tom had thought ahead (kudos!) and provided the facility to update more than one form element with the contents of more than one spreadsheet list. 

var FORMID = "PUT_YOUR_FORM_ID_HERE";
var LIST_DATA = [{formFieldTitle:"Job Number", worksheetName:"JobNumbers"},
             {formFieldTitle:"Task 1 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 2 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 3 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 4 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 5 Category", worksheetName:"TaskCategories"},
             {formFieldTitle:"Task 6 Category", worksheetName:"TaskCategories"},
            ];

function onOpen(e){
  var menuEntries = [];
  menuEntries.push({name: "Update Lists", functionName: "updateLists"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
};

function updateLists() {
  var form = FormApp.openById(FORMID);
  var items = form.getItems();
  for (var i = 0; i < items.length; i += 1){
    for (var j = 0; j < LIST_DATA.length; j += 1) {
      var item = items[i]
      if (item.getTitle() === LIST_DATA[j].formFieldTitle){
        updateListChoices(item.asListItem(), LIST_DATA[j].worksheetName);
        break;
      }
    }
  }
}

function updateListChoices(item, sheetName){
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              .getSheetByName(sheetName)
              .getDataRange()
              .getValues());
  var choices = [];
  for (var i = 0; i < data.length; i+=1){
    choices.push(item.createChoice(data[i][0]));
  }
  item.setChoices(choices);
}

To summarise how to get this to work (for those, like me, who are using Google Apps Script for the first time). I've referred to sheet names and form field names in the code snippet above to hopefully make it more understandable:

  1. In the spreadsheet which is associated with your form:

    • Create a new worksheet. This will contain the list of items you want added to a field. Give the worksheet a suitable name (for example, TaskCategories). Put your list of items there in the first column of that worksheet. Delete all extra columns and rows on that sheet (this may or may not be necessary - haven't tested)
    • From the Tools menu, choose "Script Editor". Copy and paste the code snippet above into the script editor. You will need to change the following parts of the script:
      • The value of variable FORMID will need to be changed to the ID of your form. The ID is the long code (between forward slashes) in the URL bar in your browser for the desired form.
      • The LIST_DATA variable will need to be modified to suit your form and your needs. You'll note that each item in LIST_DATA is a tuple of formFieldTitle and worksheetName. The former is the name of the form field (that must be a "choose from list" type of field) - in the form editor it's called Question Title. The latter is the name of the worksheet that you created earlier that contains the list of items you want to populate the field with. Add as many of these tuples to the list as you need.
      • Now save the script (Click on the save icon)
      • Test the script by choosing 'Run' from the script editor menu and choosing "updateLists". The first time you do this it will ask for permission. If the script succeeds then you can observe that your form now has the specified fields populated, otherwise you'll note an error message on screen.

  2. If you return to the spreadsheet, you should see a new menu item entitled List Updater. It has one item Update Lists which you need to run every time you change any of your list(s) worksheet(s) - it will update the form accordingly.

I also note, for the benefit of readers, that FormRanger does not work with the new Google Spreadsheets. The FormRanger developers say as much on their website. Perhaps/hopefully that will change in future, but as at time of this posting the above code works for me and I'm using it in a deployed form for a client.

Related Topic