As part of creating a framework for dual maintenance of Google Apps Script and VBA versions of the same application, one of the key things was to produce a set of functions in GAS that would be equivalent to the most common ones in VBA. This includes various objects missing from GAS such as the VBA collection.
Here is a selection of the GAS equivalent of common VBA functions. For more like this, take a look at the ramblings site, or contact me on our forum.
Strings
function LCase(s) {
return s.toLowerCase();
}
function Trim(s) {
return LTrim(RTrim(s));
};
function Len(s) {
return s.length;
}
function Left(str,optLen) {
return Mid( str, 1 , optLen);
}
function Right(str,optLen) {
var length = IsMissing (optLen) ? Len(str) : optLen;
var start = 1+ Len(str) - length;
return Mid( str, start );
}
function Mid (str,optStart,optLen) {
var start = IsMissing (optStart) ? 0 : optStart - 1;
var length = IsMissing (optLen) ? Len(str) - start + 1 : optLen ;
DebugAssert( str.slice, str + ' is not a valid string for Mid function');
return str.slice ( start, start + length);
}
function Split(s,optDelim,optLimit) {
return s.split(fixOptional(optDelim,","),fixOptional(optLimit,-1));
};
function LTrim(s) {
return CStr(s).replace(/^\s\s*/, "");
};
function Rept(n,s){
return n ? Array(n).join(s) : '';
}
function Space(n){
return Rept(n," ");
}
function RTrim(s) {
return CStr(s).replace(/\s\s*$/, "");
};
User Interaction
function MsgBox(a) {
Browser.msgBox(a);
}
function InputBox(a) {
return Browser.inputBox(a);
}
Conversion
function CStr(v) {
return v ? v.toString() : '';
}
function DateSerial(y,m,d){
return new Date(y,m,d);
}
function Year(dt){
return dt.getFullYear();
}
Informational and general
function Empty() {
return "";
}
function vbLf() {
return "\n";
}
function IsEmpty(cellData) {
return typeof(cellData) == "string" && cellData == Empty();
}
function IsDate(sDate) {
var tryDate = new Date(sDate);
return (tryDate.toString() != "NaN" && tryDate != "Invalid Date") ;
}
function IsNumeric(n) {
return !isNaN(parseFloat(n)) && isFinite(n);
}
function IsMissing (x) {
return isUndefined(x);
}
function IsObject (x) {
return VarType(x) == 'object';
}
function VarType (v) {
return typeof v;
}
function Xor (a,b) {
return a ? !b : b ;
}
function Abs (x) {
return Math.abs(x);
}
This is a great resource for people converting from Excel to Google spreadsheets. It would be great if you could publish this set of functions to the script gallery, to make it easy for people to use them in their spreadsheets.
Funny you should say that – I just did that today
http://excelramblings.blogspot.co.uk/2012/06/managing-libraries-with-google-apps.html
regards
bruce