What do we do when we’re under attack?
SIT DOWN AND BUILD SPREADSHEETS!
Well, at least that’s what *I* do when seasoned organizers in my community need help coming up with a place to store contact data in key, contested geographies.
This is a blog post not only about technical solutions to sticky issues, but also about how to think about which problems are worth solving and what tools are available to solve them. So, you might not learn every step I took (though I’m happy to show you!). Instead, I hope we can explore innovative approaches to solving problems and the “why” behind the decisions we make, all while expanding our collective toolbox.
understanding the problem
My friend came to me with a thorny spreadsheet on her hands. The spreadsheet overall represented one state, and each tab within the spreadsheet represented one county. Within the tabs, my friend was keeping track of contact info for community leaders ready to spring into action if there was election funny business. There were a few existential problems with the spreadsheet that we wanted to sort out:
- How to handle contacts that are active in more than one county, while still being able to sort/filter by county
- How to have a drop-down menu in a cell which allowed you to pick more than one option (we often call these multi-select drop down, or “check all that apply”)
- How to (quickly) duplicate this spreadsheet style for all of the states in her purview
Next, let me explain the constraints. There are always constraints!
- We wanted to do this quickly (same day if possible – we have an election to protect!)
- We wanted to stay on Google Sheets*
- Budget is $0
*this is important because some of these goals could be met more easily in a different platform (Airtable comes to mind), but that would introduce other dilemmas
boundaries – knowing our limits
I have many ideas about how to solve #1 from our wishlist, however, I couldn’t do it within our constraints. So, we decided to stick with having a separate list for each county, which means that some contacts will be inevitably duplicated. That being said, we did create a tab for “statewide contacts” which fortunately prevented us from having certain contacts repeated on every single tab. What are your best ideas for this request? Leave me a comment!
quick fix – using (new) standard features
Solving #2 was a flex and a breeze. Let me explain how:
I previously used custom code (adapted from the internet) to facilitate multi-select drop downs in Google Sheets since it was famously not available. However, this feature was introduced in Aug 2024 (!!!!) and since then, the spreadsheet blogosphere has gone wiiiiiiiiild. My Spreadsheet Soulmate and BFF Emily had told me about this release, but I hadn’t needed to use it until this request came in.
I was absolutely delighted by how easy it was to turn “Data Validation” in Google Sheets from single-select to multi-select. So, I won’t bore you with a blog post about how to do it 😉
But I do want to give you a hot tip! 🔥
Instead of adding your “dropdown menu options” in the Data Validation screen:
I highly recommend that you select “Dropdown from a Range” under the Criteria header. I usually create a tab called “Data Validation” and put all of my dropdown menu options here. When I select the Range, I highlight the entire column that contains my dropdown menu options. Then, if I need to add/remove/change menu options later, I can do it in one place. This was particularly effective for this spreadsheet project because we wanted to reference the same menu options on each tab! However, I would still do this for just one data validation rule. It’s become part of my comfort zone and I think it is much easier to manage this way over time.
Using the built-in feature in Google Sheets and my “hot tip,” we were able to create the multi-select drop down menus of our dreams!
customizing for bigger impact
Next, I tackled our desire to replicate the spreadsheet for 8 different states. To accomplish this, I decided my best option was to use Zapier. I already had a spreadsheet with the states and counties that I needed. “All” I had to do was create 8 different workbooks (series of tabs) where each workbook was customized by state with the right number of counties (tabs) and where each tab had the custom data validation rule with multiselect properties that I built in step #2 above.
I created a template spreadsheet in Google Drive with three tabs:
- Table of Contents (this contains of list of all counties in the state)
- Template (this has all of the formatting for storing contact data, all County tabs should be a clone of this)
- Data Validation (this is where I store my drop down menu options)
Then, I created a “Zap” in Zapier to connect to my template spreadsheet, and clone the Template tab the correct number of times based on the number of rows in the Table of Contents tab. I also wanted to re-name the tabs based on the County but I couldn’t make Zapier cooperate with this task so I eventually did them all manually.
Why did I decide to do this with help from Zapier?
It’s time for another hot tip 🔥
I have a manual/automate threshhold that I’ve honed and refined with years of practice.
If a repetitive task requires 30 or more iterations, I’m likely to automate it. If it requires 29 or fewer, I’m likely to do it manually.
I use this rule with data entry, mail merge, emails, and much more. It has always served me well. Manual data entry takes time, but so does automating appropriately. If it seems like manual is going to take a lot of time, I like to remind myself how much I value the “flow state” and sense of appreciation that can come from some repetitive actions. Given that this project required creating 8 spreadsheets with 10-20 tabs each, AND I enjoy opportunities to learn Zapier, I decided that this was ripe for automation.
I mentioned previously that I had trouble renaming the tabs to match the bespoke counties. I think I could have figured this out with some Google Scripting (maybe with some help from ChatGPT) but I decided that I had already squeezed a lot out of the automation orange 😉 and I was ready to finish the project. Plus, when I started entering the counties manually, I engaged my brain in a different way. For example, did you know that TWELVE states have a Polk County? Neither did I, until I found myself re-typing Polk several times…
end result
[My friend] showed them to the organizers in each state and they each basically turned into the heart eyes emoji
The end result is a Google Drive folder, with 8 Google Sheets, each with 10-20 tabs, each tab with a multi-select drop down menu in the appropriate column.
The end result is strategically deciding when to use built-in spreadsheet features and when to branch out into custom automation.
The end result is learning and having fun with systems.
The end result is an organizer who got a whole day back to use her time to do what she does best, and downstream, organizers who are going to have fewer headaches with inconsistent/unusable spreadsheets.
The end result is that the underlying problems were fixed, instead of turning into a procrastination engine/shame spiral/cleanup effort after the fact. (Yeah… I’ve been there).
The end result is a reliable way to coordinate contact info, so that organizers can contact the right people at the right time.
So in other words, the end result is people power.