Remove Choices
A client needed a way with a google form, to offer a few dates to her clients, but have the date choices disappear from the form as the choices were taken. I have built a system that does this using Form Ranger to populate the date choices from a list on the form response sheet. The problem I encountered is that when you submit form data as in the example below, the data that is on the spreadsheet adds an apostrophe in front of the date, which breaks the query formula I use to pull the dates that have been chosen.
In the images below one can see how the Form is setup, how we add a sheet that builds the Possible, Taken, and Available dates.
Form Ranger then uses the column Available to fill out the dates column on the form. This way, as the dates are chosen, they disappear from the form and the next client does not see those dates as an option.
We built a form and wanted to remove choices (in this case dates) from the form as the dates were chosen. We are using Form Ranger to remove the choices. There is an add-on called Choice Eliminator, but I have found it to be unreliable. I have built a system that does work, using some formulas and extra sheets on the form response spreadsheet
On the Form Response sheet, we add a new sheet with these three columns. The first Column, named Possible is where you manually add the dates that you are offering. Then the Taken column has the formula number 1 in cell B1. This queries the form response sheet (I always rename 'Form Responses 1' to 'data' to make it easier to write formulas) and brings in the dates that have been requested on the form submissions. The Available column uses the formula number 2 in cell C1 to basically subtract the 'Taken' dates from the 'Possible' dates, leaving just the 'Available' dates, which Form Ranger auto populates back to the form.
- ={"TAKEN"; ARRAYFORMULA(DATEVALUE(QUERY(data!D2:D, "SELECT *")))} is the formula in cell B1
- ={"AVAILABLE"; IFERROR(FILTER(A2:A, ISNA(MATCH(A2:A, B2:B, 0))),"No Dates Available")} is the formula in cell C1