Add Dropdown When Google Form Submitted to Google Sheets
Learn how to automatically add dropdown menus to Google Sheets when a Google Form is submitted, allowing for better categorization of responses.
You're trying to categorize form submissions, and whenever we get a new Google Form submission, we want to be adding a dropdown . This is a sheet. We can add dropdowns just by copying and pasting them, right? But when we do a Google Form , issues happen. So I'm going to show you how to solve that in this video. First, we're going to go to tools, create a new form, and create a new form. In our use case here, we're just going to say we're Taking issues from customer issues. And we just want to ask what is the issue or the complaint. And we just need a short answer. So, this is a pretty simple version of a form you may have. We'll publish the form and we can go and give a few responses. Plumbing broke. Submit. If we go back to our sheet, we have a form responses tab
and we have these issues here. And in the C column here, we want to add a drop down to assign each of these issues to some department. Let's create a new sheet called departments and give some departments here. So we'll say housekeeping, engineering, concierge. Let's go over and code extensions app script . Once we're here, we're going to do two things. One, we're going to create a function , and then we're going to create the trigger that triggers that function when a form is submitted. Our function will be called insertDropdown. We need an event. This could be the word event, but we're going to use e as a shorthand version of event, so we don't have to type event every single time. We're going to want to know the row. Variable row equals e. range .getRow. When we create the trigger for this, this event will have some interesting
information such as the row that it's going to, the sheet that it's going to, and we can also get that. Variable sheet equals e. range .getSheet. Now we need to know what is the drop down range that we actually want to use. to put in there. So we'll call this variable drop down range equals and we're going to get spreadsheet app dot get active spreadsheet .getSheetByName and the name is going to be departments that's going to be this form here departments and the a column dot get range a colon a So that's the range . And now we're going to have to create a rule. Variable rule equals spreadsheetApp .newDataValidation newDataValidation.requireValueInRange That's
our option. And our range is going to be drop down range . We need to add a true here, comma true. And then after this build. So this is the whole rule. It's just going to put the dropdown in there as data validation . And let's put it all together. We need to go to the sheet that we're on that we're putting these. form submissions, getting the range , get the row, and in our case, let's go back to our form responses. It's going to go in the C column or column three. So let's put a number three here, dot set data validation , and we'll put in the rule. So this rule with the drop down range is going to be added as data validation. So we've created our function.
Now we need to create the trigger . On the left side, click triggers. Once here, go to the bottom right and click add trigger . Next, we'll choose which function to run. We'll choose insert dropdown . And select event type, we're going to select on form submit . Click save. We will have to validate or authorize this, so click allow. Once we have our trigger here, you'll see the function in our triggers menu. And if there's any errors, you'll see an error right here on the right. Let's create some more issues, new complaint, click submit. Let's go back to our sheet. We see new complaint here, and we have a dropdown menu that we can select the department. Pretty cool. Let's add another one, one more issue, click submit, go back to our sheet.
And here we also have another dropdown menu already there waiting for us to assign this. So our code in our editor looks like this. Insert dropdown E, get the row, get the sheet, create a dropdown , put that dropdown into a rule, and then go to the sheet that we're putting our new submissions on, get the range , make sure we have the correct column, and set the data validation rule here. That's it. If you're looking to get more out of your Google Sheets and learn more automation, subscribe here on YouTube to BetterSheets.