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 addBusinessDays(date, n, holidays) {
n = Number(n) || 0;
var tz;
try {
tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
} catch (e) {
tz = Session.getScriptTimeZone();
}
var skip = {};
(holidays || []).forEach(function (h) { skip[String(h).trim()] = true; });
var pad = function (x) { return (x < 10 ? '0' : '') + x; };
var parts = Utilities.formatDate(date || new Date(), tz, 'yyyy-MM-dd').split('-');
var y = Number(parts[0]), m = Number(parts[1]), d = Number(parts[2]);
var step = n >= 0 ? 1 : -1;
var left = Math.abs(n);
while (left > 0) {
d += step;
var cur = new Date(Date.UTC(y, m - 1, d));
y = cur.getUTCFullYear();
m = cur.getUTCMonth() + 1;
d = cur.getUTCDate();
var dow = cur.getUTCDay();
var key = y + '-' + pad(m) + '-' + pad(d);
if (dow !== 0 && dow !== 6 && !skip[key]) left--;
}
// Return a Date at noon UTC on that calendar day so formatDate in tz stays on the same day.
return new Date(Date.UTC(y, m - 1, d, 12, 0, 0));
}
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 batchGetRanges(spreadsheetId, ranges) {
var out = {};
if (!ranges || !ranges.length) return out;
var res = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, { ranges: ranges });
(res.valueRanges || []).forEach(function (vr, i) {
out[ranges[i]] = vr.values || [];
});
return out;
}
function buildPrefilledFormUrl(formUrl, entryMap, rowObject) {
rowObject = rowObject || {};
entryMap = entryMap || {};
var base = String(formUrl || '').trim();
if (!base) throw new Error('buildPrefilledFormUrl: formUrl is required');
// Normalize to the viewform endpoint; strip existing query/hash.
base = base.replace(/\/(edit|viewform|formResponse).*$/, '/viewform');
if (base.indexOf('/viewform') === -1) {
base = base.replace(/\/?$/, '/') + 'viewform';
}
base = base.split('?')[0].split('#')[0];
var parts = [];
Object.keys(entryMap).forEach(function (field) {
var entryId = entryMap[field];
if (entryId == null || entryId === '') return;
var val = Object.prototype.hasOwnProperty.call(rowObject, field) ? rowObject[field] : '';
if (val == null) return;
var s = String(val);
if (!s) return; // skip blanks
parts.push('entry.' + entryId + '=' + encodeURIComponent(s));
});
return parts.length ? base + '?' + parts.join('&') : base;
}
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 businessHoursBetween(start, end, opts) {
opts = opts || {};
if (!start || !end) return 0;
var a = start instanceof Date ? new Date(start.getTime()) : new Date(start);
var b = end instanceof Date ? new Date(end.getTime()) : new Date(end);
if (isNaN(a.getTime()) || isNaN(b.getTime())) return 0;
var sign = 1;
if (b.getTime() < a.getTime()) { var t = a; a = b; b = t; sign = -1; }
var ss = opts.spreadsheet || (typeof SpreadsheetApp !== 'undefined' ? SpreadsheetApp.getActive() : null);
var tz = opts.timezone
|| (ss && ss.getSpreadsheetTimeZone && ss.getSpreadsheetTimeZone())
|| Session.getScriptTimeZone()
|| 'America/New_York';
var workStart = opts.workStart || '09:00';
var workEnd = opts.workEnd || '17:00';
var workdays = opts.workdays || [1, 2, 3, 4, 5];
var skip = {};
(opts.holidays || []).forEach(function (h) { skip[String(h).trim()] = true; });
function toMins(hhmm) {
var p = String(hhmm).split(':');
return Number(p[0]) * 60 + Number(p[1] || 0);
}
var openM = toMins(workStart);
var closeM = toMins(workEnd);
if (closeM <= openM) throw new Error('businessHoursBetween: workEnd must be after workStart');
function dayKey(d) { return Utilities.formatDate(d, tz, 'yyyy-MM-dd'); }
function weekday(d) { return Number(Utilities.formatDate(d, tz, 'u')); }
function minuteOfDay(d) {
return Number(Utilities.formatDate(d, tz, 'H')) * 60 + Number(Utilities.formatDate(d, tz, 'm'));
}
var totalMins = 0;
var keyA = dayKey(a);
var keyB = dayKey(b);
// Iterate calendar days from keyA to keyB using UTC date parts of the keys.
var parts = keyA.split('-');
var y = Number(parts[0]), m = Number(parts[1]), d = Number(parts[2]);
for (var i = 0; i < 2000; i++) {
var key = y + '-' + (m < 10 ? '0' : '') + m + '-' + (d < 10 ? '0' : '') + d;
// Representative instant at noon UTC on this calendar day (stable for formatDate weekday in most US tz)
var noon = new Date(Date.UTC(y, m - 1, d, 12, 0, 0));
var wd = weekday(noon);
var isWork = workdays.indexOf(wd) !== -1 && !skip[key];
if (isWork) {
var from = openM;
var to = closeM;
if (key === keyA) from = Math.max(from, minuteOfDay(a));
if (key === keyB) to = Math.min(to, minuteOfDay(b));
if (to > from) totalMins += to - from;
}
if (key === keyB) break;
// next calendar day
var next = new Date(Date.UTC(y, m - 1, d + 1));
y = next.getUTCFullYear();
m = next.getUTCMonth() + 1;
d = next.getUTCDate();
}
return sign * totalMins / 60;
}
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 closeFormAtLimit(form, opts) {
opts = opts || {};
if (!form.isAcceptingResponses()) return false;
var count = form.getResponses().length;
var full = opts.maxResponses && count >= opts.maxResponses;
var late = opts.closeAt && new Date() >= new Date(opts.closeAt);
if (!full && !late) return false;
form.setCustomClosedFormMessage(
opts.message || (full ? 'This form has reached its response limit. Thank you!' : 'This form is now closed. Thank you!')
);
form.setAcceptingResponses(false);
return true;
}
function combineTabsWithSource(ss, pattern, opts) {
opts = opts || {};
var sourceHeader = opts.sourceHeader || 'Source';
var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
var re = pattern instanceof RegExp ? pattern : new RegExp(pattern);
var headers = [];
var seen = {};
var records = [];
ss.getSheets().forEach(function (sh) {
var name = sh.getName();
if (!re.test(name) || sh.getLastRow() <= headerRows) return;
var values = sh.getDataRange().getValues();
var head = values[headerRows - 1].map(function (h) { return String(h).trim(); });
head.forEach(function (h) {
if (h && !seen[h]) { seen[h] = true; headers.push(h); }
});
for (var r = headerRows; r < values.length; r++) {
var row = values[r];
if (row.join('') === '') continue;
var rec = {};
head.forEach(function (h, c) { if (h) rec[h] = row[c]; });
records.push({ source: name, rec: rec });
}
});
var out = [[sourceHeader].concat(headers)];
records.forEach(function (x) {
out.push([x.source].concat(headers.map(function (h) {
return x.rec[h] === undefined ? '' : x.rec[h];
})));
});
return out;
}
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 copyFormattingFromRow(templateRowRange, targetRange) {
var sheet = targetRange.getSheet();
var cols = Math.min(templateRowRange.getNumColumns(), targetRange.getNumColumns());
var template = templateRowRange.offset(0, 0, 1, cols);
var startRow = targetRange.getRow();
var startCol = targetRange.getColumn();
var numRows = targetRange.getNumRows();
for (var i = 0; i < numRows; i++) {
template.copyTo(
sheet.getRange(startRow + i, startCol, 1, cols),
SpreadsheetApp.CopyPasteType.PASTE_FORMAT,
false
);
}
return targetRange;
}
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 createDraftsFromRows(rows, buildFn) {
var created = 0;
var skipped = 0;
(rows || []).forEach(function (row) {
var msg = buildFn(row);
if (!msg || msg.to == null || !String(msg.to).trim()) {
skipped++;
return;
}
var to = String(msg.to).trim();
var subject = msg.subject == null ? '' : String(msg.subject);
var htmlBody = msg.htmlBody != null ? String(msg.htmlBody) : (msg.body == null ? '' : String(msg.body));
var options = { htmlBody: htmlBody };
if (msg.cc) options.cc = msg.cc;
if (msg.bcc) options.bcc = msg.bcc;
if (msg.name) options.name = msg.name;
if (msg.attachments) options.attachments = msg.attachments;
GmailApp.createDraft(to, subject, htmlBody, options);
created++;
});
return { created: created, skipped: skipped };
}
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 fillDocTemplate(templateId, folder, rowObj) {
rowObj = rowObj || {};
var folderObj = typeof folder === 'string' ? DriveApp.getFolderById(folder) : folder;
var copy = DriveApp.getFileById(templateId).makeCopy(folderObj);
var doc = DocumentApp.openById(copy.getId());
var body = doc.getBody();
var keys = {};
var text = body.getText();
var re = /\{\{\s*([^{}]+?)\s*\}\}/g;
var m;
while ((m = re.exec(text)) !== null) keys[m[1].trim()] = true;
Object.keys(keys).forEach(function (key) {
var val = Object.prototype.hasOwnProperty.call(rowObj, key) ? rowObj[key] : '';
if (val == null) val = '';
body.replaceText('\\{\\{\\s*' + key.replace(/[.*+?^${}()|[\]\\]/g, '\\$&') + '\\s*\\}\\}', String(val));
});
doc.saveAndClose();
return copy.getUrl();
}
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 findErrorCells(sheetOrSpreadsheet) {
var ERR = /^#(REF!|N\/A|VALUE!|DIV\/0!|NAME\?|NUM!|NULL!|ERROR!|SPILL!)/;
var sheets = sheetOrSpreadsheet.getSheets ? sheetOrSpreadsheet.getSheets() : [sheetOrSpreadsheet];
var out = [];
function a1(row, col) {
var s = '';
while (col > 0) { var m = (col - 1) % 26; s = String.fromCharCode(65 + m) + s; col = Math.floor((col - 1) / 26); }
return s + row;
}
sheets.forEach(function (sh) {
var range = sh.getDataRange();
if (range.getNumRows() === 0 || range.getNumColumns() === 0) return;
var shown = range.getDisplayValues();
var formulas = range.getFormulas();
for (var r = 0; r < shown.length; r++) {
for (var c = 0; c < shown[r].length; c++) {
var m = ERR.exec(shown[r][c]);
if (m && formulas[r][c]) {
out.push({ sheet: sh.getName(), a1: a1(r + 1, c + 1), error: m[0], formula: formulas[r][c] });
}
}
}
});
return out;
}
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 formulaInventoryForPrompt(range) {
var r1c1 = range.getFormulasR1C1();
var a1 = range.getFormulas();
var row0 = range.getRow();
var col0 = range.getColumn();
var groups = {};
var order = [];
function colName(col) {
var s = '';
while (col > 0) { var m = (col - 1) % 26; s = String.fromCharCode(65 + m) + s; col = Math.floor((col - 1) / 26); }
return s;
}
for (var r = 0; r < r1c1.length; r++) {
for (var c = 0; c < r1c1[r].length; c++) {
var f = r1c1[r][c];
if (!f) continue;
if (!groups[f]) {
groups[f] = { r1c1: f, formula: a1[r][c], count: 0, example: colName(col0 + c) + (row0 + r) };
order.push(f);
}
groups[f].count++;
}
}
return order.map(function (f) { return groups[f]; })
.sort(function (x, y) { return y.count - x.count; });
}
function geocodeCached(address, opts) {
opts = opts || {};
var key = String(address == null ? '' : address).trim().replace(/\s+/g, ' ');
if (!key) return null;
var cacheKey = 'geo:' + Utilities.base64EncodeWebSafe(
Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key.toLowerCase()));
var cache = CacheService.getScriptCache();
var hit = cache.get(cacheKey);
if (hit) return JSON.parse(hit);
var geocoder = Maps.newGeocoder();
if (opts.region) geocoder.setRegion(opts.region);
if (opts.language) geocoder.setLanguage(opts.language);
var res = geocoder.geocode(key);
var top = res && res.status === 'OK' && res.results && res.results[0];
var out = top ? {
lat: top.geometry.location.lat,
lng: top.geometry.location.lng,
formatted: top.formatted_address
} : null;
// Cache misses too (shorter), so a bad address isn't retried every row.
var ttl = out ? (opts.ttlSeconds || 21600) : Math.min(opts.ttlSeconds || 21600, 3600);
cache.put(cacheKey, JSON.stringify(out), Math.min(ttl, 21600));
return out;
}
function getDailyRate(from, to, date) {
from = String(from || '').toUpperCase(); to = String(to || '').toUpperCase();
if (!from || !to) throw new Error('getDailyRate: from and to are required');
if (from === to) return 1;
var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var day = date ? Utilities.formatDate(new Date(date), tz, 'yyyy-MM-dd') : 'latest';
var key = 'fx:' + day + ':' + from + ':' + to;
var cache = CacheService.getScriptCache();
var hit = cache.get(key);
if (hit) return Number(hit);
var res = UrlFetchApp.fetch('https://api.frankfurter.app/' + day + '?from=' + from + '&to=' + to,
{ muteHttpExceptions: true });
if (res.getResponseCode() !== 200) throw new Error('getDailyRate: HTTP ' + res.getResponseCode());
var rate = JSON.parse(res.getContentText()).rates[to];
if (typeof rate !== 'number') throw new Error('getDailyRate: no rate for ' + from + '->' + to);
// Past dates never change; "latest" refreshes every 6 hours.
cache.put(key, String(rate), 21600);
return rate;
}
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 getLatestCsvAttachment(query) {
var threads = GmailApp.search(query + ' has:attachment filename:csv', 0, 10);
var best = null;
threads.forEach(function (t) {
t.getMessages().forEach(function (m) {
m.getAttachments().forEach(function (a) {
if (!/\.csv$/i.test(a.getName())) return;
if (!best || m.getDate() > best.date) best = { date: m.getDate(), att: a };
});
});
});
if (!best) return null;
return {
name: best.att.getName(),
date: best.date,
rows: Utilities.parseCsv(best.att.getDataAsString())
};
}
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 getSheetByGid(ss, gid) {
var target = Number(gid);
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetId() === target) return sheets[i];
}
return null;
}
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 listCalendarEventRows(calendarId, start, end, opts) {
opts = opts || {};
var cal = calendarId ? CalendarApp.getCalendarById(calendarId) : CalendarApp.getDefaultCalendar();
if (!cal) throw new Error('Calendar not found: ' + calendarId);
var tz = opts.timeZone || SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var events = cal.getEvents(new Date(start), new Date(end));
var rows = [['Date', 'Start', 'End', 'Hours', 'Title', 'Guests', 'Event Id']];
events.forEach(function (ev) {
if (opts.skipAllDay !== false && ev.isAllDayEvent()) return;
if (opts.titleFilter && !opts.titleFilter.test(ev.getTitle())) return;
var s = ev.getStartTime(), e = ev.getEndTime();
rows.push([
Utilities.formatDate(s, tz, 'yyyy-MM-dd'),
Utilities.formatDate(s, tz, 'HH:mm'),
Utilities.formatDate(e, tz, 'HH:mm'),
Math.round((e - s) / 36e5 * 100) / 100,
ev.getTitle(),
ev.getGuestList().map(function (g) { return g.getEmail(); }).join(', '),
ev.getId()
]);
});
return rows;
}
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 mergeByKeyNewestWins(listA, listB, keyField, dateField) {
var norm = function (v) { return String(v == null ? '' : v).trim().toLowerCase(); };
var time = function (r) { var t = new Date(r[dateField]).getTime(); return isNaN(t) ? -Infinity : t; };
var byKey = {}, order = [];
listA.concat(listB).forEach(function (row) {
var k = norm(row[keyField]);
if (!k) return;
var cur = byKey[k];
if (!cur) { byKey[k] = row; order.push(k); return; }
var newer = time(row) >= time(cur) ? row : cur;
var older = newer === row ? cur : row;
var merged = {};
Object.keys(older).forEach(function (f) { merged[f] = older[f]; });
Object.keys(newer).forEach(function (f) {
if (newer[f] !== '' && newer[f] != null) merged[f] = newer[f];
});
byKey[k] = merged;
});
return order.map(function (k) { return byKey[k]; });
}
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 nextSequenceId(prefix) {
prefix = prefix == null ? '' : String(prefix);
var lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
var props = PropertiesService.getScriptProperties();
var key = 'seq:' + prefix;
var n = Number(props.getProperty(key) || '0') + 1;
if (!isFinite(n) || n < 1) n = 1;
props.setProperty(key, String(n));
var pad = ('00000' + n).slice(-5);
return prefix + pad;
} finally {
lock.releaseLock();
}
}
function normalizePhone(raw, defaultCountryCode) {
if (raw == null) return null;
var s = String(raw).trim();
if (!s) return null;
var hasPlus = s.charAt(0) === '+';
var digits = s.replace(/\D/g, '');
if (!digits) return null;
if (hasPlus) return '+' + digits;
var cc = defaultCountryCode == null || defaultCountryCode === ''
? '1'
: String(defaultCountryCode).replace(/\D/g, '');
if (!cc) cc = '1';
// Already includes the country code without a plus.
if (digits.indexOf(cc) === 0 && digits.length > cc.length + 1) return '+' + digits;
// US/CA local 10-digit.
if (cc === '1' && digits.length === 10) return '+1' + digits;
// National numbers often keep a leading 0 trunk prefix — drop it before adding cc.
if (digits.charAt(0) === '0') digits = digits.substring(1);
return '+' + cc + digits;
}
function normalizeEmail(email) {
if (email == null) return '';
return String(email).trim().toLowerCase();
}
// normalizeEmail(' Ada@Example.com ') // → 'ada@example.com'
function notifyOnce(key, sendFn) {
var props = PropertiesService.getScriptProperties();
var flag = 'notified_' + key;
if (props.getProperty(flag)) return false;
sendFn();
props.setProperty(flag, new Date().toISOString());
return true;
}
function resetNotify(key) {
PropertiesService.getScriptProperties().deleteProperty('notified_' + key);
}
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 parseLooseDate(value, opts) {
opts = opts || {};
if (value instanceof Date) return isNaN(value.getTime()) ? null : value;
if (typeof value === 'number') {
// Sheets serial number (days since 1899-12-30)
return new Date(Math.round((value - 25569) * 86400000) + new Date().getTimezoneOffset() * 60000);
}
var s = String(value || '').trim();
if (!s) return null;
var MONTHS = { jan: 0, feb: 1, mar: 2, apr: 3, may: 4, jun: 5, jul: 6, aug: 7, sep: 8, oct: 9, nov: 10, dec: 11 };
var y, m, d, mt;
if ((mt = s.match(/^(\d{4})[-\/.](\d{1,2})[-\/.](\d{1,2})/))) {
y = +mt[1]; m = +mt[2] - 1; d = +mt[3];
} else if ((mt = s.match(/^(\d{1,2})[-\/.](\d{1,2})[-\/.](\d{2,4})$/))) {
var a = +mt[1], b = +mt[2];
if (opts.dayFirst === undefined && a <= 12 && b <= 12 && a !== b) {
throw new Error('Ambiguous date "' + s + '": pass {dayFirst: true} or {dayFirst: false}');
}
var dayFirst = opts.dayFirst === undefined ? a > 12 : opts.dayFirst;
d = dayFirst ? a : b; m = (dayFirst ? b : a) - 1; y = +mt[3];
if (y < 100) y += 2000;
} else if ((mt = s.match(/^(\d{1,2})\s+([a-z]{3})[a-z]*\.?,?\s+(\d{4})$/i))) {
d = +mt[1]; m = MONTHS[mt[2].toLowerCase()]; y = +mt[3];
} else if ((mt = s.match(/^([a-z]{3})[a-z]*\.?\s+(\d{1,2})(?:st|nd|rd|th)?,?\s+(\d{4})$/i))) {
m = MONTHS[mt[1].toLowerCase()]; d = +mt[2]; y = +mt[3];
} else {
return null;
}
if (m === undefined) return null;
var out = new Date(y, m, d);
if (out.getFullYear() !== y || out.getMonth() !== m || out.getDate() !== d) return null;
return out;
}
function pickColumns(rows, headers) {
return rows.map(function (row) {
return headers.reduce(function (out, h) {
out[h] = Object.prototype.hasOwnProperty.call(row, h) ? row[h] : '';
return out;
}, {});
});
}
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 protectRowForEditors(sheet, row, keepEmails) {
var existing = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).filter(function (p) {
var r = p.getRange();
return r.getRow() === row && r.getNumRows() === 1;
});
if (existing.length) return null; // already locked
var keep = (keepEmails || []).map(function (e) { return String(e).toLowerCase(); });
var me = Session.getEffectiveUser().getEmail().toLowerCase();
var p = sheet.getRange(row, 1, 1, sheet.getMaxColumns()).protect()
.setDescription('Row ' + row + ' locked');
if (keep.length) p.addEditors(keep);
var remove = p.getEditors().filter(function (u) {
var e = u.getEmail().toLowerCase();
return e !== me && keep.indexOf(e) === -1;
});
if (remove.length) p.removeEditors(remove);
if (p.canDomainEdit()) p.setDomainEdit(false);
return p;
}
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 redactRowsForPrompt(rows, opts) {
opts = opts || {};
var named = (opts.columns || []).map(function (c) { return String(c).toLowerCase(); });
var EMAIL = /[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}/gi;
var PHONE = /\+?\d[\d\s().-]{6,}\d/g;
var LETTERS = 'abcdefghijklmnopqrstuvwxyz';
var seq = 0;
function sameShape(s) { // letters → letters, digits → digits, keep punctuation and case
seq++;
return String(s).replace(/[A-Za-z0-9]/g, function (ch, i) {
var n = (i * 7 + seq * 13) % 26;
if (/\d/.test(ch)) return String(n % 10);
var c = LETTERS.charAt(n);
return ch === ch.toUpperCase() ? c.toUpperCase() : c;
});
}
function fakeEmail(m) { return sameShape(m); } // same length, still looks like an email
function fakeValue(v) {
if (v instanceof Date) return new Date(2026, 0, 1 + (++seq % 28));
if (typeof v === 'number') {
var digits = String(Math.abs(Math.round(v))).length;
var n = Math.pow(10, digits - 1) + (++seq % 9);
return v < 0 ? -n : n;
}
if (typeof v === 'boolean' || v === '' || v == null) return v;
return sameShape(v);
}
function scrub(v) {
if (typeof v !== 'string') return v;
return v.replace(EMAIL, fakeEmail).replace(PHONE, function (m) { return sameShape(m); });
}
function cell(header, v) {
return named.indexOf(String(header).toLowerCase()) !== -1 ? fakeValue(v) : scrub(v);
}
if (rows.length && Array.isArray(rows[0])) {
var header = rows[0];
return rows.map(function (r, i) {
if (i === 0) return opts.keepHeader === false ? r.map(function () { return ''; }) : r.slice();
return r.map(function (v, c) { return cell(header[c], v); });
});
}
return rows.map(function (o) {
var out = {};
Object.keys(o).forEach(function (k) { out[k] = cell(k, o[k]); });
return out;
});
}
function renameFilesFromRows(rows, opts) {
opts = opts || {};
var dryRun = !!opts.dryRun;
return (rows || []).map(function (row) {
var fileId = row && row.fileId != null ? String(row.fileId).trim() : '';
var newName = row && row.newName != null ? String(row.newName).trim() : '';
if (!fileId) return { fileId: fileId, newName: newName, status: 'skipped', detail: 'missing fileId' };
if (!newName) return { fileId: fileId, newName: newName, status: 'skipped', detail: 'missing newName' };
try {
var file = DriveApp.getFileById(fileId);
var oldName = file.getName();
if (dryRun) {
return { fileId: fileId, newName: newName, oldName: oldName, status: 'would-rename', detail: oldName + ' → ' + newName };
}
if (oldName === newName) {
return { fileId: fileId, newName: newName, oldName: oldName, status: 'unchanged', detail: 'already named' };
}
file.setName(newName);
return { fileId: fileId, newName: newName, oldName: oldName, status: 'renamed', detail: oldName + ' → ' + newName };
} catch (e) {
return { fileId: fileId, newName: newName, status: 'error', detail: String(e && e.message ? e.message : e) };
}
});
}
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 rowsOlderThan(rows, dateField, days, opts) {
opts = opts || {};
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tz = opts.timezone || (ss && ss.getSpreadsheetTimeZone()) || Session.getScriptTimeZone();
var now = opts.now instanceof Date ? opts.now : new Date();
var inclusive = !!opts.inclusive; // true: age >= days; false (default): age > days
var todayYmd = Utilities.formatDate(now, tz, 'yyyy-MM-dd');
var t = todayYmd.split('-');
var todayUtc = Date.UTC(+t[0], +t[1] - 1, +t[2]);
return (rows || []).filter(function (row) {
var v = row[dateField];
if (v == null || v === '') return false;
var d = v instanceof Date ? v : new Date(v);
if (isNaN(d.getTime())) return false;
var ymd = Utilities.formatDate(d, tz, 'yyyy-MM-dd');
var p = ymd.split('-');
var ageDays = Math.round((todayUtc - Date.UTC(+p[0], +p[1] - 1, +p[2])) / 86400000);
return inclusive ? ageDays >= days : ageDays > days;
});
}
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 sendDigestPerOwner(rows, ownerKey, renderFn, opts) {
opts = opts || {};
var maxSends = opts.maxSends || 50;
var groups = {};
rows.forEach(function (r) {
var owner = String(r[ownerKey] || '').trim().toLowerCase();
if (!owner) return;
(groups[owner] = groups[owner] || []).push(r);
});
var result = { sent: [], skipped: [], dryRun: !!opts.dryRun };
Object.keys(groups).sort().forEach(function (owner) {
if (result.sent.length >= maxSends) { result.skipped.push(owner); return; }
var msg = renderFn(owner, groups[owner]);
if (opts.dryRun) {
console.log('[dry run] to ' + owner + ': ' + msg.subject + ' (' + groups[owner].length + ' rows)');
} else {
MailApp.sendEmail({ to: owner, subject: msg.subject, body: msg.body, htmlBody: msg.htmlBody });
}
result.sent.push(owner);
});
if (result.skipped.length) console.log('maxSends reached; skipped: ' + result.skipped.join(', '));
return result;
}
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 setDependentDropdown(cell, lookup, opts) {
opts = opts || {};
var target = cell.offset(0, opts.offset || 1);
var pick = String(cell.getValue());
var options = lookup[pick] || [];
var current = String(target.getValue());
if (!options.length) {
target.clearDataValidations();
} else {
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(options, true)
.setAllowInvalid(!!opts.allowInvalid)
.build();
target.setDataValidation(rule);
}
var cleared = false;
if (current && options.indexOf(current) === -1) {
target.clearContent();
cleared = true;
}
return { options: options, cleared: cleared };
}
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 setHighlightRule(range, opts) {
if (!opts || !opts.tag || !opts.formula) throw new Error('setHighlightRule: tag and formula are required');
var sh = range.getSheet();
var marker = 'N("tag:' + opts.tag + '")';
var body = String(opts.formula).replace(/^=/, '');
var tagged = '=(' + body + ')+' + marker; // TRUE + 0 is still truthy
var kept = [], replaced = 0;
sh.getConditionalFormatRules().forEach(function (rule) {
var cond = rule.getBooleanCondition();
var vals = cond ? cond.getCriteriaValues() : [];
var f = vals.length ? String(vals[0]) : '';
if (f.indexOf(marker) !== -1) { replaced++; return; }
kept.push(rule);
});
var builder = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(tagged)
.setRanges([range]);
if (opts.background) builder.setBackground(opts.background);
if (opts.fontColor) builder.setFontColor(opts.fontColor);
kept.push(builder.build());
sh.setConditionalFormatRules(kept);
return replaced;
}
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 slugify(text, maxLength) {
var s = String(text == null ? '' : text)
.normalize('NFKD').replace(/[\u0300-\u036f]/g, '') // strip accents
.toLowerCase()
.replace(/&/g, ' and ')
.replace(/[^a-z0-9]+/g, '-')
.replace(/^-+|-+$/g, '');
if (maxLength && s.length > maxLength) s = s.slice(0, maxLength).replace(/-+$/, '');
return s;
}
function snapshotRangeToHistory(sourceRange, historySheetName) {
var ss = sourceRange.getSheet().getParent();
var tz = ss.getSpreadsheetTimeZone();
var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
var hist = ss.getSheetByName(historySheetName) || ss.insertSheet(historySheetName);
var last = hist.getLastRow();
if (last > 0) {
var lastDate = hist.getRange(last, 1).getDisplayValue();
if (lastDate === today) return false; // already snapshotted today
}
var values = sourceRange.getDisplayValues();
var flat = [].concat.apply([], values);
hist.getRange(last + 1, 1).setNumberFormat('@'); // plain text, so Sheets doesn't turn it into a date
hist.getRange(last + 1, 1, 1, flat.length + 1).setValues([[today].concat(flat)]);
return true;
}
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 splitMultiSelectToRows(values, colIndex, delimiter) {
delimiter = delimiter || ',';
var out = [values[0]];
for (var r = 1; r < values.length; r++) {
var row = values[r];
var parts = String(row[colIndex] == null ? '' : row[colIndex]).split(delimiter)
.map(function (s) { return s.trim(); }).filter(String);
if (!parts.length) { out.push(row.slice()); continue; }
parts.forEach(function (p) {
var copy = row.slice();
copy[colIndex] = p;
out.push(copy);
});
}
return out;
}
function splitTextUnderLimit(text, limit) {
text = String(text == null ? '' : text);
if (!limit || limit < 1) throw new Error('splitTextUnderLimit: limit must be >= 1');
var chunks = [];
var rest = text;
while (rest.length > limit) {
var window = rest.slice(0, limit + 1);
var cut = window.lastIndexOf('\n');
if (cut <= 0) cut = window.lastIndexOf(' ');
if (cut <= 0) cut = limit; // one very long word: hard cut
chunks.push(rest.slice(0, cut).replace(/\s+$/, ''));
rest = rest.slice(cut).replace(/^[\n ]+/, '');
}
if (rest.length || !chunks.length) chunks.push(rest);
return chunks;
}
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 stampEditTimestamps(e, opts) {
opts = opts || {};
var r = e && e.range;
if (!r) return 0;
var sh = r.getSheet();
var headerRows = opts.headerRows == null ? 1 : opts.headerRows;
var created = opts.createdCol, updated = opts.updatedCol;
var first = Math.max(r.getRow(), headerRows + 1);
var last = r.getLastRow();
if (last < first) return 0; // header-only edit
// Ignore edits that only touched the timestamp columns themselves
var c1 = r.getColumn(), c2 = r.getLastColumn();
var onlyStampCols = true;
for (var c = c1; c <= c2; c++) {
if (c !== created && c !== updated) { onlyStampCols = false; break; }
}
if (onlyStampCols) return 0;
var n = last - first + 1;
var now = new Date();
if (created) {
var cr = sh.getRange(first, created, n, 1);
var vals = cr.getValues().map(function (row) { return [row[0] === '' ? now : row[0]]; });
cr.setValues(vals);
}
if (updated) {
sh.getRange(first, updated, n, 1).setValues(Array.from({ length: n }, function () { return [now]; }));
}
return n;
}
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 sumByKey(values, keyIndex, valueIndex) {
var totals = {};
values.slice(1).forEach(function (row) {
var key = String(row[keyIndex]).trim();
if (!key) return;
var n = Number(row[valueIndex]);
totals[key] = (totals[key] || 0) + (isNaN(n) ? 0 : n);
});
return [[values[0][keyIndex], values[0][valueIndex]]].concat(
Object.keys(totals).sort().map(function (k) { return [k, totals[k]]; })
);
}
function timeStep(name, fn) {
var label = name == null ? 'step' : String(name);
var t0 = Date.now();
try {
var result = fn();
console.log('[timeStep] ' + label + ': ' + (Date.now() - t0) + 'ms');
return result;
} catch (e) {
console.log('[timeStep] ' + label + ': ' + (Date.now() - t0) + 'ms (error)');
throw e;
}
}
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 translateCells(range, targetLang, opts) {
opts = opts || {};
var src = opts.sourceLang || '';
var values = range.getValues();
var memo = {};
var out = values.map(function (row) {
return row.map(function (v) {
if (typeof v !== 'string' || !v.trim()) return v;
if (!(v in memo)) memo[v] = LanguageApp.translate(v, src, targetLang);
return memo[v];
});
});
var dest = opts.destination || range.offset(0, range.getNumColumns());
dest.offset(0, 0, out.length, out[0].length).setValues(out);
return out;
}
function triggerInventoryForPrompt() {
var triggers = ScriptApp.getProjectTriggers();
var lines = ['Installed triggers in this project (' + triggers.length + '):'];
if (!triggers.length) lines.push('- (none)');
triggers.forEach(function (t) {
var source = String(t.getTriggerSource());
var sourceId = '';
try { sourceId = t.getTriggerSourceId() || ''; } catch (e) {}
lines.push('- ' + t.getHandlerFunction() + ': ' + String(t.getEventType()) +
' (source: ' + source + (sourceId ? ' ' + sourceId : '') + ')');
});
lines.push('');
lines.push('Simple triggers (onOpen, onEdit, doGet, doPost) are not listed: they run by name. ' +
'Do not add a duplicate of any trigger above, and tell me before renaming a handler.');
return lines.join('\n');
}
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 unmergeAndFillDown(range) {
var r0 = range.getRow(), c0 = range.getColumn();
var values = range.getValues();
var rows = values.length, cols = values[0].length;
range.getMergedRanges().forEach(function (m) {
var top = m.getRow() - r0, left = m.getColumn() - c0;
var value = m.getCell(1, 1).getValue();
m.breakApart();
for (var i = Math.max(top, 0); i < Math.min(top + m.getNumRows(), rows); i++) {
for (var j = Math.max(left, 0); j < Math.min(left + m.getNumColumns(), cols); j++) {
values[i][j] = value;
}
}
});
range.setValues(values);
return range;
}
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 withMenuCooldown(key, seconds, fn, opts) {
opts = opts || {};
var title = opts.title || 'Menu';
var ss = SpreadsheetApp.getActiveSpreadsheet();
var lock = LockService.getDocumentLock();
if (!lock.tryLock(opts.lockMs || 5000)) {
if (ss) ss.toast('Please wait — another action is running.', title, 5);
return null;
}
try {
var props = PropertiesService.getDocumentProperties();
var propKey = 'menuCooldown:' + key;
var now = Date.now();
var last = Number(props.getProperty(propKey) || 0);
var waitMs = Math.max(0, Number(seconds) || 0) * 1000;
if (waitMs && now - last < waitMs) {
var left = Math.ceil((waitMs - (now - last)) / 1000);
if (ss) ss.toast('Wait ' + left + 's before running again.', title, 5);
return null;
}
props.setProperty(propKey, String(now));
return fn();
} finally {
lock.releaseLock();
}
}
function writeAsPlainText(range, values, opts) {
opts = opts || {};
var rows = values.length;
var cols = rows ? values[0].length : 0;
if (!rows || !cols) return range;
var target = range.offset(0, 0, rows, cols);
var textCols = opts.textColumns || null;
if (!textCols) {
target.setNumberFormat('@');
} else {
textCols.forEach(function (c) {
target.offset(0, c - 1, rows, 1).setNumberFormat('@');
});
}
var out = values.map(function (row) {
return row.map(function (v, i) {
var isText = !textCols || textCols.indexOf(i + 1) !== -1;
return isText && v !== null && v !== '' ? String(v) : v;
});
});
target.setValues(out);
return target;
}
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 writeGroupsToTabs(ss, groups, headers) {
var written = [];
Object.keys(groups).forEach(function (key) {
var name = String(key || '(blank)').replace(/[\[\]\*\?:\/\\]/g, '-').slice(0, 100);
var sheet = ss.getSheetByName(name) || ss.insertSheet(name);
sheet.clearContents();
var rows = groups[key].map(function (r) {
return Array.isArray(r) ? r : headers.map(function (h) { return r[h] === undefined ? '' : r[h]; });
});
var values = [headers].concat(rows);
sheet.getRange(1, 1, values.length, headers.length).setValues(values);
sheet.setFrozenRows(1);
written.push(name);
});
return written;
}
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 */ });