Google Sheets Custom Menus

OVERVIEW

1. add a script (short)

2. connect to code (easy)

3. execute (fun)

RESOURCES

Good tools to make life easier. You’re welcome :)

• Coefficient - Live spreadsheet connections to 60+ business systems

• Lido - Automate your spreadsheets; accurately extract date from PDFs

• HubSpot - all-in-one CRM solutions for businesses of every size.

• Quickbooks - accounting software…still industry standard

• TransactionPro - quickly import, export or delete data in Quickbooks

• beehiiv - my choice for a newsletter operating system

• Carrd - free one-page website builder

• Notion - notetaking + project management + database

• Transistor - my favorite podcast host

• Fathom - AI-powered notetaking app

How to Add Custom Menus in Google Sheets

I’ve been adding custom menus ever since I found out it was a thing back in the mid 2010’s.

Is it an unnecessary flex from time to time?

But of course! 🙂 

However, it’s also a very useful tool when you’re adding even a little bit of Apps Script functionality to your sheets.

And Apps Script is THE way to go to automate time-saving tasks in your Google Sheets.

Set the Menu

First things first, let’s create the menu. We’ll just make it like the screenshot above: a menu titled, Style, with a few options inside it.

function onOpen(){
  SpreadsheetApp.getUi()
    .createMenu('Style')
    .addItem("Dark","darkMode")
    .addItem("Papyrus","papyrusMode")
    .addItem("Light","lightMode")
    .addItem("Synth","synthMode")
    .addToUi();
}

To make the menu appear when you open the spreadsheet, everything gets wrapped in the onOpen() function.

We’re going to use the UI Class to create and add the menu and items to the Google Sheets user interface (UI).

We use the SpreadsheetApp.getUi() method to string together all the actions we want to happen. It’s good for code readability to put each new method on a new line, but you could write it all in one line too. The dots in front of all the commands indicate that a new method is being called.

For .createMenu(‘Style’), the menu is created in the Google Sheet.

For all the .addItem() methods, it creates an item in the dropdown list with the title included first i.e. “Dark”. The second word is the function name that is going to be run when that item is clicked i.e. “darkMode”.

Then at the end, .addToUi() actually puts all this into play in the menu.

Add the Function

Now, the only thing missing is what script is going to be executed when these items are clicked. Nothing happens unless they’re connected with something.

For our “Dark” example, we can add the following script in the apps script file:

function darkMode() {
  SpreadsheetApp.getActive().getRange('A1:Z')
    .setBackground("#333333")
    .setFontColor("white")
    .setFontFamily("Google Sans")
    .setBorder(false,false,false,false,true,true,"#444444",SpreadsheetApp.BorderStyle.SOLID)
}

Now, when we select Dark from the menu, our sheet’s background, font color, font family and borders will all be changed.

This is a simple example, but I’ve used these custom menus in many real-world scenarios at work to make life just a little easier.

If you’ve got Apps Scripts that need to run, this is a real easy way to execute them.

Got any use-cases like this that you use or could use?
Let me know, I read every reply 👇

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Work with me
   I am available for consulting projects. Reach out and let’s chat.

2. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

3. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

4. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.

5. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.