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.

Page Content hide

2 Dates

3 Results

4 Library

4.1 names of functions

6.3 manipulating timezones and dates

7 Links

8 Implemented functions

Types

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.

Dates

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.

Results

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.

Library

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.

Debugging

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

/**
 * all the functions in the library can be accessed as custom functions
 * @customfunction
 * @param {string} name of the function
 * @param {...*} var_args
 * @return {*}
 */
const custom  = (name, ...args) =>  {
  // this is essential if the script is running in a different timezone to the sheets timezone for custom functions to work in the calling sheet
  bmSheet.customFunctions('settimezone', SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone())
  return bmSheet.customFunctions(name.toLowerCase(), ...args)
}

exposing as custom functions in a 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

Helpers

A few extra helpers are exposed as well as the worksheet functions.

Temporal

Dates use the new JavaScript proposal for replacing the Date object – see Temporal – dates, times and timezones and the proposed new Date system for ECMAScript for an Apps Script polyfill.

If you want to use Temporal directly, you don’t need to bother linking to the bmTemporal library as it’s exposed via bmSheet

const { Temporal } = bmSheet

Using temporal directly

Timezones

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.

  const scriptTimeZone = Session.getScriptTimeZone()
  console.log(scriptTimeZone)

  // might not match the spreadsheettimezone
  const sheetTimeZone = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone()
  console.log(sheetTimeZone)

  // which might not match what temporal thinks the local timezone is
  const report = bmSheet.helpers.report()
  console.log(report)

  // make sure that the plain time is translated to script timezone
  if (!report.equal) {
    console.log('plain and script timezone conversion dont match')
  }

  // make sure that library timezone matches calling script
  if (report.libraryTimeZone !== scriptTimeZone) {
    console.log('script and library timezone dont match')
  }

  // make sure that library timezone matches calling sheet
  if (report.libraryTimeZone !== sheetTimeZone) {
    console.log('script and sheet timezone dont match')
  }


  functions.settimezone(sheetTimeZone)

checking timezone matches

In fact all you have to do is set the desired timezone before starting out eg

for a container bound script

functions.settimezone(SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone())

or if you have a specific timezone in mind

functions.settimezone('Asia/Singapore')

but bmSheet.helpers.report() will return an object like this to help you decide if you need to take any action

{ libraryTimeZone: 'Europe/Paris',
  zonedDate: '2021-07-01T13:24:02.172641918 02:00[Europe/Paris]',
  plain: '2021-07-01T13:24:02.177642172',
  instant: {},
  ms: 1625138642172,
  local: '2021-07-01T11:24:02.172641918Z',
  legacyDate: Thu Jul 01 2021 13:24:02 GMT 0200 (Central European Summer Time),
  equal: true }

bmSheet.report

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.

	// Setting a default timezone
	// tz is something like "Europe/Paris"
	bmSheet.functions.settimezone  (tz)
  
	// Getting the default timezone
	const tz = bmSheet.functions.gettimezone () 
  
  	// converting a date or dateString (ISO) to a Temporal.Instant using the default timezone
	// (if you want to play around with temporal objects directly)
	const instant = bmSheet.functions.fromdate(date)
	
	// this is handy for say, converting to iso strings
	console.log(instant.toString())
	
	// converting a temporal date to a JavaScript date
	const date = bmSheet.functions.todate(instant)