I have been trying to get this to work for a couple of days now and I give up.
I want to create a Google form with a drop down list populated form a spreadsheet. I don't what anyone to choose the same as any one else. (like in a potluck situation)
example:
- I am giving away :
- a comb
- a brush
- a bowl full of mush
I tell Thomas, Richard and Henry that they can each have one and send them a link to a Google form I created. Tom is quick and opens the form 1st. He enters his name and chooses a comb out of a three item drop down list. Dick opens the form link and in the same drop down question he chooses out of the two remaining items. He chooses the brush. Harry is a bit of a slow poke, so when he gets home he opens my link, but alas, he can only have a bowl full of mush.
How can I get this done?
Based on my research so far I will be needing to use the if function on the responses spread sheet to see if there has been a take for an item (see if the cell is vacant) and maybe VLOOKUP, but I can't get a clear picture of how to make it all work together.
Thank you,
Good night
EDIT:
Based on gssi's answer, I wanted to post the code and describe the way I did it.
function updateListChoices(item){
var inventory = (SpreadsheetApp.openById(theIdOfTheResponceSpreadsheet)
.getSheetByName("inventory")
.getDataRange()
.getValues());
var selected = (SpreadsheetApp.openById("0Al-3LXunCqgodHB5RGNpR0RyQ0pERmVnek1JeUJKS0E")
.getSheetByName("responses")
.getDataRange()
.getValues());
var choices = [];
var selectedReal = [];
for (var i = 0; i< selected.length; i+=1){
selectedReal.push(selected[i][2]) }
for (var i = 1; i< inventory.length; i+=1){
if(selectedReal.indexOf(inventory[i][0])=== -1){
choices.push(item.createChoice(inventory[i][0]));}
}
item.setChoices(choices);
}
var LIST_DATA = [{title:"the title of the question", sheet:"inventory"}]
function updateLists() {
var form = FormApp.getActiveForm();
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.getIndex() === 1){
updateListChoices(item.asListItem(), "inventory");
break;
}
}
}
}
In the building of the form, click the tools menu, then click script editor. Copy the code from here (with changes to fit your needs) to the script editor and hit save. Click the Resources menu and hit the project triggers (the 1st option). Click Add trigger. Choose updateLists from form do this once with when sending and once when opening (you should end up with 2 lines.)
It isn't very elegant, but this is what I am capable of. Good Luck.
I tried to accomplish exactly the same (list with products to select from), but I couldn't make it work with your final code example. Here's mine, with detailed instructions. Just for anybody who is landing on this page and is looking for a working code.
(Menu names might differ from yours, because I'm using a non-English Google Forms, and I'm just guessing the translations here.)
1) Create a new form, and create a new radio button based question (multiple choice) (In this example, I use the question name: "Select a product"). Don't add any options to it. Save it.
2) Open the spreadsheet where the responses are going to be stored, and add a new sheet in it (name: "inventory")
3) Fix the first row (A) of the inventory sheet, and put in A1: "Select a product"
4) Put in column A all the products you want to appear in the form
5) Open the form editor again, and go to tools > script editor
6) Paste this code in the editor, put in your form and spreadsheet ID's (3x) and save it.
var LIST_DATA = [{title:"Select a product", sheet:"inventory"}];
function updateLists() {
//var form = FormApp.getActiveForm();
var form = FormApp.openById("paste_ID_of_your_FORM_here");
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[0].title){
updateListChoices(item.asMultipleChoiceItem(), LIST_DATA[0].sheet);
break;
}
}
}
}
function updateListChoices(item, sheetName){
var inventory = (SpreadsheetApp.openById("paste_ID_of_your_RESPONSE_SHEET_here")
.getSheetByName("inventory")
.getDataRange()
.getValues());
var selected = (SpreadsheetApp.openById("paste_ID_of_your_RESPONSE_SHEET_here")
.getSheetByName("responses")
.getDataRange()
.getValues());
var choices = [];
var selectedReal = [];
for (var i = 0; i< selected.length; i+=1){
selectedReal.push(selected[i][1])
}
for (var i = 1; i< inventory.length; i+=1){
if(selectedReal.indexOf(inventory[i][0])=== -1){
choices.push(item.createChoice(inventory[i][0]));}
}
if (choices.length < 1) {
var form = FormApp.getActiveForm();
form.setAcceptingResponses(false);
} else {
item.setChoices(choices);
}
}
7) With the code editor open, go to Resources > Create triggers and create these two triggers. They need to appear in this order:
- updateLists - from form - sending
- updateLists - from form - opening
Now you're good to go. If you open the form editor, the products added in the inventory sheet will appear as options.
Every time a product is chosen, it will disappear from the form. To reset all the chosen products, go to the form editor, and choose Responses > Remove all responses. You might need to remove all responses from the responses sheet manually as well (Don't know why, but that happened to me). After that, you need to manually run the updateLists script in the code editor.
Here's a 'How-To' that describes how to construct the spreadsheet.
Column A : named 'Inventory', contains the names of the items initially available.
Column B : named 'Indices', in cell B1 contains the formula =if(isnumber(match(Inventory,Selected,0)),"",if(row(B1)=1,1,max(offset(Indices,0,0,row(B1)-1,1))+1)). Copy the formula in B1 into all cells below it in column B.
Column C : named 'Selected', contains the names of items currently selected starting in row 1 and continuing down contiguously.
Column D : named 'Available', cell D1 contains the formula =if(isnumber(match(row(D1),Indices,0)),index(Inventory,match(row(D1),Indices,0),1),"") which is then copied into all cells below it in column D.
The Available column will always contain a contiguous list of the 'as-yet-unselected' inventory items.
