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.

Motivation

If you are handling data from sheets it might be useful to use the same logic as is available in the worksheet for common tasks rather than reinventing them.  Dates and Times are especially complicated as JavaScript dates are not good with timezones and date arithmetic, especially since spreadsheets and scripts can exist in different timezones from each other. I’m using Temporal – dates, times and timezones and the proposed new Date system for ECMAScript to emulate what Sheets functions do, and I’ll go through the implementation as a learning aid for Temporal as much as a documentation of the functions.

Background

For some general introduction on implementation see Sheets Workbook functions – converted to Apps Script

Timezones

Default timezone

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.

Spreadsheet timezone

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 Date object in JavaScript holds a date as the number of milliseconds since 1st Jan 1970. This is from UTC perspective (so there is no timezone adjustment). The time displayed in a sheet or a UI would be created by adapting this UTC into a date an time using  the timezone offset of the timezone the user is in. This means that parsing date strings is tricky unless

  • 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

// valid
datedif("2001-11-23", "2001-11-25","d")
datedif("2019-01-23T12:34:56.123456789-05:00", "2020-09-17", "d")
datedif("2019-01-23T12:34:56.123456789Z", new Date(), "D")
valid dates

Accessing functions

All functions are accessible as follows

const date = bmSheet.functions.date(2018, 2,1)
accessing functions

Functions

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.

Private functions

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

  /**
   * convert a date from a date string where the timzezone is implicit
   * for now only valid ISO dates are supported as strings (eg 1/2/1999) wont work
   * @param {string | Date} date the date or datestring
   * @return {Instant} 
   */
  const parseDate = (date) => {
    if (isDate(date)) {
      return Temporal.Instant.fromEpochMilliseconds(date.getTime())
    }
    // TODO here - might do some string clean up/conversion to allow unclean datestrings
    return Temporal.PlainDateTime.from(date).toZonedDateTime({
      timeZone: _tz
    }).toInstant()

  }
  /**
   * check if its a date object
   * @param {*} date the date or datestring
   * @return {boolean} 
   * .. for some reason (date instanceof Date) doesnt work if date is passed to a library, so using this hack instead
   */
  const isDate = (date) => date && typeof date === 'object' && typeof date.getMonth === 'function'

  /**
   * convert a date to a zoneddate
   * for now only valid ISO dates are supported as strings (eg 1/2/1999) wont work
   * @param {string | Date} date the date or datestring
   * @return {ZonedDateTimeISO} 
   */
  const zonedISO = (date) => fromdate(date).toZonedDateTimeISO({
    timeZone: _tz
  })
parseDate and other useful stuff

Handy functions

These are not Workbook functions but exposed in case you want to use them for anything

  /**
   * convert a date from a temporal to a Date
   * @param {object} temporal one of the temporal date types
   * @return {Date} 
   */
  const todate = (temporal) => new Date(temporal.epochMilliseconds)

  /**
   * convert a date from a date string where the timzezone is implicit
   * for now only valid ISO dates are supported as strings (eg 1/2/1999) wont work
   * @param {string | Date} date the date or datestring
   * @return {Instant} 
   */
  const fromdate = (date) => parseDate(date)
to and from dates

 

Workbook functions

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)

  /**
   *  DATE
      Converts a year, month, and da into a date.
      Sample Usage
      DATE(1969,7,20)
   * @param {number} year eg 2001
   * @param {number} month (starts at 1)
   * @param {number} day of month
   * @return {Date}
   */
  const date = (year, month, day) => {
    const pd = _tz.getInstantFor({
      year,
      month,
      day,
    })
    return todate(pd)
  }
date

datedif (start_date, end_date, unit)

I’ve only implement D,M, and Y units for now. The others are a bit esoteric

  const differ = (start_date, end_date) => start_date.until(end_date)
  /**
   * DATEDIF(start_date, end_date, unit)
    start_date - The start date to consider in the calculation. Must be a reference to a cell containing a DATE, a function returning a DATE type, or a number.
    end_date - The end date to consider in the calculation. Must be a reference to a cell containing a DATE, a function returning a DATE type, or a number.
    unit - A text abbreviation for unit of time. For example,"M" for month. Accepted values are "Y","M","D" ,"MD","YM","YD".
    "Y": the number of whole years between start_date and end_date.
    "M": the number of whole months between start_date and end_date.
    "D": the number of days between start_date and end_date.
    --- these below are not implemented yet
    "MD": the number of days between start_date and end_date after subtracting whole months.
    "YM": the number of whole months between start_date and end_date after subtracting whole years.
    "YD": the number of days between start_date and end_date, assuming start_date and end_date were no more than one year apart.
   * @param {date | string} start_date 
   * @param {date | string} end_date
   * @param {string} unit - see above
   * @return {number} difference (not inclusive)
  */
  const _units = {
    'y': 'years',
    'm': 'months',
    'd': 'days'
  }
 
  const datedif = (start_date, end_date, unit) => {
    const start = fromdate(start_date)
    const end = fromdate(end_date)
    const d = differ(start, end)
    const u = _units[unit.toLowerCase()]
    return d.round({ relativeTo: start.toString(), smallestUnit: u, roundingMode: "trunc" })[u]
  }
datedif

 

edate (start_date, months)

  /**
   * EDATE(start_date, months)
   * months - The number of months before (negative) or after (positive) start_date to calculate.
   * @param {string | Date} start_date the date or datestring- The date from which to calculate the result.
   * @param {number} months The number of months before (negative) or after (positive) start_date to calculate.
   * @return {Date} 
   */
  const edate = (start_date, months) => todate(zonedISO(start_date).add({ months }))
edate

days (start_date, end_date)

  /**
   * DAYS function
   * The DAYS function returns the number of days between two dates.
   * days is different from datedif('d') as its absolute and inclusive
   * @param {string | Date} start_date the date or datestring start
   * @param {string | Date} end_date the date or datestring end
   * @return {number} the days between (inclusive)
   */
  const days = (start_date, end_date) => Math.abs(datedif(start_date, end_date, 'd')) + 1
days

eomonth (date , months)

  /**
   * EOMONTH
   * Returns a date representing the last day of a month which falls a specified number of months before or after another date.
   * @param {string | Date} start_date the date or datestring start
   * @param {number} [months=0] how many months between 0 = end of this month
   * @return {Date} the end of month date
   */
  const eomonth = (start_date, months = 0) => {
    const ed = zonedISO(start_date).add({ months })
    return todate(ed.with({ day: ed.daysInMonth }))
  }

day (date)

  /**
   * DAY
   * @param {string | Date} date the date or datestring
   * @return {number} the day of month
   */
  const day = (date) => zonedISO(date).day
day

hour (date)

  /**
   * HOUR
   * Returns the hour component of a specific time, in numeric format.
   * @param {string | Date} date the date or datestring
   * @return {number} the hour
   */
  const hour = (date) => zonedISO(date).hour
hour

minute (date)

  /**
    * MINUTE
    * Returns the minute component of a specific time, in numeric format.
    * @param {string | Date} date the date or datestring
    * @return {number} the minute
   */
  const minute = (date) => zonedISO(date).minute
minute

second (date)

  /**
   * SECOND
   * Returns the  component of a specific time, in numeric format.
   * @param {string | Date} date the date or datestring
   * @return {number} the second
   */
  const second = (date) => zonedISO(date).second
second

month (date)

  /**
   * MONTH
   * Returns the  component of a specific time, in numeric format.
   * @param {string | Date} date the date or datestring
   * @return {number} the month
   */
  const month = (date) => zonedISO(date).month
month

year (date)

  /**
   * YEAR
   * Returns the  component of a specific time, in numeric format.
   * @param {string | Date} date the date or datestring
   * @return {number} the year
   */
  const year = (date) => zonedISO(date).year
year

now ()

  /**
   * NOW
   * Returns the current date and time as a date value.
   * @return {Date} now
   */
  const now = () => todate(Temporal.now.instant())
now

time (hour, minute, second)


  /**
   * TIME
    TIME(hour, minute, second)
   * @param {number} hour - The hour component of the time.
   * @param {number} minute - The minute component of the time.
   * @param {number} second - The second component of the time.
   * @return {Date}the current date and time as a date value.
  */
  const time = (hour, minute, second) => {
    const pd = _tz.getInstantFor({
      day: 1,
      month: 1,
      year: 1970,
      hour,
      minute,
      second,
    })
    return todate(pd)
  }
time

weekday (date, type)

  /**
   * 
    WEEKDAY(date, [type])
    date - The date for which to determine the day of the week. Must be a reference to a cell containing a date, a function returning a date type, or a number.
    type - [ OPTIONAL - 1 by default ] - A number indicating which numbering system to use to represent weekdays. By default counts starting with Sunday = 1.
    If type is 1, days are counted from Sunday and the value of Sunday is 1, therefore the value of Saturday is 7.
    If type is 2, days are counted from Monday and the value of Monday is 1, therefore the value of Sunday is 7.
    If type is 3, days are counted from Monday and the value of Monday is 0, therefore the value of Sunday is 6
  * @param {date | dateString } date The date for which to determine the day of the week
  * @param {number} [type=1] the type - see above
  * @return {number} day of week
*/
  const weekday = (date, type = 1) => {
    // this is where monday is 1 - so make it 0 -6
    const w = zonedISO(date).dayOfWeek - 1
    switch (type) {
      // if sunday is 1
      case 1:
        return ((w + 1) % 7) + 1
      // if monday is 1
      case 2:
        return w + 1
      // if monday is 0
      case 3:
        return w
      default:
        throw new Error('weekday type should be 1-3')
    }

  }
weekday

 

isoweeknum (date)

  /** 
   * 
    ISOWEEKNUM
    Returns the number of the ISO (International Organization for Standardization) week of the year where the provided date falls.
    * @param {date | dateString } date The date for which to determine the iso week num
    * @return {number} isoweek number
  */
  const isoweeknum = (date) => zonedISO(date).weekOfYear
isoweeknum

today ()

  /** TODAY
   * Returns the current date as a date value.
   */
  const today = () => todate(Temporal.now.instant().toZonedDateTimeISO({ timeZone: _tz }).with({ hour: 0, minute: 0, second: 0 }))
today

datevalue (date)

  /**
   * 
   * DATEVALUE
   * Converts a provided date string in a known format to an (excel) date value.
   */
  const datevalue = (dateString) => dateToValue(dateString)

  // makes an excel type datevalue from a date

  const dateToValue = (date) => {
    // this is 1/1/1970in excel time
    const zeroHour = 25569
    // ms in a day
    const msd = 1000 * 60 * 60 * 24
    const d = fromdate(date)
    const ms = d.epochMilliseconds
    return ms / msd + zeroHour
  }
datevalue

 

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.

/**
 * 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)
}
to expose as container bound custom functions

You can then call them like this

=custom ('date', 2001, 12, 1)
as custom function

 

Links

library: 1B4T8Ec3Mlmqj91vnn_oqhBfjcEiVl3EcRS_1gIkdeJCDEvy5u5x3jRHT

bmSheet: IDE

github: https://github.com/brucemcpherson/bmSheet

 

Temporal – dates, times and timezones and the proposed new Date system for ECMAScript

Sheets Workbook functions – converted to Apps Script