Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

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:

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.

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:

The process of transforming and mapping data from one “raw” data form into another format with the intent of making it more appropriate and valuable for a variety of downstream purposes such as analytics.

Source: Wikipedia

I started using Google Apps Script with one goal in mind: I wanted to “sync” data from a table in Coda (my company’s product) to a Google Sheets file. In order to do this, I had to utilize the Coda API to get data out of my tables in Coda, and then use the Google Sheets API to get data in to my Google Sheet. The problem is that data returned from the Coda API is structured in a format that is different from what Google Sheets needs.

When you view your data in Coda via the Google Apps Script debugger, the data looks like this:

At first, I had no idea what I was look at. After further analysis, I realized that the “rows” in my Coda table were each of the numbers with a “+” next to it (the second arrow in the screenshot above). Each column of data in my table is represented by a “row” in this debugger output. Once I realized all my data is there, I just needed to figure out how to transform the arrays of data from the Coda API into the multi-dimensional array format that Google Sheets needs via the API like this:

var values = [  
  [ "Green", "1,000,000", "$2.99" ],
  [ "Red", "3,000,000", "$1.99" ]
];

If you are used to playing around with formulas in Excel or Google Sheets to get data to look exactly the way you like (see previous episode on extracting text from a middle of a cell), playing with these arrays of data should be pretty straightforward for you.

Working with the “data model” instead of the spreadsheet UI

The biggest lesson I learned from building this Google Apps Script out (you can see the script in this repo) is this: use the data “behind” the spreadsheet instead of moving the cursor around the UI.

What does this mean?

When you record a macro, the code ends up looking like this where you’ll inevitably see a bunch of Range selects:

This is fine on a small spreadsheet. But if you have a spreadsheet with hundreds of thousands of rows, moving the cursor from one cell to the next to pull data out of Excel or Google Sheets can get quite slow. “Pasting” data into each cell one at a time is also inefficient.

Instead, I started pulling data one table at a time so that the entire data model was stored in a variable somewhere in my Apps Script. This means I can cycle through each row on the backend, apply some transformations, and then sync the data over in my Google Sheet. I would also avoid syncing one cell or row at time. When possible, I would try to sync an entire range of data into the Sheet to avoid unnecessary operations.

Shifting your mindset from selecting cells and setting values in cells via Google App Script to working with the data model is important as you’re dealing with bigger tables of data.

Key takeaways on being more data driven

If you work in a marketing, customer support, human resources, or some other role where working with data is not your primary responsibility, you’ve probably had to deal with a spreadsheet at one point or another. These takeaways are a few tips for you to consider to learn how to be more data-driven in your role. I think any knowledge worker who works with spreadsheets every day has the ability to create powerful workflows in Google Apps Script.

1. Be skilled in workflows in addition to tools

Sometimes I get asked about which tools to use once you’ve “mastered” Excel or Google Sheets. First of all, there is always more to learn about your tools; especially spreadsheets.

Unless your job requires you to learn SQL or Python or some hot new tool, I would encourage you to think about becoming proficient in workflows.

Understand how your team’s Salesforce data ends up in a database and then gets reported out by a different tool. Find processes where people are copying/pasting data from Google Sheets into email to send out weekly updates and use tools like Zapier to automate these manual processes.

2. Work through short tutorials to learn Google Apps Script (or any workflow tool)

Instead of signing up for a 5-hour long class on how to use Google Apps Script or some other tool, take small tutorials (<10 minutes) to learn the basics. Why? Chances are 80% of what you are trying to automate on your job can be accomplished by a few small features in Google Apps Script.

Many people think they need to learn all the little details about Google Apps Script by taking multiple classes, but most of your learning will happen via doing and Googling. Plain and simple.

3. Know VLOOKUP and PivotTables if you claim you are “proficient” in Excel or Google Sheets

Many people put “proficient” in Excel or Google Sheets on their resume, but the minute you ask them about doing a VLOOKUP or summarizing data in a PivotTable, they freeze.

Once you understand all the nuances of lookups and PivotTables, you’ll already be in the upper echelon of all spreadsheet users out there. The big advantage here is that you’ll start to see how all the tools you use at work are basically lookups to each other and data is all stored in rows and columns. Think about every tool that you end up doing an export to CSV in.

4. Break your problem down into small pieces

Are you tasked with doing a big analysis? Need to create a data-driven presentation to close a sale? The data problem, like many other problems in life, should be broken down into small steps.

With Google Apps Script, I knew I wanted to sync data over into Google Sheets which felt like an insurmountable problem at the time. I started with the basic (like selecting cells in a Google Sheet using Google Apps Script). Then I moved on to writing data into cells. And before you know it, you’ve solved a bunch of small problems, and the big problem doesn’t feel so big anymore.

5. You don’t need to have a computer science or data science degree

I majored in marketing. I didn’t touch Excel until my first job out of college. The idea that you need a computer science or data science degree (or take a bootcamp) to be data-driven is debunked, in my opinion.

More and more knowledge workers who are comfortable with using spreadsheets and SaaS tools realize that they are actually “developers” in their own right. When you think something looks complicated or you think something can be automated, just Google it.

I really like this Stack Overflow blog post from 2016 digging into whether developers need college degrees. Guess how most developers learned how to do their jobs? Self-teaching.

As the blog post discusses, at the end of the day it needing a degree depends on the specific job you’re applying for. For most roles out there (especially at startups), I think being data-driven, curious, and knowing how different tools play together will get you going a long ways.

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• No other episodes/blog posts this week!