A Google Sheets Script Beginner’s Guide
Google Scripts lets you add code to your spreadsheet to automate data entry, bring external data into your sheets, or run calculations on a periodic schedule.
If you use Google Sheets, you may be interested to know that Google Scripts can greatly enhance your spreadsheets.
Think of Google Scripts like VBA in Microsoft Excel. It lets you add code to your spreadsheet to automate data entry. You can also use it to bring external data into your sheets. Best of all, you can run calculations on a periodic schedule, even when you don’t have the spreadsheet open.
In this article, you’ll learn how to enable Google Scripts and how to write and run a simple script.
How to Use Google Scripts
There are a few things in Google Sheets that you need to enable before you can start writing Google Scripts there.
Select the Tools menu and select Script editor from the menu.
This will open the Google Scripts editor in an Untitled project window.
This script is actually contained inside your Google account within the Google Scripts tool. This particular project is connected to the spreadsheet you’re currently working on.
You can write a script, but can’t run it until scripts are actually enabled in the sheet. You’ll need to do this the first time you run it (as you’ll see below).
To see this in action let’s make a simple script.
Create Your First Google Script
In this example, you’re going to write a script that adds up all of the quiz and final exam scores for the B column (Chris’s scores).
This is the example spreadsheet.
Back in the Google Scripts window, paste the following code inside the function myFunction () curly brackets, like so:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0]
var range = sheet.getRange(2,2,5);
var values = range.getValues();
var sum = 0
for (var row in values) {
for (var col in values[row]){
sum = values[row][col] + sum;
}
}
sheet.getRange(7,2).setValue(sum);
}
This code may look complicated, but let’s break down exactly what it’s doing. It’s simpler than it looks.
Let’s break this code down one line at a time.
Defining the Variables
1. var ss = SpreadsheetApp.getActiveSpreadsheet();
This line retrieves the current spreadsheet that’s active. Since you’re only running this script when the spreadsheet you want to run the calculation on is active, it’ll always get the correct spreadsheet. The sheet gets saved as an “object” variable called “ss”.
2. var sheet = ss.getSheets()[0]
This gets the first sheet and saves it as an object variable named sheet. There’s an alternative function you could use to get the sheet by the sheet name.
That would be var sheet = ss.getSheetByName(“Sheet1”);
You can use either function you like.
3. var range = sheet.getRange(2,2,5);
This function gets a range of values by row, column, and number of rows. In this case we’re pulling all of the values starting at the second row, second column, and getting values five rows down.
The variable “range” becomes what’s known as an “array”. An array is a variable that contains multiple values. In Google Script, you refer to any of those numbers using a special element addressing scheme with brackets, as you’ll see below.
4. var values = range.getValues();
The getValues() function actually pulls the values from the range object you defined above.
5. var sum = 0
This is just a single number variable that we’re initializing with the number 0. This becomes the start of how we’re going to sum up all of the numbers in the column.
Creating the For Loops
6. for (var row in values) {
This is what’s called a “For” loop. This kind of loop steps through a specific number of items in a list. In this case, it’ll go through the “values” array we discussed above. This first loop will loop through all of the rows in the array. In this case there will only be one.
Note: array rows are not the same as spreadsheet rows. When the array loaded the values from the column in the spreadsheet, it put them into a single line (or row) of data, like this: “89, 92, 78, 98, 89”. So, in this case the array has one line (row), and five numbers across (columns).
7. for (var col in values[row]){
This second for loop will go through all “rows” in the array. In this case it’ll loop through all 5 values.
8. sum = values[row][col] + sum;
Every time the loop runs, this statement will keep adding up the sum of the new value to itself. By the time all 5 loops happen, all 5 numbers should be totaled into the “sum” variable.
9 sheet.getRange(7,2).setValue(sum);
This is a two-function statement. The getRange function points to the cell at the 7th row and the 2nd column. The setValue function then places the value of the “sum” variable into that cell.
Now that you understand how each line of the code works, it’s time to see it all in action.
Saving Your Google Script
Once you’re done typing your Google script, it’s time to save it and step through the code. First, type over “Untitled project” to give your project a name. Then, select the floppy disk icon in the menu to save the script.
You may see an error when you try to save. Don’t panic. This is actually a part of the debugging process. Pay close attention to the line number where it says you have an error. Look closely through your code and resolve any typos or syntax errors you may have.
In this example, my “for” loop on line 8 was missing the opening “(” symbol. Once fixed, try saving again. Once you can save your code, Google considers your code syntax okay.
This doesn’t mean your code will run as intended, it just means it can “compile” fine, without any syntax issues.
Running Your Google Script
Now select the play icon at the top to step through your code. Since this is the first time you’re running your new script, you need to provide the Google Script app with permission to access your spreadsheet (data) on your Google Account. Select Review Permissions on the pop-up window.
You’ll need to sign into your Google account. And since this is a custom app and not one registered with Google, you’ll see a warning that the app isn’t verified. Select the Advanced link and then select Go to Sample Google Script (unsafe) to continue.
On the next screen, you’ll need to select Allow to give Google Script access to your Google account. Once you do, the script will run.
If you don’t see any errors, switch back to the spreadsheet and you should see the results from your script have appeared at the bottom of the first column.
Writing Advanced Google Scripts
This was a very basic Google script that simply added up the cells in a single column. If you wanted to add variables to the two For loops in the code, you could loop through all of the columns as well and provide a summary in row 7 for all of them.
Google Scripts offers all sorts of possibilities, like sending email from a spreadsheet. You can even tap into special Google APIs that let you integrate other Google apps like Gmail, Calendar, and even Google Analytics. Once you start learning how to write Google scripts, the possibilities are endless.
Leave a Reply
Leave a Reply
