Log stamps and export filenames that disagree with the sheet’s timezone are a classic bootstrapper footgun. formatInSpreadsheetTimezone(date, pattern) formats with SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), falling back to Session.getScriptTimeZone(), via Utilities.formatDate — so what you log matches what you see in the grid.
What you'll need
- A container-bound script (or any script that can open a spreadsheet)
- A
Date(or somethingnew Date(...)accepts) - Optional pattern string (
yyyy-MM-dd HH:mm:ssby default)
How to use this snippet
/**
* Format a date in the active spreadsheet's timezone (fallback: script timezone).
* Null/undefined date → empty string. Default pattern: yyyy-MM-dd HH:mm:ss.
* @param {Date|string|number} date
* @param {string=} pattern Utilities.formatDate pattern
* @return {string}
*/
function formatInSpreadsheetTimezone(date, pattern) {
if (date == null) return '';
pattern = pattern || 'yyyy-MM-dd HH:mm:ss';
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tz = (ss && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone();
var d = date instanceof Date ? date : new Date(date);
if (isNaN(d.getTime())) return '';
return Utilities.formatDate(d, tz, pattern);
}
Example
function stampLogRow_(message) {
var sheet = SpreadsheetApp.getActive().getSheetByName('Log');
sheet.appendRow([
formatInSpreadsheetTimezone(new Date()),
message
]);
}
function nightlyExportName_() {
// File name matches File → Settings → Time zone, not the server's UTC clock
return 'export-' + formatInSpreadsheetTimezone(new Date(), 'yyyyMMdd-HHmm') + '.csv';
}
Tips:
- Prefer the spreadsheet timezone when humans read the sheet; use script timezone only when there is no active spreadsheet.
- Invalid dates return
''— don’t silently stringifyInvalid Date. - Keep one pattern for logs and another for filenames (
yyyyMMdd-HHmm) so both stay sortable. Utilities.formatDateuses ICU-style patterns, not Moment.js tokens.
Tip: NitroGAS Co-Pilot can swap ad-hoc Utilities.formatDate(..., 'GMT', …) calls for formatInSpreadsheetTimezone so your Log tab stops lying about “when.”
Happy Coding!
