Work with dates and time zones
Format Google Sheets dates with Utilities.formatDate and getSpreadsheetTimeZone, set appsscript.json timeZone, and compare calendar due dates.
Distinguish a UTC instant from a spreadsheet calendar date, prevent off-by-one day shifts when script and spreadsheet time zones differ, format dates and timestamps with Utilities.formatDate, and compare due dates without daylight-saving bugs.
Separate an instant from a calendar date
Read a four-row Requests table from a bound Google Sheet, format each due date in the spreadsheet's own time zone, and calculate how many calendar days each request is ahead of or behind 2026-04-15. You need object and strict-equality rules from Work with JavaScript values and objects, row-array indexing from Work with JavaScript arrays, and manifest configuration from Understand the runtime and permissions.
Date bugs in Apps Script start when two different ideas share one JavaScript type. An instant is a single moment on the global timeline, such as when a script logs an action (2026-04-15T18:30:00Z). A calendar date names a day on a local calendar, such as a due date of April 15, 2026 (2026-04-15), with no hour or minute attached.
JavaScript uses one built-in Date object for both jobs. According to MDN's Date reference, a JavaScript Date stores one number: elapsed milliseconds since 1970-01-01T00:00:00Z (the Unix epoch). A Date instance carries no time-zone property inside it.
When a Google Sheet cell holds a formatted date such as 2026-04-15, Sheets stores a serial day count interpreted in that spreadsheet's time zone. Calling Range.getValues() converts each date cell into a JavaScript Date whose millisecond value equals midnight (00:00:00) on that day in the spreadsheet's time zone. Calling Utilities.formatDate(date, timeZone, format) turns a Date instant into plain text for a chosen time zone.
Compare the three representations your script handles:
| Representation | What it stores | Carries a time zone? | Value in .gs code | Primary use |
|---|---|---|---|---|
JavaScript Date | Milliseconds since 1970-01-01T00:00:00Z | No | Object (value instanceof Date is true) | Passing instants to Apps Script APIs and writing date cells to Sheets |
| Sheets date cell | Serial number for a calendar day or local wall clock | Spreadsheet setting (File > Settings) | Returned by getValues() as a Date at spreadsheet midnight (or cell time) | Sorting, filtering, and date formulas inside Google Sheets |
Formatted string ('2026-04-15') | Characters in a fixed text pattern | Only if an offset or 'Z' appears in the text | Primitive string (typeof value === 'string') | Logs, emails, filenames, PropertiesService, CacheService, and google.script.run |
Range.getDisplayValues() returns every cell as the exact string shown on screen, such as 4/15/2026 or Apr 15. Because any editor of the sheet can change a column's display format, parsing getDisplayValues() strings breaks when formatting changes. Reading Date objects with getValues() and formatting them yourself keeps the output pattern under your script's control.
Internally, Google Sheets stores a date or date-time cell as a floating-point day offset from December 30, 1899, where 0 is 1899-12-30, 1 is 1899-12-31, 2 is 1900-01-01, and 0.5 is 12:00:00 (see Google Sheets DATEVALUE). That epoch starts on December 30 rather than December 31 because early spreadsheets inherited Lotus 1-2-3's convention of counting 1900 as a leap year. From March 1, 1900 onward, the day count aligns with the modern calendar, and getValues() translates the serial number into a JavaScript Date anchored in the spreadsheet's time zone.
Choose the right time zone for each operation
A bound Sheets project has two separate time-zone settings, plus UTC:
| Time-zone source | Where it is configured | What it governs | When to pass it to Utilities.formatDate |
|---|---|---|---|
spreadsheet.getSpreadsheetTimeZone() | Google Sheets under File > Settings | Spreadsheet date serials, TODAY() and NOW() formulas, and the Date instants returned by getValues() | Formatting a Date read from that spreadsheet so its calendar day matches the cell |
Session.getScriptTimeZone() | "timeZone" in appsscript.json and Project Settings in the script editor | new Date(year, monthIndex, day), getFullYear(), getMonth(), getDate(), getHours(), setHours(), and time-driven triggers | Formatting script-local schedules or timestamps when no spreadsheet owns the date |
'Etc/UTC' | Passed as an IANA identifier (and used in this book's manifests) | Zero offset from UTC all year, with no daylight-saving transitions | Recording machine timestamps ("yyyy-MM-dd'T'HH:mm:ss'Z'"), calling APIs, and counting calendar days |
Google's manifest structure reference defines "timeZone" as an IANA identifier such as "Etc/UTC", "America/New_York", "America/Los_Angeles", or "Asia/Tokyo". Changing File > Settings in Google Sheets updates spreadsheet.getSpreadsheetTimeZone() without changing "timeZone" in appsscript.json.
When those two time zones differ, formatting in the wrong zone shifts dates in both directions:
Spreadsheet east of UTC, formatted in UTC: In a spreadsheet set to
Asia/Tokyo(UTC+9), a cell showing2026-04-15starts at2026-04-15T00:00:00+09:00, which is the instant2026-04-14T15:00:00Z. Formatting thatDatein'Etc/UTC'produces'2026-04-14', one day earlier than the cell.UTC midnight string, formatted west of UTC: Standard JavaScript
new Date('2026-04-15')parses a date-only ISO string as UTC midnight (2026-04-15T00:00:00Z), whereas a date-time string without an offset ('2026-04-15T00:00:00') parses in the runtime's local time zone (see MDN'sDatetime-string rules). InAmerica/Los_Angeles(UTC-7 in April),2026-04-15T00:00:00Zfalls at17:00on2026-04-14, so formatting it in'America/Los_Angeles'produces'2026-04-14'.Evening timestamp west of UTC, formatted in UTC: A cell holding
2026-04-15 18:30inAmerica/Los_Angelesequals2026-04-16T01:30:00Z, which formats as'2026-04-16'in'Etc/UTC'.
In this chapter's project, appsscript.json sets "timeZone": "Etc/UTC" while your new practice Google Sheet keeps your account's regional time zone. That difference lets you watch spreadsheet.getSpreadsheetTimeZone() preserve 2026-04-10, 2026-04-15, and 2026-04-20 even when Session.getScriptTimeZone() is Etc/UTC.
Set up the practice sheet and bound project
Create a disposable spreadsheet for this lesson so your tests do not touch any live tracker.
Create a blank Google Sheet named
Request due datesand rename its first tab toRequests.Open File > Settings in Google Sheets, check the current Time zone, and leave it unchanged so the sheet keeps your default regional zone.
Copy the comma-separated text from
sample.csvinto cellA1, or import the file with File > Import.
Request ID,Summary,Due Date,Status
R-101,Renew room projector lamp,2026-04-10,Open
R-102,Confirm catering headcount,2026-04-15,Open
R-103,Print workshop name badges,2026-04-20,Open
R-104,Archive winter sign-in sheets,,Done
If you pasted into
A1, select column A, choose Data > Split text to columns, and set Separator to Comma.Confirm that
Requests!A1:D5holds headers in row 1, real dates inC2:C4(2026-04-10,2026-04-15,2026-04-20), and a blankC5. Double-clickingC2opens a calendar picker.Choose Extensions > Apps Script from the spreadsheet and replace
Code.gswith the file below.Open Project Settings and enable Show "appsscript.json" manifest file in editor.
Return to Editor, replace
appsscript.jsonwith the manifest below, and save both files.
const REFERENCE_DATE = '2026-04-15';
const MILLISECONDS_PER_HOUR = 60 * 60 * 1000;
const MILLISECONDS_PER_DAY = 24 * MILLISECONDS_PER_HOUR;
/**
* Reads the bound Requests sheet, formats each real Due Date cell in the
* spreadsheet's own time zone, and logs the calendar-day offset from
* REFERENCE_DATE alongside an ISO 8601 UTC execution timestamp.
*
* @returns {void}
*/
function inspectRequestDueDates() {
const spreadsheet = getActiveRequestsSpreadsheet_();
const sheet = getRequestsSheet_(spreadsheet);
const rows = readRequestRows_(sheet);
const scriptTimeZone = Session.getScriptTimeZone();
const spreadsheetTimeZone = spreadsheet.getSpreadsheetTimeZone();
const executedAtUtc = Utilities.formatDate(
new Date(),
'Etc/UTC',
"yyyy-MM-dd'T'HH:mm:ss'Z'"
);
Logger.log('Script time zone: ' + scriptTimeZone);
Logger.log('Spreadsheet time zone: ' + spreadsheetTimeZone);
Logger.log('Reference date: ' + REFERENCE_DATE);
Logger.log('Executed at (UTC): ' + executedAtUtc);
if (rows.length === 0) {
Logger.log('No request rows found below the header row.');
return;
}
for (let i = 0; i < rows.length; i++) {
const record = parseRequestRow_(rows[i], i + 2, spreadsheetTimeZone);
if (record.dueDate === null) {
Logger.log(
record.requestId +
' | no due date | skipped date comparison | status: ' +
record.status
);
continue;
}
const dayOffset = calendarDaysBetween_(REFERENCE_DATE, record.dueDate);
Logger.log(
record.requestId +
' | due ' +
record.dueDate +
' | ' +
describeDayOffset_(dayOffset) +
' | status: ' +
record.status
);
}
}
/**
* Logs fixed-instant comparisons without reading a spreadsheet so you can
* inspect time-zone day shifts, a 23-hour daylight-saving day, and ISO 8601
* UTC versus regional-offset timestamps directly in Execution log.
*
* @returns {void}
*/
function runLocalDateChecks() {
const tokyoMidnight = new Date('2026-04-15T00:00:00+09:00');
Logger.log(
'Tokyo midnight in Asia/Tokyo: ' +
Utilities.formatDate(tokyoMidnight, 'Asia/Tokyo', 'yyyy-MM-dd')
);
Logger.log(
'Tokyo midnight in Etc/UTC: ' +
Utilities.formatDate(tokyoMidnight, 'Etc/UTC', 'yyyy-MM-dd')
);
const utcMidnight = new Date('2026-04-15');
Logger.log(
'new Date("2026-04-15") in Etc/UTC: ' +
Utilities.formatDate(utcMidnight, 'Etc/UTC', 'yyyy-MM-dd')
);
Logger.log(
'new Date("2026-04-15") in America/Los_Angeles: ' +
Utilities.formatDate(utcMidnight, 'America/Los_Angeles', 'yyyy-MM-dd')
);
const dstStart = new Date('2026-03-08T00:00:00-05:00');
const dstNextMidnight = new Date('2026-03-09T00:00:00-04:00');
const elapsedHours =
(dstNextMidnight.getTime() - dstStart.getTime()) / MILLISECONDS_PER_HOUR;
const flooredDayCount = Math.floor(
(dstNextMidnight.getTime() - dstStart.getTime()) / MILLISECONDS_PER_DAY
);
Logger.log(
'2026-03-08 to 2026-03-09 in America/New_York: ' +
elapsedHours +
' hours, Math.floor(ms / 86400000) = ' +
flooredDayCount +
', calendarDaysBetween = ' +
calendarDaysBetween_('2026-03-08', '2026-03-09')
);
const sampleInstant = new Date('2026-04-15T18:30:00Z');
Logger.log(
'Instant in Etc/UTC: ' +
Utilities.formatDate(
sampleInstant,
'Etc/UTC',
"yyyy-MM-dd'T'HH:mm:ss'Z'"
)
);
Logger.log(
'Instant in America/Los_Angeles: ' +
Utilities.formatDate(
sampleInstant,
'America/Los_Angeles',
"yyyy-MM-dd'T'HH:mm:ssXXX"
)
);
}
/**
* @returns {SpreadsheetApp.Spreadsheet}
*/
function getActiveRequestsSpreadsheet_() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error(
'No active spreadsheet found. Open this bound project from your practice Google Sheet with Extensions > Apps Script.'
);
}
return spreadsheet;
}
/**
* @param {SpreadsheetApp.Spreadsheet} spreadsheet
* @returns {SpreadsheetApp.Sheet}
*/
function getRequestsSheet_(spreadsheet) {
const sheet = spreadsheet.getSheetByName('Requests');
if (!sheet) {
throw new Error(
'Add a Sheet tab named Requests and import sample.csv before running inspectRequestDueDates.'
);
}
return sheet;
}
/**
* Reads all rows from Requests and checks the four-column header row.
*
* @param {SpreadsheetApp.Sheet} sheet
* @returns {unknown[][]}
*/
function readRequestRows_(sheet) {
const values = sheet.getDataRange().getValues();
const expectedHeaders = ['Request ID', 'Summary', 'Due Date', 'Status'];
const headerRow = values[0] || [];
if (
headerRow.length !== expectedHeaders.length ||
expectedHeaders.some((header, index) => headerRow[index] !== header)
) {
throw new Error(
'Requests must start in A1:D1 with headers: Request ID, Summary, Due Date, Status.'
);
}
return values.slice(1);
}
/**
* Validates one sheet row and formats any real Date cell in the spreadsheet's
* own time zone so its calendar day does not shift when the script uses UTC.
*
* @param {unknown[]} row
* @param {number} rowNumber
* @param {string} spreadsheetTimeZone
* @returns {{requestId: string, summary: string, dueDate: string | null, status: string}}
*/
function parseRequestRow_(row, rowNumber, spreadsheetTimeZone) {
if (row.length < 4) {
throw new Error('Row ' + rowNumber + ' must contain four columns.');
}
return {
requestId: readRequiredText_(row[0], 'Request ID', rowNumber),
summary: readRequiredText_(row[1], 'Summary', rowNumber),
dueDate: readOptionalDueDate_(row[2], rowNumber, spreadsheetTimeZone),
status: readRequiredText_(row[3], 'Status', rowNumber),
};
}
/**
* @param {unknown} value
* @param {string} columnName
* @param {number} rowNumber
* @returns {string}
*/
function readRequiredText_(value, columnName, rowNumber) {
if (typeof value !== 'string' || value.trim() === '') {
throw new Error(
'Row ' + rowNumber + ' is missing required text in ' + columnName + '.'
);
}
return value.trim();
}
/**
* Accepts an empty cell ('') or a valid Date instance from Range.getValues(),
* and rejects unparsed date strings instead of calling new Date(value).
*
* @param {unknown} value
* @param {number} rowNumber
* @param {string} spreadsheetTimeZone
* @returns {string | null}
*/
function readOptionalDueDate_(value, rowNumber, spreadsheetTimeZone) {
if (value === '') {
return null;
}
if (value instanceof Date) {
if (Number.isNaN(value.getTime())) {
throw new Error('Row ' + rowNumber + ' has an invalid Date in Due Date.');
}
return Utilities.formatDate(value, spreadsheetTimeZone, 'yyyy-MM-dd');
}
if (typeof value === 'string') {
throw new Error(
'Row ' +
rowNumber +
' Due Date holds text (' +
JSON.stringify(value) +
') instead of a Sheets date. Format column C as Date and enter YYYY-MM-DD.'
);
}
throw new Error(
'Row ' + rowNumber + ' Due Date must be a real Sheets date or blank.'
);
}
/**
* Converts a validated YYYY-MM-DD calendar date into an integer UTC day count
* since 1970-01-01 so calendar-day subtraction ignores daylight saving time.
*
* @param {string} isoDate
* @returns {number}
*/
function toUtcDayNumber_(isoDate) {
const match = /^(\d{4})-(\d{2})-(\d{2})$/.exec(isoDate);
if (!match) {
throw new Error(
'Expected YYYY-MM-DD calendar date, got ' + JSON.stringify(isoDate) + '.'
);
}
const year = Number(match[1]);
const month = Number(match[2]);
const day = Number(match[3]);
const utcMillis = Date.UTC(year, month - 1, day);
const check = new Date(utcMillis);
if (
check.getUTCFullYear() !== year ||
check.getUTCMonth() !== month - 1 ||
check.getUTCDate() !== day
) {
throw new Error('Invalid calendar date ' + JSON.stringify(isoDate) + '.');
}
return utcMillis / MILLISECONDS_PER_DAY;
}
/**
* Returns endIsoDate minus startIsoDate in whole calendar days.
*
* @param {string} startIsoDate
* @param {string} endIsoDate
* @returns {number}
*/
function calendarDaysBetween_(startIsoDate, endIsoDate) {
return toUtcDayNumber_(endIsoDate) - toUtcDayNumber_(startIsoDate);
}
/**
* @param {number} dayOffset
* @returns {string}
*/
function describeDayOffset_(dayOffset) {
if (dayOffset < 0) {
const overdueDays = Math.abs(dayOffset);
return (
dayOffset +
' day(s) from ' +
REFERENCE_DATE +
' (overdue by ' +
overdueDays +
' day(s))'
);
}
if (dayOffset === 0) {
return '0 day(s) from ' + REFERENCE_DATE + ' (due on reference date)';
}
return (
'+' +
dayOffset +
' day(s) from ' +
REFERENCE_DATE +
' (due in ' +
dayOffset +
' day(s))'
);
}
{
"timeZone": "Etc/UTC",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
The manifest declares https://www.googleapis.com/auth/spreadsheets.currentonly (project manifests guide). Both entrypoints run as the signed-in user who clicks Run (authorization guide). inspectRequestDueDates reads Requests and writes eight lines to Execution log without changing any cell. runLocalDateChecks formats fixed timestamps in memory and reads no spreadsheet data.
Run both entrypoints and verify the log
Select
inspectRequestDueDatesin the toolbar and click Run.Review the consent prompt, confirm it requests access only to the current spreadsheet, and approve it.
Read Execution log.
Spreadsheet time zoneshows your sheet's regional zone andExecuted at (UTC)shows the current UTC clock, while the other six lines match this output:
Script time zone: Etc/UTC
Spreadsheet time zone: America/Los_Angeles
Reference date: 2026-04-15
Executed at (UTC): 2026-04-15T18:42:10Z
R-101 | due 2026-04-10 | -5 day(s) from 2026-04-15 (overdue by 5 day(s)) | status: Open
R-102 | due 2026-04-15 | 0 day(s) from 2026-04-15 (due on reference date) | status: Open
R-103 | due 2026-04-20 | +5 day(s) from 2026-04-15 (due in 5 day(s)) | status: Open
R-104 | no due date | skipped date comparison | status: Done Select
runLocalDateChecksand click Run. Because every input is a fixed timestamp, Execution log prints these exact seven lines in every account:
Tokyo midnight in Asia/Tokyo: 2026-04-15
Tokyo midnight in Etc/UTC: 2026-04-14
new Date("2026-04-15") in Etc/UTC: 2026-04-15
new Date("2026-04-15") in America/Los_Angeles: 2026-04-14
2026-03-08 to 2026-03-09 in America/New_York: 23 hours, Math.floor(ms / 86400000) = 0, calendarDaysBetween = 1
Instant in Etc/UTC: 2026-04-15T18:30:00Z
Instant in America/Los_Angeles: 2026-04-15T11:30:00-07:00 Those lines demonstrate day shifts east and west of UTC, a 23-hour spring-forward day where raw millisecond division drops a day, and one instant rendered in both UTC and a regional offset.
Guard missing tabs and unparsed date cells
getActiveRequestsSpreadsheet_() checks SpreadsheetApp.getActiveSpreadsheet(), and getRequestsSheet_(spreadsheet) checks spreadsheet.getSheetByName('Requests') before calling getDataRange(). If the tab is missing or misspelled, getSheetByName('Requests') returns null, and calling getRange() or getDataRange() on null throws TypeError: Cannot read properties of null (reading 'getRange'). See Cannot read properties of null (reading 'getRange') for how to diagnose missing spreadsheet and tab lookups.
readRequestRows_(sheet) verifies that A1:D1 matches ['Request ID', 'Summary', 'Due Date', 'Status'] before reading row[2] as the due date. Each cell in Range.getValues() arrives as unknown until narrowed:
A real Sheets date cell (
C2:C4) arrives as aDateinstance. Becausetypeof valuereturns'object'for any object includingnull,readOptionalDueDate_checksvalue instanceof Dateand!Number.isNaN(value.getTime()). An invalidDatestill passesinstanceof Datewhile holdingNaNas its timestamp.An empty cell (
C5onR-104) arrives as'', nevernullorundefined.readOptionalDueDate_returnsnullfor''. Passing''toNumber()produces0, andnew Date(0)fabricates1970-01-01T00:00:00Z.An unparsed text cell such as
'04/15/2026'or'Pending'arrives as a non-emptystring.readOptionalDueDate_throws an error naming the row number and text instead of callingnew Date(value).
Rejecting text in a date column prevents silent misreads: '04/05/2026' means April 5 in the United States and May 4 in the United Kingdom, and '2026-04-15' as text parses at UTC midnight rather than spreadsheet midnight.
Format dates and timestamps with Utilities.formatDate
Utilities.formatDate(date, timeZone, format) requires all three arguments. Omitting timeZone or passing the pattern second throws Exception: Invalid argument: timeZone. Should be of type: String.
Apps Script delegates pattern strings to Java's SimpleDateFormat, where letter case changes what a token means:
| Intended field | Correct token | Output on 2025-12-31T14:07:09Z in Etc/UTC | Lookalike trap | Trap output and cause |
|---|---|---|---|---|
| Calendar year | yyyy | 2025 | YYYY (week-based year) | 2026, because Wednesday, Dec 31, 2025 belongs to week 1 of 2026 |
| Month of year | MM (or MMM) | 12 (or Dec) | mm (minute of hour) | 07, placing minutes into the month slot |
| Day of month | dd | 31 | DD (day of year, 1 to 366) | 365 (or 105 on April 15) |
| Hour (24-hour clock) | HH | 14 | hh (12-hour clock without a) | 02, hiding whether the time is AM or PM |
| Minute and second | mm:ss | 07:09 | MM:SS (SS is milliseconds) | 12:00 |
Literal 'T' and UTC 'Z' | 'T' and 'Z' | 2025-12-31T14:07:09Z | Unquoted T or unquoted Z | Unquoted T throws an illegal-pattern error, and unquoted Z outputs +0000 |
Every unquoted ASCII letter (A through Z and a through z) is reserved as a pattern symbol in SimpleDateFormat. Wrap literal letters such as 'T' and 'Z' in single quotes ("yyyy-MM-dd'T'HH:mm:ss'Z'"). In the Apps Script editor, the myFunction extension flags week-year YYYY and other common Utilities.formatDate pattern mistakes as warnings before you run the function.
Append a quoted literal 'Z' only when the timeZone argument is 'Etc/UTC' (or 'UTC'). Passing a regional zone such as Session.getScriptTimeZone() with "yyyy-MM-dd'T'HH:mm:ss'Z'" stamps local wall-clock hours with 'Z', mislabeling a regional time as UTC. For an ISO 8601 timestamp in a regional zone, use unquoted XXX ("yyyy-MM-dd'T'HH:mm:ssXXX"), which prints the real offset such as -07:00 or +09:00.
Compare calendar days without daylight-saving bugs
Comparing two Date objects with === checks object identity rather than their timestamps:
const first = new Date('2026-04-15T00:00:00Z');
const second = new Date('2026-04-15T00:00:00Z');
// first === second is false (separate objects)
// first.getTime() === second.getTime() is true Relational operators (<, <=, >, >=) compare millisecond numbers, which works for instants. For calendar due dates, once each date is formatted as 'yyyy-MM-dd' in its owning time zone, compare days without local clock shifts:
Order or match
'yyyy-MM-dd'strings directly: Four-digit years, two-digit months, and two-digit days sort in chronological order, so'2026-04-10' < '2026-04-15'and'2026-04-15' === '2026-04-15'are bothtrue.Count whole days with
Date.UTC(): Subtracting two local-midnightDateobjects and dividing by24 * 60 * 60 * 1000(86,400,000ms) fails across daylight-saving transitions. On March 8, 2026 inAmerica/New_York, clocks spring forward from02:00to03:00, shortening the local day to 23 hours (82,800,000ms) soMath.floor(diffMs / 86400000)returns0instead of1. On the November fall-back Sunday, the local day lasts 25 hours (90,000,000ms).
toUtcDayNumber_(isoDate) avoids regional transitions by converting 'yyyy-MM-dd' to a UTC day number:
It matches
/^(\d{4})-(\d{2})-(\d{2})$/and converts the three groups withNumber().It calls
Date.UTC(year, month - 1, day). JavaScriptDateconstructors andDate.UTCindex months from0(January) through11(December), while days start at1.It checks
getUTCFullYear(),getUTCMonth(), andgetUTCDate()so impossible dates such as'2026-02-30'throw an error instead of rolling into March.Because every UTC day in ECMAScript has
86,400,000milliseconds,utcMillis / MILLISECONDS_PER_DAYis an exact integer, andcalendarDaysBetween_(REFERENCE_DATE, record.dueDate)returns exact whole days.
When shifting a Date object with setDate() or setHours(), copy it first (new Date(original.getTime())) because Date setters mutate the object in place.
Pass dates across storage and client boundaries
Inside .gs code, pass Date instances to Apps Script methods that expect them, such as Range.setValue(), Utilities.formatDate(), and CalendarApp methods (covered in Create and update Calendar events). When data crosses into storage, caches, or HTML interfaces, serialize dates as strings instead:
| Boundary | Behavior when given a Date | Format to pass |
|---|---|---|
Google Sheets Range.setValue and setValues | Writes a real Sheets date or date-time serial in the spreadsheet's time zone | Pass a Date for sortable date cells, or 'yyyy-MM-dd' only when you want plain text |
PropertiesService | Stores strings only. Direct setProperty coerces a Date to a display string, and JSON.stringify turns a Date into an ISO string that JSON.parse leaves as a string | Store 'yyyy-MM-dd' for calendar dates or "yyyy-MM-dd'T'HH:mm:ss'Z'" (date.toISOString()) for instants |
CacheService | Cache.put(key, value) accepts string values only | Serialize JSON records with 'yyyy-MM-dd' or ISO 8601 UTC strings |
google.script.run in HtmlService | Rejects Date objects as parameters and return values, even nested inside an array or object, causing the call to fail | Convert every Date cell to 'yyyy-MM-dd' or an ISO 8601 UTC string before returning rows to browser code |
Returning sheet.getDataRange().getValues() directly from a google.script.run function fails as soon as any cell holds a formatted date or time. Map Date cells through Utilities.formatDate() before returning the array to a sidebar or modal.
Exercise: test a text date and a time-zone change
In
Requests!C3(rowR-102), type'Pendingwith a leading apostrophe so Sheets stores plain text.Run
inspectRequestDueDatesand confirm it stops withRow 3 Due Date holds text ("Pending") instead of a Sheets date. Format column C as Date and enter YYYY-MM-DD.Enter
2026-04-15inRequests!C3without an apostrophe. If column C kept plain-text formatting, selectC3and choose Format > Number > Date.Rerun
inspectRequestDueDatesand confirmR-102logsdue 2026-04-15 | 0 day(s) from 2026-04-15 (due on reference date).In Google Sheets, open File > Settings, set Time zone to Tokyo (
(GMT+09:00) Tokyo), and click Save settings. Leave"timeZone": "Etc/UTC"inappsscript.jsonunchanged.Run
inspectRequestDueDatesagain. ConfirmSpreadsheet time zonelogsAsia/Tokyo, whileR-101throughR-103still log2026-04-10,2026-04-15, and2026-04-20with-5,0, and+5day offsets.Delete the disposable
Request due datesspreadsheet when finished, which also removes its bound script.
Next, read Extract text with regular expressions to clean copied Workspace strings and parse structured fields and identifiers out of free text.
Project files
Complete source files for this chapter's examples.
Inspect spreadsheet due dates across time zones
Code.gs
Download fileconst REFERENCE_DATE = '2026-04-15';
const MILLISECONDS_PER_HOUR = 60 * 60 * 1000;
const MILLISECONDS_PER_DAY = 24 * MILLISECONDS_PER_HOUR;
/**
* Reads the bound Requests sheet, formats each real Due Date cell in the
* spreadsheet's own time zone, and logs the calendar-day offset from
* REFERENCE_DATE alongside an ISO 8601 UTC execution timestamp.
*
* @returns {void}
*/
function inspectRequestDueDates() {
const spreadsheet = getActiveRequestsSpreadsheet_();
const sheet = getRequestsSheet_(spreadsheet);
const rows = readRequestRows_(sheet);
const scriptTimeZone = Session.getScriptTimeZone();
const spreadsheetTimeZone = spreadsheet.getSpreadsheetTimeZone();
const executedAtUtc = Utilities.formatDate(
new Date(),
'Etc/UTC',
"yyyy-MM-dd'T'HH:mm:ss'Z'"
);
Logger.log('Script time zone: ' + scriptTimeZone);
Logger.log('Spreadsheet time zone: ' + spreadsheetTimeZone);
Logger.log('Reference date: ' + REFERENCE_DATE);
Logger.log('Executed at (UTC): ' + executedAtUtc);
if (rows.length === 0) {
Logger.log('No request rows found below the header row.');
return;
}
for (let i = 0; i < rows.length; i++) {
const record = parseRequestRow_(rows[i], i + 2, spreadsheetTimeZone);
if (record.dueDate === null) {
Logger.log(
record.requestId +
' | no due date | skipped date comparison | status: ' +
record.status
);
continue;
}
const dayOffset = calendarDaysBetween_(REFERENCE_DATE, record.dueDate);
Logger.log(
record.requestId +
' | due ' +
record.dueDate +
' | ' +
describeDayOffset_(dayOffset) +
' | status: ' +
record.status
);
}
}
/**
* Logs fixed-instant comparisons without reading a spreadsheet so you can
* inspect time-zone day shifts, a 23-hour daylight-saving day, and ISO 8601
* UTC versus regional-offset timestamps directly in Execution log.
*
* @returns {void}
*/
function runLocalDateChecks() {
const tokyoMidnight = new Date('2026-04-15T00:00:00+09:00');
Logger.log(
'Tokyo midnight in Asia/Tokyo: ' +
Utilities.formatDate(tokyoMidnight, 'Asia/Tokyo', 'yyyy-MM-dd')
);
Logger.log(
'Tokyo midnight in Etc/UTC: ' +
Utilities.formatDate(tokyoMidnight, 'Etc/UTC', 'yyyy-MM-dd')
);
const utcMidnight = new Date('2026-04-15');
Logger.log(
'new Date("2026-04-15") in Etc/UTC: ' +
Utilities.formatDate(utcMidnight, 'Etc/UTC', 'yyyy-MM-dd')
);
Logger.log(
'new Date("2026-04-15") in America/Los_Angeles: ' +
Utilities.formatDate(utcMidnight, 'America/Los_Angeles', 'yyyy-MM-dd')
);
const dstStart = new Date('2026-03-08T00:00:00-05:00');
const dstNextMidnight = new Date('2026-03-09T00:00:00-04:00');
const elapsedHours =
(dstNextMidnight.getTime() - dstStart.getTime()) / MILLISECONDS_PER_HOUR;
const flooredDayCount = Math.floor(
(dstNextMidnight.getTime() - dstStart.getTime()) / MILLISECONDS_PER_DAY
);
Logger.log(
'2026-03-08 to 2026-03-09 in America/New_York: ' +
elapsedHours +
' hours, Math.floor(ms / 86400000) = ' +
flooredDayCount +
', calendarDaysBetween = ' +
calendarDaysBetween_('2026-03-08', '2026-03-09')
);
const sampleInstant = new Date('2026-04-15T18:30:00Z');
Logger.log(
'Instant in Etc/UTC: ' +
Utilities.formatDate(
sampleInstant,
'Etc/UTC',
"yyyy-MM-dd'T'HH:mm:ss'Z'"
)
);
Logger.log(
'Instant in America/Los_Angeles: ' +
Utilities.formatDate(
sampleInstant,
'America/Los_Angeles',
"yyyy-MM-dd'T'HH:mm:ssXXX"
)
);
}
/**
* @returns {SpreadsheetApp.Spreadsheet}
*/
function getActiveRequestsSpreadsheet_() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
if (!spreadsheet) {
throw new Error(
'No active spreadsheet found. Open this bound project from your practice Google Sheet with Extensions > Apps Script.'
);
}
return spreadsheet;
}
/**
* @param {SpreadsheetApp.Spreadsheet} spreadsheet
* @returns {SpreadsheetApp.Sheet}
*/
function getRequestsSheet_(spreadsheet) {
const sheet = spreadsheet.getSheetByName('Requests');
if (!sheet) {
throw new Error(
'Add a Sheet tab named Requests and import sample.csv before running inspectRequestDueDates.'
);
}
return sheet;
}
/**
* Reads all rows from Requests and checks the four-column header row.
*
* @param {SpreadsheetApp.Sheet} sheet
* @returns {unknown[][]}
*/
function readRequestRows_(sheet) {
const values = sheet.getDataRange().getValues();
const expectedHeaders = ['Request ID', 'Summary', 'Due Date', 'Status'];
const headerRow = values[0] || [];
if (
headerRow.length !== expectedHeaders.length ||
expectedHeaders.some((header, index) => headerRow[index] !== header)
) {
throw new Error(
'Requests must start in A1:D1 with headers: Request ID, Summary, Due Date, Status.'
);
}
return values.slice(1);
}
/**
* Validates one sheet row and formats any real Date cell in the spreadsheet's
* own time zone so its calendar day does not shift when the script uses UTC.
*
* @param {unknown[]} row
* @param {number} rowNumber
* @param {string} spreadsheetTimeZone
* @returns {{requestId: string, summary: string, dueDate: string | null, status: string}}
*/
function parseRequestRow_(row, rowNumber, spreadsheetTimeZone) {
if (row.length < 4) {
throw new Error('Row ' + rowNumber + ' must contain four columns.');
}
return {
requestId: readRequiredText_(row[0], 'Request ID', rowNumber),
summary: readRequiredText_(row[1], 'Summary', rowNumber),
dueDate: readOptionalDueDate_(row[2], rowNumber, spreadsheetTimeZone),
status: readRequiredText_(row[3], 'Status', rowNumber),
};
}
/**
* @param {unknown} value
* @param {string} columnName
* @param {number} rowNumber
* @returns {string}
*/
function readRequiredText_(value, columnName, rowNumber) {
if (typeof value !== 'string' || value.trim() === '') {
throw new Error(
'Row ' + rowNumber + ' is missing required text in ' + columnName + '.'
);
}
return value.trim();
}
/**
* Accepts an empty cell ('') or a valid Date instance from Range.getValues(),
* and rejects unparsed date strings instead of calling new Date(value).
*
* @param {unknown} value
* @param {number} rowNumber
* @param {string} spreadsheetTimeZone
* @returns {string | null}
*/
function readOptionalDueDate_(value, rowNumber, spreadsheetTimeZone) {
if (value === '') {
return null;
}
if (value instanceof Date) {
if (Number.isNaN(value.getTime())) {
throw new Error('Row ' + rowNumber + ' has an invalid Date in Due Date.');
}
return Utilities.formatDate(value, spreadsheetTimeZone, 'yyyy-MM-dd');
}
if (typeof value === 'string') {
throw new Error(
'Row ' +
rowNumber +
' Due Date holds text (' +
JSON.stringify(value) +
') instead of a Sheets date. Format column C as Date and enter YYYY-MM-DD.'
);
}
throw new Error(
'Row ' + rowNumber + ' Due Date must be a real Sheets date or blank.'
);
}
/**
* Converts a validated YYYY-MM-DD calendar date into an integer UTC day count
* since 1970-01-01 so calendar-day subtraction ignores daylight saving time.
*
* @param {string} isoDate
* @returns {number}
*/
function toUtcDayNumber_(isoDate) {
const match = /^(\d{4})-(\d{2})-(\d{2})$/.exec(isoDate);
if (!match) {
throw new Error(
'Expected YYYY-MM-DD calendar date, got ' + JSON.stringify(isoDate) + '.'
);
}
const year = Number(match[1]);
const month = Number(match[2]);
const day = Number(match[3]);
const utcMillis = Date.UTC(year, month - 1, day);
const check = new Date(utcMillis);
if (
check.getUTCFullYear() !== year ||
check.getUTCMonth() !== month - 1 ||
check.getUTCDate() !== day
) {
throw new Error('Invalid calendar date ' + JSON.stringify(isoDate) + '.');
}
return utcMillis / MILLISECONDS_PER_DAY;
}
/**
* Returns endIsoDate minus startIsoDate in whole calendar days.
*
* @param {string} startIsoDate
* @param {string} endIsoDate
* @returns {number}
*/
function calendarDaysBetween_(startIsoDate, endIsoDate) {
return toUtcDayNumber_(endIsoDate) - toUtcDayNumber_(startIsoDate);
}
/**
* @param {number} dayOffset
* @returns {string}
*/
function describeDayOffset_(dayOffset) {
if (dayOffset < 0) {
const overdueDays = Math.abs(dayOffset);
return (
dayOffset +
' day(s) from ' +
REFERENCE_DATE +
' (overdue by ' +
overdueDays +
' day(s))'
);
}
if (dayOffset === 0) {
return '0 day(s) from ' + REFERENCE_DATE + ' (due on reference date)';
}
return (
'+' +
dayOffset +
' day(s) from ' +
REFERENCE_DATE +
' (due in ' +
dayOffset +
' day(s))'
);
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"dependencies": {},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
sample.csv
Download fileRequest ID,Summary,Due Date,Status
R-101,Renew room projector lamp,2026-04-10,Open
R-102,Confirm catering headcount,2026-04-15,Open
R-103,Print workshop name badges,2026-04-20,Open
R-104,Archive winter sign-in sheets,,Done