Quick Links

Other stuff

Site owners

  • Bruce Mcpherson

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.

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. 



Comments