One of the things missing from Apps Script compared to VBA, is the ability to run workbook functions, which you can do in VBA
I’ve often thought about implementing the sheets functions from Apps Script, but never got round to it as I figured that Google would eventually build it into Apps Script anyway, but since they haven’t shown any signs of it – may as well get started. The problem is there are hundreds of them and not all of them make sense in Apps Script, and others are going to be pretty difficult. Nevertheless here goes.
Sheets functions are divided up into groups of function types such as Date, Array and so on, and I’ll be implementing them according to the same kind of organization, starting with the members of the groups that are the simplest to implement.
One of the challenges is always going to be dates, times and timezones, partly because the timezone of the script may not be the same as the timezone of sheet that it’s acting on, and partly because dates are hard. I’m using the polyfill described in Temporal – dates, times and timezones and the proposed new Date system for ECMAScript to manipulate dates behind the seen, but if you’re using the Date workbook functions from Apps Script you’ll need to be very careful about the timezone gotchas.
The results from these functions will be as if they are being returned from a custom function – so range values will be returned as an array of arrays, just as you would receive from Range.getValues() and single values will be returned as the same type as is returned from the real Workbook functions.
The functions are available through a library – bmSheet
In VBA functions are called like this
Application.WorksheetFunction.VLookup(prodNum, Range(“A1:B51”), 2, FALSE)
In Appscript they will be called like this
bmSheets.functions.date(2001, 3, 20)
names of functions
For simplicity all sheet names are lower case, and if they have underscore in the official corresponding Sheet function (eg array_constrain) the underscore is preserved.
This is going to be tough. In addition to testing from within Apps Script, you can also expose these ‘fake workbook functions’ as custom functions so they can be set alongside their native ones to easily check if they come up with the same results.
Assuming you are testing these as container bound functions from a sheet to compare the result with the real thing, you can add this to your container bound script
note that this will also automatically set the appropriate timezone to match the container spreadsheet (assuming this is the behavior you want).
You can then call each of the Apps Script versions of the workbook functions like this
=custom("date", 1969, 7, 20)
Note that the functions implemented this way will be a lot slower than their equivalent native and indeed when they are called directly from Apps Script – but the only reason you’d need to call them is during testing in any case
A few extra helpers are exposed as well as the worksheet functions.
If you want to use Temporal directly, you don’t need to bother linking to the bmTemporal library as it’s exposed via bmSheet
If you are working with a sheet and your script and sheet timezones are different from each other (you have to assume they might be), you’ll need to compensate for that if you are using any date functions. bmSheet has some tools to help with that. Consider the following code.
In fact all you have to do is set the desired timezone before starting out eg
for a container bound script
or if you have a specific timezone in mind
but bmSheet.helpers.report() will return an object like this to help you decide if you need to take any action
manipulating timezones and dates
A couple of useful functions are available for setting the default time timezone of temporal to convert to your spreadsheet locale by default.
There will be a page for each group of functions gradually added below
- Currency exchange rates library for Apps Script – optimized for the parsimonious – swop.cx
- Sheets Workbook functions in Apps Script – Date and Time and Temporal primer
- Sheets Workbook functions in Apps Script – Array functions
- Sheets Workbook functions in Apps Script – Database functions
- Sheets Workbook functions in Apps Script – Filter functions
- SQL for Apps Script – it’s here