Coding in a Spreadsheet

What is Apps Script Exactly?

According to Google, it is "a rapid application development platform that makes it fast and easy to create business applications that integrate with Google Workspace." 🥱

Okay, what does that mean? It's basically a code editor built in to many of Google's web applications like Google Sheets, Docs, Forms, Calendar, etc.

When I first learned about Google Apps Script, I’d just graduated from college and was promoted into management. I’d been exposed to spreadsheets throughout my business program, but had never done a ton with them.

Video Walkthrough

Here’s a walkthrough of the real world apps script I discuss below:

Using Apps Script at Work

One of my first projects at work was getting rid of an archaic ordering system and moving it online.

Google Sheets provided the perfect solution for our use case, and I implemented a lot of linked spreadsheets for our stores and our warehouse.

As we began using it month by month, it became necessary to copy the previous month’s sheet and clear out much of the data to create a new month’s sheet.

(For a few reasons this was better than using a blank template.)

I got pretty tired of selecting columns and deleting them manually.

So, a little searching revealed that you can write code behind the scenes when you need Google Sheets to do things that aren’t baked into its functions.

Lo and behold! I could set up a script (a little bit of computer code to make something happen) to clear all those ranges with the click of a button.

When you open Apps Script (Extensions → Apps Script) from the top menu, you’ll be greeted by a code editor.

Think of this editor as a word document...but for code. You write commands in here and then you can execute them by clicking run.

I was not skilled when I first learned how to do a little bit of Apps Script coding. But I knew what I needed to do: clear ranges. And I knew how to Google things.

Sure enough, there was a host of others who had gone before me creating little scripts like this that I was able to learn from.

The Actual Code I Use

In my spreadsheet, I had multiple sheets for multiple stores. Here is an example of how I was able to clear specific ranges for one of those stores named Quarter.

You can see the code in the image above. And I'll quickly walk through the pieces of it in the snippet below.

// first you name the function clearRange
function clearRange(){
  //make a variable of the Quarter tab
  var sheet = SpreadsheetApp.getActive().getSheetByName('Quarter');
    //clear each range
    sheet.getRange('D6:F103').clearContent();
    sheet.getRange('H6:J103').clearContent();
    sheet.getRange('L6:N103').clearContent();
    sheet.getRange('P6:R103').clearContent();
    sheet.getRange('T6:V103').clearContent();
    sheet.getRange('W6:W103').clearContent();
}

1. Make a function. Think of this as the program that is running. Ours is called clearRange.

2. Inside the function (between the curly braces) we make a variable (var sheet). This is the Quarter's sheet

3. Then we clear specific ranges in that sheet by using what's called dot notation: sheet.getRange('D6:F103).clearContent();

The dot notation lets us use two built in "methods": getRange() and clearContent(). These do exactly what they sound like. They get a range of cells that we specify and then they clear the contents of that range.

Methods are built-in pieces of code that are pre-written to do certain things. Similar to the functions you already use in a spreadsheet like SUM or AVERAGE.

Much More to Learn

As you can imagine, there is a ton of complexity and power available by unlocking a programming environment in an already powerful spreadsheet.

The documentation for developers where I've learned a handful of methods to manipulate my own spreadsheets is overflowing with hundreds of other methods and information.

It can be overwhelming when you are just starting out, but be encouraged: it's very possible to learn a little at a time for your specific uses.