Timezone-safe dates in Sheets and Apps Script

TL;DR — Stop off-by-one dates between Apps Script and Google Sheets. Use the spreadsheet timezone, Utilities.formatDate, and careful cell reads/writes so timestamps mean the same thing to you and your teammates.

new Date() in Apps Script, a date cell in Sheets, and a teammate in another city will happily disagree by a day — and you'll spend an afternoon blaming "JavaScript date bugs." Most of the time it's timezone mismatch, not cursed code. Here's the boring checklist we use so timestamps stay readable and triggers fire on the day you meant.

TL;DR

Spreadsheet timezone ≠ script runtime timezone ≠ your laptop timezone. Pick the spreadsheet as source of truth for business dates.
Prefer Utilities.formatDate(date, tz, pattern) when you need a string humans (or other systems) will parse consistently.
Reading a date cell gives you a Date object already — don't re-parse the displayed text unless you mean to.
Writing timestamps: store real Date values in cells when you can; format for display with the sheet's locale/timezone.
Before a time-driven trigger goes live, verify the project's timezone and the spreadsheet timezone.

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

The failure mode

Classic bootstrapper day:

Form submit at "end of day" lands on the next calendar day in the sheet
A "due date" column shifts backward one day after a script touches it
Nightly trigger set for 9pm runs at what feels like morning because the script project timezone was left on default
You log date.toString() in Executions, paste it into Slack, and three people read three different local times

We've been that person adding + 12 * 60 * 60 * 1000 "just to fix it." That patch works until daylight saving or the next client in America/Chicago. Fix the timezone story once instead.

What not to do

Trust new Date().toLocaleString() for persisted values — locale strings are for humans glancing at logs, not for round-tripping into Sheets or comparing across machines.
Parse the cell's displayed text — getDisplayValue() on a date and new Date('9/10/2026') invites ambiguous MM/DD vs DD/MM and silent off-by-ones.
Assume script timezone == spreadsheet timezone — they are configured separately. Bound scripts often feel aligned until someone changes File → Settings → Time zone in Sheets and forgets Project Settings in Apps Script.
Stamp Utilities.formatDate with a hard-coded 'GMT' (or your home zone) for every client file — fine for one workbook you own; painful in templates you reuse.

Mental model

Piece	What it controls
Spreadsheet timezone	How Sheets displays and interprets date serials for that file
Apps Script project timezone	`Session.getScriptTimeZone()`, time-driven triggers, default for some utilities
`Date` object in JS	An instant (UTC under the hood) — not "a calendar day in a city" until you format it
`Utilities.formatDate(date, timeZone, format)`	Stable string for a specific timezone + pattern

Rule of thumb: for anything a human reads as a calendar day in the workbook, format (or compare) in the spreadsheet's timezone. For trigger schedules, also check the script timezone so "9:00pm daily" means 9pm where you intended.

function getSpreadsheetTimeZone_() {
  return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
}

function getScriptTimeZone_() {
  return Session.getScriptTimeZone();
}

Log both once on a new project. If they differ and you didn't mean it, align them before you build date logic.

Reading dates from cells (without off-by-one)

When the cell is typed as a Date, getValue() returns a JavaScript Date. Use that.

function readDateCellSafe() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');
  var raw = sheet.getRange('B2').getValue();

  if (!(raw instanceof Date)) {
    throw new Error('B2 is not a date cell. Got: ' + typeof raw + ' / ' + raw);
  }

  var tz = getSpreadsheetTimeZone_();
  // Calendar day as the spreadsheet means it — not your laptop
  var ymd = Utilities.formatDate(raw, tz, 'yyyy-MM-dd');
  Logger.log('Spreadsheet calendar day: ' + ymd);
  return { instant: raw, calendarDay: ymd, timeZone: tz };
}

Avoid:

// Fragile — display string depends on locale; parsing is ambiguous
var bad = new Date(sheet.getRange('B2').getDisplayValue());

If you must accept text input (imports, CSV), parse explicitly (yyyy-MM-dd) or use a library/strategy you control — don't shrug and pass free-form strings into new Date().

Writing timestamps teammates won't misread

Prefer writing a real Date into the cell and letting Sheets format it:

function stampProcessedAt(row) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Tasks');
  var tz = getSpreadsheetTimeZone_();
  var now = new Date();

  // Real date value — teammates see it in the spreadsheet timezone / number format
  sheet.getRange(row, 4).setValue(now);

  // Optional: adjacent human-readable audit string locked to spreadsheet TZ
  sheet.getRange(row, 5).setValue(
    Utilities.formatDate(now, tz, 'yyyy-MM-dd HH:mm:ss z')
  );
}

Tips that save Slack threads:

Set the column's number format to include date and time if the time matters (Format → Number → Date time).
For "due date" as a day (no time), store midnight in the spreadsheet timezone deliberately — or store a plain yyyy-MM-dd string if you truly only care about the calendar day and never want time math.
Don't mix "date-only" and "datetime" in the same column without labeling which is which.

Creating "today" in the spreadsheet timezone:

function todayInSpreadsheetTz_() {
  var tz = getSpreadsheetTimeZone_();
  var ymd = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  // Construct a Date at midday UTC-ish via Parts — or setValue(ymd) into a date-formatted cell.
  // Simplest for Sheets: write the yyyy-MM-dd string into a Date-formatted cell and let Sheets coerce,
  // or use Utilities.parseDate when you need a Date object back:
  return Utilities.parseDate(ymd, tz, 'yyyy-MM-dd');
}

Utilities.parseDate respects the timezone you pass — that's the point versus new Date(ymd).

`new Date()` vs `Utilities.formatDate`

Need	Use
Capture "now" as an instant	`new Date()`
Compare instants / durations	Date objects / `getTime()`
Show or persist a calendar day in a known zone	`Utilities.formatDate(date, tz, 'yyyy-MM-dd')`
Build a Date from a calendar day in a known zone	`Utilities.parseDate(str, tz, pattern)`
Schedule time-driven triggers	Apps Script project timezone in Project Settings

function demoFormatVsRaw() {
  var now = new Date();
  var sheetTz = getSpreadsheetTimeZone_();
  var scriptTz = getScriptTimeZone_();

  Logger.log('Raw toString(): ' + now.toString());
  Logger.log('Sheet TZ: ' + Utilities.formatDate(now, sheetTz, 'yyyy-MM-dd HH:mm:ss z'));
  Logger.log('Script TZ: ' + Utilities.formatDate(now, scriptTz, 'yyyy-MM-dd HH:mm:ss z'));
}

Run that once. If the two formatted lines differ, every "date-only" bug you hit suddenly has a suspect.

Checklist before time-driven triggers

Before you trust a nightly job:

Spreadsheet — File → Settings → Time zone (matches the business / client).
Apps Script project — Project Settings → Time zone (matches when you want triggers to fire).
Trigger clock — confirm "9:00pm to 10:00pm" in the trigger UI against the script timezone, not your wristwatch alone.
DST — if the client observes daylight saving, prefer named zones (America/New_York) over fixed GMT-5.
Smoke test — log Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss z') from the triggered function on the first few runs.

Misaligned trigger timezone is the silent cousin of off-by-one cell dates — same family of bug, different menu.

Minimal test plan

Set spreadsheet TZ to America/Los_Angeles and script TZ to America/New_York on a scratch file (on purpose).
Put a date-only value in A1 via the UI. Read with getValue(), format with both timezones — note the calendar day difference near midnight.
Write new Date() with setValue, then format the cell display — confirm the column format shows what teammates need.
Create a one-time trigger a few minutes ahead; confirm it fires in the script timezone you configured.
Align both timezones the way production should be, re-run, and keep that as the template default.

Keep the helpers handy

If you want these timezone helpers (and the rest of the bootstrapper kit) one click away in the editor, NitroGAS keeps snippets inside Apps Script — Co-Pilot's there when you need to adapt them. Free extension; Co-Pilot optional. The patterns above stand alone either way.

Closing checklist

Spreadsheet timezone and script timezone are intentional (and documented for the client)
Date cells are read with getValue() as Date — not re-parsed from display text
Human-facing day strings go through Utilities.formatDate / parseDate with an explicit TZ
Written timestamps use real Date values (plus clear number formats); audit strings include z when useful
Time-driven triggers were verified against the script timezone, including near midnight / DST edges

That's it. One spreadsheet timezone as source of truth, format on purpose, and stop paying the off-by-one tax.

Happy Coding!