I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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.

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.


The semi colon

Although some javaScript interpreters don’t insist on this, a javaScript line is terminated by a semicolon;
 x= y;


Blocks of code, such as if then clauses, are enclosed within {} in javaScript
if bTrue then


if (bTrue) {


The equals sign.

One of the most common mistake js newbies make is to use = in place of ==.
if (a=b) then .. do something
in javaScript, only the first one would have the same result.  = means assign, and == means test for equality, so this is the equivalent
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.
a. if (--x) { .. this is false ..}
b. if (x--) {.. this is true..}

In case a.  the result of –x is tested for non-zero i.e. after being decremented, but in case b, x is tested for non-zero before being decremented. In both cases, x is 0 after the if statement is executed.

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.
if (a) then
in javaScript, you could say
but this is so much more satisfying
x = a ? y : z;
we can combine multiple tests, as in this example
    ks = isUndefined(k) ? 
      self.generateKey() : 
      (pCleanKey ? makeKey(k.toString()) : k.toString());
or use the result in an expression
var x = isTypeNumber(k) ? k - pBase : pKeys[pCleanKey ? makeKey(k) : k];

or even build a whole function out of it

cCache.prototype.getKey = function (ob) {

  return DebugAssert 
    ( isTypeString(ob) ? 
        ob :     
        ob.getSheet ? 
          ob.getSheet().getName() :
            ob.getName ? 
              ob.getName() : 
              null ) ;

Of course you dont have to use it- a series of ifs and elses will do the job, but isn’t it so much more close to how you tackle the problem?


Iterating across a range of values.
VBA – the value of i after the loop in this case will be 10
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.
for ( var i=0; i<10 ; i++) {
    'do something;


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,
Dim i as long
dim a(0 to 9)
do i = lbound(a) to ubound(a)
next i
javaScript – array starts at 0.
var = new array(10);
for ( var i=0; i<a.length ; i++) {

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.