Apps Script custom functions

Just like Excel, you can extend Google Sheets by writing custom functions. These functions are written in Google Apps Script. In this section, I”ll show how to do some common VBA things you might want to emulate in Google Apps Script. 


In many ways, the built in set of worksheet functions for Google Apps Script are richer are more powerful than in Excel. Quite often you’ll find you can achieve more things without needing to resort to scripting.  If you do, here are some cautions.

  • Speed

custom functions are quite slow to execute compared to native formulas. You’ll see this in a cell where custom (or indeed any) formulas are being executed.       

Loading….

  • Arguments are passed by value

you cannot access the range of an argument, only its value. Consider this function in VBA 

Public Function whereIsIt(r As Range) As String
    whereIsIt = r.Address
End Function

Entering =whereIsIt (a2) in a cell will evalute to $A$2 in Excel. In Google Apps Script, you can only pass the value of a2, not its address… so

function whereIsIt (v) {
    return v;
}

VBA will also use the value of the argument if you define it as not being a range, so the GAS example is equivalent to this 

Public Function whereIsIt(v as variant) As variant
    whereIsIt = v
End Function

or

Public Function whereIsIt(r As Range) As variant
    whereIsIt = r.value
End Function
  • Arrays as Arguments

 arrays are passed as values in Google Apps Script also. Let’s look at this formula.  

=arrayTest(A1:D2)

In VBA we can examine the range argument, so that 

Public Function arrayTest(r As Range) As Variant
    arrayTest = "{rows:" & r.rows.count & ",columns:" & r.columns.count & "}"
End Function

gives

{rows:2,columns:4}

But in Google Apps Script, what gets passed is a two dimensional array of values, so you need to check the size of the array by checking the lengths of the array like this.

function arrayTest(values) {
    return "{rows:" + values.length + ",columns:" + values[0].length + "}"
}

The values array looks like this 

[[10,"something else",1,2],[11,"something",5,2]]

for data that looks like this

10Something else12
11Something52

VBA can also treat passed arrays as values, but rather less elegantly 

Public Function arrayValues(passedArray As Variant) As Variant
    Dim a As Variant
    a = passedArray
    arrayValues = "{rows:" & (UBound(a, 1) - LBound(a, 1) + 1) & ",columns:" & (UBound(a, 2) - LBound(a, 2) + 1) & "}"
End Function
  • Shared Libraries

VBA doesnt have a direct equivalent, but one of the nice features of Google Apps Script is the ability to access shared libraries from other projects. You cannot execute functions held in other libraries directly from a sheet. You can however attach a library to your script, and then execute it from there. 

For example – library myLib contains the function doSomething() and you want to call it as a custom from your spreadsheet.  

in the spreadsheet cell

=localSomething(a1)

in your container bound script for the spreadsheet, include a reference to myLib, (see Creating a Google Apps Script Library)  and create a function like this

function localSomething ( someValue ) {
     return myLib.localSomething ( someValue) ;   
}

Some example common tasks

Please contact me  on the Forum gadget, with things you’d like to see how to do and I’ll try to include them here.

Continue reading about VBA to Google Apps Script here