Google Apps Script versus javaScriptGoogle 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.
Getting started with google apps scriptJust 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 variablesAs 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 namesIn 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
strFirstname javaScript
firstName The semi colonAlthough some javaScript interpreters don't insist on this, a javaScript line is terminated by a semicolon;
x= y; BlocksBlocks of code, such as if then clauses, are enclosed within {} in javaScript
VBA
if bTrue then ..xx ..yy endif javaScript
if (bTrue) { ..xx; ..yy; }
OperatorsThe equals sign.One of the most common mistake js newbies make is to use = in place of ==.
VBA
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
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 evaluationYou will sometimes see --x and x--. The mean almost the same thing , x=x-1, but the position of the -- is significant.
x=1; 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 assignmentI 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
if (a) then x=y else x=z endif in javaScript, you could say
if(a) x=y; else x=z; 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]; cCache.prototype.getKey = function (ob) { return DebugAssert ( isTypeString(ob) ? ob : ob.getSheet ? ob.getSheet().getName() : ob.getName ? ob.getName() : null ) ; }; LoopingIterating 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; } ArraysIn 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
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.
var = new array(10);
for ( var i=0; i<a.length ; i++) { alert(a[i].toString()); } Functions and ProceduresThis is a big topic, and deserves its own page - see javaScript functions
|
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > From VBA to Google Apps Script >