When I worked in FP&A, I discovered that VBA could automate a lot of tedious tasks I was doing in Excel. From creating charts to formatting data, I realized that there possibilities with VBA were endless. As I started using Google Sheets more, I found that Google Apps Script offers similar functionality to extend what your Google Sheets can do. The specific use case I wanted to solve was syncing data to and from my Google Sheets from other workplace tools. This episode talks about how I picked up Google Apps Script, and how you can level-up your skills to be more data driven in your job. Original slides for this episode are here.

This episode was adapted from a talk I gave for Promotable.io's "Breaking into data" series. The original presentation I gave is here.

Starting with the macro

The way I started with VBA was simply recording a macro. You hit record, do a bunch of stuff in Excel, and then see what code is outputted from those actions you took. For example, this little script selects the range A1:A6 in your spreadsheet and applies a right-align formatting to the cells (among other things):

What's nice about these macros is that you don't have to know how to write code. At least initially. Just by doing stuff in Excel, you can see how VBA interprets those actions in the VBA editor (as shown above).

The first thing I tried to do with VBA back in the day was simply select some cells. This is the Range("A1:A6").Select portion of the script above. Then you can hit "play" in the macro, and Excel will select these cells for you without you touching your mouse or keyboard! The first time I saw this happen in my Excel file was a mind-blowing event. I realized I could control my spreadsheet just from pushing play.

Doing more with Google Sheets with Google Apps Script

Google Apps Script is the VBA of Google Sheets. Since Google Sheets has an extensive API, you can access pretty much any part of the Google Sheets UI. The reason I like using Google Apps Script include:

* It's free* The language looks and feels like Javascript* Lots of built-in services to access not only Google Sheets, but also Gmail, Google Calendar, and other products in Google Workspace (formerly G Suite)

I was worried that learning Google Apps Script would be difficult since it's different from VBA. I started with simple tutorials like this one (teaches you how to programmatically create a Google Doc file and send you the link via Gmail). Google is clearly trying to target "citizen developers" like myself who don't really have any forma programming experience but know just enough to be dangerous. Tutorials like the one below make it seem like anyone can use Google Apps Script and take advantage of its robustness:

https://www.youtube.com/watch?v=JE4pF40ujh8&feature=emb_logo

Just like I first selected a range of cells with VBA, I did super simple tasks and workflows with Google Apps Script like selecting some cells or applying some formatting to numbers.

The Google Apps Script editor

Data transformation and munging

The first time I heard the term "munging," I thought it was some kind of disease. This is all data munging is: