Intro

This will be quite the long post detailing my process of getting a fairly nice experience for writing custom functions for Google Sheets. It is also my first post (anywhere) so please be kind...

TL;DR

https://github.com/MWallin/GAS_insertValues

Check here for the repo with the complete package. Clone, install, pull with CLASP and begin!

What is Google apps scripts custom functions?

Google apps is awesome! We use it for work and it has almost every bit of functionality that Excel does, but it includes live editing. Most of the functions in Excel is there and Google has added some of its own to make it into a really powerful spreadsheet tool. Maybe not the best, but enough for 90% of all regular use cases.

What are custom functions?

When the built-in functions aren't enough to do what you want, you can add your own!

Excel has had this almost from the beginning and it is part of what has made Excel so popular, it is easy to extend. So, of course, Google apps Sheets has this also.

In Excel, the IDE for editing macros/Custom functions is fairly good and has a lot of bells and whistles. However, VBA (Visual Basic for Applications) isn't really a lot of fun and the IDE hasn't been updated for a good while now.

Here one could think that Google is better with a good IDE for editing custom functions and sure, it isn't bad, it just isn't very good. Especially not when compared to the likes of VS Code that I use for my other coding. But at least Google uses Javascript instead of VBA. However, I'm not entirely sure which version of Javascript they use and I cannot find any information regarding this...

Why custom functions?

There are a lot of built-in functions in Excel and Google Sheets but every now and then I need to do something custom. At work, we share a lot of configuration data that will eventually wind in SQL-database. A table in a SQL-database is essentially a worksheet in Sheets, and Sheets is much easier to edit and work with than a database. Therefore we use Sheets for sharing and manipulating a lot of data. It's not perfect, but it works.

However, eventually, the data needs to go back into the database and that's usually in the form of an INSERT () ... VALUES () ... statement. What goes into the values bit is essentially a concatenated form of a row in Sheets with some separators.

It is possible to use regular functions in Sheets to combine data but its very brittle and not really scalable. Plus, most people at work get a bit nervous when there is a HUGE formula in a cell. A short two-word formula feels much better.

Extra plus, with a custom function, one can manipulate the data however one likes. Remove line breaks in a cell, no worries, escape any ' characters, easy.

How to begin?

So how to begin writing a custom function then?

Easy, just open Sheets, go to tools and then Script editor and you have opened the integrated code editor in a new tab.

Everything you need is there. But everything you want from a modern development environment or IDE like Visual Studio Code? Hardly! It feels like a port of notepad in terms of usability.

I wrote my own first custom function here, a small script to calculate the new car tax in Sweden, not a big function but not trivial either. So the environment works, it just feel a bit sub-optimal. So let's fix that!

Getting a decent workflow

I write all my javascript in Visual Studio Code which I think is just fantastic. I came from Sublime text which I never really felt clicked for me. But VSCode, just amazing. So how to make it possible to write all my Custom functions here?

CLASP

The first step is CLASP, https://github.com/google/clasp.

This is a Command Line tool that works a bit like Git. It has a lot of functionality but in the context of custom functions its really only necessary to learn a few commands; clone and push.

Clone makes a copy of a local script and Push copies the local code back to Sheets.

After a whole lot of frustration, I learned that with custom functions it isn't possible to create the script with CLASP as the script is bound to a specific Sheets instance (or just Sheet).

So step one is to create a new script from within Sheets and then figuring out the script-ID. Inside the script editor, you can find this in Archive and then Project properties.

Write a few lines in the editor to make it super easy to verify that everything worked.

Then install CLASP as per the instructions on GitHub. Remember to log in and enable the Apps Script API.

The type clasp clone in a directory of your choice. This directory will now house the project.

Linting and other goodies

Next step is to open the project in VSCode, https://code.visualstudio.com/!

I have a lot of extensions installed in VSCode but my favorite one is without a doubt ESLint so next up is getting linting to work.

I like to install as many dependencies as possible locally in every project so let's do npm install eslint and eslint-plugin-googleappsscript -D -E.

The rules I use are in the example repo. I wouldn't say that these are the best eslint setup for GAS development, but it works. Feel free to customize. Mostly the rules are my standard for javascript development with a few tweaks for GAS.

Testing...

Now you might have a function or two written and would like to run it and see if it works as you hope. But alas, the debugging experience in custom functions is not even bad, it just isn't there at all! No logging, no debugger, no nothing when running custom functions with actual input from a Sheet. Which really isn't all that surprising when you think about it. But it does make for hard work.

But, this thing called TDD, might that help? It sure can!

This won't be a TDD tutorial but I will give you a few pointers to enable TDD when working with custom functions in this way.

Write many tests! For every possible scenario. And more than one test for each scenario to guarantee robustness.

I write my tests using mocha and chai which works really good and I also use the Mocha sidebar extension in VSCode. It makes for a really good flow and experience.

But wait, to be able to write tests for a function you must export it and import it into another file. How does Google Sheets like those kinds of statements?

Well, not at all actually! There will be nothing but compile errors if you push code that contains statements like module.exports = insertValues;

Building

But worry not! Gulp is your friend!

I experimented a bit with webpack since it is the miracle-worker for any project that needs a custom build process. But with no success at all!

Webpack might work really well for this but after a couple of hours I gave up and searched for alternatives. And enter Gulp, a lightweight, understandable tool that was super easy to configure to do my bidding. Is it the best tool out there, probably not, but it works.

Check out the repo for all the details but in summary, there is a short script that removes all lines contain any bit of exporting och importing and then concatenating everything into a main file and a utils file.

The main contains the custom functions that gets added to the worksheet and utils is everything else that makes it work.

By concatenating everything this way its possible to use smaller files with its own function to make coding easy and then putting it all together to make Google Sheets happy to not have too many files around.

Pushing

Next up is pushing all the changes back to Google Sheets to use the new custom function. That's easy, just run clasp push.

But!

First, you need to build the code. And we only want to do that and to push if all the tests pass. No point in pushing code that won't work.

So how to do this in VSCode?

Here is my way of doing it, in package.json, add this bit:

"scripts": {
"publish": "npm run test && npm run build && npm run deploy",
"deploy": "clasp push",
"build": "gulp build",
"test": "mocha "
}

Test makes sure that all the test run.
Build takes care of the building of the code.
Deploy pushes everything.

Publish combines all the tasks with && and aborts everything if one step fails.

If the tests won't pass, then nothing else will execute.

Handling input

Puh, its getting long, thanks for reading all this way, it's not much left now.

Now comes the trickiest part of it all. You have written your function and a lot of tests and you know its solid! The only problem is that it just won't work when you push it and run it with real data.

That is because the input from Sheets is most likely not what you think it is.

Read this answer and run the code in the example, https://stackoverflow.com/a/20288490.

Depending on if you input is a cell, a couple of cells on the same row or column och a whole range of cells the input will be completely different.

This took me way too many hours to figure out since the documentation that I found at first said that one cell = one value and multiple cells = array. But it depends. Sometimes you get an array of arrays, now quite the same thing.

Feel free to use my makeArray function in the repo to solve this bit. I cant guarantee it will work in every situation, but it works good enough for me.

Putting it all together

And here is the finished project: https://github.com/MWallin/GAS_insertValues

Probably not the best piece of code out there. But probably not the worst.

Conclusion

The end!

This was pretty much my journey wich I hope will help someone to figure these things out. Sure, it is quite a lot of setup and learning of new tools. But I like that. I like learning and getting things to work. Because when everything works I feel so much more confident in the code I write! And having tests for everything, that's just amazing, I can't tell how many times it saved me during development.

Sure, my example project is really tiny, but for us, at work, it is really valuable.

---

I think I got every step included here but If you think somethings missing than please leve a comment!