Introduction to Google Script

​By Lee Allard, Director of Institutional Research, Siena College

Many IR practitioners are familiar with Visual Basic for Applications (VBA), which allows a user to do custom programming in Excel or other Microsoft applications. VBA allows users to perform various tasks that would not be possible in Excel itself, thereby extending the functionality of Excel.

Many users (or entire institutions) are increasingly using Google for various functions, including the creation of spreadsheets and documents. Google offers an application similar to VBA, called Google Script, which allows the same kind of extended functionality in Google applications as VBA does in Microsoft applications. If you use Google and are familiar with VBA programming, you can easily learn to use Google Script. This Tech Tip provides a very basic introduction to getting started in Google Script. From there you’re only limited by your imagination (and time constraints, of course).

As an example, we will create a very simple Google sheet and use Google Script to perform some basic calculations in that sheet. To get started, from Google Drive click the red “Create” button and select the “Spreadsheet” option.

Gscript1.JPG
This will open up a new spreadsheet that you can populate with data as follows:

Gscript2.JPG

Our goal is to use Google Script to calculate the total hours for each student. (Obviously in the “real world” we would just use a simple formula to accomplish this task. But for illustrative purposes, we’ll do it here with Google Script.)

To open Google Script, click on the “Tools” button at the top of the spreadsheet, and click “Script editor.”

Gscript3.JPG
You should see a pop-up box like this:

Gscript4.JPG

Obviously you have various choices here, including some helpful tutorials, but for now just click “Blank Project” to get started. The the screen should look something like this (which will seem somewhat familiar to VBA users when creating a new module).

Gscript5.JPG

Change the name from “myFunction” to “TotalHours” (or anything you want it to be, as long as the name doesn’t include any spaces).  Then click the save icon (i.e. the picture of the floppy disk, as if anyone uses floppy disks anymore!)

Note that when you run a piece of code for the first time, you may see a pop-up box requesting permission to run the code. Just click “OK” then “Accept” and you should be set.

As noted above, our goal here is just to write a simple piece of code that will calculate the total hours for each student in the data sheet. Here is what the script editor looks like with the created code:

Gscript6.JPG

The code is repeated below, in a format that is easier to read and copy:

function TotalHours() {
 
  // Identify and activate the sheet we are using
  var spreadsheet = SpreadsheetApp.getActive();
  var dataSheet = spreadsheet.getSheetByName('Sheet1');
  dataSheet.activate();
 
  // Create an array that will be used to store the calculated
  // values for total hours for each student
  var TotalHours = new Array(10);
 
  // Calculate the total hours by adding together the data from
  // columns B and C
   for (var i = 2; i <= 5; i++) {
       TotalHours[i] = dataSheet.getRange(i,2,1,1).getValue() +
         dataSheet.getRange(i,3,1,1).getValue();
  }
 
  // Copy the total hours from the array to the fourth column
  // of the data sheet
  for (var i = 2; i <= 5; i++) {
    dataSheet.getRange(i,4,1,1).setValue(TotalHours[i]);
  }
 
}

Note that two slashes (//) are used to mark off comments which will not be run in the code.

The first three lines of code (after the function statement) simply identify and activate the sheet you are using.  The fourth line of code initializes a new array called TotalHours that will be used to store the calculated values for total credit hours for each student. (The number 10 in parentheses defines the size of the array; obviously for a larger file you would want to increase this number.)

Lines five and six calculate the total hours by adding together the data from columns B and C (i.e. the fall and spring values). We have defined a loop (using the “for” statement) to go through lines 2 through 5 of the data sheet.  At this stage the total hours are stored in the array that was created called TotalHours.

(Note that the getRange statement includes four parameters, e.g. (i,2,1,1). These parameters identify the appropriate range on the data sheet. In this case we are only activating a single cell, so the last two parameters are 1. If you were activating a larger range (say for a copy operation) the last two parameters would be greater than one to identify the block of cells that constitute the range. )

Finally, a second loop in the last two lines of code copies the total hours from the array (TotalHours) to the fourth column of the data sheet.

Note that discrete sections of code are bracketed using forward and backward braces, i.e { }. In the script editor, if you place the cursor on one brace it will highlight the corresponding brace, which helps you to ensure your code is structured correctly.

Next, save the script that was created by clicking on the save icon. To run the code, click on the “Run” button in the menu bar. The data sheet should look like this:

Gscript7.JPG
As shown above, column D now contains the total number of credit hours.

This is a very simple example of using Google Script, but hopefully it’s enough to get you started. If you have experience with VBA, you will be able to learn the basics of Google Script fairly quickly, and will soon be writing more complex (and useful) code.

In closing, I’ll add just a few general comments. Google Script is based on JavaScript, in much the same way that Microsoft VBA is based on Visual Basic. But as with VBA, Google Script contains a large number of statements and commands that are specific to the Google environment.

There are lots of good books and good online resources for learning more about JavaScript. An online resource that I’ve found to be helpful is w3schools.com. There are fewer resources available for Google Script. Google does provide some tutorials and reference guides, which are useful on occasion. Clicking the “Help” button on the script editor will take you to some of these resources, as shown below. Clicking the “Help” button on the script editor will take you to some of these resources, as shown below.

Gscript8.JPG

Good luck and happy Google Scripting!

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 7
 
JR posted on 2/12/2015 6:48 PM
Just did this from start to finish and... it worked exactly like you said it would! I have been away from VBA for some time but just needed to get back into it recently, so this is a very timely tip -- and the link to w3schools is also a great asset to know about! Being able to readily and efficiently manipulate data and being comfortable in a variety of coding landscapes is a skill that cannot be underemphasized - thanks for the motivation and good documentation!
Keith posted on 2/13/2015 10:35 AM
Lee, Thank you for opening my eyes to Google Scripts! I have been using Google docs and spreadsheets for years and had no idea we could use programming to enhance them. This will become a very useful tool for us as we look to share more complex data sets via Google Apps.
Ishuan posted on 2/26/2015 3:48 PM
Thanks Lee for the tech tips. I was not familiar with Google script. This tips will get me started on a new tool for data management.
Lisa posted on 3/12/2015 3:15 PM
Thank you for this insight into Google Scripts; I was not at all familiar with it or the use of VBA. Your documentation is excellent. We all love new tools that help streamline our day-to-day workloads.
Shawn posted on 3/13/2015 9:34 AM
Yet another reason why Google tools are becoming increasingly useful. Thank you for the clear and concise overview!
Lisa posted on 3/13/2015 6:40 PM
Very useful tutorial! Thank you. I haven't used VBA yet, and this helps me realize its functionality. The only part I didn't follow was with the array size (probably due to my unfamiliarity with VBA). I'm not following how you determined that the array size is 10, given that the dataset has 5 rows and 4 columns. Could you explain this a little more?
Gary posted on 9/8/2015 12:57 PM
This tip represents the right thinking, for sure - investing in more powerful and efficient tools. Personally, I'm not attracted to a proprietary product that only exists and runs on Google's servers. User control is severely limited with products like these. When they pull the plug on this project or revise it in ways that break existing code, the user loses. There's nothing wrong with dabbling, but any effort beyond that would be better spent on learning an open-source workhorse language like Python that respects user freedom and control, and has a robust user community. w3schools is a great resource that I forgot about, but I plan to check it out again soon. Thanks!