I’m trying to write some simple function extensions in Google Apps Script. I’m a retired developer who just assumed that the simple date arithmetic functions I used to have access to in Excel would be readily available in Sheets. Not the case, I find. There is an extremely modest set of functions available in Sheets. For example, I have two cells in a sheet, a date and a time. I want to add the two values together somehow. Not so easy.

I found my way over to Google Apps Script. On the Spreadsheet itself, I have access to the DATEVALUE() function for which, given the input 06/10/2024, returns 45453, evidently the number of days since 01/01/1900. Good enough for my purposes. Now I wish to write a more complicated function that adds a time to this date, with suitable multiplications to return a number of minutes in the date plus the time.

This is the function:

/**
 * Calculates a numeric value in minutes for the sum of a date plus a time
 *
 * @param {date} the date to be added
 * @param {time} the time to be added to the date
 * @return the number of minutes since 12:00 am 01/01/1900 of the sum of the date plus the time
 * @customfunction
 */
function date_time(date, time) {
  let dateminutes = DATEVALUE(date)*24*60
  let timeminutes = HOUR(time)*60 + MINUTE(time);
  return dateminutes+timeminutes;
}

But if I try to run this function, either from my spreadsheet or pressing the run button in the Apps Script window, I get an error message telling me that the DATEVALUE() function is unknown. I suppose if the execution got to the next line, the HOUR() and MIMUTE() functions would also blow up.

Ignoring my irritation that what used to be simple in the pre-AI days (please forgive my geezerhood) is now complicated, I would like to know what must be imported in order for the Google Apps Script to run? Is there a “library” of this stuff?

Or maybe this is not the best tool to use for creating such simple functions. I don’t want to publish, sell, or share these all over the web, or with others in “my organization” which doesn’t in fact exist. It’s just something I’m doing for me.

Welcoming all suggestions.