A bright fall day of 2012 was one of the best days of my life. I pranced with my #1 favorite activist performance group of all time, the Bread and Puppet Theater. I have since seen them perform many, many times, including going on a Great Blizzard Pilgrimage to the NY state fair grounds for a particularly special installation. The acts begin to blend together in my memory, but one that stands out (in simple elegance) was Bread and Roses. As the circus performers sang the classic folk song, guest performers (me!) held incredibly tall rose puppets (they seemed to me to ascend multiple stories into the air), gently swaying in the breeze on bamboo poles. Perhaps this was the day that I first internalized the notion that we must fight for bread (sustenance) and roses (beauty) – a theme that I will return to at the end of this post.
Newsflash! I’m recently recovered from a non-emergency tonsillectomy, and the recovery was … awful. Here’s a popcorn selection of words that described the recovery: fish bones… razor blades… narcotics… tongue infection… hysteria… ravenous… no ice cream D: Bad, bad, bad.
Finally after lots of tears and complaints, last weekend, I felt like I got my special sauce back!! After a few weeks of “bare minimum” (if you need help, I will fix it, but proactive problem solving was out of the question)… curiosity, ambition, and stubborness got the best of me, and I put my powers to work for good, building out some cool spreadsheet stuff in the process, and learning new skills! In this post, I want to highlight two of those “wins” and share the story of how I got there.
google sheets flex
Imagine this scenario: your organization runs programs that require a set-up to do list of approximately 40 steps. So, you have a spreadsheet where each tab represents a program, and each tab has a similar to-do list, but with some manual customization. This is working good and fine if you only have 2-3 tabs, but when you have 6-10 or more, things start to fall between the cracks. No one has time/brainspace to check every tasklist every day. You start to want to surface insights about WHICH TABS need checking. Which tasks are overdue? Sure, we could move all of the tasks into a more automated database system (like Airtable, Salesforce, etc), but that would take a lot of time and reduce flexibility to easily add/delete rows/columns that we love in spreadsheets. I suggested that we stick with the current spreadsheet system, but add a summary tab with insights about what’s going on on the detail tabs.
First thing’s first… I figured, let’s put in a formula that tells us how many tabs there are! This will save me the time of counting them, plus it will be a useful “at a glance” metric of how many programs are being planned. Lo and behold, there is no such function in Google Sheets. So, what should I do? Time to get creative!
Through some googling, I learned that you can use code to add functionality to Google Sheets. The interwebs was generous enough to even provide me with the code I needed, but I couldn’t quite figure out where to put it in such a way that I could *access* it in the spreadsheet. I followed some instructions through a combination of this link, intuition, and trial and error. Eventually, I added a menu to the standard Google Sheets bar that had a drop down menu for my new formula. Then, I referenced my new formula in a cell and I got the right answer!
I went into this project with an open mind. I knew that this was possible because my bestie Emily has created Google Scripts. I used the old “if they can do it, I can do it” mindset and put on my “exploration” hat. If this doesn’t work out, I can always (1) pat myself on the back for trying; (2) make a list of things I learned along the way; (3) ask for help.
Lo and behold, it totally DID work and I’m completely delighted!
Next, I wanted to surface some key insights about each program, especially “inflection points” where follow up is the most important. However, I wanted to do it in a visually pleasing way! My collaborator on this project had shared with me that thoughtful design elements are important to her. And I completely agree – beautiful spreadsheets make me more likely to use them!
Let’s look at what happened in each one of these columns:
- Steps: This is a COUNTA() formula that looks at the number of to-do steps in the given tab
- Completed Steps: This is a COUNTIF() formula that looks at the tasks and counts the completed ones (where checkbox = TRUE)
- Progress: This is a visual indicator of the percent of tasks completed. I used a new-to-me method for this, borrowed from here. (and I am totally tickled!)
- Status: I used an IF() formula here, that looks at the values in the next three columns. If they are all, “Ok,” the formula returns an emoji checkmark! I copied the emojis from Emojipedia. You can use them in any cell or formula in Google Sheets!
- Invoice sent but not paid: This is an IF() statement that looks at the “Invoice Sent” checkbox and compares it to the “Payment Received” checkbox.
- Remaining columns are the same logic as above
I’m really happy with this because it met some of our big outcomes and I got to be creative with HOW to meet them! Let’s review the overall criteria:
- Create “birds eye view” of programs, while retaining the flexibility of using spreadsheets to manage onboarding tasks
- Identify tasks that are falling between the cracks or would benefit from follow up
- Make spreadsheet beautiful (or at least be intentional about an engaging design)
How do you make multi-tab spreadsheets easy to use? Do you have questions about the formulas used above? Leave me a comment and I will write back!
salesforce flex
*enter cartoon montage of drinking Bubble Tea, embroidering, and making fettucini alfredo*
New project!
I’m designing a fresh, crisp, new Salesforce org for an amazing nonprofit that is doing land reparations work in the Midwest. I decided that I wanted to incorporate as much beauty into the system as possible, including photos of beautiful landscapes, popup reminders for mindfulness and mission alignment, and thoughtful, minimalist database pages. I want staff to feel like they are connected to their mission when they are doing data entry, not like they are stuck in front of a computer doing grunt work. Well, I can’t INSIST that people enjoy data entry, but I can be generous/thoughtful about this at every step of the design process! This is the first time I’ve set up a Salesforce org from scratch, so I’m trying to take my time, enjoy the scenery, and take care of every step along the way.
Instead of just uploading pics to the Lightning Home Page, I thought it would be really cool to have a “carousel” of rotating pics – to keep things fresh! I found a free app from Salesforce Labs and I installed it right away. Unfortunately, the instructions left out a few key pieces of information that I needed, namely the format of the image URL that the app would reference when it rendered the image on the page.
Something that should have taken 5-10 minutes stretched into 3-4 hours of uploading images, refreshing the page, re-reading the instructions, getting frustrated, etc. Finally, I decided that since the declarative method wasn’t working, I should check out the code. I found an example of code written in the language JSON by reading the comments of the original App Exchange app listing.
In order to use the code, I had to create a Sandbox version of my Salesforce org (ugh, that took about half an hour!) and replace the code sample with the image URL and the button text overlay that I wanted. Lo and behold, I was able to render the images in my Sandbox!
And THEN I had a lightbulb moment! The reason why the code worked is that I had used a different image URL formulation, one that ended with “.jpg” instead of a long Google Docs code. I think that the application did not recognize my image links as true image links. I spent soooo much time trying to find a complex solution when the root of the problem was simple syntax. Once I figured that out, I ditched the JSON code and went back to the declarative version of the app. It’s working just swimmingly now 🙂
Do you have ideas for making Salesforce pages more beautiful? Do you have experience with downloading apps and being unable to interpret their documentation? Leave me a comment so that we can continue the conversation!
looking for beauty
I started out this blog post thinking that I wanted to write about creativity and problem solving, taking an idea and figuring out how to do it with tools I’ve never used before, and getting past feeling stuck. I even used code/code-adjacent processes which are not in my comfort zone!
However, the more I wrote, the more I realized that the theme subconsciously tying these projects together is Looking for Beauty. I’m tired of impersonal databases… I’m tired of ugly spreadsheets… I’m willing to put in the extra effort to make tools and systems that are beautiful and bring out our best selves and our best data.
Sure, I could make a task list with no formatting, or a Salesforce org with no pictures. They would get the job done, but they would leave something essential behind. Joy, ease, human connection. I want those to be part of the technology that I use and build. I want it all, and I’m not stopping until we get it!
