Google Sheets drop down list multiple selection

Hi! Welcome to The Data Are Alright, a blog about tips and tricks for changemakers. According to my stats, most of you found your way here through Google. I hope you enjoy the post below. If you’d like to stick around, here are a couple of great options to read. You can even SUBSCRIBE, which would absolutely make my day! (I post approx 3x / month)

• How social justice makes me a better database admin
• Why changemakers need good data
• 4 part series on Mail Merge
• 3 part series on digital self-care
• Clipboards and Megaphones: a new perspective on Dr. King’s legacy

Friends, a LOT of keys have been typed about the perils of using the field type “multi-select picklist” in Salesforce. But that’s not the point of this blog post, ok? Let’s say that using multi-select picklists are a type of harm reduction on the way to clean, reportable data. And that destination is really more of an iterative process anyway!

In this blog post, I will introduce a Google Script that ANYONE can use to turn a “single-select” drop down menu (choose one) option into a “multi-select” (choose all that apply) option. I made improvements on the code presented here so that the results are aligned with the importing requirements for standard Salesforce data loaders.

What is a multi-select picklist?

Many spreadsheet and database tools/platforms allow you to configure restrictions for what types of data you can include in each cell or field. For example, when you are typing in an address, you might be accustomed to a dropdown menu that lists abbreviations for every state (apologies for the US-centric example). As a result, we can make sure that we don’t end up with some people typing PA and other people typing Pennsylvania (or a typo version of Pennsylvania), rendering our data somewhat useless (unless the use is, how many different ways can someone spell Pennsylvania?) So. “Single select” “picklists” are very useful!

• Microsoft Excel and Google Sheets call this feature “data validation”

Then there are picklists that go even farther with the selection opportunities. Why just pick one topping on your ice cream sundae when you want to choose ALL of your favorites? That’s where multi-select picklists come into play! Sure, they have some limitations and sure there are workarounds (like this and this), but IMO they are here to stay.

One of the main issues with MSPLs is data manipulation, aka filtering, grouping, updating, visualizing, etc. I agree, it’s a big problem. Especially for moving data from non-Salesforce systems into Salesforce. Migrating data to custom objects (let alone training users on this, albeit a bit easier now with Screen Flows, but that’s a tangent for another time) is so so much more difficult than just moving the clean data into an already-existing MSPL, for crying out loud!

Dealing with data in Spreadsheets

I like to say that spreadsheets are the building blocks of databases – Salesforce and basically every other one out there. Run a report? Spreadsheet. Need to upload data? Spreadsheet. Etc etc etc. And when I am helping someone move data into Salesforce for the very first time, well, there’s a lot of spreadsheet data cleaning that has to happen.

In one recent example, I combined contact data from three different sources (email newsletter, Google contact list, and donation payment processor). One of those sources had an MSPL already and we wanted to retain the options that were pre-selected while allowing relationship experts to UPDATE the data and add/remove values before moving the whole kit and kaboodle into Salesforce. I wanted to help them do that in Google Sheets, but the standard Google Sheets functionality only lets you have drop-down menus with one result.

I decided that I had 4 options ahead of me:

1. Abandon multi-select picklist and move to a different data model
   
2. Abandon Google Sheets and move to a platform than can handle MSPL (like Airtable)
   
3. Stick with Google Sheets and make one column for each MSPL “option” and then use formulas to mush them back together into one super-list (this would have added 15 columns to a workbook that already had 30 columns and about 600 contacts)
   
4. Use code to enhance Google Sheets so that it can handle MSPLs (thanks, Emily Hicks-Rotella)

If you’ve made it this far, you’re probably interested in #4, which is exactly what I did!

Introducing Google Scripts

Most of the common explanations for Google Scripts made my eyes glaze over. So let me explain the best way I know how… with examples! Google Scripting is a feature that lets you use code to create formulas that Google Sheets doesn’t automatically have. I think it can also do other things, but I haven’t needed those things (yet).

Luckily for us, Google Sheets already has a LOT of formulas (like Add, Subtract, Average, VLOOKUP, etc). One time I needed a formula that counts the number of tabs in a Google Sheets workbook. There’s no out-of-the-box formula for this, but it turns out that you can do it pretty easily with a Script! That kind of tinkering is also what made me curious about adding MSPLs to my Google Sheet for the purpose of staging data before moving it into Salesforce.

Code

My solution borrows extensively from the good folks at Spreadsheet Point.

When you use this code, be sure to update the columns and the name of the Active Sheet (line 6 below)

Steps

1. Create your spreadsheet in Google Sheets with appropriate columns that map to your fields of interest in Salesforce
   
2. Use Data Validation feature in Google Sheets to restrict data entry to values (directions included here). For available values, copy the list of picklist or global picklist values from Salesforce. You can either “hard code” these values in the Data Validation dialogue box or you can reference them in another tab. I prefer to put them in another tab (labeled Drop Down Options) so that I can see them all at once.
   
3. Follow directions here to paste and publish code in your Sheet’s script editor. (Code is pasted below). Make sure to update column numbers and tab name!
   
4. You will now have the ability to override any dropdown menu with more than one selection!
   
5. When you are done cleaning your data, download your Active Sheet (working tab) as a CSV. Follow standard procedures to import (insert or update) data to Salesforce!
   
6. Celebrate! You’re a badass!

function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if((activeCell.getColumn()==15||activeCell.getColumn()==16||activeCell.getColumn()==28||activeCell.getColumn()==34) &&ss.getActiveSheet().getName()=="Working Tab") {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue("");
}
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
}
else {
if(oldValue.indexOf(newValue) <0) {
activeCell.setValue(oldValue+'; '+newValue);
}
else {
activeCell.setValue(oldValue);
}
}
}
}
}

Limitations

1. This solution doesn’t let you select more than one option AT A TIME. You need to add one, then add another, etc. Each one that you add is appended with a semicolon delimiter.
   
2. The solution doesn’t handle “removing” a value… it only adds. To remove one, you need to clear out the cell and add additional values back one at a time.
   
3. The multi-select result violates the data validation rules, which means that Google Sheets shows a little red triangle in the top corner of each cell.

Back to YOU!

Thanks for getting this far in a technical blog post! If you are a Salesforce admin dealing with multi-select picklists, I’d love to hear from you. How do you handle exporting and importing data? Do you have access to Google Sheets at your org? Do you want to (or have you already) tried this solution? Please don’t be shy about writing back. Your comments *absolutely* make my day!