Things to look out for
As you work with javaScript (most of these gotchas will be about javaScript rather than apps script specifically), you’ll scratch your head about a few things. I know I did. So I thought I’d keep a log of what I wasted time on, so you can avoid doing the same. Many of them will have been covered in passing in other pages, but are work repeating.
Case
javaScript is case sensitive. javaScript is not the same as JavaScript. Those of you coming from VBA will be used to the IDE automatically matching case with defined variables (except, strangely, eNums) and are used to not having to worry too much, except aesthetically, about getting upper and lower case messed up. There are non-official standards for the use of ‘camelCase’ in javaScript, and it’s best to stick to a standard. Generally, we follow the java convention, where classes use UpperCamelCase and most everything else uses lowerCamelCase. In VBA (and in javaScript), I have always used lowerCamelCase, and my classes are defined as cUpperCamelCase. Essentially the java standard, except with a lower case c in front of every class so that in VBA, it is clear it is a custom class. The exception is where I emulate built in VBA functions – I keep whatever the VBA case is, such as LCase for example. This means my conversion is easy, but yours may not be. If you don’t stick to something, you’ll spend hours tracking down misspellings.
this
this in js is kind of like Me in VBA. It refers to the object or class instance we are working in. However, in javaScript, everything is an object, including functions. Consider
this.forEach (
function(cItem,cIndex) {
DebugPrint (cItem , cIndex, this.someProperty);
}
);
At first, you would think that this.someProperty is based on the same this as this.forEach. However, since a function is an object, the second this would look for someProperty of the function it is part of, and would fail (silently unfortunately, since the value returned would be null and perfectly acceptable). Use self
var self = this;
this.forEach (
function(cItem,cIndex) {
DebugPrint (cItem , cIndex, self.someProperty);
}
);
To get over this kind of thing, I usually define a variable self to contain the value of this that refers to the instance of the object we are inside, otherwise I always get caught out. I guarantee that sooner or later you will be too. Even though I know about, I still do it today.
Methods or properties
VBA doesn’t care much. a=b is the same as a=b(). In javaScript, if b is a function, a=b will not execute b, but rather copy the source code for b, whereas a=b() will execute b and transfer the return value of b(). So you need to be very careful that you don’t get your methods and properties mixed up. See Google Apps Conversion approach for how I tackle this.
getValues()
Range.getValues() in google apps script will return a two dimensional array the size of the Range, just like Range.Values does in Excel. But not always. If the specified range is outside the current dataRange() of the sheet, the returned array will not be the same size as the requesting range. This can really suck if you didn’t know that. If you use the worksheet caching I developed in Optimizing access to sheet values, the cache.getValues(range) method restores the Excel behavior.
Transitioning is covered more comprehensively in my book, Going Gas – from VBA to Apps script.
Summary
For more like this, see From VBA to Google Apps Script . Why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available. Much of the code referred to here is the mcpher library. You can find out how to include it in your project here.