function activeUserEmailOr(fallback) {
var email = '';
try { email = Session.getActiveUser().getEmail(); } catch (e) {}
if (!email) { try { email = Session.getEffectiveUser().getEmail(); } catch (e) {} }
if (!email) email = PropertiesService.getScriptProperties().getProperty('OPERATOR_EMAIL') || '';
return email || fallback || '';
}
Snippets
Code snippets
Copy-ready Apps Script patterns — drop them into your project or into NitroGAS.
function appendDeadLetterRow(sheetOrSs, rowObj, err, opts) {
opts = opts || {};
var ss = sheetOrSs.getSheetByName ? sheetOrSs : sheetOrSs.getParent();
var name = opts.tabName || 'DeadLetter';
var sh = ss.getSheetByName(name) || ss.insertSheet(name);
var meta = ['_failedAt', '_attempts', '_error'];
// Headers: first use, or new fields since last time
var headers = sh.getLastColumn() ? sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0] : [];
Object.keys(rowObj).concat(meta).forEach(function (h) {
if (headers.indexOf(h) === -1) headers.push(h);
});
sh.getRange(1, 1, 1, headers.length).setValues([headers]);
var message = String(err && err.message ? err.message : err).slice(0, 500);
var target = -1, attempts = 1;
if (opts.key && sh.getLastRow() > 1) {
var keyCol = headers.indexOf(opts.key);
var data = sh.getRange(2, 1, sh.getLastRow() - 1, headers.length).getValues();
for (var i = 0; i < data.length; i++) {
if (String(data[i][keyCol]) === String(rowObj[opts.key])) {
target = i + 2;
attempts = Number(data[i][headers.indexOf('_attempts')] || 0) + 1;
break;
}
}
}
var out = headers.map(function (h) {
if (h === '_failedAt') return new Date();
if (h === '_attempts') return attempts;
if (h === '_error') return message;
return rowObj[h] === undefined ? '' : rowObj[h];
});
if (target === -1) target = sh.getLastRow() + 1;
sh.getRange(target, 1, 1, out.length).setValues([out]);
return { row: target, attempts: attempts };
}
function appendStatusAuditRow(ssOrSheet, entry, opts) {
opts = opts || {};
var ss = ssOrSheet.getSheetByName ? ssOrSheet : ssOrSheet.getParent();
var name = opts.tabName || 'Audit';
var oldV = entry.oldValue == null ? '' : String(entry.oldValue);
var newV = entry.newValue == null ? '' : String(entry.newValue);
if (oldV === newV) return false;
var sh = ss.getSheetByName(name);
if (!sh) {
sh = ss.insertSheet(name);
sh.getRange(1, 1, 1, 6).setValues([['Timestamp', 'Actor', 'Key', 'Field', 'Old', 'New']]);
sh.setFrozenRows(1);
}
sh.appendRow([new Date(), entry.actor || 'unknown', entry.key, entry.field, oldV, newV]);
return true;
}
function appendObjectAsRow(sheet, obj) {
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var row = headers.map(function (h) {
return obj.hasOwnProperty(h) ? obj[h] : '';
});
sheet.appendRow(row);
return sheet.getLastRow();
}
// appendObjectAsRow(sheet, { name: 'Ada', email: 'ada@example.com' });
function archiveRowsBeforeDate(sheet, dateHeader, cutoff, opts) {
opts = opts || {};
var prefix = opts.prefix || 'Archive_';
var ss = sheet.getParent();
var tz = ss.getSpreadsheetTimeZone();
var lock = LockService.getDocumentLock();
if (!lock.tryLock(30000)) throw new Error('archiveRowsBeforeDate: sheet is busy, try again');
try {
var data = sheet.getDataRange().getValues();
var header = data.shift();
var col = header.indexOf(dateHeader);
if (col === -1) throw new Error('archiveRowsBeforeDate: no column "' + dateHeader + '"');
var keep = [], byMonth = {};
data.forEach(function (r) {
var d = r[col];
if (d instanceof Date && d < cutoff) {
var tab = prefix + Utilities.formatDate(d, tz, 'yyyy-MM');
(byMonth[tab] = byMonth[tab] || []).push(r);
} else {
keep.push(r);
}
});
var tabs = {};
Object.keys(byMonth).forEach(function (t) { tabs[t] = byMonth[t].length; });
var result = { moved: data.length - keep.length, kept: keep.length, tabs: tabs };
if (opts.dryRun || !result.moved) return result;
// 1. Append to archive tabs first
Object.keys(byMonth).sort().forEach(function (t) {
var rows = byMonth[t];
var arch = ss.getSheetByName(t);
if (!arch) {
arch = ss.insertSheet(t);
arch.getRange(1, 1, 1, header.length).setValues([header]);
}
arch.getRange(arch.getLastRow() + 1, 1, rows.length, header.length).setValues(rows);
});
SpreadsheetApp.flush();
// 2. Then rewrite the source: kept rows up top, clear the leftovers
if (keep.length) sheet.getRange(2, 1, keep.length, header.length).setValues(keep);
sheet.getRange(2 + keep.length, 1, result.moved, header.length).clearContent();
return result;
} finally {
lock.releaseLock();
}
}
function assertRequiredKeys(obj, requiredKeys, opts) {
opts = opts || {};
obj = obj || {};
var missing = (requiredKeys || []).filter(function (key) {
var v = obj[key];
// Missing = undefined, null, empty, or only spaces. 0 and false count as present.
return v === undefined || v === null || (typeof v === 'string' && v.trim() === '');
});
var result = { ok: missing.length === 0, missing: missing };
if (!result.ok && opts.throwOnMissing) {
throw new Error((opts.label ? opts.label + ': ' : '') + 'missing ' + missing.join(', '));
}
return result;
}
// var check = assertRequiredKeys(row, ['Email', 'Amount']); // { ok: false, missing: ['Amount'] }
function sortSheet(sheet, column, direction) {
sheet.sort(column, new RegExp('asc', 'ig').test(direction) ? true : false)
}
// sortSheet(sheet, 2, 'Ascending')
// Sort by Column B ascending
range.sort(2)
// Sort by Column B ascending and then Column A ascending
range.sort([2, 1])
// Sort by Column B descending and then Column A ascending
range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}])
function basicJsonApiGetRequest(url) {
const result = JSON.parse(UrlFetchApp.fetch(url, {method: 'get'}).getContentText())
return result
}
// basicJsonApiGetRequest('https://rickandmortyapi.com/api')
function basicJsonApiPostRequest(url, payload) {
const result = JSON.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/json"}, method: 'post', payload: payload}).getContentText())
return result
}
// basicJsonApiPostRequest('https://dummy.restapiexample.com/api/v1/create', {name:"test",salary:"123",age:"23"})
function basicXmlApiPostRequest(url, payload) {
const result = XmlService.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/xml"}, method: 'post', payload: payload}))
console.log(result.getRootElement().getChildren().map(element => element.getText()))
return result
}
function basicXmlApiGetRequest(url) {
const result = XmlService.parse(UrlFetchApp.fetch(url, {headers: {"Content-Type": "application/xml"}, method: 'get'}))
console.log(result.getRootElement().getChildren().map(element => element.getText()))
return result
}
function buildHeaderMap(sheet, headerRow) {
headerRow = headerRow || 1;
var lastCol = sheet.getLastColumn();
if (lastCol < 1) return {};
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
var map = {};
for (var i = 0; i < headers.length; i++) {
var name = String(headers[i] == null ? '' : headers[i]).trim();
if (!name) continue;
if (map.hasOwnProperty(name)) {
throw new Error('buildHeaderMap: duplicate header "' + name + '"');
}
map[name] = i + 1; // 1-based column index for getRange
}
return map;
}
// var cols = buildHeaderMap(sheet); sheet.getRange(2, cols.email).getValue();
function buildQueryString(params) {
params = params || {};
var parts = [];
Object.keys(params).forEach(function (key) {
var v = params[key];
if (v == null || v === '') return;
parts.push(encodeURIComponent(key) + '=' + encodeURIComponent(String(v)));
});
return parts.length ? '?' + parts.join('&') : '';
}
// buildQueryString({ q: 'ada love', page: 2 }) // → '?q=ada%20love&page=2'
function cacheGetJson(key) {
var raw = CacheService.getScriptCache().get(key);
if (raw == null) return null;
return JSON.parse(raw);
}
function cachePutJson(key, value, ttlSeconds) {
ttlSeconds = ttlSeconds || 600;
CacheService.getScriptCache().put(key, JSON.stringify(value), ttlSeconds);
}
// cachePutJson('rates', rates, 300);
// var rates = cacheGetJson('rates');
var CANCEL_PREFIX_ = 'cancel:';
function cancelCache_() {
return CacheService.getDocumentCache() || CacheService.getScriptCache();
}
function requestCancel(key) {
// Sidebar / menu calls this. TTL so a crashed UI can't leave Cancel stuck on.
cancelCache_().put(CANCEL_PREFIX_ + String(key), '1', 21600);
}
function isCancelRequested(key) {
return cancelCache_().get(CANCEL_PREFIX_ + String(key)) === '1';
}
function clearCancel(key) {
cancelCache_().remove(CANCEL_PREFIX_ + String(key));
}
// // In the job loop: if (isCancelRequested('nightlySync')) break;
// // Sidebar button: requestCancel('nightlySync');
// // finally: clearCancel('nightlySync');
function claimNextQueueRow(sheet, statusHeader, claimedBy, options) {
options = options || {};
var pending = options.pendingStatus || 'pending';
var claimed = options.claimedStatus || 'claimed';
var headerRow = options.headerRow || 1;
return withDocumentLock(function () {
var lastCol = sheet.getLastColumn();
var lastRow = sheet.getLastRow();
if (lastRow <= headerRow) return null;
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
var statusCol = headers.indexOf(statusHeader);
if (statusCol === -1) throw new Error('claimNextQueueRow: missing status header "' + statusHeader + '"');
var values = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol).getValues();
for (var i = 0; i < values.length; i++) {
if (String(values[i][statusCol]).toLowerCase() !== String(pending).toLowerCase()) continue;
var row = headerRow + 1 + i;
sheet.getRange(row, statusCol + 1).setValue(claimed);
if (options.claimedByHeader) {
var byCol = headers.indexOf(options.claimedByHeader);
if (byCol !== -1) sheet.getRange(row, byCol + 1).setValue(claimedBy || Session.getActiveUser().getEmail());
}
if (options.claimedAtHeader) {
var atCol = headers.indexOf(options.claimedAtHeader);
if (atCol !== -1) sheet.getRange(row, atCol + 1).setValue(new Date());
}
var obj = { _row: row };
for (var c = 0; c < headers.length; c++) {
var key = String(headers[c] == null ? '' : headers[c]).trim();
if (!key) continue;
obj[key] = values[i][c];
}
obj[statusHeader] = claimed;
return obj;
}
return null;
});
}
// var job = claimNextQueueRow(sheet, 'status', null, { claimedByHeader: 'claimedBy', claimedAtHeader: 'claimedAt' });
function clearSheetKeepHeaders(sheet, headerRows) {
headerRows = headerRows || 1;
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow <= headerRows || lastCol < 1) return;
sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastCol).clearContent();
}
// clearSheetKeepHeaders(SpreadsheetApp.getActiveSheet(), 1);
function confirmDangerousAction(title, prompt) {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
title || 'Confirm',
prompt || 'Are you sure?',
ui.ButtonSet.YES_NO
);
return result === ui.Button.YES;
}
// if (!confirmDangerousAction('Clear sheet', 'Wipe all data rows?')) return;
function escapeCsvCell(value) {
var s = value == null ? '' : String(value);
if (/[",\n\r]/.test(s)) {
s = '"' + s.replace(/"/g, '""') + '"';
}
return s;
}
function valuesToCsv(values) {
return values.map(function (row) {
return row.map(escapeCsvCell).join(',');
}).join('\n');
}
// var csv = valuesToCsv(sheet.getDataRange().getValues());
function convertColumn(target, value) {
if (target == 'letter') {
let temp, letter = ''
while (value > 0) {
temp = (value - 1) % 26
letter = String.fromCharCode(temp + 65) + letter
value = (value - temp - 1) / 26
}
return letter
} else if (target == 'number') {
let column = 0, length = value.length;
for (i=0; i<length; i++) {
column += (value.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
}
return column
} else {
return 0
}
}
function convertJsonToArray(object) {
return new Array(Object.keys(object), Object.values(object))
}
function convertArrayToObjectArray(keys, data) {
let jsonArray = []
for(let item in data) {
let object = {}
let row = data[item]
for(let value in keys) {
let key = keys[value]
object[key] = row[value]
}
jsonArray.push(object)
}
return jsonArray
}
function convertObjectArrayToArray(objectArray) {
const finalArray = []
for(let item in objectArray) {
let object = objectArray[item]
if (item == 0) {
finalArray.push(Object.keys(object))
}
let array = []
for(let key in object) {
array.push(object[key])
}
finalArray.push(array)
}
return finalArray
}
function copyValuesToExternalSheet(targetId, tabName, values, opts) {
opts = opts || {};
var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
var ss = SpreadsheetApp.openById(targetId);
// Get or create the destination tab
var sheet = ss.getSheetByName(tabName) || ss.insertSheet(tabName);
if (opts.headers && sheet.getLastRow() === 0) {
sheet.getRange(1, 1, 1, opts.headers.length).setValues([opts.headers]);
}
// Clear old data below the header row(s); headers and formatting stay
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow > headerRows && lastCol > 0) {
sheet.getRange(headerRows + 1, 1, lastRow - headerRows, lastCol).clearContent();
}
if (!values || !values.length) return 0;
var numRows = values.length;
var numCols = values[0].length;
// Grow the tab if the block is bigger than the grid
var needRows = headerRows + numRows - sheet.getMaxRows();
if (needRows > 0) sheet.insertRowsAfter(sheet.getMaxRows(), needRows);
var needCols = numCols - sheet.getMaxColumns();
if (needCols > 0) sheet.insertColumnsAfter(sheet.getMaxColumns(), needCols);
sheet.getRange(headerRows + 1, 1, numRows, numCols).setValues(values);
return numRows;
}
function copyTemplateSheet(ss, templateName, newName, options) {
options = options || {};
var template = ss.getSheetByName(templateName);
if (!template) throw new Error('copyTemplateSheet: missing template "' + templateName + '"');
if (ss.getSheetByName(newName)) throw new Error('copyTemplateSheet: sheet already exists "' + newName + '"');
var copy = template.copyTo(ss).setName(newName);
if (options.clearDataRows !== false) {
var lastRow = copy.getLastRow();
if (lastRow > 1) copy.getRange(2, 1, lastRow - 1, copy.getLastColumn()).clearContent();
}
if (options.createdHeader) {
var headers = copy.getRange(1, 1, 1, copy.getLastColumn()).getValues()[0];
var idx = headers.indexOf(options.createdHeader);
if (idx !== -1) copy.getRange(2, idx + 1).setValue(new Date());
}
return copy;
}
// copyTemplateSheet(ss, 'Client Template', 'Acme — 2026-09', { createdHeader: 'Created' });
function createCalendarEventIfMissing(opts) {
opts = opts || {};
var cal = opts.calendarId ? CalendarApp.getCalendarById(opts.calendarId) : CalendarApp.getDefaultCalendar();
if (!cal) throw new Error('createCalendarEventIfMissing: calendar not found: ' + opts.calendarId);
// Already created (id stored on the row)? Return it instead of making a double
if (opts.eventId) {
var existing = cal.getEventById(opts.eventId);
if (existing) return { created: false, eventId: existing.getId(), event: existing };
}
var start = new Date(opts.start);
var end = new Date(opts.end);
if (!opts.title) throw new Error('createCalendarEventIfMissing: title required');
if (isNaN(start) || isNaN(end) || end <= start) throw new Error('createCalendarEventIfMissing: need start < end');
var options = {};
if (opts.description) options.description = opts.description;
if (opts.location) options.location = opts.location;
if (opts.guests && opts.guests.length) {
options.guests = [].concat(opts.guests).join(',');
options.sendInvites = opts.sendInvites !== false;
}
var ev = cal.createEvent(opts.title, start, end, options);
return { created: true, eventId: ev.getId(), event: ev };
}
// var res = createCalendarEventIfMissing({ title: 'Kickoff', start: s, end: e, eventId: row.EventId });
function createOneShotTriggerAt(handlerName, when) {
var at = when instanceof Date ? when : new Date(when);
if (isNaN(at.getTime())) throw new Error('createOneShotTriggerAt: invalid date');
if (at.getTime() <= Date.now()) throw new Error('createOneShotTriggerAt: date is in the past');
if (typeof globalThis[handlerName] !== 'function') {
throw new Error('createOneShotTriggerAt: no function named ' + handlerName);
}
// Runs once, around `at` (Apps Script may be a few minutes late)
var trigger = ScriptApp.newTrigger(handlerName).timeBased().at(at).create();
return trigger.getUniqueId();
}
function deleteTriggerById(triggerId) {
// Delete ONLY this trigger, leave every other trigger for the handler alone
var all = ScriptApp.getProjectTriggers();
for (var i = 0; i < all.length; i++) {
if (all[i].getUniqueId() === triggerId) {
ScriptApp.deleteTrigger(all[i]);
return true;
}
}
return false;
}
// var id = createOneShotTriggerAt('sendReminder', new Date(Date.now() + 60 * 60 * 1000));
// function sendReminder(e) { try { /* work */ } finally { deleteTriggerById(e.triggerUid); } }
function onOpen() {
// FormApp.getUi() // For Google Forms
// DocumentApp.getUi() // For Google Docs
// SlidesApp.getUi() // For Google Slides
SpreadsheetApp.getUi() // For Google Sheets
.createMenu('Custom Menu Option')
.addItem('Option One', 'functionName')
.addToUi()
}
function createDailyTrigger(handlerName, hour) {
hour = (hour == null) ? 6 : hour;
ScriptApp.newTrigger(handlerName)
.timeBased()
.everyDays(1)
.atHour(hour)
.create();
}
// createDailyTrigger('runNightlySync', 2);
function createLogFile() {
let snapshot = createSnapshot()
let sheetName = snapshot.sheetName
let dataBackup = snapshot.sheetData
let filename = `'${sheetName}' Snapshot - ${Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd HH:mm')}`
let log_file = SpreadsheetApp.create(filename);
log_file.getSheetByName('Sheet1').setName(`${sheetName} Snapshot`).getRange(1, 1, dataBackup.length, dataBackup[0].length).setValues(dataBackup);
DriveApp.getFileById(log_file.getId()).moveTo(getDataBackupFolder())
}
function debounceOrSkip(actionKey, ttlSeconds) {
ttlSeconds = ttlSeconds == null ? 5 : ttlSeconds;
var cache = CacheService.getScriptCache();
var token = 'debounce:' + actionKey;
if (cache.get(token)) return false;
cache.put(token, '1', Math.max(1, Math.min(ttlSeconds, 21600)));
return true;
}
// if (!debounceOrSkip('onEdit:Status', 3)) return; // quiet window
function deleteTriggersByHandler(handlerName) {
var triggers = ScriptApp.getProjectTriggers();
var removed = 0;
for (var i = 0; i < triggers.length; i++) {
if (triggers[i].getHandlerFunction() === handlerName) {
ScriptApp.deleteTrigger(triggers[i]);
removed++;
}
}
return removed;
}
// deleteTriggersByHandler('runNightlySync');
function diffObjectsByKey(before, after, key) {
function index(list) {
var m = {};
(list || []).forEach(function (o) { m[String(o[key])] = o; });
return m;
}
function norm(v) { return v instanceof Date ? v.toISOString() : String(v == null ? '' : v); }
var a = index(before), b = index(after);
var out = { added: [], removed: [], changed: [] };
Object.keys(b).forEach(function (k) {
if (!(k in a)) { out.added.push(b[k]); return; }
var fields = {};
var cols = Object.keys(a[k]).concat(Object.keys(b[k]));
cols.forEach(function (c) {
if (c in fields) return;
if (norm(a[k][c]) !== norm(b[k][c])) fields[c] = [a[k][c], b[k][c]];
});
if (Object.keys(fields).length) out.changed.push({ key: b[k][key], fields: fields });
});
Object.keys(a).forEach(function (k) { if (!(k in b)) out.removed.push(a[k]); });
return out;
}
function emailErrorAlert(subject, err, context) {
var to = Session.getActiveUser().getEmail();
if (!to) throw new Error('emailErrorAlert: no active user email');
var msg = (err && err.message) ? err.message : String(err);
var stack = (err && err.stack) ? String(err.stack).substring(0, 800) : '';
var ctx = '';
if (context != null) {
try { ctx = JSON.stringify(context).substring(0, 500); }
catch (e) { ctx = String(context).substring(0, 500); }
}
var body = [
'Apps Script error alert',
'',
'Message: ' + msg,
stack ? ('Stack:\n' + stack) : '',
ctx ? ('Context: ' + ctx) : '',
'',
'Time: ' + new Date().toISOString()
].filter(Boolean).join('\n');
GmailApp.sendEmail(to, subject || 'Apps Script error', body);
}
// try { nightlySync(); } catch (e) { emailErrorAlert('nightlySync failed', e, { rows: 42 }); throw e; }
function ensureHeaders(sheet, expectedHeaders) {
expectedHeaders = expectedHeaders || [];
if (!expectedHeaders.length) throw new Error('ensureHeaders: expectedHeaders required');
var lastCol = Math.max(sheet.getLastColumn(), expectedHeaders.length);
var existing = lastCol < 1 ? [] : sheet.getRange(1, 1, 1, lastCol).getValues()[0];
var hasAny = existing.some(function (c) { return c !== '' && c !== null; });
if (!hasAny) {
sheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
sheet.setFrozenRows(1);
return { created: true, headers: expectedHeaders.slice() };
}
var missing = expectedHeaders.filter(function (h) { return existing.indexOf(h) === -1; });
if (missing.length) {
throw new Error('ensureHeaders: missing columns: ' + missing.join(', '));
}
return { created: false, headers: existing.slice(0, expectedHeaders.length) };
}
// ensureHeaders(sheet, ['email', 'name', 'status']);
function escapeHtml(s) {
return String(s == null ? '' : s)
.replace(/&/g, '&')
.replace(/</g, '<')
.replace(/>/g, '>')
.replace(/"/g, '"')
.replace(/'/g, ''');
}
// escapeHtml('<Ada & co>') // → '<Ada & co>'
function escapeLeadingFormula(value) {
if (value === null || value === undefined) return value;
// Numbers, Dates, booleans stay as-is — Sheets won't treat them as formulas
if (typeof value !== 'string') return value;
// Leading = + - @ (and a tab) make Sheets parse the cell as a formula / signed number
if (/^[=+\-@\t]/.test(value)) return "'" + value;
return value;
}
// sheet.getRange(2, 1, rows.length, 1).setValues(rows.map(function (r) {
// return [escapeLeadingFormula(r.note)];
// }));
function extractDriveFileId(input) {
if (input == null) return '';
var s = String(input).trim();
if (!s) return '';
if (/^[a-zA-Z0-9_-]{25,}$/.test(s) && s.indexOf('/') === -1 && s.indexOf('?') === -1) {
return s;
}
var patterns = [
/\/file\/d\/([a-zA-Z0-9_-]+)/,
/\/folders\/([a-zA-Z0-9_-]+)/,
/\/spreadsheets\/d\/([a-zA-Z0-9_-]+)/,
/\/document\/d\/([a-zA-Z0-9_-]+)/,
/\/presentation\/d\/([a-zA-Z0-9_-]+)/,
/[?&]id=([a-zA-Z0-9_-]+)/,
/open\?id=([a-zA-Z0-9_-]+)/
];
for (var i = 0; i < patterns.length; i++) {
var m = s.match(patterns[i]);
if (m && m[1]) return m[1];
}
return '';
}
// extractDriveFileId('https://drive.google.com/file/d/1AbC…/view') // → '1AbC…'
function fetchAllPages(fetchPage, opts) {
opts = opts || {};
var maxPages = opts.maxPages || 50;
var budget = opts.timeBudgetMs || 4.5 * 60 * 1000;
var started = Date.now();
var cursor = opts.startCursor == null ? null : opts.startCursor;
var items = [], pages = 0;
while (pages < maxPages && Date.now() - started < budget) {
var page = fetchPage(cursor);
pages++;
items = items.concat(opts.getItems(page) || []);
cursor = opts.getNextCursor(page);
if (!cursor) return { items: items, pages: pages, done: true, nextCursor: null };
}
return { items: items, pages: pages, done: false, nextCursor: cursor };
}
function fillHtmlTemplate(html, values, opts) {
opts = opts || {};
var onMissing = opts.onMissing || 'blank'; // 'blank' | 'throw'
values = values || {};
function escapeHtml(s) {
return String(s == null ? '' : s)
.replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>')
.replace(/"/g, '"').replace(/'/g, ''');
}
// {{{token}}} = raw HTML; {{token}} = escaped
return String(html).replace(/\{\{\{([^{}]+)\}\}\}|\{\{([^{}]+)\}\}/g, function (_, rawKey, escKey) {
var key = String(rawKey || escKey).trim();
var has = Object.prototype.hasOwnProperty.call(values, key);
if (!has) {
if (onMissing === 'throw') throw new Error('fillHtmlTemplate: missing key "' + key + '"');
return '';
}
var val = values[key];
if (rawKey) return String(val == null ? '' : val); // raw / unescaped
return escapeHtml(val);
});
}
// fillHtmlTemplate('<p>Hi {{name}}</p>', { name: 'Ada <3' });
function filterObjectsByDateWindow(rows, field, opts) {
opts = opts || {};
var ss = opts.spreadsheet || SpreadsheetApp.getActive();
var tz = opts.timeZone || (ss && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone();
// Turn a Date or 'yyyy-MM-dd…' string into a calendar-day key in tz
function dayKey(v) {
if (v == null || v === '') return null;
if (typeof v === 'string' && /^\d{4}-\d{2}-\d{2}/.test(v)) return v.slice(0, 10);
var d = v instanceof Date ? v : new Date(v);
return isNaN(d.getTime()) ? null : Utilities.formatDate(d, tz, 'yyyy-MM-dd');
}
function addDays(key, n) {
var p = key.split('-').map(Number);
var d = new Date(Date.UTC(p[0], p[1] - 1, p[2] + n));
return d.toISOString().slice(0, 10);
}
var start = dayKey(opts.start);
var end = dayKey(opts.end);
if (opts.lastNDays) {
var today = dayKey(opts.now || new Date());
start = addDays(today, -(opts.lastNDays - 1)); // lastNDays: 1 = today only
end = today;
}
return (rows || []).filter(function (row) {
var k = dayKey(row[field]);
if (!k) return false;
return (!start || k >= start) && (!end || k <= end); // inclusive both ends
});
}
// filterObjectsByDateWindow(rows, 'Created', { lastNDays: 7 });
function filterOutBlankRows(data, targetColumnNum) {
const filteredData = data.filter((row) => row[targetColumnNum-1] !== "")
return filteredData
}
// filterOutBlankRows([['100', 'a'], ['101','b'], ['','c'], ['103','d']], 1)
function filterOutWithRegex(data, targetColumn, regexPattern, regexOptions) {
// For targetColumn, start from position 0
// For help with regex, try regex101.com
const regex = new RegExp(regexPattern, regexOptions)
const filteredData = data.filter((row) => {return regex.test(row[targetColumn]) == false})
return filteredData
}
// filterOutWithRegex([[1,'hi','HELLO'],[2,'bye','goodbye']], 2, /hello/, 'ig')
function getColumnByName(sheet, column_name, header_row) {
var data = sheet.getDataRange().getValues();
var col_index = data[header_row-1].indexOf(column_name);
return col_index+1
}
// getColumnByName(sheet, 'id', 1)
function diffArrays(array1, array2) {
array1 = array1.flat(Infinity), array2 = array2.flat(Infinity)
return [...new Set([array1.filter(x => !array2.includes(x)), array2.filter(x => !array1.includes(x))].flat())]
}
// diffArrays([1,2,3],[3,4,5])
function findMaxLengthOfNestedArrays(nestedArray) {
return Math.max(...nestedArray.map(row => row.length))
}
// findMaxLengthOfNestedArrays([[1,1,1],[2,2]])
function flushThenGetValues(rangeOrA1, opts) {
opts = opts || {};
// Push pending writes so formulas recalculate against them
SpreadsheetApp.flush();
var range = typeof rangeOrA1 === 'string'
? (opts.sheet || SpreadsheetApp.getActive()).getRange(rangeOrA1)
: rangeOrA1;
// Display values = exactly what the cell shows ("$1,200.00", "Oct 1")
return opts.display ? range.getDisplayValues() : range.getValues();
}
// sheet.getRange('B2').setValue(42);
// var total = flushThenGetValues('Summary!D10')[0][0]; // formula result, up to date
function forLoopItems(data) {
for(let item in data) {
console.log(data[item])
}
}
// forLoopItems([1,2,3,4,5,6,7])
function forLoopIncremental(data, start, end) {
for(let i = start; i < end; i++) {
console.log(data[i])
}
}
// forLoopIncremental([1,2,3,4,5], 2, 4)
function formatInSpreadsheetTimezone(date, pattern) {
if (date == null) return '';
pattern = pattern || 'yyyy-MM-dd HH:mm:ss';
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tz = (ss && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone();
return Utilities.formatDate(date instanceof Date ? date : new Date(date), tz, pattern);
}
// Logger.log(formatInSpreadsheetTimezone(new Date())); // matches the grid
function getSheetData() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
let sheet = spreadsheet.getSheetByName('TARGET_SHEET_NAME')
let sheetData = sheet.getDataRange().getValues()
return sheetData
}
function getDisplayRowObjects(sheet, opts) {
sheet = sheet || SpreadsheetApp.getActiveSheet();
opts = opts || {};
var headerRow = opts.headerRow || 1;
var lastRow = sheet.getLastRow(), lastCol = sheet.getLastColumn();
if (lastRow <= headerRow || !lastCol) return [];
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getDisplayValues()[0];
var rows = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol).getDisplayValues();
var out = [];
rows.forEach(function (r, i) {
if (opts.skipBlank !== false && r.join('') === '') return;
var o = {};
headers.forEach(function (h, c) { if (h) o[h] = r[c]; });
if (opts.includeRowNumber !== false) o._row = headerRow + 1 + i;
out.push(o);
});
return out;
}
function getSelectedRowObjects(sheet, headerRow) {
sheet = sheet || SpreadsheetApp.getActiveSheet();
headerRow = headerRow || 1;
var lastCol = sheet.getLastColumn();
var lastRow = sheet.getLastRow();
if (lastCol < 1 || lastRow <= headerRow) return [];
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
// Collect unique data rows from every selected range (Ctrl/Cmd-click works too)
var rowSet = {};
var list = sheet.getActiveRangeList();
var ranges = list ? list.getRanges() : [sheet.getActiveRange()];
ranges.forEach(function (r) {
for (var row = r.getRow(); row <= r.getLastRow(); row++) {
if (row > headerRow && row <= lastRow) rowSet[row] = true;
}
});
var rows = Object.keys(rowSet).map(Number).sort(function (a, b) { return a - b; });
// Read the FULL row width, even if only one column was selected
return rows.map(function (row) {
var values = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
var obj = { _row: row };
headers.forEach(function (h, i) {
var key = String(h == null ? '' : h).trim();
if (key) obj[key] = values[i];
});
return obj;
});
}
// var picked = getSelectedRowObjects(); // [{ _row: 5, Email: ..., Status: ... }, ...]
function getUiSafe() {
try {
return SpreadsheetApp.getUi();
} catch (e) {
return null;
}
}
// var ui = getUiSafe(); if (ui) ui.createMenu('Ops').addItem('Run', 'runJob').addToUi();
function createUniqueArray(data) {
return [...new Set(data)]
}
// createUniqueArray([1,1,2,3,4,5,5])
function getUnprocessedThreads(query, labelName, maxThreads) {
// GmailApp.search returns at most 500 threads per call, so page with start/max.
var PAGE = 500;
var limit = maxThreads == null ? 2000 : Number(maxThreads);
var q = (query ? query + ' ' : '') + '-label:"' + labelName + '"';
var threads = [];
for (var start = 0; threads.length < limit; start += PAGE) {
var size = Math.min(PAGE, limit - threads.length);
var batch = GmailApp.search(q, start, size);
threads = threads.concat(batch);
if (batch.length < size) break;
}
return threads;
}
function markThreadsProcessed(threads, labelName) {
// Call ONLY after the sheet write succeeded. The label is your receipt.
if (!threads || !threads.length) return 0;
var label = GmailApp.getUserLabelByName(labelName) || GmailApp.createLabel(labelName);
for (var i = 0; i < threads.length; i += 100) {
label.addToThreads(threads.slice(i, i + 100)); // max 100 threads per call
}
return threads.length;
}
// var threads = getUnprocessedThreads('from:billing@vendor.com', 'Imported/Receipts');
// writeRows_(threads); // throws on failure
// markThreadsProcessed(threads, 'Imported/Receipts'); // only reached if the write worked
function getVisibleRowObjects(sheet, headerRow) {
sheet = sheet || SpreadsheetApp.getActiveSheet();
headerRow = headerRow || 1;
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
if (lastRow <= headerRow || lastCol < 1) return [];
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
// One read for all data rows; visibility is checked per row
var values = sheet.getRange(headerRow + 1, 1, lastRow - headerRow, lastCol).getValues();
var out = [];
values.forEach(function (vals, i) {
var row = headerRow + 1 + i;
// Skip rows hidden by a filter OR hidden by hand (right-click → Hide row)
if (sheet.isRowHiddenByFilter(row) || sheet.isRowHiddenByUser(row)) return;
var obj = { _row: row };
headers.forEach(function (h, c) {
var key = String(h == null ? '' : h).trim();
if (key) obj[key] = vals[c];
});
out.push(obj);
});
return out;
}
// var rows = getVisibleRowObjects(); // only what the filter is showing
function getOrCreateFolder(parent, name) {
var folders = parent.getFoldersByName(name);
if (folders.hasNext()) {
return folders.next();
}
return parent.createFolder(name);
}
// getOrCreateFolder(DriveApp.getRootFolder(), 'Exports');
function getOrCreateFolderPath(parent, path) {
if (!parent) throw new Error('getOrCreateFolderPath: parent folder required');
var parts = String(path || '')
.split(/[/\\]+/)
.map(function (p) { return p.trim(); })
.filter(Boolean);
var folder = parent;
for (var i = 0; i < parts.length; i++) {
var name = parts[i];
var kids = folder.getFoldersByName(name);
folder = kids.hasNext() ? kids.next() : folder.createFolder(name);
}
return folder;
}
// getOrCreateFolderPath(DriveApp.getFolderById(ID), 'Exports/2026/09');
function getOrCreateSheet(ss, name) {
var sheet = ss.getSheetByName(name);
if (!sheet) {
sheet = ss.insertSheet(name);
}
return sheet;
}
// getOrCreateSheet(SpreadsheetApp.getActiveSpreadsheet(), 'Import');
function getActiveRowAsObject(sheet, headerRow) {
sheet = sheet || SpreadsheetApp.getActiveSheet();
headerRow = headerRow || 1;
var row = sheet.getActiveRange().getRow();
if (row <= headerRow) throw new Error('getActiveRowAsObject: active row is in the header');
var lastCol = sheet.getLastColumn();
if (lastCol < 1) return {};
var headers = sheet.getRange(headerRow, 1, 1, lastCol).getValues()[0];
var values = sheet.getRange(row, 1, 1, lastCol).getValues()[0];
var obj = { _row: row };
for (var i = 0; i < headers.length; i++) {
var key = String(headers[i] == null ? '' : headers[i]).trim();
if (!key) continue;
obj[key] = values[i];
}
return obj;
}
// var lead = getActiveRowAsObject(); // { _row: 12, email: 'ada@…', status: 'new' }
function getLastDataRow(sheet, column) {
column = column || 1;
var values = sheet.getRange(1, column, sheet.getMaxRows(), 1).getValues();
for (var i = values.length - 1; i >= 0; i--) {
var cell = values[i][0];
if (cell !== '' && cell !== null) {
return i + 1;
}
}
return 0;
}
// getLastDataRow(sheet, 1); // last non-blank in column A
function groupObjectsByKey(rows, keyOrFn) {
var out = {};
if (!rows || !rows.length) return out;
var getKey = typeof keyOrFn === 'function'
? keyOrFn
: function (row) { return row[keyOrFn]; };
rows.forEach(function (row) {
var key = getKey(row);
// Blank / missing keys become '' so they still land in one bucket
if (key == null) key = '';
key = String(key);
if (!out[key]) out[key] = [];
out[key].push(row);
});
return out;
}
// var byClient = groupObjectsByKey(rows, 'Client');
function htmlTableFromObjects(rows, columns) {
function esc_(s) {
return String(s == null ? '' : s)
.replace(/&/g, '&').replace(/</g, '<')
.replace(/>/g, '>').replace(/"/g, '"').replace(/'/g, ''');
}
// … build <table> from rows + columns …
}
// var html = htmlTableFromObjects(rows, [{key:'name',label:'Name'}, 'email']);
function hashStringSha256(text) {
var bytes = Utilities.computeDigest(
Utilities.DigestAlgorithm.SHA_256,
text == null ? '' : String(text),
Utilities.Charset.UTF_8
);
return bytes.map(function (b) {
var v = (b < 0 ? b + 256 : b).toString(16);
return v.length === 1 ? '0' + v : v;
}).join('');
}
// var key = 'lead:' + hashStringSha256(email); // cache/dedupe without raw PII
var APP_VERSION = '1.4.0';
var DEPLOYED_AT = '2026-10-13T18:00:00Z';
function healthCheckPayload() {
var lastOk = PropertiesService.getScriptProperties().getProperty('LAST_OK') || null;
return { ok: true, version: APP_VERSION, deployedAt: DEPLOYED_AT, lastOk: lastOk };
}
function doGet() {
return ContentService.createTextOutput(JSON.stringify(healthCheckPayload()))
.setMimeType(ContentService.MimeType.JSON);
}
function getAllSheets() {
const spreadsheet = SpreadsheetApp.openByUrl(`${YOUR_SPREADSHEET_URL}`)
const sheets = spreadsheet.getSheets()
return sheets
}
function includeHtml(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function openIframeDialog(title, width, height) {
var html = HtmlService.createTemplateFromFile('Dialog')
.evaluate()
.setWidth(width || 480)
.setHeight(height || 360);
SpreadsheetApp.getUi().showModalDialog(html, title || 'Dialog');
}
// In Dialog.html: <?!= includeHtml('Styles'); ?>
// Array.splice(starting_position, positions_to_delete, new_value)
// injecting a new value
yourArray.splice(2,0,"new value")
// replacing an existing value
yourArray.splice(0,1,"new value")
function installMenuSafely(caption, items) {
var result = { added: 0, skipped: [] };
try {
var menu = SpreadsheetApp.getUi().createMenu(caption);
(items || []).forEach(function (item) {
if (item === null) { menu.addSeparator(); return; }
var label = item[0], fnName = item[1];
if (typeof globalThis[fnName] !== 'function') {
result.skipped.push(fnName);
console.warn('installMenuSafely: skipped "' + label + '", ' + fnName + ' is not defined');
return;
}
menu.addItem(label, fnName);
result.added++;
});
menu.addToUi();
} catch (err) {
// No UI (trigger or editor run) or a menu API error: log it, don't break onOpen
console.warn('installMenuSafely: menu not installed: ' + (err && err.message ? err.message : err));
}
return result;
}
function isLastBusinessDayOfMonth(date, tz, holidays) {
// Read the calendar date in the sheet's timezone, not the server's.
var parts = Utilities.formatDate(date || new Date(), tz, 'yyyy-MM-dd').split('-');
var y = Number(parts[0]), m = Number(parts[1]), today = Number(parts[2]);
var skip = {};
(holidays || []).forEach(function (h) { skip[String(h).trim()] = true; });
var pad = function (n) { return (n < 10 ? '0' : '') + n; };
// Walk back from the last calendar day, skipping weekends + holidays.
var lastDay = new Date(Date.UTC(y, m, 0)).getUTCDate(); // day 0 of next month
for (var d = lastDay; d >= 1; d--) {
var dow = new Date(Date.UTC(y, m - 1, d)).getUTCDay();
var key = y + '-' + pad(m) + '-' + pad(d);
if (dow !== 0 && dow !== 6 && !skip[key]) return d === today;
}
return false;
}
// var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
// if (!isLastBusinessDayOfMonth(new Date(), tz, ['2026-12-31'])) return;
function isWithinBusinessHours(startHHmm, endHHmm, opts) {
opts = opts || {};
var ss = opts.spreadsheet || (typeof SpreadsheetApp !== 'undefined' ? SpreadsheetApp.getActive() : null);
var tz = (ss && ss.getSpreadsheetTimeZone && ss.getSpreadsheetTimeZone())
|| Session.getScriptTimeZone()
|| 'America/New_York';
var when = opts.when || new Date();
// Weekday: 1=Mon … 7=Sun in spreadsheet timezone (Utilities 'u')
var weekday = Number(Utilities.formatDate(when, tz, 'u'));
var allowed = opts.weekdays || [1, 2, 3, 4, 5];
if (allowed.indexOf(weekday) === -1) return false;
var mins = function (hhmm) {
var p = String(hhmm).split(':');
return Number(p[0]) * 60 + Number(p[1] || 0);
};
var nowMins = Number(Utilities.formatDate(when, tz, 'H')) * 60
+ Number(Utilities.formatDate(when, tz, 'm'));
var start = mins(startHHmm);
var end = mins(endHHmm);
// Half-open window [start, end) so 17:00 is "closed"
if (end <= start) return nowMins >= start || nowMins < end; // overnight window
return nowMins >= start && nowMins < end;
}
// if (!isWithinBusinessHours('09:00', '17:00')) return;
function jsonOk(data) {
return ContentService
.createTextOutput(JSON.stringify({ ok: true, data: data }))
.setMimeType(ContentService.MimeType.JSON);
}
function jsonErr(message, code) {
return ContentService
.createTextOutput(JSON.stringify({
ok: false,
error: message,
code: code == null ? 400 : code
}))
.setMimeType(ContentService.MimeType.JSON);
}
// return jsonOk({ id: rowId });
function listDriveFilesModifiedSince(folderIdOrFolder, sinceDate, opts) {
opts = opts || {};
var folder = typeof folderIdOrFolder === 'string'
? DriveApp.getFolderById(folderIdOrFolder) : folderIdOrFolder;
var since = sinceDate ? new Date(sinceDate) : new Date(0);
var mimes = opts.mimeType ? [].concat(opts.mimeType) : null;
// Drive query narrows the scan; the exact > check below is what counts
var q = "trashed = false and modifiedDate > '" +
Utilities.formatDate(since, 'UTC', "yyyy-MM-dd'T'HH:mm:ss") + "'";
var out = [];
(function walk(f) {
var files = f.searchFiles(q);
while (files.hasNext()) {
var file = files.next();
var updated = file.getLastUpdated();
if (updated.getTime() <= since.getTime()) continue;
if (mimes && mimes.indexOf(file.getMimeType()) === -1) continue;
out.push({ id: file.getId(), name: file.getName(), url: file.getUrl(),
mimeType: file.getMimeType(), lastUpdated: updated, size: file.getSize() });
}
if (opts.recursive) {
var subs = f.getFolders();
while (subs.hasNext()) walk(subs.next());
}
})(folder);
// Oldest first, so a checkpoint can advance to the last item you processed
out.sort(function (a, b) { return a.lastUpdated - b.lastUpdated; });
return out;
}
// var files = listDriveFilesModifiedSince(folderId, lastCursor, { mimeType: MimeType.PDF });
function findRowIndexByTerm(lookupSheet, columnIndex, lookupValue) {
let values = lookupSheet.getDataRange().getValues()
let index = values.findIndex(row => row[columnIndex] === lookupValue)
return index + 1
}
function maskSecret(secret, keepStart, keepEnd) {
keepStart = keepStart == null ? 4 : keepStart;
keepEnd = keepEnd == null ? 4 : keepEnd;
if (secret == null) return '';
var s = String(secret);
if (s.length <= keepStart + keepEnd) return s.replace(/./g, '•');
return s.slice(0, keepStart) + '…' + s.slice(s.length - keepEnd);
}
// Logger.log('key=' + maskSecret(props.getProperty('API_KEY'))); // abcd…wxyz
function moveFileToFolder(fileId, folderOrId) {
var file = DriveApp.getFileById(fileId);
var target = typeof folderOrId === 'string' ? DriveApp.getFolderById(folderOrId) : folderOrId;
var targetId = target.getId();
// Look at where the file lives right now
var parents = [];
var it = file.getParents();
while (it.hasNext()) parents.push(it.next());
var inTarget = parents.some(function (p) { return p.getId() === targetId; });
// Already only in the target: nothing to do (safe to re-run)
if (inTarget && parents.length === 1) return { moved: false, file: file };
// moveTo replaces the parent (works in My Drive and shared drives)
if (!inTarget) file.moveTo(target);
// Legacy multi-parent files: drop any parent that isn't the target
var it2 = file.getParents();
while (it2.hasNext()) {
var p = it2.next();
if (p.getId() !== targetId) p.removeFile(file);
}
return { moved: true, file: file };
}
// moveFileToFolder(uploadId, clientFolder);
function moveRowToSheet(sourceSheet, rowNumber, targetSheet) {
if (rowNumber < 2) throw new Error('moveRowToSheet: refuse to move header row');
var lastCol = sourceSheet.getLastColumn();
if (lastCol < 1) throw new Error('moveRowToSheet: source sheet has no columns');
var values = sourceSheet.getRange(rowNumber, 1, 1, lastCol).getValues()[0];
targetSheet.appendRow(values);
sourceSheet.deleteRow(rowNumber);
return targetSheet.getLastRow();
}
// moveRowToSheet(active, 5, archiveSheet);
function normalizeEmail(email) {
if (email == null) return '';
return String(email).trim().toLowerCase();
}
// normalizeEmail(' Ada@Example.com ') // → 'ada@example.com'
function openByUrlOrId(urlOrId) {
var s = String(urlOrId || '').trim();
var m = s.match(/\/spreadsheets\/d\/([a-zA-Z0-9-_]+)/);
var id = m ? m[1] : s;
if (!id) throw new Error('openByUrlOrId: empty urlOrId');
return SpreadsheetApp.openById(id);
}
// var ss = openByUrlOrId(Browser.inputBox('Spreadsheet URL or id'));
function parseCsvText(text, options) {
options = options || {};
var delim = options.delimiter != null ? options.delimiter : ',';
var rows = [];
var row = [];
var field = '';
var i = 0;
var inQuotes = false;
text = String(text == null ? '' : text);
while (i < text.length) {
var ch = text.charAt(i);
if (inQuotes) {
if (ch === '"') {
if (text.charAt(i + 1) === '"') { field += '"'; i += 2; continue; }
inQuotes = false; i++; continue;
}
field += ch; i++; continue;
}
if (ch === '"') { inQuotes = true; i++; continue; }
if (ch === delim) { row.push(field); field = ''; i++; continue; }
if (ch === '\r') { i++; continue; }
if (ch === '\n') { row.push(field); rows.push(row); row = []; field = ''; i++; continue; }
field += ch; i++;
}
if (field.length || row.length) { row.push(field); rows.push(row); }
return rows;
}
// var values = parseCsvText(DriveApp.getFileById(id).getBlob().getDataAsString('UTF-8'));
function postToChatWebhook(webhookUrl, text) {
var res = UrlFetchApp.fetch(webhookUrl, {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify({ text: String(text) }),
muteHttpExceptions: true // we read the status ourselves
});
var code = res.getResponseCode();
var body = res.getContentText() || '';
if (code < 200 || code >= 300) {
throw new Error('Chat webhook failed (HTTP ' + code + '): ' + body.slice(0, 200));
}
try { return body ? JSON.parse(body) : {}; } catch (e) { return body; } // Slack replies "ok"
}
// var url = PropertiesService.getScriptProperties().getProperty('CHAT_WEBHOOK_URL');
// postToChatWebhook(url, 'Nightly sync done: 42 rows');
function readWriteCheckpoint(key) {
return {
get: function () { return getCheckpoint(key); },
set: function (cursor, extra) { return setCheckpoint(key, cursor, extra); },
clear: function () { clearCheckpoint(key); }
};
}
// var cp = readWriteCheckpoint('job:sync'); var cur = (cp.get() || {}).cursor || 0; cp.set(cur + 50);
function requireSharedSecret(e, propertyKey) {
var expected = PropertiesService.getScriptProperties().getProperty(propertyKey || 'WEBHOOK_SECRET');
if (!expected) return { ok: false, reason: 'Server secret not configured' };
var got = (e && e.parameter && e.parameter.secret) || '';
if (!got && e && e.postData && /json/i.test(e.postData.type || '')) {
try { got = JSON.parse(e.postData.contents).secret || ''; } catch (x) {}
}
if (!got) return { ok: false, reason: 'Missing secret' };
return got === expected ? { ok: true } : { ok: false, reason: 'Bad secret' };
}
function requireScriptProperty(key) {
var v = PropertiesService.getScriptProperties().getProperty(key);
if (v == null || String(v).trim() === '') {
throw new Error('Missing Script Property: ' + key);
}
return v;
}
// var sheetId = requireScriptProperty('ACTIVE_SPREADSHEET_ID');
function retryUrlFetch(url, options, maxAttempts) {
maxAttempts = maxAttempts || 5;
options = options || {};
var lastError;
for (var attempt = 1; attempt <= maxAttempts; attempt++) {
try {
var response = UrlFetchApp.fetch(url, options);
var code = response.getResponseCode();
if (code === 429 || code >= 500) {
throw new Error('Retryable HTTP ' + code + ': ' + response.getContentText().substring(0, 200));
}
return response;
} catch (err) {
lastError = err;
if (attempt === maxAttempts) break;
var waitMs = Math.min(30000, Math.pow(2, attempt - 1) * 500 + Math.floor(Math.random() * 250));
Utilities.sleep(waitMs);
}
}
throw new Error('retryUrlFetch failed after ' + maxAttempts + ' attempts: ' + lastError);
}
// retryUrlFetch('https://httpbin.org/status/500', { muteHttpExceptions: true }, 4)
function safeJsonParse(text, fallback, onError) {
if (text == null || text === '') return fallback;
try {
return JSON.parse(text);
} catch (err) {
if (typeof onError === 'function') {
try { onError(err, text); } catch (ignore) {}
}
return fallback;
}
}
// var cfg = safeJsonParse(props.getProperty('config'), { mode: 'safe' });
function sanitizeSheetName(name, options) {
options = options || {};
var maxLen = options.maxLength || 100;
var fallback = options.fallback || 'Sheet';
var s = String(name == null ? '' : name).trim();
s = s.replace(/[:\\\/\?\*\[\]]/g, ' ');
s = s.replace(/\s+/g, ' ').trim();
if (s.charAt(0) === "'") s = s.substring(1);
if (!s) s = fallback;
if (s.length > maxLen) s = s.substring(0, maxLen).trim();
if (!s) s = fallback;
return s;
}
// sanitizeSheetName('Acme / Q3: Final*') // → 'Acme Q3 Final'
function scriptPropertyKeysForPrompt(opts) {
opts = opts || {};
var keys = PropertiesService.getScriptProperties().getKeys().sort();
if (opts.exclude) keys = keys.filter(function (k) { return !opts.exclude.test(k); });
var lines = [(opts.title || 'Script Properties already set (names only)') + ':'];
if (!keys.length) lines.push('- (none)');
keys.forEach(function (k) { lines.push('- `' + k + '`'); });
lines.push('');
lines.push(opts.instruction ||
'Read config from these keys with PropertiesService.getScriptProperties().getProperty(name). ' +
'Do not invent new key names or hardcode values. If you need a new key, name it and tell me to add it.');
return lines.join('\n');
}
var SCRIPT_WRITE_KEY_ = 'scriptWriting';
function writeGuardCache_() {
// Document cache for bound scripts; script cache for standalone
return CacheService.getDocumentCache() || CacheService.getScriptCache();
}
function beginScriptWrite(ttlSeconds) {
// Short TTL: if the job dies before endScriptWrite, the flag expires on its own
writeGuardCache_().put(SCRIPT_WRITE_KEY_, '1', Math.max(1, Math.min(ttlSeconds || 120, 21600)));
}
function endScriptWrite() {
writeGuardCache_().remove(SCRIPT_WRITE_KEY_);
}
function isScriptWriting() {
return writeGuardCache_().get(SCRIPT_WRITE_KEY_) === '1';
}
// beginScriptWrite(180); try { /* batch writes */ } finally { endScriptWrite(); }
// function onEditInstalled(e) { if (isScriptWriting()) return; /* ... */ }
function sendEmailWithBudget(message, opts) {
opts = opts || {};
var budget = opts.dailyBudget || 90;
var props = PropertiesService.getScriptProperties();
var key = 'MAIL_SENT_' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd');
var sent = Number(props.getProperty(key) || 0);
if (sent + 1 > budget) return { sent: false, reason: 'Daily budget reached (' + sent + '/' + budget + ')' };
if (MailApp.getRemainingDailyQuota() < 1) return { sent: false, reason: 'Gmail quota exhausted' };
if (opts.delayMs) Utilities.sleep(opts.delayMs);
MailApp.sendEmail(message);
props.setProperty(key, String(sent + 1));
return { sent: true, count: sent + 1 };
}
function sendHtmlEmail(to, subject, htmlBody, options) {
options = options || {};
var msg = {
to: to,
subject: subject,
htmlBody: htmlBody,
name: options.name || undefined,
cc: options.cc || undefined,
bcc: options.bcc || undefined,
replyTo: options.replyTo || undefined
};
Object.keys(msg).forEach(function (k) { if (msg[k] === undefined) delete msg[k]; });
GmailApp.sendEmail(msg.to, msg.subject, stripHtml_(htmlBody), msg);
}
function stripHtml_(html) {
return String(html).replace(/<[^>]+>/g, ' ').replace(/\s+/g, ' ').trim();
}
// sendHtmlEmail('ada@example.com', 'Welcome', '<p>Hello <b>Ada</b></p>', { name: 'Ops', cc: 'team@example.com' });
function sendEmail(recipient, subject, emailBody) {
GmailApp.sendEmail(recipient, subject, emailbody)
}
// sendEmail('email@example.com', 'Awesome Subject', `Send this awesome email`)
function setDropdownFromList(targetRange, source, opts) {
opts = opts || {};
var list = source;
if (source && typeof source.getValues === 'function') {
// A Range: flatten, trim, drop blanks and duplicates (first one wins)
var seen = {};
list = [];
source.getValues().forEach(function (row) {
row.forEach(function (v) {
var s = String(v == null ? '' : v).trim();
if (s && !seen[s]) { seen[s] = true; list.push(s); }
});
});
}
if (!Array.isArray(list) || !list.length) throw new Error('setDropdownFromList: no values to use');
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(list.map(String), opts.showDropdown !== false)
.setAllowInvalid(opts.allowInvalid === true) // default: reject values not in the list
.build();
targetRange.setDataValidation(rule);
return list.length;
}
// setDropdownFromList(sheet.getRange('E2:E'), ['Pending', 'Approved', 'Rejected']);
function shareFileWithEmail(fileIdOrFile, email, role, opts) {
opts = opts || {};
role = String(role || 'viewer').toLowerCase();
if (role !== 'viewer' && role !== 'editor') throw new Error('shareFileWithEmail: role must be viewer or editor');
var file = typeof fileIdOrFile === 'string' ? DriveApp.getFileById(fileIdOrFile) : fileIdOrFile;
var who = String(email || '').trim().toLowerCase();
if (!who) throw new Error('shareFileWithEmail: email required');
var emails = function (users) { return users.map(function (u) { return String(u.getEmail()).toLowerCase(); }); };
// Owner and editors already have "editor or higher"
var owner = file.getOwner();
var editors = emails(file.getEditors()).concat(owner ? [String(owner.getEmail()).toLowerCase()] : []);
var viewers = emails(file.getViewers()); // includes commenters
if (editors.indexOf(who) !== -1) return { changed: false, method: 'none' };
if (role === 'viewer' && viewers.indexOf(who) !== -1) return { changed: false, method: 'none' };
// Quiet share needs the Drive advanced service (Services → Drive API)
if (opts.notify === false && typeof Drive !== 'undefined' && Drive.Permissions) {
var apiRole = role === 'editor' ? 'writer' : 'reader';
if (Drive.Permissions.create) { // Drive API v3
Drive.Permissions.create({ role: apiRole, type: 'user', emailAddress: who }, file.getId(),
{ sendNotificationEmail: false, supportsAllDrives: true });
} else { // Drive API v2
Drive.Permissions.insert({ role: apiRole, type: 'user', value: who }, file.getId(),
{ sendNotificationEmails: false, supportsAllDrives: true });
}
return { changed: true, method: 'drive-api-quiet' };
}
// DriveApp always sends Google's "shared with you" email
if (role === 'editor') file.addEditor(who); else file.addViewer(who);
return { changed: true, method: 'driveapp' };
}
// shareFileWithEmail(fileId, 'client@example.com', 'viewer', { notify: false });
function sheetSchemaForPrompt(sheet, opts) {
opts = opts || {};
var lastCol = sheet.getLastColumn();
if (!lastCol) return 'Sheet "' + sheet.getName() + '" is empty.';
var n = opts.headerOnly ? 0 : (opts.sampleRows == null ? 1 : opts.sampleRows);
var max = opts.maxCellChars || 40;
var rows = sheet.getRange(1, 1, Math.min(Math.max(sheet.getLastRow(), 1), n + 1), lastCol).getDisplayValues();
function letter(i) { var s = ''; i++; while (i) { s = String.fromCharCode(65 + (i - 1) % 26) + s; i = Math.floor((i - 1) / 26); } return s; }
function cut(v) { v = String(v); return v.length > max ? v.slice(0, max - 1) + '…' : v; }
var lines = ['Sheet "' + sheet.getName() + '" columns:'];
rows[0].forEach(function (h, i) { lines.push(' ' + letter(i) + ': ' + (h || '(blank header)')); });
if (rows.length > 1) {
lines.push('Sample rows:');
rows.slice(1).forEach(function (r) { lines.push(' ' + r.map(cut).join(' | ')); });
}
return lines.join('\n');
}
function sheetToast(message, title, timeoutSeconds) {
title = title || 'NitroGAS';
timeoutSeconds = (timeoutSeconds == null) ? 5 : timeoutSeconds;
SpreadsheetApp.getActiveSpreadsheet().toast(message, title, timeoutSeconds);
}
// sheetToast('Synced 42 rows', 'Import', 3);
function snippetCatalogForPrompt(entries, opts) {
opts = opts || {};
entries = entries && entries.length ? entries : [
{ name: 'rowsToObjects', intent: 'turn getValues() rows into objects keyed by header' },
{ name: 'objectsToRows', intent: 'turn objects back into rows for setValues()' },
{ name: 'withScriptLock', intent: 'run a function under LockService.getScriptLock()' },
{ name: 'retryUrlFetch', intent: 'UrlFetchApp.fetch with retries and backoff on 429/5xx' },
{ name: 'claimNextQueueRow', intent: 'claim the next pending row in a sheet queue' },
{ name: 'jsonOk / jsonErr', intent: 'return ContentService JSON from a web app' }
];
var lines = [(opts.title || 'Helpers already in this project') + ':'];
entries.forEach(function (e) {
lines.push('- `' + e.name + '`' + (e.intent ? ': ' + e.intent : ''));
});
lines.push('');
lines.push(opts.instruction ||
'Call these helpers by name instead of rewriting them. Do not redefine them. ' +
'If one is missing something you need, say so instead of writing a new version.');
return lines.join('\n');
}
function createBatchedArray(data, chunkSize) {
const result = []
for(let i=0; i<data.length; i+=chunkSize) {
let chunk = data.slice(i, i+chunkSize)
result.push(chunk)
}
return result
}
function stampLastSuccessfulRun(target, status, opts) {
opts = opts || {};
status = String(status || 'OK').toUpperCase();
var ss = opts.spreadsheet || SpreadsheetApp.getActive();
// Named range first, then A1 notation like 'Status!B2'
var cell = (ss.getRangeByName(target) || ss.getRange(target)).getCell(1, 1);
var tz = ss.getSpreadsheetTimeZone();
var now = Utilities.formatDate(new Date(), tz, opts.format || 'yyyy-MM-dd HH:mm:ss z');
var sheet = cell.getSheet();
var row = cell.getRow();
var col = cell.getColumn();
// [last OK time] [status] [last run time] [message] — stored as plain text
sheet.getRange(row, col, 1, 4).setNumberFormat('@');
if (status === 'OK') cell.setValue(now); // FAIL never overwrites the last OK time
sheet.getRange(row, col + 1, 1, 3).setValues([[status, now, opts.message || '']]);
return now;
}
function getJson(key, fallback) {
var raw = PropertiesService.getScriptProperties().getProperty(key);
if (raw == null || raw === '') return fallback;
return JSON.parse(raw);
}
function setJson(key, value) {
PropertiesService.getScriptProperties().setProperty(key, JSON.stringify(value));
}
// setJson('config', { apiBase: 'https://example.com' });
// var config = getJson('config', {});
function assertEquals(actual, expected, label) {
var a = JSON.stringify(actual), e = JSON.stringify(expected);
if (a !== e) {
throw new Error((label ? label + ': ' : '') + 'expected ' + e + ' but got ' + a);
}
}
function runTests(tests) {
var passed = 0, failures = [];
Object.keys(tests).forEach(function (name) {
try {
tests[name]();
passed++;
console.log('✅ ' + name);
} catch (err) {
failures.push(name + ': ' + (err && err.message ? err.message : err));
console.log('❌ ' + name + ': ' + (err && err.message ? err.message : err));
}
});
var summary = passed + ' passed, ' + failures.length + ' failed';
console.log(summary);
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
if (ss) ss.toast(summary, failures.length ? 'Tests failed' : 'Tests passed', 5);
} catch (e) {} // not bound to a sheet, or no UI
if (failures.length) throw new Error(summary + '\n' + failures.join('\n'));
return { passed: passed, failed: 0, failures: [] };
}
function truncateForCell(text, maxLen) {
maxLen = maxLen == null ? 50000 : Number(maxLen);
if (text == null) return '';
var s = String(text);
if (!(maxLen > 0) || s.length <= maxLen) return s;
var suffix = '…[truncated]';
var keep = Math.max(0, maxLen - suffix.length);
return s.slice(0, keep) + suffix;
}
// sheet.getRange('A1').setValue(truncateForCell(err.stack));
function tryLockOrSkip(kind, timeoutMs) {
timeoutMs = timeoutMs == null ? 0 : timeoutMs;
var lock = (kind === 'document')
? LockService.getDocumentLock()
: LockService.getScriptLock();
var ok = timeoutMs > 0 ? lock.tryLock(timeoutMs) : lock.tryLock(1);
if (!ok) return null;
return lock;
}
// var lock = tryLockOrSkip('script', 0); if (!lock) return; try { /* work */ } finally { lock.releaseLock(); }
function underTimeBudget(startedAt, budgetMs) {
// Apps Script hard-stops ~6 minutes. Default budget leaves ~30s to checkpoint + exit.
var budget = budgetMs == null ? 330000 : Number(budgetMs); // 5.5 min
var start = startedAt instanceof Date ? startedAt.getTime() : Number(startedAt);
if (!isFinite(start) || !isFinite(budget) || budget <= 0) return false;
return (Date.now() - start) < budget;
}
// var started = new Date();
// while (underTimeBudget(started) && queue.length) { processNext_(queue.shift()); }
function upsertRowByKey(sheet, keyHeader, keyValue, valuesByHeader) {
var data = sheet.getDataRange().getValues();
var headers = data[0];
var keyCol = headers.indexOf(keyHeader);
if (keyCol === -1) throw new Error('Missing header: ' + keyHeader);
var rowIndex = -1;
for (var r = 1; r < data.length; r++) {
if (String(data[r][keyCol]) === String(keyValue)) { rowIndex = r; break; }
}
var row = headers.map(function (h) {
return valuesByHeader.hasOwnProperty(h) ? valuesByHeader[h] : (rowIndex === -1 ? '' : data[rowIndex][headers.indexOf(h)]);
});
if (rowIndex === -1) { sheet.appendRow(row); return sheet.getLastRow(); }
sheet.getRange(rowIndex + 1, 1, 1, row.length).setValues([row]);
return rowIndex + 1;
}
// upsertRowByKey(sheet, 'email', 'ada@example.com', { email: 'ada@example.com', name: 'Ada' });
function writeBlockLeavingFormulaCols(sheet, rows, dataHeaders, opts) {
opts = opts || {};
if (!rows || !rows.length) return { startRow: null, numRows: 0, runs: [] };
var headerRow = opts.headerRow || 1;
// Header name -> 1-based column number (read from the sheet if not passed in)
var map = opts.headerMap;
if (!map) {
map = {};
sheet.getRange(headerRow, 1, 1, sheet.getLastColumn()).getValues()[0]
.forEach(function (h, i) { var k = String(h).trim(); if (k && !map[k]) map[k] = i + 1; });
}
// Only allowlisted headers get written; everything else (formulas) is left alone
var cols = dataHeaders.filter(function (h, i) { return dataHeaders.indexOf(h) === i; })
.map(function (h) {
if (!map[h]) throw new Error('writeBlockLeavingFormulaCols: header not found: ' + h);
return { header: h, col: map[h] };
})
.sort(function (a, b) { return a.col - b.col; });
// Group side-by-side columns into runs so each run is one setValues call
var runs = [];
cols.forEach(function (c) {
var last = runs[runs.length - 1];
if (last && c.col === last.startCol + last.headers.length) last.headers.push(c.header);
else runs.push({ startCol: c.col, headers: [c.header] });
});
var startRow = opts.startRow || Math.max(sheet.getLastRow(), headerRow) + 1;
runs.forEach(function (run) {
var values = rows.map(function (obj) {
return run.headers.map(function (h) { return obj[h] == null ? '' : obj[h]; });
});
sheet.getRange(startRow, run.startCol, rows.length, run.headers.length).setValues(values);
});
return { startRow: startRow, numRows: rows.length, runs: runs };
}
function writeValuesAsCsvToDrive(values, folder, filename) {
var csv = valuesToCsv(values); // see Convert Sheet Values to CSV Text
var existing = folder.getFilesByName(filename);
if (existing.hasNext()) {
var file = existing.next();
file.setContent(csv);
return file;
}
return folder.createFile(filename, csv, MimeType.CSV);
}
// writeValuesAsCsvToDrive(values, folder, 'Exports/2026-09-18.csv'.split('/').pop());
function rowsToObjects(rows) {
if (!rows || !rows.length) return [];
var headers = rows[0].map(function (h) { return String(h).trim(); });
return rows.slice(1).map(function (row) {
var obj = {};
for (var i = 0; i < headers.length; i++) {
obj[headers[i]] = row[i];
}
return obj;
});
}
function objectsToRows(objects, headers) {
if (!objects || !objects.length) return [headers || []];
headers = headers || Object.keys(objects[0]);
var body = objects.map(function (obj) {
return headers.map(function (key) {
return obj.hasOwnProperty(key) ? obj[key] : '';
});
});
return [headers].concat(body);
}
// rowsToObjects([['id','name'],[1,'Ada'],[2,'Lin']]);
// objectsToRows([{id:1,name:'Ada'}], ['id','name']);
function withDocumentLock(fn, waitMs) {
waitMs = waitMs || 30000;
var lock = LockService.getDocumentLock();
if (!lock.tryLock(waitMs)) {
throw new Error('Could not acquire document lock within ' + waitMs + 'ms');
}
try {
return fn();
} finally {
lock.releaseLock();
}
}
// withDocumentLock(function () { /* mutate the bound spreadsheet safely */ });
function withScriptLock(fn, waitMs) {
waitMs = waitMs || 30000;
var lock = LockService.getScriptLock();
if (!lock.tryLock(waitMs)) {
throw new Error('Could not acquire script lock within ' + waitMs + 'ms');
}
try {
return fn();
} finally {
lock.releaseLock();
}
}
// withScriptLock(function () { /* project-wide critical section */ });
function withUserLock(fn, waitMs) {
waitMs = waitMs || 30000;
var lock = LockService.getUserLock();
if (!lock.tryLock(waitMs)) {
throw new Error('Could not acquire user lock within ' + waitMs + 'ms');
}
try {
return fn();
} finally {
lock.releaseLock();
}
}
// withUserLock(function () { /* per-user critical section */ });