How I Created My First Google Sheets Add-On and Why You Should Try It Too

This is quite a seminal moment in Better Sheets’ history. Instead of just another tutorial, I've built an actual tool within the Google Sheets universe.

Have you ever thought about taking your Google Sheets experience to the next level by creating your own add-on?

In this article, I'll share my story of how I created my first Google Sheets add-on and why you should consider doing it too.

Why Create an Add-on?

When I first started using Google Sheets, I was intimidated by its capabilities and features. Like very few users press on and see what else it can do. After learning Apps Script, I continued to extend the power of Google Sheets myself. It was empowering.

At times it felt like pure raging magic.

I've been writing little apps scripts for years. If you know javascript, you know google script. If you're looking to learn how to code in sheets, and you're already an experienced coder, watch this 15 minute video:

https://www.youtube.com/watch?v=44B6_svdD9Y?embedable=true

Even after coding in sheets for a long time, I was a little intimidated by the prospect of building an add-on from scratch. But with some research, practice, and persistence, I was able to create an add-on that improved my Google Sheets experience and made me more productive. And the best part is, anyone can do it too.

I wanted to write an add-on that executed the advice I give many Better Sheets Members.

Instead of having to remember my advice, I wished that they could click a button and it happened.

The Advice

Decrease contrast between your text and background color. Ideally font: #444 and background color #FBFBF8. Doing so makes it almost look like paper.

I've done this for years and has helped me deal with eye-strain for long hours.

Not My First Try

One moment's pause to mention that I did try almost a year ago to make an Add-on and I got stuck in the review process. This time I had a helpful guide.

I was helped by Alice Keeler's great step-by-step article.

And originally I learned about Apps Script from Labnol and Digital Inspiration.

Here's what I made: Sheet Styles

It turns your background color, font color, and font family into a pre-created template. I have 4 such styles ready.

All of them have less contrast than the black/white Arial combo that is the default Google Sheets style. Less contrast, decreases eye-strain and increase productivity.

I wrote a few lines of script that selected the entire active sheet, and changed it to the color I wanted to. I already knew the hex code. The code looked like this


    var sheet = SpreadsheetApp.getActiveSheet()
    var rowCount = sheet.getMaxRows()
    var colCount = sheet.getMaxColumns()
    sheet.getRange(1,1,rowCount,colCount).setBackground('#fbfbf8')
    sheet.getRange(1,1,rowCount,colCount).setFontFamily('Karla')
    sheet.getRange(1,1,rowCount,colCount).setFontColor('#444')`

To make this add-on I created my own HTML sidebar. This was fun for a while until my code just didn't run from the buttons in the sidebar.

The key discovery I made was that the side bar does not run google scripts as it says in the documentation, IF the user in sheets is a different user than logged into Chrome. What happens is that there is a deep deep deep error that shows up includes "PERMISSION DENIED". I found a issue tracker for this issue in Google's documentation and found that this issue has not been fixed.

The workaround for this was to change the runtime version from "V8" to "DEPRECATED_ES5". Which looks and sounds scary.

But it works.

Of course maybe in 2017 when this issue first came up there weren't many ways to run multiple google accounts in one chrome, but that is a very common case now. And no longer should be considered an "edge case". I do wish they fix this issue.

I tested my code and all works.

Why you should make a Google Sheets Add-on:

You might want to add integration from your app to sheets. And then also your add-on will be discoverable in the Google Workspace Marketplace.

You might want to help yourself. Create an add-on if you have multiple accounts and have some code you want to run in all of them. You can deploy GAS code as a web app, or as a library too. But as an Add-on you can just go and install it easily in any sheet, in anyone's account.

It's Your Style

You can make an add-on in the flavor of you app. I used custom CSS in my HTML sidebar. Also, you can make a sidebar with the google CSS so it looks and feels more connected to Sheets. This might be useful if you want to put functionality first, and leave your brand behind.

In Conclusion

Creating a Google Sheets add-on is like going on a wild adventure. Sure, it might be a little scary at first, but once you start exploring and experimenting, you'll discover a whole new world of possibilities. And just like a hero on a quest, you'll overcome obstacles and challenges, and ultimately emerge victorious with a powerful tool that improves your Google Sheets experience and makes you more productive. So why not take the plunge and try your hand at creating a Google Sheets add-on? You never know what amazing things you might discover along the way.

There’s even a Dark Mode for you hacker dark mode freaks!

Resources so you can build Sheet Add-ons as well

Watch this 15 minute video.
Alice Keeler's step-by-step article
I also first learnt the art of Apps Scripts from reading Labnol’s blogs. Follow him on Twitter.

Also published here.