Version 1
function runFetchBatchEnhanced() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var fetch = ss.getSheetByName('fetch');
if (!fetch) throw new Error('fetch sheet not found');
var cfg = readConfigFromSheet(fetch);
var src = ss.getSheetByName(cfg.source.sheetname);
var manipSheet = ss.getSheetByName(cfg.manipulate.sheetname);
var postSheet = ss.getSheetByName(cfg.post_values.sheetname);
if (!src || !manipSheet || !postSheet) throw new Error('one or more configured sheets missing');
var srcCol = getColumnIndex(cfg.source.column);
var postCol = getColumnIndex(cfg.post_values.column);
var lastRow = findLastDataRow(src, srcCol);
if (lastRow < 2) return;
var manipRange = manipSheet.getRange(cfg.manipulate.cell);
var debug = ensureDebugSheet();
var perRowTimeoutMs = 8000; // Increased slightly for buffer
var pollMs = 200; // Reduced for more frequent polling
var initialDelayMs = 800;
var maxCycles = 1; // only 1 cycle now
var startProps = PropertiesService.getScriptProperties();
var startIndex = parseInt(startProps.getProperty('lastIndex') || '0', 10);
var batchNo = parseInt(startProps.getProperty('batchNo') || '1', 10);
var startTime = Date.now();
var maxRunMs = 280 * 1000;
var rowsProcessed = 0;
ensureTrigger(); // added trigger setup
for (var i = startIndex; i < lastRow - 1; i++) {
var rowNumber = i + 2;
var inputVal = src.getRange(rowNumber, srcCol).getValue();
if (inputVal === '' || inputVal === null || inputVal === undefined) continue;
var finalValues = null;
var finalStatus = 'failed';
for (var cycle = 1; cycle <= maxCycles; cycle++) {
var attemptStarted = Date.now();
try {
// only normal cycle (cycle 1)
manipRange.setValue(inputVal);
SpreadsheetApp.flush();
Utilities.sleep(initialDelayMs);
var pollResult = pollCells(cfg.get_values, perRowTimeoutMs, pollMs); // Removed attemptStarted
finalValues = pollResult.values;
finalStatus = pollResult.status;
appendDebug(debug, rowNumber, cycle, Date.now() - attemptStarted, finalValues, finalStatus);
if (pollResult.status === 'ready') break; // success for this row
} catch (errCycle) {
appendDebug(debug, rowNumber, cycle, Date.now() - attemptStarted, ['ERR:' + errCycle.message], 'error');
}
// small backoff before next cycle
Utilities.sleep(200);
}
if (!finalValues) finalValues = [''];
try {
postSheet.getRange(rowNumber, postCol, 1, finalValues.length).setValues([finalValues]);
} catch (e) {
// fallback to single cell
postSheet.getRange(rowNumber, postCol).setValue(finalValues[0] || '');
}
rowsProcessed++;
var elapsedTotal = Date.now() - startTime;
if (elapsedTotal > maxRunMs) {
startProps.setProperty('lastIndex', i + 1);
startProps.setProperty('batchNo', batchNo + 1);
logBatch(rowsProcessed, lastRow - 1, batchNo);
return;
}
}
startProps.deleteProperty('lastIndex');
startProps.deleteProperty('batchNo');
logBatch(rowsProcessed, lastRow - 1, batchNo, true);
stopTrigger(); // added trigger stop
}
// helpers
function readConfigFromSheet(fetchSheet) {
var b2 = fetchSheet.getRange('B2').getDisplayValue();
var b3 = fetchSheet.getRange('B3').getDisplayValue();
var b4 = fetchSheet.getRange('B4').getDisplayValue();
var b5 = fetchSheet.getRange('B5').getDisplayValue();
return {
source: parseSheetColumn(b2),
manipulate: parseSheetCell(b3),
get_values: parseGetValues(b4),
post_values: parseSheetColumn(b5)
};
}
function parseSheetColumn(str) {
var p = String(str || '').replace(/[{}]/g,'').split(',');
return { sheetname: (p[0]||'').trim(), column: (p[1]||'').trim() };
}
function parseSheetCell(str) {
var p = String(str || '').replace(/[{}]/g,'').split(',');
return { sheetname: (p[0]||'').trim(), cell: (p[1]||'').trim() };
}
function parseGetValues(str) {
var raw = String(str || '').replace(/[{}]/g,'').trim();
if (!raw) return [];
return raw.split(',').map(function(s){ return s.trim(); });
}
function ensureDebugSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('fetch_debug');
if (!sh) {
sh = ss.insertSheet('fetch_debug');
sh.getRange(1,1,1,6).setValues([['ts','row','cycle','elapsed_ms','values','status']]);
sh.hideSheet();
}
return sh;
}
function appendDebug(debugSheet, row, cycle, elapsedMs, values, status) {
try {
var now = new Date();
debugSheet.appendRow([Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss'),
row, cycle, elapsedMs, values.join(' | '), status]);
} catch (e) { Logger.log('appendDebug failed: ' + e.toString()); }
}
function pollCells(addressArray, maxWaitMs, pollMs, startTime) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var start = startTime || Date.now();
var ranges = addressArray.map(function(addr){
if (!addr) return null;
if (addr.indexOf('!') > -1) {
var parts = addr.split('!');
var sh = ss.getSheetByName(parts[0].trim());
if (!sh) return null;
return sh.getRange(parts[1].trim());
} else {
return ss.getActiveSheet().getRange(addr.trim());
}
});
while (Date.now() - start < maxWaitMs) {
SpreadsheetApp.flush();
var vals = [];
var allGood = true;
for (var i = 0; i < ranges.length; i++) {
var r = ranges[i];
var v = '';
try { if (r) v = r.getDisplayValue(); } catch(e) { v = ''; }
vals.push(v);
if (isInvalidDisplay(v)) allGood = false;
}
if (allGood) return { status: 'ready', values: vals };
Utilities.sleep(pollMs);
}
var finalVals = ranges.map(function(r){ try { return r ? r.getDisplayValue() : ''; } catch(e){ return ''; } });
// Optional: Log timeout for easier debugging
Logger.log('Poll timed out after ' + maxWaitMs + 'ms; final values: ' + finalVals.join(' | '));
return { status: 'timeout', values: finalVals };
}
function nudgeDependentFormulas(addressArray) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var i = 0; i < addressArray.length; i++) {
var addr = addressArray[i];
if (!addr) continue;
var sh, cellA1;
if (addr.indexOf('!') > -1) {
var parts = addr.split('!');
sh = ss.getSheetByName(parts[0].trim());
cellA1 = parts[1].trim();
} else {
sh = ss.getActiveSheet();
cellA1 = addr.trim();
}
if (!sh) continue;
try {
var rng = sh.getRange(cellA1);
var f = rng.getFormula();
if (f && f !== '') {
rng.setFormula(f + ' '); // small nudge
rng.setFormula(f);
} else {
// if there's no formula, try setting value to itself to trigger: read then set
var v = rng.getValue();
rng.setValue(v);
}
} catch (e) {
// ignore per-cell failures
}
}
}
function isInvalidDisplay(v) {
if (v === null || v === undefined) return true;
var s = String(v).trim();
if (s === '') return true;
var up = s.toUpperCase();
if (up.indexOf('LOADING') === 0) return true;
if (up.indexOf('#') === 0) return true;
return false;
}
function getColumnIndex(col) {
if (!col) return 0;
col = String(col).toUpperCase().replace(/[^A-Z]/g,'');
var idx = 0;
for (var i = 0; i < col.length; i++) idx = idx * 26 + (col.charCodeAt(i) - 64);
return idx;
}
function findLastDataRow(sheet, col) {
var last = sheet.getLastRow();
if (last < 2) return 1;
var vals = sheet.getRange(2, col, last - 1, 1).getValues();
for (var i = vals.length - 1; i >= 0; i--) {
var v = vals[i][0];
if (v !== null && v !== undefined && String(v).trim() !== '') return i + 2;
}
return 1;
}
function logBatch(done, total, batch, finished) {
var tz = Session.getScriptTimeZone();
var now = new Date();
var ts = Utilities.formatDate(now, tz, 'yyyy-MM-dd HH:mm:ss');
if (finished) Logger.log('Finished ' + done + '/' + total + ' rows in batch #' + batch + ' @ ' + ts);
else Logger.log('Partial ' + done + '/' + total + ' rows in batch #' + batch + ' @ ' + ts);
}
// added trigger functions from code 1
function ensureTrigger() {
var triggers = ScriptApp.getProjectTriggers();
var exists = triggers.some(function(t) {
return t.getHandlerFunction() === "runFetchBatchEnhanced";
});
if (!exists) {
ScriptApp.newTrigger("runFetchBatchEnhanced")
.timeBased()
.everyMinutes(5)
.create();
Logger.log("⏲️ Trigger created: will run every 5 minutes.");
}
}
function stopTrigger() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "runFetchBatchEnhanced") {
ScriptApp.deleteTrigger(triggers[i]);
Logger.log("🛑 Trigger deleted: no further runs scheduled.");
}
}
}
Version 2
function runFetchBatchEnhanced() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var fetch = ss.getSheetByName('fetch');
if (!fetch) throw new Error('fetch sheet not found');
var cfg = readConfigFromSheet(fetch);
var src = ss.getSheetByName(cfg.source.sheetname);
var manipSheet = ss.getSheetByName(cfg.manipulate.sheetname);
var postSheet = ss.getSheetByName(cfg.post_values.sheetname);
if (!src || !manipSheet || !postSheet) throw new Error('one or more configured sheets missing');
// --- source columns (array) -> indices
var srcColsArr = cfg.source.columns || [];
if (!srcColsArr.length && cfg.source.column) srcColsArr = [cfg.source.column];
var srcColIndices = srcColsArr.map(function(c){ return getColumnIndex(c); });
var primarySrcCol = srcColIndices[0] || 1; // used for last-row detection
// --- manipulate target cells (array) -> ranges
var manipCells = cfg.manipulate.cells || [];
if (!manipCells.length && cfg.manipulate.cell) manipCells = [cfg.manipulate.cell];
var manipRanges = manipCells.map(function(c){ return manipSheet.getRange(c); });
// --- post_values columns (array)
var postColsArr = cfg.post_values.columns || [];
if (!postColsArr.length && cfg.post_values.column) postColsArr = [cfg.post_values.column];
var lastRow = findLastDataRow(src, primarySrcCol);
if (lastRow < 2) return;
var debug = ensureDebugSheet();
var perRowTimeoutMs = 8000; // Increased slightly for buffer
var pollMs = 200; // Reduced for more frequent polling
var initialDelayMs = 800;
var maxCycles = 1; // only 1 cycle now
var startProps = PropertiesService.getScriptProperties();
var startIndex = parseInt(startProps.getProperty('lastIndex') || '0', 10);
var batchNo = parseInt(startProps.getProperty('batchNo') || '1', 10);
var startTime = Date.now();
var maxRunMs = 280 * 1000;
var rowsProcessed = 0;
ensureTrigger(); // added trigger setup
for (var i = startIndex; i < lastRow - 1; i++) {
var rowNumber = i + 2;
// read all configured source inputs for this row
var inputs = srcColIndices.map(function(colIdx){
try { return src.getRange(rowNumber, colIdx).getValue(); } catch(e){ return ''; }
});
// skip if all inputs empty
var allEmpty = inputs.every(function(v){ return v === '' || v === null || v === undefined; });
if (allEmpty) continue;
var finalValues = null;
var finalStatus = 'failed';
for (var cycle = 1; cycle <= maxCycles; cycle++) {
var attemptStarted = Date.now();
try {
// write inputs into manipulate targets
try {
if (manipRanges.length === 0) {
// nothing configured — do nothing
} else if (manipRanges.length === 1) {
// single target: write joined inputs (backwards-compatible)
var joint = inputs.map(function(x){ return (x===null||x===undefined)?'':String(x); }).join('|');
manipRanges[0].setValue(joint);
} else {
// multiple targets: map one-to-one, extra inputs (if any) get joined into last target
var minCount = Math.min(manipRanges.length, inputs.length);
for (var m = 0; m < minCount; m++) {
manipRanges[m].setValue(inputs[m]);
}
if (inputs.length > manipRanges.length) {
// join leftover into last range
var leftover = inputs.slice(manipRanges.length - 1).map(function(x){ return (x===null||x===undefined)?'':String(x); }).join('|');
manipRanges[manipRanges.length - 1].setValue(leftover);
} else if (manipRanges.length > inputs.length) {
// if more manipulate targets than inputs, clear the extra ones (optional)
for (var mm = inputs.length; mm < manipRanges.length; mm++) {
// don't overwrite if you prefer — here we leave them alone. Comment next line if undesired:
// manipRanges[mm].setValue('');
}
}
}
} catch (errWrite) {
appendDebug(debug, rowNumber, cycle, Date.now() - attemptStarted, ['ERR:write:' + errWrite.message], 'error');
}
SpreadsheetApp.flush();
Utilities.sleep(initialDelayMs);
var pollResult = pollCells(cfg.get_values, perRowTimeoutMs, pollMs);
finalValues = pollResult.values;
finalStatus = pollResult.status;
appendDebug(debug, rowNumber, cycle, Date.now() - attemptStarted, finalValues, finalStatus);
if (pollResult.status === 'ready') break; // success for this row
} catch (errCycle) {
appendDebug(debug, rowNumber, cycle, Date.now() - attemptStarted, ['ERR:' + errCycle.message], 'error');
}
// small backoff before next cycle
Utilities.sleep(200);
}
if (!finalValues) finalValues = [''];
try {
// If post_values explicitly lists the same count as finalValues, write each to its absolute column
if (postColsArr.length === finalValues.length && postColsArr.length > 0) {
for (var j = 0; j < finalValues.length; j++) {
try {
var tgtCol = getColumnIndex(postColsArr[j]);
postSheet.getRange(rowNumber, tgtCol).setValue(finalValues[j]);
} catch (e) {
// fallback: write into first column span
var fallbackCol = getColumnIndex(postColsArr[0]);
postSheet.getRange(rowNumber, fallbackCol, 1, finalValues.length).setValues([finalValues]);
break;
}
}
} else {
// fallback: write returned values starting at first configured post column (old behavior)
var startPostCol = postColsArr.length ? getColumnIndex(postColsArr[0]) : getColumnIndex(cfg.post_values.column || '');
postSheet.getRange(rowNumber, startPostCol, 1, finalValues.length).setValues([finalValues]);
}
} catch (e) {
// ultimate fallback: single cell
var startPostColSingle = postColsArr.length ? getColumnIndex(postColsArr[0]) : getColumnIndex(cfg.post_values.column || '');
postSheet.getRange(rowNumber, startPostColSingle).setValue(finalValues[0] || '');
}
rowsProcessed++;
var elapsedTotal = Date.now() - startTime;
if (elapsedTotal > maxRunMs) {
startProps.setProperty('lastIndex', i + 1);
startProps.setProperty('batchNo', batchNo + 1);
logBatch(rowsProcessed, lastRow - 1, batchNo);
return;
}
}
startProps.deleteProperty('lastIndex');
startProps.deleteProperty('batchNo');
logBatch(rowsProcessed, lastRow - 1, batchNo, true);
stopTrigger(); // added trigger stop
}
// helpers
function readConfigFromSheet(fetchSheet) {
var b2 = fetchSheet.getRange('B2').getDisplayValue();
var b3 = fetchSheet.getRange('B3').getDisplayValue();
var b4 = fetchSheet.getRange('B4').getDisplayValue();
var b5 = fetchSheet.getRange('B5').getDisplayValue();
return {
source: parseSheetColumn(b2),
manipulate: parseSheetCell(b3),
get_values: parseGetValues(b4),
post_values: parseSheetColumn(b5)
};
}
function parseSheetColumn(str) {
// Accept: {"SheetName", A, B, C} -> returns { sheetname: 'SheetName', columns: ['A','B','C'], column: 'A' }
var raw = String(str || '').replace(/[{}]/g,'').trim();
if (!raw) return { sheetname: '', columns: [], column: '' };
var parts = raw.split(',').map(function(s){ return s.trim(); }).filter(Boolean);
var sheetname = parts.length ? parts[0] : '';
var cols = parts.length > 1 ? parts.slice(1) : [];
// keep backward-compatible single-field name 'column'
return { sheetname: sheetname, columns: cols, column: (cols[0]||'') };
}
function parseSheetCell(str) {
// Accept: {"SheetName", A1, B1, C1} -> returns { sheetname: 'SheetName', cells:['A1','B1','C1'], cell:'A1' }
var raw = String(str || '').replace(/[{}]/g,'').trim();
if (!raw) return { sheetname: '', cells: [], cell: '' };
var parts = raw.split(',').map(function(s){ return s.trim(); }).filter(Boolean);
var sheetname = parts.length ? parts[0] : '';
var cells = parts.length > 1 ? parts.slice(1) : [];
return { sheetname: sheetname, cells: cells, cell: (cells[0]||'') };
}
function parseGetValues(str) {
var raw = String(str || '').replace(/[{}]/g,'').trim();
if (!raw) return [];
return raw.split(',').map(function(s){ return s.trim(); });
}
function ensureDebugSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName('fetch_debug');
if (!sh) {
sh = ss.insertSheet('fetch_debug');
sh.getRange(1,1,1,6).setValues([['ts','row','cycle','elapsed_ms','values','status']]);
sh.hideSheet();
}
return sh;
}
function appendDebug(debugSheet, row, cycle, elapsedMs, values, status) {
try {
var now = new Date();
debugSheet.appendRow([Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss'),
row, cycle, elapsedMs, values.join(' | '), status]);
} catch (e) { Logger.log('appendDebug failed: ' + e.toString()); }
}
function pollCells(addressArray, maxWaitMs, pollMs, startTime) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var start = startTime || Date.now();
var ranges = addressArray.map(function(addr){
if (!addr) return null;
if (addr.indexOf('!') > -1) {
var parts = addr.split('!');
var sh = ss.getSheetByName(parts[0].trim());
if (!sh) return null;
return sh.getRange(parts[1].trim());
} else {
return ss.getActiveSheet().getRange(addr.trim());
}
});
while (Date.now() - start < maxWaitMs) {
SpreadsheetApp.flush();
var vals = [];
var allGood = true;
for (var i = 0; i < ranges.length; i++) {
var r = ranges[i];
var v = '';
try { if (r) v = r.getDisplayValue(); } catch(e) { v = ''; }
vals.push(v);
if (isInvalidDisplay(v)) allGood = false;
}
if (allGood) return { status: 'ready', values: vals };
Utilities.sleep(pollMs);
}
var finalVals = ranges.map(function(r){ try { return r ? r.getDisplayValue() : ''; } catch(e){ return ''; } });
Logger.log('Poll timed out after ' + maxWaitMs + 'ms; final values: ' + finalVals.join(' | '));
return { status: 'timeout', values: finalVals };
}
function nudgeDependentFormulas(addressArray) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var i = 0; i < addressArray.length; i++) {
var addr = addressArray[i];
if (!addr) continue;
var sh, cellA1;
if (addr.indexOf('!') > -1) {
var parts = addr.split('!');
sh = ss.getSheetByName(parts[0].trim());
cellA1 = parts[1].trim();
} else {
sh = ss.getActiveSheet();
cellA1 = addr.trim();
}
if (!sh) continue;
try {
var rng = sh.getRange(cellA1);
var f = rng.getFormula();
if (f && f !== '') {
rng.setFormula(f + ' '); // small nudge
rng.setFormula(f);
} else {
var v = rng.getValue();
rng.setValue(v);
}
} catch (e) {
// ignore per-cell failures
}
}
}
function isInvalidDisplay(v) {
if (v === null || v === undefined) return true;
var s = String(v).trim();
if (s === '') return true;
var up = s.toUpperCase();
if (up.indexOf('LOADING') === 0) return true;
if (up.indexOf('#') === 0) return true;
return false;
}
function getColumnIndex(col) {
if (!col) return 0;
col = String(col).toUpperCase().replace(/[^A-Z]/g,'');
var idx = 0;
for (var i = 0; i < col.length; i++) idx = idx * 26 + (col.charCodeAt(i) - 64);
return idx;
}
function findLastDataRow(sheet, col) {
var last = sheet.getLastRow();
if (last < 2) return 1;
var vals = sheet.getRange(2, col, last - 1, 1).getValues();
for (var i = vals.length - 1; i >= 0; i--) {
var v = vals[i][0];
if (v !== null && v !== undefined && String(v).trim() !== '') return i + 2;
}
return 1;
}
function logBatch(done, total, batch, finished) {
var tz = Session.getScriptTimeZone();
var now = new Date();
var ts = Utilities.formatDate(now, tz, 'yyyy-MM-dd HH:mm:ss');
if (finished) Logger.log('Finished ' + done + '/' + total + ' rows in batch #' + batch + ' @ ' + ts);
else Logger.log('Partial ' + done + '/' + total + ' rows in batch #' + batch + ' @ ' + ts);
}
// added trigger functions from code 1
function ensureTrigger() {
var triggers = ScriptApp.getProjectTriggers();
var exists = triggers.some(function(t) {
return t.getHandlerFunction() === "runFetchBatchEnhanced";
});
if (!exists) {
ScriptApp.newTrigger("runFetchBatchEnhanced")
.timeBased()
.everyMinutes(5)
.create();
Logger.log("⏲️ Trigger created: will run every 5 minutes.");
}
}
function stopTrigger() {
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === "runFetchBatchEnhanced") {
ScriptApp.deleteTrigger(triggers[i]);
Logger.log("🛑 Trigger deleted: no further runs scheduled.");
}
}
}