const SHEETS_API_SCOPE = 'https://www.googleapis.com/auth/spreadsheets'; const EXTERNAL_REQUEST_SCOPE = 'https://www.googleapis.com/auth/script.external_request'; const FORECAST_CACHE_PREFIX = 'book.parallel-http.station.v1:'; const LAST_SYNC_PROPERTY_KEY = 'book.parallel-http.last-sync.v1'; const PLACEHOLDER_SPREADSHEET_ID = 'PASTE_PRACTICE_SPREADSHEET_ID_HERE'; const MAX_CACHE_TTL_SECONDS = 21600; const MAX_CACHE_KEY_LENGTH = 250; const MAX_CACHE_VALUE_LENGTH = 90000; const MAX_HTTP_BODY_LENGTH = 16384; const OPEN_METEO_ATTRIBUTION = 'Weather data by Open-Meteo.com (CC BY 4.0, https://open-meteo.com/en/licence), unmodified daily high'; const PARALLEL_SHEETS_CONFIG = Object.freeze({ spreadsheetId: PLACEHOLDER_SPREADSHEET_ID, cacheTtlSeconds: 1800, approveSheetWrite: false, confirmClearState: false, }); const FORECAST_STATIONS = Object.freeze([ Object.freeze({ stationCode: '0042', location: 'Berlin', latitude: 52.52, longitude: 13.41, }), Object.freeze({ stationCode: '0108', location: 'Tokyo', latitude: 35.68, longitude: 139.69, }), Object.freeze({ stationCode: '0215', location: 'Chicago', latitude: 41.88, longitude: -87.63, }), ]); /** * @typedef {{stationCode: string, location: string, latitude: number, longitude: number}} ForecastStationSpec_ */ /** * @typedef {{version: number, stationCode: string, location: string, forecastDate: string, highCelsius: number, fetchedAtUtc: string}} CachedStationForecast_ */ /** * @typedef {{stationCode: string, location: string, highText: string, statusText: string, source: 'cache' | 'fetched' | 'failed'}} StationSyncRow_ */ /** * Dry-run preview: inspects CacheService.getAll for the three stations, builds * the planned UrlFetchApp.fetchAll request array for cache misses, and verifies * workbook headers when spreadsheetId has been pasted. Makes no HTTP or sheet writes. */ function previewParallelForecastPlan() { const config = requireParallelSheetsConfig_(false); const cache = CacheService.getScriptCache(); const cacheKeys = FORECAST_STATIONS.map((station) => stationCacheKey_(station.stationCode)); const rawCached = cache.getAll(cacheKeys); const cacheMap = decodeCachedForecastMap_(rawCached); /** @type {{stationCode: string, location: string, cacheStatus: 'hit' | 'miss', url: string}[]} */ const stationPlans = []; /** @type {UrlFetchApp.URLFetchRequestOptions[]} */ const missRequests = []; for (const station of FORECAST_STATIONS) { const key = stationCacheKey_(station.stationCode); const hit = cacheMap.has(key); const url = buildStationForecastUrl_(station); stationPlans.push({ stationCode: station.stationCode, location: station.location, cacheStatus: hit ? 'hit' : 'miss', url: url, }); if (!hit) { missRequests.push(buildStationFetchRequest_(url)); } } let workbookVerified = false; if (config.spreadsheetId !== null) { ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [SHEETS_API_SCOPE]); verifyPracticeWorkbookLayout_(config.spreadsheetId); workbookVerified = true; } const lastReceiptRaw = PropertiesService.getScriptProperties().getProperty(LAST_SYNC_PROPERTY_KEY); const report = { mode: 'preview', spreadsheetConfigured: config.spreadsheetId !== null, workbookVerified: workbookVerified, cacheHits: cacheMap.size, cacheMisses: missRequests.length, plannedFetchAllCount: missRequests.length, disjointWriteRanges: ['Forecast!C2:D4', 'Audit!A2:C2'], valueInputOption: 'RAW', stations: stationPlans, hasSavedReceipt: lastReceiptRaw !== null, }; console.log(JSON.stringify(report, null, 2)); return report; } /** * Live batch sync: verifies workbook layout with Sheets.Spreadsheets.Values.batchGet, * reads cached forecasts with CacheService.getAll, fetches only misses concurrently * with UrlFetchApp.fetchAll, caches valid 200 payloads with putAll, and writes * Forecast!C2:D4 plus Audit!A2:C2 in one Values.batchUpdate call using RAW mode. */ function syncParallelForecastBatch() { const config = requireParallelSheetsConfig_(true); if (config.spreadsheetId === null) { throw new Error('Set PARALLEL_SHEETS_CONFIG.spreadsheetId before running syncParallelForecastBatch.'); } if (!config.approveSheetWrite) { throw new Error( 'Run previewParallelForecastPlan first, then set PARALLEL_SHEETS_CONFIG.approveSheetWrite to true.', ); } ScriptApp.requireScopes(ScriptApp.AuthMode.FULL, [SHEETS_API_SCOPE, EXTERNAL_REQUEST_SCOPE]); const lock = LockService.getScriptLock(); lock.waitLock(5000); try { verifyPracticeWorkbookLayout_(config.spreadsheetId); const cache = CacheService.getScriptCache(); const cacheKeys = FORECAST_STATIONS.map((station) => stationCacheKey_(station.stationCode)); const cachedByKey = decodeCachedForecastMap_(cache.getAll(cacheKeys)); /** @type {ForecastStationSpec_[]} */ const missStations = []; /** @type {UrlFetchApp.URLFetchRequestOptions[]} */ const missRequests = []; for (const station of FORECAST_STATIONS) { const key = stationCacheKey_(station.stationCode); if (!cachedByKey.has(key)) { missStations.push(station); missRequests.push(buildStationFetchRequest_(buildStationForecastUrl_(station))); } } const runTimestampUtc = new Date().toISOString(); /** @type {Map} */ const fetchedByCode = new Map(); /** @type {{[key: string]: string}} */ const freshCacheEntries = {}; let freshCacheCount = 0; if (missRequests.length > 0) { const responses = UrlFetchApp.fetchAll(missRequests); if (!Array.isArray(responses) || responses.length !== missStations.length) { throw new Error('UrlFetchApp.fetchAll returned an unexpected number of responses.'); } for (let index = 0; index < missStations.length; index += 1) { const station = missStations[index]; const response = responses[index]; const parsedOutcome = inspectStationHttpResponse_(response); if ('errorLabel' in parsedOutcome) { fetchedByCode.set(station.stationCode, parsedOutcome); continue; } fetchedByCode.set(station.stationCode, parsedOutcome); /** @type {CachedStationForecast_} */ const cacheRecord = { version: 1, stationCode: station.stationCode, location: station.location, forecastDate: parsedOutcome.forecastDate, highCelsius: parsedOutcome.highCelsius, fetchedAtUtc: runTimestampUtc, }; const serialized = JSON.stringify(cacheRecord); const key = stationCacheKey_(station.stationCode); if (serialized.length <= MAX_CACHE_VALUE_LENGTH) { freshCacheEntries[key] = serialized; freshCacheCount += 1; } } if (freshCacheCount > 0) { cache.putAll(freshCacheEntries, config.cacheTtlSeconds); } } const stationRows = buildStationOutputRows_(cachedByKey, fetchedByCode); const forecastValues = stationRows.map((row) => [row.highText, row.statusText]); const cacheHits = stationRows.filter((row) => row.source === 'cache').length; const fetchedOk = stationRows.filter((row) => row.source === 'fetched').length; const failedCount = stationRows.filter((row) => row.source === 'failed').length; const auditSummary = cacheHits + ' cached, ' + fetchedOk + ' fetched, ' + failedCount + ' failed'; const updateResponse = Sheets.Spreadsheets.Values.batchUpdate( { valueInputOption: 'RAW', data: [ { range: 'Forecast!C2:D4', majorDimension: 'ROWS', values: forecastValues, }, { range: 'Audit!A2:C2', majorDimension: 'ROWS', values: [[runTimestampUtc, auditSummary, OPEN_METEO_ATTRIBUTION]], }, ], }, config.spreadsheetId, ); if ( !updateResponse || !Array.isArray(updateResponse.responses) || updateResponse.responses.length !== 2 ) { throw new Error('Sheets Values.batchUpdate returned an unexpected response structure.'); } const receipt = { version: 1, ranAtUtc: runTimestampUtc, cacheHits: cacheHits, fetchedOk: fetchedOk, failedCount: failedCount, updatedCells: typeof updateResponse.totalUpdatedCells === 'number' ? updateResponse.totalUpdatedCells : 0, }; PropertiesService.getScriptProperties().setProperty( LAST_SYNC_PROPERTY_KEY, JSON.stringify(receipt), ); const report = { mode: 'sync', ranAtUtc: runTimestampUtc, cacheHits: cacheHits, fetchedOk: fetchedOk, failedCount: failedCount, updatedCells: receipt.updatedCells, rows: stationRows, }; console.log(JSON.stringify(report, null, 2)); return report; } finally { lock.releaseLock(); } } /** * Removes the three station keys from CacheService with removeAll and deletes * the last-sync receipt key from Script Properties. Does not modify the workbook. */ function clearForecastCacheAndState() { const config = requireParallelSheetsConfig_(false); if (!config.confirmClearState) { throw new Error( 'Set PARALLEL_SHEETS_CONFIG.confirmClearState to true before clearing cached forecasts and state.', ); } const lock = LockService.getScriptLock(); lock.waitLock(5000); try { const cacheKeys = FORECAST_STATIONS.map((station) => stationCacheKey_(station.stationCode)); CacheService.getScriptCache().removeAll(cacheKeys); PropertiesService.getScriptProperties().deleteProperty(LAST_SYNC_PROPERTY_KEY); const report = { clearedCacheKeys: cacheKeys, deletedPropertyKey: LAST_SYNC_PROPERTY_KEY, }; console.log(JSON.stringify(report, null, 2)); return report; } finally { lock.releaseLock(); } } /** * Verifies both tabs and expected station rows in one Sheets API batchGet call. * @param {string} spreadsheetId */ function verifyPracticeWorkbookLayout_(spreadsheetId) { const batch = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, { ranges: ['Forecast!A1:D4', 'Audit!A1:C2'], valueRenderOption: 'UNFORMATTED_VALUE', }); if (!batch || !Array.isArray(batch.valueRanges) || batch.valueRanges.length !== 2) { throw new Error('Sheets Values.batchGet did not return both Forecast and Audit ranges.'); } const forecastGrid = batch.valueRanges[0].values || []; const auditGrid = batch.valueRanges[1].values || []; const expectedForecastHeaders = ['Station Code', 'Location', 'Next High C', 'Fetch Status']; const forecastHeaderRow = forecastGrid[0] || []; for (let col = 0; col < expectedForecastHeaders.length; col += 1) { if (forecastHeaderRow[col] !== expectedForecastHeaders[col]) { throw new Error( 'Forecast!A1:D1 must match headers: ' + expectedForecastHeaders.join(', '), ); } } for (let i = 0; i < FORECAST_STATIONS.length; i += 1) { const row = forecastGrid[i + 1] || []; const expected = FORECAST_STATIONS[i]; const normalizedCode = normalizeStationCodeCell_(row[0]); if (normalizedCode !== expected.stationCode || row[1] !== expected.location) { throw new Error( 'Forecast row ' + (i + 2) + ' must contain station ' + expected.stationCode + ' (' + expected.location + ') in columns A:B.', ); } } const expectedAuditHeaders = ['Last Run UTC', 'Cache Hits / Misses', 'Attribution']; const auditHeaderRow = auditGrid[0] || []; for (let col = 0; col < expectedAuditHeaders.length; col += 1) { if (auditHeaderRow[col] !== expectedAuditHeaders[col]) { throw new Error('Audit!A1:C1 must match headers: ' + expectedAuditHeaders.join(', ')); } } } /** * Accepts '0042' or numeric 42 when CSV import stripped leading zeros in column A. * @param {unknown} cell * @returns {string} */ function normalizeStationCodeCell_(cell) { if (typeof cell === 'string') return cell.trim().padStart(4, '0'); if (typeof cell === 'number' && Number.isInteger(cell) && cell >= 0) { return String(cell).padStart(4, '0'); } return ''; } /** * @param {string} stationCode * @returns {string} */ function stationCacheKey_(stationCode) { const key = FORECAST_CACHE_PREFIX + stationCode; if (key.length > MAX_CACHE_KEY_LENGTH) { throw new Error('Cache key exceeds the 250-character CacheService limit.'); } return key; } /** * @param {ForecastStationSpec_} station * @returns {string} */ function buildStationForecastUrl_(station) { return ( 'https://api.open-meteo.com/v1/forecast' + '?latitude=' + encodeURIComponent(String(station.latitude)) + '&longitude=' + encodeURIComponent(String(station.longitude)) + '&daily=temperature_2m_max' + '&forecast_days=1' + '&temperature_unit=celsius' + '&timezone=UTC' ); } /** * @param {string} url * @returns {UrlFetchApp.URLFetchRequestOptions} */ function buildStationFetchRequest_(url) { return { url: url, method: 'get', headers: { Accept: 'application/json', }, muteHttpExceptions: true, followRedirects: false, }; } /** * Inspects one HTTPResponse from fetchAll without throwing away sibling responses. * @param {UrlFetchApp.HTTPResponse} response * @returns {{highCelsius: number, forecastDate: string} | {errorLabel: string}} */ function inspectStationHttpResponse_(response) { const status = response.getResponseCode(); if (status !== 200) { return { errorLabel: 'HTTP ' + status }; } const headers = response.getAllHeaders(); if (!hasJsonContentType_(headers)) { return { errorLabel: 'Non-JSON Content-Type' }; } const body = response.getContentText('UTF-8'); if (body.length === 0 || body.length > MAX_HTTP_BODY_LENGTH) { return { errorLabel: 'Invalid body length' }; } /** @type {unknown} */ let parsed; try { parsed = JSON.parse(body); } catch (_error) { return { errorLabel: 'Malformed JSON' }; } if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) { return { errorLabel: 'Unexpected JSON root' }; } const root = /** @type {{[key: string]: unknown}} */ (parsed); const units = root.daily_units && typeof root.daily_units === 'object' && !Array.isArray(root.daily_units) ? /** @type {{[key: string]: unknown}} */ (root.daily_units) : null; const daily = root.daily && typeof root.daily === 'object' && !Array.isArray(root.daily) ? /** @type {{[key: string]: unknown}} */ (root.daily) : null; if (!units || units.temperature_2m_max !== '°C' || !daily) { return { errorLabel: 'Missing Celsius daily block' }; } const dates = Array.isArray(daily.time) ? daily.time : null; const highs = Array.isArray(daily.temperature_2m_max) ? daily.temperature_2m_max : null; if (!dates || !highs || dates.length < 1 || highs.length < 1) { return { errorLabel: 'Empty daily forecast arrays' }; } const firstDate = dates[0]; const firstHigh = highs[0]; if ( typeof firstDate !== 'string' || !/^\d{4}-\d{2}-\d{2}$/.test(firstDate) || typeof firstHigh !== 'number' || !Number.isFinite(firstHigh) ) { return { errorLabel: 'Invalid forecast values' }; } return { forecastDate: firstDate, highCelsius: firstHigh, }; } /** * @param {{[key: string]: string | string[]}} headers * @returns {boolean} */ function hasJsonContentType_(headers) { for (const name of Object.keys(headers)) { if (name.toLowerCase() !== 'content-type') continue; const raw = headers[name]; const value = Array.isArray(raw) ? raw.join('; ') : raw; return value.toLowerCase().includes('application/json'); } return false; } /** * @param {{[key: string]: string}} rawCached * @returns {Map} */ function decodeCachedForecastMap_(rawCached) { /** @type {Map} */ const valid = new Map(); if (!rawCached || typeof rawCached !== 'object') return valid; for (const station of FORECAST_STATIONS) { const key = stationCacheKey_(station.stationCode); const entry = rawCached[key]; if (typeof entry !== 'string' || entry.length === 0 || entry.length > MAX_CACHE_VALUE_LENGTH) { continue; } /** @type {unknown} */ let parsed; try { parsed = JSON.parse(entry); } catch (_error) { continue; } if (!parsed || typeof parsed !== 'object' || Array.isArray(parsed)) continue; const record = /** @type {{[field: string]: unknown}} */ (parsed); if ( record.version === 1 && record.stationCode === station.stationCode && record.location === station.location && typeof record.forecastDate === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(record.forecastDate) && typeof record.highCelsius === 'number' && Number.isFinite(record.highCelsius) && typeof record.fetchedAtUtc === 'string' ) { valid.set(key, { version: 1, stationCode: station.stationCode, location: station.location, forecastDate: record.forecastDate, highCelsius: record.highCelsius, fetchedAtUtc: record.fetchedAtUtc, }); } } return valid; } /** * @param {Map} cachedByKey * @param {Map} fetchedByCode * @returns {StationSyncRow_[]} */ function buildStationOutputRows_(cachedByKey, fetchedByCode) { return FORECAST_STATIONS.map((station) => { const key = stationCacheKey_(station.stationCode); const cached = cachedByKey.get(key); if (cached) { return { stationCode: station.stationCode, location: station.location, highText: cached.highCelsius.toFixed(1), statusText: 'OK (cache ' + cached.forecastDate + ')', source: 'cache', }; } const fetched = fetchedByCode.get(station.stationCode); if (fetched && !('errorLabel' in fetched)) { return { stationCode: station.stationCode, location: station.location, highText: fetched.highCelsius.toFixed(1), statusText: 'OK (fetched ' + fetched.forecastDate + ')', source: 'fetched', }; } const reason = fetched && 'errorLabel' in fetched ? fetched.errorLabel : 'Not fetched'; return { stationCode: station.stationCode, location: station.location, highText: '', statusText: 'ERROR: ' + reason, source: 'failed', }; }); } /** * @param {boolean} requireSpreadsheetId * @returns {{spreadsheetId: string | null, cacheTtlSeconds: number, approveSheetWrite: boolean, confirmClearState: boolean}} */ function requireParallelSheetsConfig_(requireSpreadsheetId) { if (typeof PARALLEL_SHEETS_CONFIG.spreadsheetId !== 'string') { throw new Error('spreadsheetId must be a string.'); } const trimmedId = PARALLEL_SHEETS_CONFIG.spreadsheetId.trim(); /** @type {string | null} */ let spreadsheetId = null; if (trimmedId !== PLACEHOLDER_SPREADSHEET_ID && trimmedId !== '') { if (trimmedId.includes('/') || !/^[A-Za-z0-9_-]{15,}$/.test(trimmedId)) { throw new Error( 'spreadsheetId must be the ID between /d/ and /edit in the spreadsheet URL, not the full URL.', ); } spreadsheetId = trimmedId; } else if (requireSpreadsheetId) { throw new Error('Set PARALLEL_SHEETS_CONFIG.spreadsheetId to your practice spreadsheet ID.'); } const ttl = PARALLEL_SHEETS_CONFIG.cacheTtlSeconds; if (!Number.isInteger(ttl) || ttl < 1 || ttl > MAX_CACHE_TTL_SECONDS) { throw new Error( 'cacheTtlSeconds must be an integer from 1 through ' + MAX_CACHE_TTL_SECONDS + '.', ); } if (typeof PARALLEL_SHEETS_CONFIG.approveSheetWrite !== 'boolean') { throw new Error('approveSheetWrite must be true or false.'); } if (typeof PARALLEL_SHEETS_CONFIG.confirmClearState !== 'boolean') { throw new Error('confirmClearState must be true or false.'); } return { spreadsheetId: spreadsheetId, cacheTtlSeconds: ttl, approveSheetWrite: PARALLEL_SHEETS_CONFIG.approveSheetWrite, confirmClearState: PARALLEL_SHEETS_CONFIG.confirmClearState, }; }