passing a date object to a google app script function

Question

This seems weird.

I have a cell a1: containing [Jan 1, 1984].

I have a function xxx

function xxx(d) {
 return d;
}

if I try this in a cell like

=xxx(a1)

that returns 1 no matter what the year is.

if on the other hand I put this in the cell =datevalue(a1) i get back 30682

it seems like google sheets is passing the day-of-the-year to the appscript function. That seems very weird.

• doubleunary: See Working with date and time values in Google Sheets.

Answer 1

It's very likely that the "problem" is caused by the number format of the cell holding the formula.

Please ensure that the cell format is set to Automatic or to Date.

Answer 2

The issue is that Google Sheets passes date values to Apps Script as serial numbers (days since December 30, 1899), but when you directly return it, you're only getting the integer part.

To properly handle dates passed from cells to Apps Script:

function xxx(d) {
 // Convert the serial number to a proper Date object
 return new Date(d);
}

However, when returned to the sheet, it will still be displayed as a serial number. To get a formatted date back in the cell, use TEXT:

=TEXT(xxx(A1), "MMM dd, yyyy")

Alternatively, format the date within the script:

function xxx(d) {
 var date = new Date(d);
 return Utilities.formatDate(date, Session.getScriptTimeZone(), "MMM dd, yyyy");
}

Why you're seeing "1":

Google Sheets is interpreting your function's return value as a number, not a date. The new Date() constructor properly converts the serial number to a Date object that Sheets can recognize.

Note: If you're still seeing issues, make sure:

1. Cell A1 is formatted as a date (not text)
2. Your Apps Script function returns a Date object or formatted string
3. The receiving cell has appropriate formatting

• doubleunary: "Google Sheets passes date values to Apps Script as serial numbers" — that's inaccurate. Dates, times and durations are passed as Date objects. See the Apps Script section at Working with date and time values in Google Sheets.