The purpose of GScript within the GDrive ecosystem is two-fold: cross-app connectivity & in-app flexibility. We previously covered technical & non-technical ways of automating across different GDrive apps. This article series tutorial explores the second concept, in-app flexibility through GScript.
You’re likely using Google Sheets already, so why not take a few minutes to learn how to customize & automate it to your particular needs? With Google Script, Google has made it extremely easy for anyone to extend Sheets.
This tutorial, for example, came out of a simple, personal need. I receive monthly dividends through Robinhood, yet they haven’t extended their UI to dividends quite yet — so how can I collect this data & use it to calculate a few fun points of observation (total received, monthly growth, etc…)?
The goal throughout this tutorial is to write a GScript attached to a single Sheet spreadsheet that takes a messy, copy/pasted list of historical dividends as an input & outputs a beautiful, useful, dashboard:
This article in the series, the very first one, is essentially a “Day 0” GScript tutorial for Google Sheets. The point of this tutorial, as opposed to heading over straight to the documentation, is to showcase the very basics through example — by doing instead of reading.
At the highest level, Google Apps Script (GScript for short), is a scripting platform for light-weight development across the entire Google Suite ecosystem. Each GSuite “app” (Sheets, Docs, Calendar, etc…) is referred to as a GScript service. Each service, such as the Google Sheets service we’ll strictly focus on throughout this article, is made up of multiple classes — each with the typical class structure of properties & methods.
Our journey starts with the most commonly used, basic classes. Our goal for this first article is simply to setup our data table on a fresh page with the click of a button. Driving down to specific behaviors, once the user has copy/pasted a list of dividend data from the Robinhood website, we need to account for the following functionality:
We begin our script the same way every time: open up a fresh Google Sheet, hover over “Tools” in the ribbon bar, & smash that “Script Editor” button — this should open up the lite-weight, GScript IDE. I refer to it as a lite-weight because, while it gets the job done, it’s missing quite a bit of functionality one would expect from an IDE in 2020 (such as auto-completion). The IDE should open to a single empty “myFunction()” method — this is our starting point.
The parent class for the entire Spreadsheet service is referred to as the SpreadsheetApp class. As one would expect, this is almost always the very first line of code (LOC) when working on GScript projects as it initializes the Google Sheet service. Additionally, the first call is traditionally used to get the active spreadsheet attached to this GScript; most examples of this use “ss” as a variable to represent the “spreadsheet” object:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
}
Admittedly confusing, the Sheets service contains two different classes that appear very similar in name. In general, the previous class has fewer methods than this second class; this isn’t a guarantee, but once the Sheets service & the active spreadsheet are initialized, it’s highly likely that you won’t use the root SpreadsheetApp class again. For accessing & modifying, it’s this second class, Spreadsheet, that contains the methods we’ll use.
The behavior of calling the similar activeSheets() method can be a bit unpredictable, so, for this walk-through, we’re going to label both our sheets: “RHDividends” (original) & “RHAnalysis” (output). We haven’t created this second sheet yet, so for now just change the label on the original sheet. Instead of querying for an active sheet, we can instead query for a specific sheet by name through the Spreadsheet method: getSheetByName():
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("RHDividends");
}
Now that we know how to access our original input sheet by setting it to the variable “inputSheet, ” we proceed to creating our new sheet that’ll contain all output — our dashboard. Using the same class, we instead call the insertSheet() method & set the new sheet name to “RHAnalysis.”
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("RHDividends");
var outputSheet = ss.insertSheet("RHAnalysis");
}
This tiny function with three LOC is now testable — let’s go ahead & add a button to fire myFunction() & see the result.
Creating an actionable button is a bit awkward in Sheets since there’s no direct way to add a “button.” Instead, we have to first manually draw & insert a shape. To do this, hover over the “Insert” option in the ribbon bar & click on the “Drawing” option — in the new popup, hover over “shapes” & click into the plain rectangle shape. We could customize, but for now, let’s just add text to the button (by clicking in the middle of the rectangle), & insert our faux-button to our original (“RHDividends”) sheet. Next, right-click on this newly-inserted element, the following sub-menu should pop-up:
Go ahead & click “assign script.” The following overlay asks “what script do you want to assign?” The verbiage here is a bit confusing since our script is already attached to this sheet. What the overlay should say is: “what function do you want to assign?” We only have a single function as-is, so go ahead & fill in the overlay input with the name of the function: “myFunction.” We’re ready to test our button! Assuming myFunction() contains no errors, clicking the button should lead to the following actions:
Excellent. We can now insert a new sheet, the structure for our future dashboard, with the click of a button. This is neat & all, but what happens if you click the button again? Once the RHAnalysis sheet already exists? As expected, Sheets throws an exception since a sheet with the name “RHAnalysis” already exists. Still, we’d like continuously test our progress as we move further, therefore, we’re going to add a little fail-safe into our script in case the sheet already exists:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("RHDividends");
if(ss.getSheetByName("RHAnalysis")) {
ss.deleteSheet(ss.getSheetByName("RHAnalysis"));
}
var outputSheet = ss.insertSheet("RHAnalysis");
}
Perfect, now we’re checking if RHAnalysis already exists within “ss”(the active spreadsheet); if it does, we erase it with a deleteSheet() method.
With our empty RHAnalysis sheet generated, it’s time to generate & format our data table heading:
Now that we’re working within a single sheet, we need a way to access & modify the cells within a specific range. Before we introduce the Range class, we’ll use the most frequently-used Sheet method used to get a specific range: getRange(). There are many different variations of syntax for this; in our case, we’re going to use the most verbose version so that we a high amount of visibility on how it works:
sheet.getRange(row, column, # of rows, # of columns)
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("RHDividends");
if(ss.getSheetByName("RHAnalysis")) {
ss.deleteSheet(ss.getSheetByName("RHAnalysis"));
}
var outputSheet = ss.insertSheet("RHAnalysis"); var analysisHeader = outputSheet.getRange(1,1,1,5);
}
With our header range saved as the analysisHeader variable, we’ll now explore the Range class methods that we’ll use to wrap up this first part.
Along with Spreadsheet & Sheet, the class Range is one of the classes most frequently used when working with Sheets; it’s used to access, format & update cells a single or group of adjacent cells. In our case, we need to set the value of the five labels & format them as headers by bolding them. Let’s do that now by covering two Range methods.
Set Values
Both setValue() & setValues() are valid methods when setting the value (numeric, string, boolean, or date) of a range or single cell. While simple & powerful methods, they’re relatively expensive computationally; therefore, when possible, us the latter, plural, setValues() method. When setting multiple values at a time, make sure that the # of values set exactly matches the dimensions of the range object.
Format
From setFontColor(), to setFontFamily(), to setFontWeight(), there are multiple Range class methods that we can use to pretty-up a range of cells. For our purpose, we’ll use the setFontWeight() method to bold our analysisHeader range:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var inputSheet = ss.getSheetByName("RHDividends");
if(ss.getSheetByName("RHAnalysis")) {
ss.deleteSheet(ss.getSheetByName("RHAnalysis"));
}
var outputSheet = ss.insertSheet("RHAnalysis"); var analysisHeader = analysisSheet.getRange(1,1,1,5);
analysisHeader.setValues([["Date", "Ticker", "Shares", "Payout", "Total"]]).setFontWeight("bold");
}
GScript is based on JavaScript so we can expect similar behavior in syntax; with this in mind, we’re taking advantage of method-chaining in the line added above by calling setValues(), then setFontWeight() one after the other.
And we’re good to go! Save the file & let’s test it out. If everything is done correctly, the following will happen:
And we’re done! At least, for this first part. Our script is tiny with only ~9 LOC, yet it packs enough functionality to setup our future dashboard. The focus of this piece mainly limited to setting up the “front-end” of our project, the next article takes a deeper dive into data management.
Specifically, we’ll capture the copy/pasted data list & sort it correctly in order paste & match it to our header now set in the RHAnalysis sheet.
