Making work easier with Apps Script: Drop Down menus in Google Forms
Currently, I work in the physical security department at a tech company. Yet, I’ve been able to use JavaScript in G Suite to improve operations at my job, and I really enjoy doing so. Personally, I’m not a big fan of tedious work, so writing scripts that prevent me from doing repetitive and monotonous tasks is a luxury. Let me give you an example.
Over the past year, our operation has nearly tripled in staff size and we have a decent turn over rate. As a result, our roster list is constantly changing. An up to date roster list is important because in G Suite we have a little over a half dozen Google Forms that have the roster list in a drop-down menu.
It is set up this way so the responses spreadsheet contains clean data for pulling statistics.
After creating the forms, staff would reach out to me on at times a weekly or daily basis requesting that I add or remove names from the drop-down menu in several Google Forms. This is a tedious task, and I don’t want to constantly update several forms, nor do I want to grant edit access to these forms because of the chance of user error.
To solve this, I turned to my second favorite tool in my arsenal, Apps Script. What I did was write a script that could take the list of names from our master roster list spreadsheet and, when that list was updated it would update the drop-down menus in all the google forms. Boom, problem solved no more tedious work.
As a result, all my team had to do was update one list and every form with a drop-down list menu of employee names was up to date. Let’s walk through how I did it.
First and foremost, let me tell you what my favorite tool in my arsenal is, the tool that rules them all, Google Search. I opened up a new search window and looked up “Update drop down lists with Google Apps Script” … or something like that. Sure enough, I found several resources that walked me through how to do it.
After I read through a couple of resources and some stack overflow posts. I opened up a fresh apps script window in our master roster list spreadsheet. I did that by going to the menu Tools > Script Editor.
Since no one updated the script for this particular spreadsheet yet, I was greeted with an empty script.
I renamed the script to update lists and then added the function onEdit(e). I’ll explain why later.
Then, I grabbed the unique id of the google form that I wanted to update. I did that by going to the link in the forms edit window and copying the id. The id is located within the URL if you look at the following location.
After that I stored the id it into a variable within a new function called updateLists().
Next, I needed to store the roster in to an array. So, I created one sheet with in my master roster list spreadsheet called DropDownNames. This sheet would use the IMPORTRANGE function to provide access to the first and last names in the master roster list sheet. Then it puts the first and last names together in a third column using the CONCATENATE function. That third column is where I grab the names I’m looking for.
I stored the names from Column C of the DropDownNames Sheet in to an array first using the SpreadsheetApp class’s getActiveSpreadsheet() method which retrieves the spreadsheet shown above. Then, to grab the particular sheet I want to access data from I used the getSheetByName(“DropDownNames”) method. Last, I used the getRange(“C1:C”) function to grab the list of names from column C. I did that by passing in the A1 notation for the third column which is ‘C1:C’, which basically means everything in the third column. Last, I call the getValues() function which will store the values within the cells of the range in to a nested array. Here’s what that looks like:
Before I moved on I had to make sure that my data was clean. Meaning, no preceding or trailing spaces and ensuring none of the names are blank. The way I did that was to run the list of names thru a for loop, use the trim() method to slice off any spaces and make sure they are not empty. Then push those values in to an empty array called cleanEmployeeList. Here’s what that looks like:
Almost done, all I had to do now was open a reference to the form, find the drop down list in the form, and then set the choice values of that list with the array of employee names.
Remember the onEdit(e) function from earlier that we left blank? It’s a special function, it’s what’s called a “trigger” and it runs every time the spreadsheet is edited. All the code we put together to update the list wouldn’t run unless we explicitly told it to. We can do that by either by clicking the play button in the apps script editor or attaching the function to a different trigger. With onEdit(e) every time the list is updated, or really any part of the spreadsheet, what ever code is in the onEdit function will execute.
So the last step is to add the updateLists() method in to the onEdit(e) function.
Putting this together took me about two hours to complete the first time I did it. I ran in to errors and some other roadblocks before I finally got it working. I implemented this code around 8 months ago and it’s still working perfectly. The best part is, the staff that use the forms always have things up to date and can easily update things on their own. Which, is a huge motivator for me in working on projects like this. I love to make people’s lives easier in the work place using Apps Script. So two hours of work that can save potentially hours of spending time cleaning up data or updating forms over months is extremely valuable.
If you made it down here, thanks for reading!
Feel free to leave a question, concern, comment, or criticism, this is my first technical Medium post and I’m looking for feedback to improve.
