A

P P E N D I X

Google Apps Script

INTRODUCTION
Google Apps Script (or Apps Script in short) is the accompanying programming interface
that is tightly coupled with Google Sheets to perform more advanced automation. It has as
its backbone the JavaScript (JS) programming language and added elements to permit it to
interact with the component objects of Google Sheets (and also other applications in the
Google Productivity Apps suite).
In a Google spreadsheet, select Tools/Script editor to access the Script Editor. With
this, codes written thereafter will be stored with this file. Once invoked, a dialog as shown
in Figure B-1a will be displayed. Click Blank Project. The Script Editor and Integrated
Development Environment (IDE) window as shown in Figure B-1b will appear. Click on
Untitled Project to rename the project. Then using code stub myFunction, renaming it if
you like, to write your own function. In Apps Script, the reserved word function is singly
used to denote both function and subroutine, as opposed to separate reserved words such
as Function and Sub respectively in Excel VBA. Further explanations are in Note 17.

Figure B-1a Google Apps Script Startup

Figure B-1b Google Script Editor and Integrated Development Environment

Let us now review the elements of the IDE:

In the top-left corner, you will see a Project Explorer window that displays your
Google Script (gs) files as tabs. The first script file by default is named as [Link]. To
rename or delete it, click on the triangle symbol on its right. You can add new script
files by selecting File/New/Script file.
Within each project, there are Google Sheet Objects such as Spreadsheet, Sheet,
Add-ons, and Range.
To the right, you will see the Code Window. This displays the macro code in your
active script file. It is a good practice to put codes related to each object in the object
when it is not shared with others.
In the Debug and Run items of the menu, there are features that you can use to
support your macro development work. The IDE also highlights syntax errors made as
you type your code.
When you edit and test your macro, remember to use the Save button often to save
your work. Sometimes, for example, when the macro runs into an infinite loop and
crashes the IDE, all of your work may be irrecoverably lost.

FURTHER REFERENCES

Google Apps Script Support site

o [Link]
o [Link]
o [Link]
Java Script Support site
o [Link]
o [Link]
Leong, 2014. Google Sheets Web Resources
o [Link]
psScript
2

o [Link]
eAppsScript
Maguire, 2014. Google Spreadsheet Programming: Use JavaScript to Build
Spreadsheet Applications in the Cloud, Leanpub.

APPS SCRIPT PRIMER

For non-programmers using Excel spreadsheets, macro recording is the fastest way to
perform simple automation. It is the simplest form of programming in that you do not
need to know how to write a single line of code. The codes can be automatically created.
All you have to do is to record your keyboard and mouse actions as you work on a
spreadsheet. The saved sequence of commands representing the executed keystrokes and
mouse movement is a macro. The actions, once recorded, can be repeated by running the
macro.
Google Apps however does not have this macro-recording feature. Therefore, all
feature-extending codes in Google Sheets have to be written by hand and thus the need to
learn JavaScript and how it interfaces with Sheets. Even then, handwritten Apps Script
codes cannot manipulate Google Sheets objects in the way VBA does in Excel. So
alternatively, Excel power users and VBA programmers may want to first construct their
spreadsheet models and record their macros in Excel, and then convert them to Apps
Script.
Nothing beats learning from interesting and practical examples. There are many
examples of them in the exercises and tools in this book. Look out for those Excel 2007
workbooks with the xlsm extension in their file names. Particularly worth an extra mention
are the completed projects and tools in Chapter 9, especially the Useful Macros tool. Do
note that xlsm files uploaded to Google Drive will not convert and open as a Google Sheet
file. You should first Save As them to become xlsx files before uploading and opening in
Google Drive.

NOTES
Note 16: MACRO RECORDING
Let us now follow how a simple macro recording is done in Excel and then attempt to read
and understand the codes created. You may skip this note and come back to it later after
reviewing Note 17.
Example: Record a macro to do automatic copying and pasting of values
1. In a new workbook, select Developer/Record Macro (or Tools/Macro/Record new
macro in Excel 2003).
2. A Record Macro dialog box as shown in Figure B-2 will appear. Enter the macro name
of your choice, assign a shortcut key (if needed), select where to store the macro (the
default location being the current workbook), and finally enter a short description. For
this example, we will accept all the given defaults.

Figure B-2 Macro Recording Dialog

3. Click OK. Once it is clicked, whatever actions you do with your mouse or keyboard,
such as selecting a cell or scrolling up and down the worksheet, will be recorded as
codes. [In Excel 2003, a small Stop Recording dialog (or toolbar) will appear when
macro recording starts. It has two buttons, the Stop Recording button on the left and
the Relative Reference button on the right. You should never click the dialog-close (X)
button in the upper right-hand corner as this will make the dialog box disappear. If
you ever make that mistake, you can recover the dialog box by selecting from the
main menu View/Toolbars, and then select Stop Recording.]
4. Deactivate the Use Relative References button if it is activated, select cells B2:B5.
Right-click and select Copy in the pop-up side menu (as shown in Figure B-3).
5. Select cell C2 (by either clicking with the mouse or keying the right-arrow on the
keyboard), right-click, and then select Paste.
6. Click the Stop Recording button to end the process.

Once recorded, the macro can be used over and over again. To run the macro, select
Developer/Macros (or Tools/Macro/Macros in Excel 2003) or key Alt + F8 and select the
macro to run.
Next, let us review and understand the codes you have recorded.
1. Key Alt + F11 to launch the VB Editor (VBE) interface.
2. Select Module1 and Macro1 to show the codes in the Code Window, as shown in
Figure B-4.
3. The codes begin with Sub Macro1( ) and end with End Sub. These mark the
beginning and the end of the subroutine.
4. Within the subroutine,
a. Line 1 = Cell range B2:B5 is selected.
b. Line 2 = The selection is copied.
c. Line 3 = Cell C2 is selected.
d. Line 4 = The copied selection is pasted with its upper-left corner in the selected
cell in the active sheet.

Figure B-3 Step 4 of Macro Recording

Figure B-4 Codes for Macro1


Notice how easy it is to understand the codes generated. One way to speed up your
learning of the VBA code and macro recording is to have the Excel and VBE windows
opened side by side when you next record your macro. See how each line of code is
recorded with each action you do in the Excel window. As an additional check, you can
reverse the process and step through the code, making it run one line at a time to watch
its action played out on the worksheet. To do this, put your cursor in the subroutine code
and select from the VB Editor main menu Debug/Step Into (or key F8). Keep keying F8 to
see how the highlight moves from one line of the code to another as the code is run one
step at a time.

Absolute and Relative Referencing
We have covered absolute and relative cell referencing in Note 3 of Appendix A. When you
copy and paste a spreadsheet formula from one cell to another, it automatically adjusts
the cell references in the formula unless they have been made Absolute using the $ sign.
There is a similar concept in macro recording. You should try to keep the Use Relative
References button deactivated as soon as it is not required so that cell referencing will be
absolute by default. What this means is that every selection of a cell would refer to that
cell and is not interpreted as some relative movement from the last active cell. You can
record the same macro as in the earlier example, but this time, select cell B2 before
recording the macro and activate the Use Relative References button in the beginning of
the macro (and thereafter remember to set it back). Study the difference between the two
approaches.
1. Select Developer/Record Macro (or Tools/Macro/Record new macro in Excel
2003).
2. In the dialog box as shown in Figure B-2, enter Macro2 as the macro name, and leave
the other inputs as default values.
3. Click OK.
4. Activate the Use Relative References button. Once activated, you will see an orange
shaded border surrounding the button.
5. Select cells B2:B5, right-click, and select Copy.
6. Select cell B2 and key the right-arrow on the keyboard. Right-click, select Paste from
the side menu.
7. Deactivate Use Relative References.
8. Click Stop Recording to end the process.

Let us read the codes for Macro2 as shown in Figure B-5.
Line 1 = A column of four cells from the current active cell is selected. Since cell B2
was selected before the macro was recorded, this means cells B2:B5 is selected.
A1:A4 reference here is with respect to the active cell as the origin A1 cell.
Line 2 = The selection is copied.
Line 3 = A relative offset of zero row down and one column to the right from the
active cell (cell B2 in our case) is selected. A1 here denotes only one cell is selected.
Movements up and to the left are recorded as negative argument values in Offset.
7

Line 4 = The copied selection is pasted into the selected cell.

Relative referencing provides greater flexibility as new active cells (to be selected
before the macro is run) can be any cell in any worksheet. For this demonstrated macro, it
means that we can copy and paste any column of four cells to its immediate right.

Figure B-5 Codes for Macro2

Quick Tip 1
o After each copy and paste sequence of operations, Excel leaves behind highlighted
cell range selections and dotted box outlines in the worksheet. It is a good
housekeeping practice to end your macro with the cursor place in the appropriate
cell and clear away all such unwarranted distractions. In the spreadsheet, just
point your mouse to the end cell location and key Esc.
o The corresponding macro statements at the end of your Sub are as follows:
Range(A1).Select
[Link] = False

Note 17: APPS SCRIPT GRAMMAR

So far all the programming we have attempted is by macro recording, letting Excel
automatically generate the codes in the VBA language. Google Apps does not yet have this
macro-recording feature and it does not use VBA; it uses Apps Script, a JavaScript-based
language. The preceding Note 16 is provided here for completeness, as an attempt to
provide a bridging connection between VBA and Apps Script. Do not let Note 16 bother
you if you do not quite understand it.

Apps Script is based on the object-oriented Java and JavaScript languages. Thus it is
useful to first understand the hierarchy of objects to better use the properties, methods,
and events of the objects. The hierarchy of Google Sheets objects is given as follows:

SpreadsheetApp Spreadsheet Function

Add-on

Spreadsheet Chart



Sheet Range




Cell

Properties
Each object has its own set of Properties that describes it. Some of the properties of the
Range object include row height, column width, and font.
You can set the value of cell E7 to 10 and change its font color to red using the code
below:

var sheet = [Link]();
[Link](C7).setValue(10);
[Link](C7).setFontColor(red);

In general, any property can be set using the syntax below:
[Link]()

Methods
Each object also has its own associated set of Methods, which are actions that can be
performed on it. For example, one of the methods for the Range object is ClearContents.
You can clear the contents of a selected range C7:D9 using the code below:

var sheet = [Link]();
[Link](C7:D9).clearContent();

In general, any method can be activated using the syntax below:
[Link]


Quick Tip 1
Apps Script programming is quite user-friendly. It provides coding help as you type in
the code. After you have entered the dot (.) following the object name, a drop-
down list will appear as shown in Figure B-6, listing all the available properties and
methods associated with this object. Do remember that JavaScript and thus Apps
Script are case-sensitive. That is, you have to write given keywords as provided; do
not change their letters in lower cases to upper cases, and vice-versa.

9

Figure B-6 Drop-down List for Object

If you have been working with Excel VBA, here is a general difference between that
and Apps Script that you should note. When an Excel object (e.g., cell range), is referenced
in VBA, this object, unless explicitly coded, is assumed to be in the currently active Excel
container (e.g., the active workbook and active worksheet). In Apps Script, each reference
to a Google Sheets object must be fully qualified; you have to specify the spreadsheet
document and sheets explicitly as shown below:

numCars =
[Link](C7).getValue();
If you want to get another spreadsheet document and sheet, this can be as show
below:

//Open your spreadsheet document ----[Link]();
[Link](Sheet1).Activate;
var sheet = [Link]();
numCars = [Link](C7).getValue();
//Close your spreadsheet document ----
You may need to remember first the active spreadsheet document, sheet, and cell,
and return there after you are done so that it would not affect other macros. This is done
as follows:
var oActiveSpreadsheet =
[Link]();
var oActiveSheet =[Link]();
var oActiveCell = [Link]();

10

//Earlier lines of code here ----[Link](oActiveSpreadsheet);

[Link];
[Link];
It may be more efficient, after the required spreadsheet is made active, not to have to
move the cursor around the sheets and directly work with Google Sheets objects as
follows:

numCars =
[Link]().getSheetByName(Sheet1)
.getRange(C7).getValue();

Quick Tip 2
The // sign (and also htmls <!--) is the shorthand for comments. All statements that
appear after the sign to the end of the line will be regarded as mere comments and
not code. You can sprinkle your macro with comments to make it easier to
understand and read. For comments that span multiple lines use /* to start and */ to
end. The other best practice is to indent lines when they are part of another
programming structure. In this case, you can see that all statements between a
function start and end are indented to show that they are part of the function. You
can observe how they are used in other examples later in the chapter.

Similarly, when you record macros in Excel, values can only be transferred from one
set of cells to another using Copy and PasteSpecial as shown in Note 16. When you edit
the macro, rewrite it in App Scripts to work directly with objects as shown below.
var sheet = [Link]();
var range1 = [Link](B2:B5);
var range2 = [Link](C2:C5);
[Link]([Link]());

The above transfers values between two cell ranges. When there are only single cells
involved, the code is even simpler:

[Link](C2).setValue(
[Link](B2).getValue());

Other than transferring data values between cells, you can set formulas in any cell in
the spreadsheet as demonstrated below:
[Link](A12).setFormula (=SUM(A2:A11));

11

When the choice of cell uses depends on other values, use the Offset method as
follows:

var r = 3; var c = 1;
[Link](C3).setValue(
[Link](A1).offset(r,c).getValue());

[Link](A1).Offset(3,1) refers to cell B4. The above thus set the
value in cell B4 to cell C3.

Subroutine and Function Procedures
A Sub procedure (or subroutine) is a set of codes which when executed performs a series
of spreadsheet actions. Each recorded Excel macro is a Sub. It however can be constructed
as well. For example, a simple subroutine written in VBA to prompt a greeting is:

Sub SayHi( )
MsgBox(Hi!)
End Sub

The equivalent in Apps Script is

function sayHi( ){
[Link](Hi!);
}

Note that Apps Script uses the keyword function instead of Sub. This is not a mistake, but
the notation chosen by Google for both subroutines and functions. As a JavaScript-based
language, it uses the { and } curly brackets to denote start and end respectively of the
function code, as well as of code segments. Also do remember to end each line of code
with a semi-colon, a mistake common among beginners. As in Excel VBA, each Apps Script
can be assigned to a drawing shape in the Google Sheets and clicking on each shape as a
button will run the assigned macro.
A function procedure is also a set of codes. However, its primary purpose is to return
a result computed with the inputs offered to it. You may have used some of the given
spreadsheet functions like AVERAGE and SUM, and soon you will be able to create more
useful functions of your own. Let us begin by writing a simple function to compute the
cube root of a number. In order for the function to return the computed value when the
function is used, the result variable must bear the functions name, which in this case is
CubeRoot.
In an empty space in a workspace, type in the following:
function cubeRoot(number) {
return number^(1/3);
}

12

Keyword return is the main distinguishing element found in function procedures,

which is not used in Apps Script subroutines. As its name suggests, it defines the value that
is to be returned. With the function done, you can use it in the worksheet. Key in formula
=CubeRoot(8) into a cell and see the number 2 appearing in it. All worksheets in the same
spreadsheet document can use this function. The function you have created can also be
used by other subroutine and function procedures in your workbook.
Though a subroutine procedure does not directly return computed results, data
values can be passed indirectly to and from it through the variable arguments specified
within the brackets next to its name, or cells in the worksheets. A subroutine with
arguments can only be called by another subroutine and not run from Google Sheets
directly as a macro since there is no way to pass the argument values to it that way. We
will leave this as a future topic for you to explore on your own.

Variables and Declaration
In many programming software languages, variables to hold values, whether entered or
computed, need to be defined first. Each variable must be of a data type, namely integer,
real, text, Boolean, array, or object. JavaScript, on which App Script is based, is simpler. It
allows only three primitive data types, namely numbers, strings of text and Boolean, and
has keyword var as its only variable declaration.
To declare a variable to be of a certain data type, you use the following syntax:

var variableName

The keyword Const used in VBA to declare the variable as unchanging and permit a value
to be assigned is not supported in JavaScript.

Here is a simple example given here as reference to help you understand better.

function Mysub() {
var k = k + 1

}

Declaring Arrays
An array, more commonly known as a matrix, is a group of variables sharing a common
name. Arrays can be 1-dimensional or multi-dimensional.
An example of a 1-dimensional array declared to store the identification number of
compact disks is:

var cd_id = new Array(); //or
var cd_id = [];

An example of a 2-dimensional array declared to store the identification number of
compact disks is:
13

var cd_id = new Array(new Array());

cd_id[i][j] =

Note 18: MORE PROGRAMMING

Up until this point, you can write simple Google Apps Script programs in which all the lines
of code are sequentially executed. However, there will be many instances where we would
like the program to skip some steps or go directly to one set of steps or another, on
satisfying or not satisfying a test condition, respectively. Here are some examples of the
most useful ones.
If-Then-Else is one of the most useful statements, which allows the program to
execute alternative codes depending on whether the test condition results in a TRUE or
FALSE. If the result is TRUE, the codes following Then (up to the line containing the Else
keyword if it exists, or up to End If if it does not) will be executed. Otherwise, the codes in
the lines following Else (up to End If) will be executed.

if (testCondition) {
doSomethingWhenTrue
}else{
doSomethingWhenFalse
}

An example is given below:
function ifThenElseDemo() {
var rating = [Link](Enter rating (1 or 2): ,
your rating here, [Link].OK_CANCEL);
if (rating == 1) {
[Link](You have entered 1.);
} else {
[Link](You have entered 2.);
}
}

Unlike Excels VBA, the equal comparison in Apps Script is done using the twin equal
symbols == and not equal comparison is !==. In particular, rating == 1 compares variable
rating against the value 1. This is not the same as setting the value of variable rating to 1.
You can already deduce from above that exclamation symbol ! is the NOT operator. The
other non-equal comparison operators for greater than, less than, greater or equal, and
less than or equal remains the same: >, <, >= and <=. The other logical operators are &&
for AND and || for OR.

Switch-Case is useful when the test condition can result in more than two
alternatives, thus requiring more paths for the codes to continue the operation.


14

switch (expression) {
case condition 1: statement set 1
break;
case condition 2: statement set 2
break;
case condition 3: statement set 3
break;
default: statement set n+1
}

An example is given below:
function switchCaseDemo() {
var rating = InputBox(Enter rating 1, 2 or 3: );
switch rating {
case 1:
[Link] (You have entered 1.);
etc
break;
case 2:
[Link] (You have entered 2.);
etc
break;
case 3:
[Link] (You have entered 3.);
etc
break;
default:
[Link](You have entered others.;
break;
}

When you need the program to loop through a set of codes for some number of
times, the For-Next statement will be very handy. The looping is controlled by a counter
that will go from a start number to an end number, increasing by a stepSize after each
execution of the loop. When not declared, a default step size is 1.

for (counter=start; test condition; iteration) {
statements
}

See the example given below:

function forLoopDemo() {
for (var j=1; j<10; j++) {
MsgBox(Hi);
}
}
15


Apps Script, like Java, JavaScript and others using the C language syntax, denote
increment by 1 with ++ and decrement by 1 with . The program above displays the
message Hi for 10 times, using j as the counter.
A Do-While statement is useful when you need the program to loop through a set of
codes until a test condition returns a FALSE. Do-While can be used in two slightly different
methods.
Method 1: This method tests the condition first, and executes the statement when the
condition is tested TRUE. The program ends immediately when the test condition results in
a FALSE.

while (testCondition) {
statements
}

Method 2: This alternative method executes the statement first, and then tests the
condition. Only when the condition is tested TRUE will the next iteration be executed.
Similarly, the program ends when the test condition returns a FALSE. The main difference
is that the codes in the loop in method 2 will be executed at least once, whereas this may
be by-passed completely in method 1.

do {
statements
} while (testCondition);

The corresponding examples are as follows:

function doWhileDemoMethod1() {
while (j < 10) {
[Link](Hi);
j += 1;
}
}

Method 2:
function doWhileDemoMethod2() {
do {
MsgBox (Hi);
j += 1;
} while (j < 10);
}

Note 19: SOLVER AND ADD-INS
Add-on (or Add-in in Excel) functions and operations are contained in workbooks, each
typically denoted in Excel by the .xla or .xlam extension. For example, Solver operations
16

are in [Link] and [Link] workbooks. An add-in is only available after it has been
activated. In Excel 2003, this is done by selecting Tools/Add-ins and ticking on the relevant
items. In Excel 2007, select Office/Excel Options/Add-ins/Excel Add-ins/Go.
Activating an add-on only allows it to run in the spreadsheet. Macros recorded with
add-in operations may not work. For example, Solver operations will not run in the
macros, unless in addition the [Link] file is referenced in the Visual Basic Editor (VBE)
for the project. This is done by selecting in VBE Tools/Reference and then ticking the check
box for Solver. You may have to browse for it if it is not there in the selection list. The file is
usually found in c:\Program Files\Microsoft Office\Office 12\Library\Solver. The file to
link to is [Link] and not [Link]. For Excel 2003, the folder is Office 11 instead and
the file is [Link].
Google Sheets does not support macro recording and it is still not possible to write
Apps Script codes to customize Solver. Thus, the above comments are only for cross-
reference to Excels capabilities, in case there are new developments to look out for.

Note 20: AUTOMATIC PROCEDURES AND EVENTS
Trigger procedures are programs that are activated by interactive actions or events. Every
object has their associated events, such as onOpen for spreadsheet and onEdit for sheets.
For example, to automatically execute a macro whenever a spreadsheet is opened,
you can write a function onOpen procedure in the spreadsheet. An example of the codes
that you can put in this is shown below:

function onOpen() {
[Link]()
.getSheetByName("Home").getRange(A1).Activate;
}

Similarly, onEdit detects any change in value in a sheet. The remaining code that follows
can take the changed value and doing something with it.

function onEdit(e){
var range = [Link];
var newValue = [Link]();
other statements to work on newValue ..
}


Note 21: RUN-TIME ERROR HANDLING
Imagine that you wrote a simple subroutine to compute the square root of a user-input
value. In order to ensure that the user has entered a positive value, you need to test the
input value before executing the computation. You test by checking if the value is positive
and if it is numeric. Alternatively, you can use a general error handling statement to trap
all possible errors whenever they occur.
17


function squareRootDemo() {
...
try {
var num = InputBox (Enter a value: );
If (num != ) {
[Link](Sqr(num));
}
}
catch(err) {
[Link] (Make sure you enter a positive
numeric value);
}
}

This example allows the subroutine to proceed straight to the error message
whenever the user input threatens to trigger a computation error.

Note 22: SPREADSHEET PROGRAMMING APPROACH

There are really four approaches to programming a spreadsheet. The first is to use only
spreadsheet functions and features (this topic is covered in Appendix A). Since its
inception, spreadsheet application software has come a long way. Features that were once
only available in programming languages are now regularly available and used in
spreadsheets. The basic ones permit one variable (as represented by a cell) to take values
from other variables, use of If-Then-Else logical branching, and multiple-stage
computations with relative cell referencing. More recently, there are random variables,
iterative or recursive computations, Lookups, and automated computation (i.e., loops in
the form of DataTable operations, though not available yet in Google Sheets). Working on
a spreadsheet workbook is really programming work, though many do not see it as such.
The second approach, also not available in Google Sheets, is to record mouse and
keyboard actions as macros, and run these macros as automated steps in the spreadsheet
operations. The steps are visible to the user by default, though it can be masked to speed
up operations. All the calculations are done in the sheets and so the user can vividly review
the interactions between variables.
The third approach, to extend the abilities of macros, is by adding programming codes
to do what mouse and keyboard actions on the worksheets cannot achieve. In addition,
the recorded macros can be tidied up and made more efficient, for example, by removing
worksheet selections, cell selections, and copy-paste operations, replacing them with
codes that work directly with spreadsheet objects.
The fourth and final approach is to write subroutines and functions using the macro
language, with minimal use of spreadsheet features, other than to read data and write
results. This is no different than normal computer programming, except now the
worksheets become data storage and reporting pages. The computations are all done in
18

the lines of codes and therefore the user must be able to read the computer language to
understand, debug, and maintain the codes.
We prefer the third approach since the computer program in spreadsheets plus
macros is already extremely powerful. On top of that, it is transparent and dynamic. This
means that you can build a spreadsheet model with nontechnical people and its results are
immediately responsive to changes in input values. Transparency, dynamism, and ease of
use are the key strengths of spreadsheets; no other analytical software comes close to
matching spreadsheets. No other software would be as readily accessible to novices and
experts alike for situational exploration and problem discovery. And to beat that, the work
done in these first steps can be further extended into user-friendly solutions, data and
solution analyses, and management reports.

19