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