Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

Would really appreciate some help from any experts in Google 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. Please let me know if there is anything that is unclear about what I am attempting to do.

Thank you.

share|improve this question
    
To bad, you're not around to see all the answer given.... –  Jacob Jan Tuinstra Oct 10 '13 at 19:53
add comment

4 Answers

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.

share|improve this answer
    
The GUI Builder is deprecated, so I wouldn't be using that anymore. –  Jacob Jan Tuinstra Oct 10 '13 at 20:54
add comment

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.

share|improve this answer
    
That's a lot of text. –  Jacob Jan Tuinstra Oct 10 '13 at 19:55
add comment

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[i].title){
        updateListChoices(item.asListItem(), LIST_DATA[i].sheet);
        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);
}
share|improve this answer
    
The URL's link to the same doc? Should this be the case? Why don't you visit the chat, so that we can have a chat..... –  Jacob Jan Tuinstra Oct 10 '13 at 19:49
    
The form link is now fixed –  Tom Horwood Oct 12 '13 at 3:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.