Why not have a column that concatenates ACC and UNQ#, like:-
AAA-1000
AAA-1002
Then when AAA was typed in you would get the two options, then you can use SPLIT to get the UNQ# to do the second vlookup.
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:
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.
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.
Best Answer
This question was brought up a few times on the site having posts with very different, concrete, working approaches.
Within the above answers there is mention of the free add-ons Form Values and formRanger which both automate the procedure without the need for you to write any code.