Menu
I need to find a way to have the multiple choice options in a Google form change based on a set of cells in a Google Spreadsheet that is constantly changing. Any ideas?
736
Yes it is. Use a Form script and update the information from the spreadsheet using a trigger on the FORM OPEN. Here is an example that gets data from two different sheets and insert data in a combo box and into a multiple choice control.
First, select the question. Then click the Add section button on the menu to the right. After you've added the section, click the three dots in the bottom right to add logic to the question by selecting Go to section based on answer. There are now dropdowns to the right of each answer option.
Create or open the form you want to use with Dynamic Fields. Either write the question you want to map data to or decide which one you want to use. Click on the add-on icon (the puzzle piece) at the top of the form, then select Dynamic Fields. Select Create mapping.
It's possible. Write your owns script in the spreadsheet to update your form. If you're not acquainted with writing script you may find someone's in Google script gallery. Let's see the sample of my script. The script is to update two list box.
function updateForm(){ // a way to get the items from the form var form = FormApp.openById("<your form id>"); var agentList = form.getItemById(<your item id>).asListItem(); var hotelList = form.getItemById(<your item id>).asListItem(); var ss = SpreadsheetApp.getActive(); var agents = ss.getSheetByName("Agents"); var hotels = ss.getSheetByName("Hotels"); // get the values in the first column accept header row 1 var agentValues = agents.getRange(2, 1, agents.getMaxRows() - 1).getValues(); var hotelValues = hotels.getRange(2, 1, hotels.getMaxRows() - 1).getValues(); var agentNames = []; var hotelNames = []; // convert 2D to 1D array and ignore empty cells for(var i = 0; i < agentValues.length; i++) { if(agentValues[i][0] != "") { agentNames[i] = agentValues[i][0]; } } for(var i = 0; i < hotelValues.length; i++) { if(hotelValues[i][0] != "") { hotelNames[i] = hotelValues[i][0]; } } // populate the list agentList.setChoiceValues(agentNames); hotelList.setChoiceValues(hotelNames); } You can also link this function to the spreadsheet edit/change events to make the list update automatically.
90
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
