Skip to content
Skip to chapter

Chapter 7

JavaScript and the runtime

11 min read

Work with dates and time zones

Format Google Sheets dates with Utilities.formatDate and getSpreadsheetTimeZone, set appsscript.json timeZone, and compare calendar due dates.

By the end

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.

Chapter navigation

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:

RepresentationWhat it storesCarries a time zone?Value in .gs codePrimary use
JavaScript DateMilliseconds since 1970-01-01T00:00:00ZNoObject (value instanceof Date is true)Passing instants to Apps Script APIs and writing date cells to Sheets
Sheets date cellSerial number for a calendar day or local wall clockSpreadsheet 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 patternOnly if an offset or 'Z' appears in the textPrimitive 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.

note

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 sourceWhere it is configuredWhat it governsWhen to pass it to Utilities.formatDate
spreadsheet.getSpreadsheetTimeZone()Google Sheets under File > SettingsSpreadsheet 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 editornew Date(year, monthIndex, day), getFullYear(), getMonth(), getDate(), getHours(), setHours(), and time-driven triggersFormatting 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 transitionsRecording 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 showing 2026-04-15 starts at 2026-04-15T00:00:00+09:00, which is the instant 2026-04-14T15:00:00Z. Formatting that Date in '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's Date time-string rules). In America/Los_Angeles (UTC-7 in April), 2026-04-15T00:00:00Z falls at 17:00 on 2026-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:30 in America/Los_Angeles equals 2026-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.

  1. Create a blank Google Sheet named Request due dates and rename its first tab to Requests.

  2. Open File > Settings in Google Sheets, check the current Time zone, and leave it unchanged so the sheet keeps your default regional zone.

  3. Copy the comma-separated text from sample.csv into cell A1, or import the file with File > Import.

sample.csv
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

Download sample.csv
  1. If you pasted into A1, select column A, choose Data > Split text to columns, and set Separator to Comma.

  2. Confirm that Requests!A1:D5 holds headers in row 1, real dates in C2:C4 (2026-04-10, 2026-04-15, 2026-04-20), and a blank C5. Double-clicking C2 opens a calendar picker.

  3. Choose Extensions > Apps Script from the spreadsheet and replace Code.gs with the file below.

  4. Open Project Settings and enable Show "appsscript.json" manifest file in editor.

  5. Return to Editor, replace appsscript.json with the manifest below, and save both files.

Code.gs
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))'
  );
}

Download Code.gs
appsscript.json
{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

Download appsscript.json

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

  1. Select inspectRequestDueDates in the toolbar and click Run.

  2. Review the consent prompt, confirm it requests access only to the current spreadsheet, and approve it.

  3. Read Execution log. Spreadsheet time zone shows your sheet's regional zone and Executed at (UTC) shows the current UTC clock, while the other six lines match this output:

Source
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

  1. Select runLocalDateChecks and click Run. Because every input is a fixed timestamp, Execution log prints these exact seven lines in every account:

Source
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 a Date instance. Because typeof value returns 'object' for any object including null, readOptionalDueDate_ checks value instanceof Date and !Number.isNaN(value.getTime()). An invalid Date still passes instanceof Date while holding NaN as its timestamp.

  • An empty cell (C5 on R-104) arrives as '', never null or undefined. readOptionalDueDate_ returns null for ''. Passing '' to Number() produces 0, and new Date(0) fabricates 1970-01-01T00:00:00Z.

  • An unparsed text cell such as '04/15/2026' or 'Pending' arrives as a non-empty string. readOptionalDueDate_ throws an error naming the row number and text instead of calling new 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 fieldCorrect tokenOutput on 2025-12-31T14:07:09Z in Etc/UTCLookalike trapTrap output and cause
Calendar yearyyyy2025YYYY (week-based year)2026, because Wednesday, Dec 31, 2025 belongs to week 1 of 2026
Month of yearMM (or MMM)12 (or Dec)mm (minute of hour)07, placing minutes into the month slot
Day of monthdd31DD (day of year, 1 to 366)365 (or 105 on April 15)
Hour (24-hour clock)HH14hh (12-hour clock without a)02, hiding whether the time is AM or PM
Minute and secondmm:ss07:09MM:SS (SS is milliseconds)12:00
Literal 'T' and UTC 'Z''T' and 'Z'2025-12-31T14:07:09ZUnquoted T or unquoted ZUnquoted 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.

warning

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:

Source
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 both true.

  • Count whole days with Date.UTC(): Subtracting two local-midnight Date objects and dividing by 24 * 60 * 60 * 1000 (86,400,000 ms) fails across daylight-saving transitions. On March 8, 2026 in America/New_York, clocks spring forward from 02:00 to 03:00, shortening the local day to 23 hours (82,800,000 ms) so Math.floor(diffMs / 86400000) returns 0 instead of 1. On the November fall-back Sunday, the local day lasts 25 hours (90,000,000 ms).

toUtcDayNumber_(isoDate) avoids regional transitions by converting 'yyyy-MM-dd' to a UTC day number:

  1. It matches /^(\d{4})-(\d{2})-(\d{2})$/ and converts the three groups with Number().

  2. It calls Date.UTC(year, month - 1, day). JavaScript Date constructors and Date.UTC index months from 0 (January) through 11 (December), while days start at 1.

  3. It checks getUTCFullYear(), getUTCMonth(), and getUTCDate() so impossible dates such as '2026-02-30' throw an error instead of rolling into March.

  4. Because every UTC day in ECMAScript has 86,400,000 milliseconds, utcMillis / MILLISECONDS_PER_DAY is an exact integer, and calendarDaysBetween_(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:

BoundaryBehavior when given a DateFormat to pass
Google Sheets Range.setValue and setValuesWrites a real Sheets date or date-time serial in the spreadsheet's time zonePass a Date for sortable date cells, or 'yyyy-MM-dd' only when you want plain text
PropertiesServiceStores 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 stringStore 'yyyy-MM-dd' for calendar dates or "yyyy-MM-dd'T'HH:mm:ss'Z'" (date.toISOString()) for instants
CacheServiceCache.put(key, value) accepts string values onlySerialize JSON records with 'yyyy-MM-dd' or ISO 8601 UTC strings
google.script.run in HtmlServiceRejects Date objects as parameters and return values, even nested inside an array or object, causing the call to failConvert 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

  1. In Requests!C3 (row R-102), type 'Pending with a leading apostrophe so Sheets stores plain text.

  2. Run inspectRequestDueDates and confirm it stops with Row 3 Due Date holds text ("Pending") instead of a Sheets date. Format column C as Date and enter YYYY-MM-DD.

  3. Enter 2026-04-15 in Requests!C3 without an apostrophe. If column C kept plain-text formatting, select C3 and choose Format > Number > Date.

  4. Rerun inspectRequestDueDates and confirm R-102 logs due 2026-04-15 | 0 day(s) from 2026-04-15 (due on reference date).

  5. In Google Sheets, open File > Settings, set Time zone to Tokyo ((GMT+09:00) Tokyo), and click Save settings. Leave "timeZone": "Etc/UTC" in appsscript.json unchanged.

  6. Run inspectRequestDueDates again. Confirm Spreadsheet time zone logs Asia/Tokyo, while R-101 through R-103 still log 2026-04-10, 2026-04-15, and 2026-04-20 with -5, 0, and +5 day offsets.

  7. Delete the disposable Request due dates spreadsheet 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
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))'
  );
}

appsscript.json

Download file
appsscript.json
{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

sample.csv

Download file
sample.csv
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