Google apps script is javaScript, with the addition of a set of classes which allow access to Google apps capabilities such as spreadsheet data structures and so on. This means that to be able write Google Apps Script, you have to know javaScript – because javaScript is the language of googleScrpit. This section will show how to do things in javaScript that you already know how to do in VBA.
Much of the code referred to here is the mcpher library. You can find out how to include it in your project here.
Page Content
hide
Getting started with google apps script
Just like VBA, Google Apps Script has an integrated development environment (IDE). Here’s how to kick it off
javaScript quick start.
Let’s look at javaScript essentials quickly so we can get on with writing our first Google App Script.
Declaring variables
As you might know from other articles on this site, I am a great stickler for declaring everything as precisely as possible, using Option Explicit, and for avoiding the variant type in VBA. In javaScript – everything, including functions, look like objects. Variable types are decided at run time. This immediately makes me uneasy. javaScript is also case sensitive, which means that we need a convention to try to stay in control.
Convention for variable names
In VBA, the typical convention (although I dislike it and don’t follow it – I actually use the javaScript convention in VBA too) is to use the first 3 letters as the type, and the rest to describe with mixed case. In javaScript, the convention is to use case to divide the word into its components, and for the first component to be in lower case.
VBA
|
1 |
strFirstname |
javaScript
|
1 |
firstName |
The semi colon
Although some javaScript interpreters don’t insist on this, a javaScript line is terminated by a semicolon;
|
1 |
x= y; |
Blocks
Blocks of code, such as if then clauses, are enclosed within {} in javaScript
VBA
|
1 2 3 4 |
if bTrue then ..xx ..yy endif |
|
1 2 3 4 |
if (bTrue) { ..xx; ..yy; } |
Operators
The equals sign.
One of the most common mistake js newbies make is to use = in place of ==.
VBA
|
1 2 3 |
a=b if (a=b) then .. do something x=(a=b) |
in javaScript, only the first one would have the same result. = means assign, and == means test for equality, so this is the equivalent
javaScript
|
1 2 3 |
a=b; if (a==b) {..do something ..} x = (a==b); |
In fact , since js allows assignments to take place inside a statement, then if (a=b) is perfectly legal. However the result will be not what you expect. Firstly b will be assigned to a, and if (a=b) will always be true for as long as b was non-zero or null. So if you are not careful, you will destroy the contents of a, and will make the wrong assumption about equality.
You can see the full list of operators here
a summary comparison against common VBA operators where they are different
+ & ( js uses + to join strings)
% mod
!= <>
== =
++ decrement by 1 (eg x++; is equivalent to x = x + 1)
— increment by 1 (eg x–; is equivalent to x = x -1)
Arithmetic operators can also be combined with assignment =
+= (eg x += 20; is equivalent to x = x + 20)
-= (eg x-=20; is equivalent to x = x – 20)
*= (eg x*=20; is equivalent to x = x *20)
/= (eg x/=20; is equivalent to x = x /20)
Note on order of evaluation
You will sometimes see –x and x–. The mean almost the same thing , x=x-1, but the position of the — is significant.
|
1 2 3 |
x=1; a. if (--x) { .. this is false ..} b. if (x--) {.. this is true..} |
Conditional assignment
I don’t know if this is the name for this, but I call it conditional assignment. It’s a favorite of old C programmers.
VBA
|
1 2 3 4 5 |
if (a) then x=y else x=z endif |
in javaScript, you could say
|
1 2 3 4 |
if(a) x=y; else x=z; |
but this is so much more satisfying
|
1 |
x = a ? y : z; |
we can combine multiple tests, as in this example
|
1 2 3 |
ks = isUndefined(k) ? self.generateKey() : (pCleanKey ? makeKey(k.toString()) : k.toString()); |
or use the result in an expression
|
1 |
var x = isTypeNumber(k) ? k - pBase : pKeys[pCleanKey ? makeKey(k) : k]; |
|
1 2 3 4 5 6 7 8 9 10 11 |
cCache.prototype.getKey = function (ob) { return DebugAssert ( isTypeString(ob) ? ob : ob.getSheet ? ob.getSheet().getName() : ob.getName ? ob.getName() : null ) ; }; |
Looping
Iterating across a range of values.
VBA – the value of i after the loop in this case will be 10
|
1 2 3 4 |
Dim i as long do i = 0 to 9 ' do something next i |
javaScript – since the var declaration comes within the for brackets, it will be undefined after this loop.
|
1 2 3 |
for ( var i=0; i<10 ; i++) { 'do something; } |
Arrays
In VBA, the starting array value is controlled by the setting for Option Base and the lbound and ubound functions are needed to discover the dimensions of an array,
VBA
|
1 2 3 4 5 6 |
Dim i as long dim a(0 to 9) do i = lbound(a) to ubound(a) msgbox(cstr(a(i))) next i |
javaScript – array starts at 0.
|
1 2 3 4 |
var = new array(10); for ( var i=0; i<a.length ; i++) { alert(a[i].toString()); } |
Functions and Procedures
This is a big topic, and deserves its own page – see javaScript functions
For more like this see From VBA to Google Apps Script In the meantime why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.