Everything Aggravating about Apps Script

I've been coding Apps Script for almost 10 years. It'll be ten years September 2023. The beginning of the beginning isn't interesting. I switched from Excel and Excel VBA because I joined a new company that used Google Sheets.

It was a startup, and had existed for about 6 months, but already they had a monolith of a Google Sheet that the "entire" company was working out of. When I joined, I joined along with about a dozen other new employees. The 1 person who ran this monolith was going crazy within days. Showing a dozen other people how to use the sheet she so carefully curated, edited, and created.

My intention in learning Apps Script, literally was to help this one person out, and thus the company be more effective. Hours were spent dealing with issues that arose every day from poor data inputs, and executions. Copy/pasting rows from the top of a tab to the bottom.

Had I not known Excel VBA existed, I wouldn't have even looked for the Google Sheets equivalent.

It took me about 2 weeks to write my first useful Google Script. Yes at the time it wasn't called Apps Script. It was Google Script. A bastardized, older version of Javascript.

I ham-handed my way through typing, copying, pasting, cutting, moving, around some code. It worked.

The code worked!

And it was "automatic"

I had set up an onEdit() function to run every time there was an edit to the sheet and then if various criteria were correct, it would execute a copy/paste.

You can see more about this script that has become the infamous: $75,000 Google Script on YouTube.

But the feeling... That special feeling of accomplishing coding something and people using it, was immense.

It was a rush.

I wanted it again.

Ultimately my job became all about Google Sheets. For the better part of the 5 years I spent at that company I worked in Google Sheets. I made Google Sheets, I wrote Apps Script, I showed people how to use the sheets I made.

And over that time I actually learned to program. Learned mySQL and Javascript. A few years later Ruby on Rails.

And now I can compare the experience of writing/using Apps Script with other programming languages.

Let me say now I'm not an expert programmer. I haven't really been a die hard professional at programming. I've happened to use Apps Script much more than most, and have a career. Of course now I teach Apps Script as part of Better Sheets.

I won't go too deep into specifics but do want to share with you the aggravating parts of Apps Script. This is meant to not tell you "THERE IS A SOLUTION!". In fact there isn't. There's no workaround. That's why they are aggravating.

• Writing Apps Script & Autocomplete
• Want to save files with Errors? Nope.
• I lose my way in gs files, too easily.
• Google Lacks documentation and examples
• Google Workspace Marketplace is complicated
• Permissions are scary to work with.

Aggravated While Writing

To get to Apps Script you can open any Google Sheet, go to the Extensions menu and go to Apps Script. In another tab opens the code editor, or the IDE.

Here's where the problems start.

Just writing Apps Script is a continually trival process. The autocomplete is okay but not very helpful. It shows up at specific points and if I go too far, go back a little and want to see the autocomplete again I have to delete characters.

The fact that there is a little bit of autocomplete is nice. But the fact that I have to get it at one particular point while typing, and that's it, well that's aggravating.

Can't Save Files With Errors

Katie of EasyFOI was using Apps Script for a Gmail add-on. She found out that Apps Script does not save a file with an error, when you go to save it.

Apps Script will just give you an error and that's it. Oh and you absolutely can close the window, even though the file is not saved.

Well this wouldn't be an issue if it was clearer that it didn't save, and another warning popped up before you closed the window.

as Katie says:

Turns out Google doesn't save your apps script with ctr+s when it's full of errors so just lost my whole add-on updating Google chrome.

This is far more than a minor inconvience. There are many times I find myself trying to solve an error, by introducing more errors and I just want to quit for the day, or go for a long walk. By not saving my work I'm literally not saving anything!

The thing is: I want to save the error. So I open a Google Doc and paste it all in there.

If I want to share an error-ridden file with someone to debug it. I can't. That would be helpful, and collaborative. I thought Google was all about the Collaboration.

Lost My Way Many Times

There is no file structure. There's a list of files on the left. You can create gs files (where script is). You can create HTML files too. But you can't group them.

In every single monolith of a product, I inevitably lose my way at some point. I create a bunch of little functions. Starting at the top I work my way down. Add functions here and there. Okay okay okay I get it, I made my own mess. But there should be a way out of this.

One might think just create a gs file for each function. But that's not ideal. It's far worse. I can't search the entire file structure. I can only search for words/function names in one single file at a time. Well that's aggravating.

Forget About Documentation

Google, come on. You should do better. This one riles me up. The documenation in and around Apps Script is seriously lacking. There should/could/would be a nice community forum but because Apps Script is run by Google, there should be someone or an entire team dedicated to the developer experience in Apps Script, and actually keep adding to the documentation.

But there doesn't seem to be someone.

The lights are on. The web pages exist. But there's just not much.

For example to create an add-on is a very complicated series of steps. And it took an outside person to write a step-by-step guide. Alice Keeler wrote this wonderful guide to adding an Add-on to the marketplace.

You have to go through Oauth process AND marketplace approval. It's a crazy amount of stuff to do. There's still not enough quality Google Sheet Add-ons and don't have to wonder why.

But the fact that an outside person has to write this kind of guide for the Google blog is bewildering to me. It's Aggravating!

Why can't there be a definitive step-by-step process? Alice Keeler wrote that article in August 2022.  Add-ons have been a feature since 2014. Apps Script was already 5 years old at that time.

Maybe there's too many options because right now if I have an Apps Script I wrote, I can share that in many different ways

• Share the sheet (Apps Script is attached to a Sheet)
• Share as a Library
• Copy/Paste into a doc and email
• Publish as an Add-on (Internal or External to a Workspace Organization)

And to be honest, I can't right now off the top of my head tell you the pro's and cons of each of those. I probably will sit down and write about it one day. I've never published an Apps Script as a Library, yet.

Google Workspace Marketplace Woes

Adam, AKA, Prolific Oaktree, who runs SheetsHelp.com brought up this point about the Google Workspace Marketplace: "Say that ten times fast. It's clunky to explain where your app is. It sounds weird to say it out loud"

You're probably going to have a hard time letting people know where to find your app. Most people I know don't browse the Marketplace itself just looking around. They have to be told where to go. And to say "Google Workspace Marketplace" is a mouthful.

Adam also went on to mention:

Google Marketplace SDK has an input box called "Google Analytics ID." I thought, "Great, I'll be able to see where my users are coming from".

Turns out it was for the old Google Analytics ID that doesn't work anymore. So, I can't tell where people are coming from when they arrive at one of my add-ons for Google Workspace Marketplace.

Permissions Sound Scary

The way that permissions are revealed to a maker, and subsequently to a user sounds down right scary.

But not just the sound of them, because that might be a security thing, but really how they are implemented and how much limitations that impose.

Adam from SheetsHelp.com mentioned he gets aggravated by the "limitations of what you can do with an add-on."

I understand that you need to reign in the permissions of add-ons so they can't reek havoc on your spreadsheet.  

I wanted to build an add-on that gave a nicer text box like the one in Excel.

No launching the drawings pop-up and having to guess how your text would be placed.

But, there isn't a method available to place a rich input box like that on top of the cells which allows input, shading, font selections, borders, etc.

One can only dream.

One Solution

One solution to these aggravations is to just keep going. Keep learning programming best practices and bring them into my own Apps Script. Learn from other professionals and actually creating workarounds might fix some of these problems.

This solution is preposing that I am the problem. That the individual programmer is the problem. Not the machine.

One more Solution: Build More

Right now there's no Apps Script 3rd Party IDE. Maybe there should be.

There's so few Apps Script examples online, maybe there should be more.

There seems to be a lot of tooling needed, more features built in Apps Script via Add-ons. You can build new features! Learn Apps Script and go build something cool.

I even made a 15 minute Apps Script course for people who already know how to generally program. If you're a programmer/coder, then you can go watch this on Youtube.

Apps Script is Awesome

Don't get me wrong. I like working in Apps Script. There are great things about it. And I love the fact that it's specific to sheets. It's literally inside of Google Sheets. But it's also available for lots of workspace: Gmail, Slides, Docs.

I'll also finish up this writing with a little bit about what makes Apps Script so awesome.

• No HTML, No CSS, unless you want to.
• Easy to Start Writing Code
• Integrates with Google Workspace

Just Javascript

At times, Apps Script is wonderful. It's just coding. It's pretty much just Javascript. But with Google Sheets.

It's a joy not to have to write HTML or CSS, if you don't want to. I know I have chosen not to lots of times.

And you absolutely can add in HTML if you want to make cool sidebars, or modals, or even do a little CSS on those sidebars.

Easy to Start Writing

I love the simplicity of opening a new sheet, open the Apps Script code editor and just start typing.  No development environment to set up. No dependencies.

Integrates Well With Others

I love that I can manipulate Gmail and Docs and Slides, from within a Google Sheet's Apps Script. It's really really powerful.

I can create docs from templates I write in a sheet.

I can tag people in my gmail from my google sheet.

Even Search Console is integrated well. I am in Search Console a lot these daysa and can export data as a CSV file or as a Google Sheet. It will literally open a new window and all my data is in a Sheet. No need to download and upload CSV files anymore. That's cool.

Empower Improvement

I wrote this not to just sound any alarm, or spout off. I wrote it to exorcise my own demons. While I might pump up coding in sheets, there are downsides and also some very big problems. But it's still fun. It's still incredibly useful.

I also wrote this, so I get better. While I can hope and pray that someone at Google fixes something, I don't think they will. Especially with the documentation. So I think it is up to individuals like myself, like Alice Keeler, like Ben Collins, like Prolific Oaktree, it is up to us to provide more documentation in the form of examples. I think it's up to us to build more, better, tools.

I hope Google empowers improvement.