This article will cover the translation of the Sheets workbook functions for Date and Time manipulation to Apps Script. Virtually all of them will be covered, except for a few exotic ones that I might do later if anybody asks.
For some general introduction on implementation see Sheets Workbook functions – converted to Apps Script
The default timezone is the Session timezone – in other words the timezone the script is running in. Since Apps Script runs on a server somewhere, you might suspect that to be the timezone of the server, but in fact its the timezone of the user running the script.
If you are working on a spreadsheet, the timezone of the spreadsheet may not be the same as the session timezone, but you probably want it to be. bmSheets allows you to set the default timezone to anything – usually the same as the sheet if you are working with sheets.
Dates and times
- The timezone (or timezone offset) is expressed as part of the string
- The timezone to which the datestring refers is known
- the date is a date object, so there is no timezone
Further difficulties arise when the date is not in ISO format (yyyy-mm-dd), for example mm/dd/yyyy or dd/mm/yyyy (which one did he mean?). For this initial implementation dates are only valid if in ISO format (with or without a timezone), or if they are expressed as a date object
All functions are accessible as follows
I’ll show how to call them, as well as the implementation code which could help to familiarize yourself with Temporal if you are interested.
Some functions are not exposed but used internally – so they’ll be referred to later. For completeness I’ll show them here
parseDate converts from a date or datestring to a Temporal date
These are not Workbook functions but exposed in case you want to use them for anything
These functions are exposed and can be used from apps script. In all cases _tz is the Temporal.TimeZone maintained as the default timezone to apply to all conversions. All input dates can be iso datestrings, or Date objects. All return dates are Date objects.
date (year, month, day)
datedif (start_date, end_date, unit)
I’ve only implement D,M, and Y units for now. The others are a bit esoteric
edate (start_date, months)
days (start_date, end_date)
eomonth (date , months)
time (hour, minute, second)
weekday (date, type)
Any other date type functions not mentioned are not implemented as of yet. If there’s any demand for them I may do it.
Testing as worksheet custom functions
Normally these functions are going to be called from Apps Script. There’s no real reason to access these functions as custom functions from a spreadsheet, except that it’s useful to check the results by comparing them against the real thing.
If you want to do this, you can add this code to your container bound script to expose all the functions as custom functions in your sheet.
You can then call them like this