Parallel HTTP and Sheets batchUpdate
Send concurrent HTTP requests with UrlFetchApp.fetchAll, cache responses in CacheService, and write disjoint ranges with Advanced Sheets batchUpdate.
Read multiple cache keys with CacheService.getAll, fetch only uncached URLs concurrently with UrlFetchApp.fetchAll, and write two disjoint ranges in one Advanced Sheets v4 Values.batchUpdate call with RAW input mode.
Replace sequential HTTP and range loops with batch calls
Import daily high temperatures for three weather stations (Berlin, Tokyo, and Chicago) without calling Cache.get(), UrlFetchApp.fetch(), or Range.setValues() in a loop. The project checks all three cache keys in one CacheService.getScriptCache().getAll() call, requests only the uncached URLs concurrently with UrlFetchApp.fetchAll(), stores fresh 200 responses in one putAll() call, and updates Forecast!C2:D4 alongside Audit!A2:C2 in a single Sheets.Spreadsheets.Values.batchUpdate call.
You need Read and write Sheets in batches for two-dimensional value arrays, Fetch data from web APIs for HTTP status and JSON validation, State across runs with PropertiesService for CacheService, PropertiesService, and LockService, and Inspect a folder with Advanced Drive v3 for Discovery-backed advanced services.
A script that loops over ten rows calling cache.get(), UrlFetchApp.fetch(), cache.put(), and sheet.getRange().setValue() makes up to forty sequential service calls (see service calls inside a loop). Combining batch cache operations, concurrent URL Fetch, and Advanced Sheets v4 collapses the entire refresh into one multi-range read, one cache lookup, at most one concurrent HTTP batch, at most one cache write, and one multi-range spreadsheet write.
Set up the two-tab practice spreadsheet and project
Create a disposable spreadsheet in your test account with two tabs (Forecast and Audit) before enabling sheet writes:
Create a new Google Sheet named
Parallel Forecast Practice.Rename the first tab to
Forecast. Format column A as Format > Number > Plain text so Google Sheets keeps leading zeros, then paste or importsample.csvintoForecast!A1:D4:
Station Code,Location,Next High C,Fetch Status
0042,Berlin,,
0108,Tokyo,,
0215,Chicago,,
| Station Code | Location | Next High C | Fetch Status |
|---|---|---|---|
0042 | Berlin | ||
0108 | Tokyo | ||
0215 | Chicago |
Click + at the bottom of the workbook to add a second tab, rename it
Audit, and enter these three headers acrossAudit!A1:C1:Last Run UTC,Cache Hits / Misses, andAttribution. LeaveAudit!A2:C2blank and unmerged.Copy the spreadsheet ID from the browser URL between
/spreadsheets/d/and/edit.At
script.google.com, create a standalone Apps Script project namedParallel Forecast Practiceunder the same Google account. ReplaceCode.gswith the complete source below.In Project Settings, enable Show "appsscript.json" manifest file in editor, return to Editor, replace
appsscript.jsonwith the supplied manifest, and save both files.
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<string, {highCelsius: number, forecastDate: string} | {errorLabel: string}>} */
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<string, CachedStationForecast_>}
*/
function decodeCachedForecastMap_(rawCached) {
/** @type {Map<string, CachedStationForecast_>} */
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<string, CachedStationForecast_>} cachedByKey
* @param {Map<string, {highCelsius: number, forecastDate: string} | {errorLabel: string}>} 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,
};
}
{
"timeZone": "Etc/UTC",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "Sheets",
"serviceId": "sheets",
"version": "v4"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/script.external_request"
]
}
Verify two settings after saving the manifest:
Under Services, confirm Google Sheets API (
v4) is listed with identifierSheets(userSymbol: "Sheets",serviceId: "sheets",version: "v4", per the manifest dependency reference). On a default Cloud project, saving the manifest enables the Google Sheets API automatically. On a standard Cloud project, an owner must enable Google Sheets API in Cloud Console.Under Overview > Project OAuth Scopes, confirm the project requests
https://www.googleapis.com/auth/spreadsheetsandhttps://www.googleapis.com/auth/script.external_request(scopes guide). Because this standalone project opens a target workbook by pastedspreadsheetIdthrough the Advanced Sheets service rather than running inside a bound sheet,spreadsheets.currentonlycannot authorize the call.
As in Fetch data from web APIs, the example queries Open-Meteo's free noncommercial forecast API under its terms of use and writes the required CC BY 4.0 attribution directly into Audit!C2. Keep the three fixed city coordinates for this exercise.
Why async and await do not parallelize UrlFetchApp.fetch
Apps Script runs modern JavaScript on V8, including Promise, async, and await, yet its host services execute synchronously on a single thread. Unlike browser fetch(), which returns a Promise while the browser's network stack works in the background, UrlFetchApp.fetch(url) blocks the V8 thread until the HTTP response arrives and returns an HTTPResponse object directly.
Wrapping UrlFetchApp.fetch() inside Promise.all(urls.map(async (url) => UrlFetchApp.fetch(url))) does not send requests concurrently. Each async callback still blocks V8 at its UrlFetchApp.fetch() call before the next iteration starts. Six sequential requests that each take 400 ms still take roughly 2,400 ms end to end.
To run outbound HTTP requests concurrently in Apps Script, call UrlFetchApp.fetchAll(requests):
const responses = UrlFetchApp.fetchAll(missRequests); fetchAll hands the entire array of request objects to Google's URL Fetch backend in one host transition. Google dispatches those HTTP requests in parallel, waits for the batch to finish, and returns an HTTPResponse[] array in the exact index order of missRequests. Even if Tokyo (missRequests[1]) finishes faster than Berlin (missRequests[0]), Berlin's response is always at responses[0] and Tokyo's is always at responses[1]. Six independent 400 ms calls finish in roughly 450 ms instead of 2.4 seconds.
Each element of missRequests built by buildStationFetchRequest_ is a URLFetchRequestOptions object:
url: the complete HTTPS URL, with query values encoded viaencodeURIComponent.method: 'get': lowercase HTTP verb name ('get','post','put','patch', or'delete').headers: { Accept: 'application/json' }: asks the remote server for a JSON payload.muteHttpExceptions: true: returns non-2xx HTTP responses asHTTPResponseobjects instead of throwing.followRedirects: false: treats unexpected3xxredirects as non-200 statuses rather than following them to another URL.
Keep partial progress with muteHttpExceptions in fetchAll
In a single UrlFetchApp.fetch(url) call, letting a 404 or 500 status throw an exception aborts that one request. In UrlFetchApp.fetchAll(requests), leaving muteHttpExceptions at its default (false) creates an all-or-nothing failure across the batch.
If one URL in a ten-request UrlFetchApp.fetchAll batch returns HTTP 404 or 429 while muteHttpExceptions is false, fetchAll throws an exception and discards all nine successful responses. You pay the latency and remote API quota of all ten requests while receiving zero responses back in JavaScript. Always set muteHttpExceptions: true on every request object in a fetchAll array and inspect response.getResponseCode() per element.
Inside inspectStationHttpResponse_(response), the script validates each HTTPResponse independently:
response.getResponseCode()must equal200. Any other HTTP status (302,400,404,429,503) returns{ errorLabel: 'HTTP ' + status }without throwing.response.getAllHeaders()must contain aContent-Typeheader (matched case-insensitively) that includesapplication/json, rejecting HTML gateway error pages.response.getContentText('UTF-8')must be non-empty and at most16,384characters.JSON.parsemust produce an object whosedaily_units.temperature_2m_maxis'°C', whosedaily.time[0]matchesYYYY-MM-DD, and whosedaily.temperature_2m_max[0]is a finite number.
When one station fails validation (for example, returning HTTP 503), the loop records { errorLabel: 'HTTP 503' } for that station, skips adding it to freshCacheEntries so the error is not cached, and still caches and writes the remaining 200 stations.
Note the boundary of muteHttpExceptions: true: it mutes HTTP response status codes (4xx and 5xx), where an HTTP response arrived from the server. A malformed URL, a DNS lookup failure, a TLS handshake error, or exhausting Apps Script's daily URL Fetch quota (20,000 calls per day for consumer accounts and 100,000 for Google Workspace accounts, where each URL in fetchAll counts toward the daily call total) throws before an HTTPResponse exists. Keep fetchAll batches bounded, such as 10 to 50 URLs per batch, rather than passing hundreds of unvalidated URLs at once.
Read and write multiple cache keys in one call
Before building missRequests, the script checks CacheService.getScriptCache() for all three station keys at once using cache.getAll(keys):
const cacheKeys = FORECAST_STATIONS.map((station) => stationCacheKey_(station.stationCode));
const cachedByKey = decodeCachedForecastMap_(cache.getAll(cacheKeys)); Compare the single-key and batch Cache methods:
| Operation | Single-key method (avoid in loops) | Batch method | Return or input contract |
|---|---|---|---|
| Read | cache.get(key) | cache.getAll(keys) | Accepts string[] and returns { [key: string]: string } containing only found keys |
| Write | cache.put(key, value, ttl) | cache.putAll(values, ttl) | Accepts { [key: string]: string } and integer ttl (1 through 21600 seconds) |
| Delete | cache.remove(key) | cache.removeAll(keys) | Accepts string[] and ignores keys that have already expired without throwing |
Notice how cache.getAll(keys) represents a cache miss. Whereas cache.get(key) returns null for an absent key, cache.getAll(keys) returns a JavaScript object that omits missing keys altogether (rawCached[key] === undefined). In decodeCachedForecastMap_, each present string is parsed and checked against version 1, the expected stationCode and location, a YYYY-MM-DD date, and a finite highCelsius. A missing, corrupted, or version-mismatched cache entry is ignored as a cache miss rather than crashing the run.
Pairing cache.getAll with UrlFetchApp.fetchAll keeps network traffic proportional to actual cache misses:
Cold cache (0 hits, 3 misses):
fetchAllsends 3 concurrent requests, andcache.putAll(freshCacheEntries, config.cacheTtlSeconds)writes all 3 validated JSON records in one call with an 1,800-second (30-minute) TTL.Partial cache (2 hits, 1 miss): if one entry was evicted early,
missRequestscontains only that 1 missing URL,fetchAllsends 1 request, andcache.putAllwrites that 1 fresh entry.Warm cache (3 hits, 0 misses):
missRequestsis empty (length === 0), so the script skips bothUrlFetchApp.fetchAllandcache.putAllcompletely.
Every CacheService call must stay within the documented Cache limits: at most 250 characters per key (stationCacheKey_ enforces MAX_CACHE_KEY_LENGTH = 250), at most 100 KB per value (MAX_CACHE_VALUE_LENGTH = 90000 characters leaves headroom for UTF-8 encoding), at most 21,600 seconds (6 hours) for expirationInSeconds, and up to 1,000 entries per cache before early eviction removes entries closest to expiration.
Acquiring LockService.getScriptLock() (lock.waitLock(5000)) before calling cache.getAll prevents a cache stampede. Without the lock, two executions starting at the same second on an empty cache would both read 0 hits, both call UrlFetchApp.fetchAll for all three stations, and race to overwrite the spreadsheet. Because syncParallelForecastBatch checks cache.getAll inside the try block protected by the lock, a second execution that waited for the lock immediately sees the 3 cache entries written by the first execution and makes zero HTTP calls.
Read and write disjoint ranges with Advanced Sheets v4
In Read and write Sheets in batches, sheet.getRange(row, col, numRows, numCols).setValues(grid) wrote one contiguous rectangle on one sheet. Many real workflows update two or more separate regions at once: for example, filling computed columns C2:D4 on a Forecast tab without overwriting input columns A2:B4, while also recording a run timestamp and status summary in Audit!A2:C2 on a second tab.
The Advanced Sheets service (Sheets.Spreadsheets.Values) handles multiple disjoint ranges across any tabs of a spreadsheet in a single API call:
| Sheets v4 REST method | Advanced service call | Role in this project |
|---|---|---|
GET /v4/spreadsheets/{spreadsheetId}/values:batchGet | Sheets.Spreadsheets.Values.batchGet(spreadsheetId, optionalArgs) | Reads Forecast!A1:D4 and Audit!A1:C2 together to verify headers and station rows before fetching |
POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdate | Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId) | Writes Forecast!C2:D4 and Audit!A2:C2 together in one atomic values update |
Pay close attention to the argument order difference between the two methods:
Values.batchGetis aGETendpoint with no request body, sospreadsheetIdcomes first and{ ranges: ['Forecast!A1:D4', 'Audit!A1:C2'], valueRenderOption: 'UNFORMATTED_VALUE' }comes second.Values.batchUpdateis aPOSTendpoint with aBatchUpdateValuesRequestbody, so the request body{ valueInputOption: 'RAW', data: [...] }comes first andspreadsheetIdcomes second (see the advanced-services parameter order rules).
Unlike SpreadsheetApp.Range.getValues(), which always returns a dense rectangle padded with "" for every empty cell, the Sheets API v4 ValueRange response omits trailing empty cells in each row and omits trailing empty rows at the end of a range. On a fresh workbook where Forecast!C2:D4 and Audit!A2:C2 are still blank, Values.batchGet returns two-element arrays ['0042', 'Berlin'] for Forecast rows 2 through 4 and a single-row [['Last Run UTC', 'Cache Hits / Misses', 'Attribution']] array for Audit!A1:C2. Always default missing rows with grid[rowIndex] || [] instead of indexing grid[1][0] unconditionally.
Each entry in data inside Values.batchUpdate uses Sheets A1 notation ('Forecast!C2:D4' and 'Audit!A2:C2') and majorDimension: 'ROWS', meaning each inner array represents one horizontal row of cells. If a tab name in your own workbook contains spaces or punctuation (such as Audit Log), wrap the tab name in single quotes inside A1 notation ("'Audit Log'!A2:C2").
Protect cells with valueInputOption: 'RAW'
Every Values.batchUpdate request must specify valueInputOption, choosing how Google Sheets interprets the values in data:
'USER_ENTERED'parses each value as though a user typed it into the Sheets formula bar. Strings starting with'=','+', or'-'become live formulas ('=HYPERLINK(...)','=IMPORTDATA(...)'),'0042'is coerced to the number42(stripping leading zeros), and'2026-12-01'is converted into a spreadsheet date serial number.'RAW'stores values literally without parsing. Every JavaScriptstringis stored as a literal text cell, and every JavaScriptnumberorbooleanis stored as a numeric or boolean cell.
When writing text derived from external HTTP APIs, webhooks, or user-submitted forms, 'RAW' blocks spreadsheet formula injection at the API boundary: even if a remote field or error message began with '=IMPORTXML(...)' or '+1+1', 'RAW' writes the literal characters into the cell instead of creating an active formula. 'RAW' also preserves string identifiers with leading zeros such as '0042' and ISO timestamps such as '2026-11-16T01:30:00.000Z'.
Under valueInputOption: 'RAW', the JavaScript type you put in the values array decides the cell's type in Sheets. Passing a string such as highCelsius.toFixed(1) ('18.4') writes a left-aligned text cell with an exact decimal representation. Passing a number such as Number(highCelsius.toFixed(1)) (18.4) writes a numeric cell that sheet formulas such as =AVERAGE(C2:C4) can aggregate, while keeping 'RAW' protection on every string column.
Note also what happens if a values row contains null: in the Sheets v4 ValueRange specification, null inside an input values array skips overwriting that cell, leaving any stale value from a previous run intact. To clear a cell's previous value during Values.batchUpdate (as buildStationOutputRows_ does for highText when a station fails), pass the empty string '', never null.
Run the preview, cold sync, and warm-cache sync
Walk through the dry-run preview, cold-cache fetch, and warm-cache reuse in order:
Before pasting your
spreadsheetId, keepspreadsheetId: 'PASTE_PRACTICE_SPREADSHEET_ID_HERE'andapproveSheetWrite: false. SelectpreviewParallelForecastPlanand click Run. Grant the requested scopes when prompted. In Execution log, confirmspreadsheetConfigured: false,cacheHits: 0,cacheMisses: 3, andplannedFetchAllCount: 3. No network request to Open-Meteo and no Sheets API call occurred.Paste your practice spreadsheet ID into
PARALLEL_SHEETS_CONFIG.spreadsheetId(keepingapproveSheetWrite: false), saveCode.gs, and runpreviewParallelForecastPlanagain. ConfirmspreadsheetConfigured: trueandworkbookVerified: true. This provesSheets.Spreadsheets.Values.batchGetverified bothForecast!A1:D4andAudit!A1:C2in one read.Change
approveSheetWritetotrue, saveCode.gs, selectsyncParallelForecastBatch, and click Run once. Inspect the logged report and both tabs of your spreadsheet:
| Output location | Expected result on first sync (cold cache) |
|---|---|
| Execution log | cacheHits: 0, fetchedOk: 3, failedCount: 0, updatedCells: 9 |
Forecast!C2:D4 | Three Celsius high strings in column C and OK (fetched YYYY-MM-DD) in column D |
Forecast!A2:B4 | Unchanged (0042 Berlin, 0108 Tokyo, 0215 Chicago) |
Audit!A2:C2 | UTC run timestamp in A2, 0 cached, 3 fetched, 0 failed in B2, and Open-Meteo CC BY 4.0 credit in C2 |
Immediately click Run on
syncParallelForecastBatcha second time without editingCode.gs. Compare the new log and spreadsheet cells:
| Output location | Expected result on second sync (warm cache) |
|---|---|
| Execution log | cacheHits: 3, fetchedOk: 0, failedCount: 0, updatedCells: 9 |
| Outbound HTTP calls | 0 (UrlFetchApp.fetchAll and cache.putAll are both skipped) |
Forecast!D2:D4 | Updated to OK (cache YYYY-MM-DD) |
Audit!B2 | Updated to 3 cached, 0 fetched, 0 failed |
Next, test cache expiration and state separation:
| Change to make | Prediction before running | What to observe |
|---|---|---|
Run previewParallelForecastPlan while the cache is warm | Reports 3 cache hits, 0 planned fetchAll requests, and hasSavedReceipt: true | cacheHits: 3, plannedFetchAllCount: 0, hasSavedReceipt: true |
Temporarily set cacheTtlSeconds: 5, set confirmClearState: true, run clearForecastCacheAndState once, restore confirmClearState: false, and run syncParallelForecastBatch | Refetches all 3 stations and caches them with a 5-second TTL | cacheHits: 0, fetchedOk: 3 |
Wait 6 seconds, then run previewParallelForecastPlan | Cache entries expired after 5 seconds, while the PropertiesService receipt survived | cacheHits: 0, cacheMisses: 3, hasSavedReceipt: true |
Restore cacheTtlSeconds: 1800 after completing the 5-second TTL check.
Classify HTTP, cache, and Sheets API failures
Each layer in this pipeline reports failures differently:
ReferenceError: Sheets is not defined: checkenabledAdvancedServicesinappsscript.json(userSymbol: "Sheets",serviceId: "sheets",version: "v4") and save the manifest (see ReferenceError diagnostics).HTTP 403
Google Sheets API has not been used in project...: on a standard Google Cloud project, enable Google Sheets API in Cloud Console. On a default Cloud project, saveappsscript.jsonagain.HTTP 400
Unable to parse range: Forecast!A1:D4orAudit!A1:C2: verify that both tabs exist with exact case-sensitive namesForecastandAuditin the target spreadsheet.spreadsheetId must be the ID between /d/ and /edit: copy only the identifier segment from the spreadsheet URL, excludinghttps://docs.google.com/spreadsheets/d/and/edit#gid=0.Row-level
ERROR: HTTP 429orERROR: HTTP 503inForecast!D2:D4: Open-Meteo rate-limited or failed on one or more requests whilemuteHttpExceptions: truepreserved the rest of the batch. Because failed responses are not passed tocache.putAll, waiting briefly and rerunningsyncParallelForecastBatchfetches only any failed stations while reusing cached200stations.cacheTtlSeconds must be an integer from 1 through 21600.,Cache key exceeds the 250-character CacheService limit., or the editor diagnosticCache expiration outside 1 to 21600 seconds: keepcacheTtlSecondsbetween1and21600and cache keys at or below250characters.Exception: Lock timeout: another process was holding the lock for too long.: another execution of this project held the script lock beyond the 5,000-millisecondwaitLock(5000)window. Wait in Executions for the active run to finish rather than removing the lock.
Clear cached forecasts and remove practice files
Clean up the cached keys, stored property receipt, and practice spreadsheet when finished:
In
PARALLEL_SHEETS_CONFIG, setapproveSheetWrite: falseandconfirmClearState: true, then saveCode.gs.Select
clearForecastCacheAndStateand click Run once. It acquires the script lock, removes all three station keys in oneCacheService.getScriptCache().removeAll(cacheKeys)call, and deletesbook.parallel-http.last-sync.v1from Script Properties.Restore
confirmClearState: false,cacheTtlSeconds: 1800, andspreadsheetId: 'PASTE_PRACTICE_SPREADSHEET_ID_HERE'inCode.gs, and save.Run
previewParallelForecastPlanonce to verifycacheHits: 0andhasSavedReceipt: false.Open your disposable
Parallel Forecast Practicespreadsheet in Google Drive, confirm it contains only the exercise data, and move it to Trash.
Next, use Reference, glossary, and troubleshooting for a quick index of Apps Script services, limits, and diagnostic patterns across the book.
Project files
Complete source files for this chapter's examples.
Fetch cached Open-Meteo stations in parallel and write disjoint Sheets ranges
Code.gs
Download fileconst 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<string, {highCelsius: number, forecastDate: string} | {errorLabel: string}>} */
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<string, CachedStationForecast_>}
*/
function decodeCachedForecastMap_(rawCached) {
/** @type {Map<string, CachedStationForecast_>} */
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<string, CachedStationForecast_>} cachedByKey
* @param {Map<string, {highCelsius: number, forecastDate: string} | {errorLabel: string}>} 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,
};
}
sample.csv
Download fileStation Code,Location,Next High C,Fetch Status
0042,Berlin,,
0108,Tokyo,,
0215,Chicago,,
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"dependencies": {
"enabledAdvancedServices": [
{
"userSymbol": "Sheets",
"serviceId": "sheets",
"version": "v4"
}
]
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/script.external_request"
]
}