Image for post
Image for post

Making work easier with Apps Script: Drop Down menus in Google Forms

Christopher Holmes
Jul 12, 2020 · 6 min read

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.

Image for post
Image for post

Since no one updated the script for this particular spreadsheet yet, I was greeted with an empty script.

Image for post
Image for post

I renamed the script to update lists and then added the function onEdit(e). I’ll explain why later.

Image for post
Image for post

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.

https://docs.google.com/forms/d/unique-id-is-here/edit

After that I stored the id it into a variable within a new function called updateLists().

Image for post
Image for post

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.

Image for post
Image for post
Image for post
Image for post

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:

Image for post
Image for post

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:

Image for post
Image for post

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.

Image for post
Image for post

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.

Image for post
Image for post

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.

Christopher Holmes

Open to Work

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store