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

Question

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.

Answer 1

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.

Answer 2

You can do this with a normal form and a script, using Form.getItems(), find the item in loop, then Item.asListItem().setChoices(...)

Here is some sample code, which I've used in this example sheet and form

var FORMID = "YOUR FORM ID HERE";
var LIST_DATA = [{title:"Who's your tutor", sheet:"TutorName"}]

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].title){
        updateListChoices(item.asListItem(), LIST_DATA[j].sheet);
        break;
      }
    }
  }
}

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

Answer 3

There is a Google Forms add-on called Form Values which at least offers a semi-automatic way: whenever you change your answers in the spreadsheet you need to manually re-populate the choices in your answer, but at least it's rather straightforward to do.

Answer 4

You can't do this with the regular Google Forms.

You can, however, create your own GUI by using Google Apps Script, and specifically the UIService. Be aware, though, that you will have to re-create the whole GUI - you cannot use parts from your existing form.

Alternatively, you can use the GUI Builder. It is a WYSIWYG editor for Google Apps Script GUIs.

Which ever you choose, you should be prepared to do some programming to achieve what you want.

Answer 5

There is a script for that, called FormRanger: https://docs.google.com/document/d/1YvgrxeZJNb2CBpSra1j59Mh1bVpk4vBQYLOoyVjby7A/edit?hl=en&forcehl=1

Answer 6

There is a way to automate the list updating by setting a Trigger[ 1 ] [ 2 ] for Update Lists.

The path is Script Editor -> Resources -> Current project's triggers

Then, add a new trigger. For the given examples, you can set updatesList to run from spreadsheet and On form submission. This way, the function onOpen won't be needed.

Answer 7

Yes it can be done, spent a good portion of the day finding the answer and then developing it for my purposes, but it can be done.

My need was to have a volunteer sign-up form for people to check multiple boxes from a list of areas that they would like to volunteer. Therefore one question with multiple boxes, the problem is that the resulting Google spreadsheet just listed everything they checked in one cell. There were 21 volunteer options, so that clearly would not be a useful format for that information. I wanted to do that which I understood you to say you wanted, I wanted to have a column with each of the 21 options in a column of its own. Then next to their Timestamp (form generated), name, email, and phone there are 21 columns with "yes" or left blank. The column heading is each option they could choose. Some might wonder why not just have 21 questions with "yes" or "no". That would have given the end result desired but the form to be completed would have been incredibly bulky, they were volunteering, I didn't want it to be or to look burdensome.

First note that when someone completes a google form the resultant spreadsheet inserts a row rather than adding the data to the next empty row. This means that any formulas that were on the row just got bumped down and the new data from the formula has no formula next two it to work with the input data. To get beyond this I added a sheet to the spreadsheet (workbook). I utilized input from brettathds at http://productforums.google.com/forum/#!topic/docs/dlW6U6cHuKw I therefore inserted the following formula into Cell A1 of this 2nd sheet: =ARRAYFORMULA(Sheet1!A1:A). I did similar for each column through column E. Column E has the question with the 21 checkboxes.

Still on the second sheet, Row 1 of Columns F-Y had headers identical to the text on each checkbox option from the checklist of areas they are willing to volunteer. Then Column Z was the next and last question on the form, it was a short text question that was just titled "Additional Comments". For that column I utilized the =ARRAYFORMULA(Sheet1!F1:F) I did this as a separate question rather than utilizing the "other"option at the end of the checkbox questions as there did not appear to be a good way to pull that data out into the spreadsheet.

Now to the part which answers how do you get the info from the cell in column E (which could have up to 21 items listed) to separate columns with "yes" or just blank. The formula in F2 on the second sheet is =iferror(if(search(F$1,$E2)>0,"Yes",)) This formula is looking to see if the exact text in the column header (F1) is found in E2, if it is then it returns a "Yes", if not it leaves it blank.

This formula referenced in F2 is written (utilizing "$", via the F4 key) so that F2 can be copy/pasted to each cell in row 2 from G-Y (Google Spreadsheet does not support copying of formulas by drag/copy as you can in Excel, but it can be done by selecting F2, then CTRL + C, then select G2:Y2, then CTRL + V). I then utilizing the same methodology copy pasted F2:Y2 to F3:Y100.

I then hid Column E on the second sheet so that the resulting spreadsheet was more compact (avoiding all the wrap from the long answers).

Now I am ready to receive 99 volunteers via the quick and easy form they complete. Utilizing the filter options at the top of the columns also helped to see who was available for what areas.

One final frustration that I did not get resolved. I completed some bogus forms to test my formulas and design, I then deleted those rows from the linked spreadsheet. I also had a few people who completed duplicate forms, I deleted their rows as well. Noteworthy in the product forums, I am not the only one frustrated to learn that somehow Google still has the test/bogus/duplicate data wired into the "Summary of Responses" (found under "Form" tab of spreadsheet). Therefore the summary of responses is useless for accuracy if there have been rows deleted to remove data from test/bogus/duplicate forms.

But it is accomplished and I am saving these notes for the next time I need similar, I am confident that it could easily be done in less than 30 minutes next time.

Hope this helps, let me know if any questions.