after/Code.gs
/* SYNTHETIC PORTFOLIO DEMO. Owned columns A:F only. No external services. */
var CRM_DEFAULTS = {
inboxSheet: 'Inbox', leadsSheet: 'Leads', sourceTimezone: 'Asia/Singapore',
batchSize: 50, maxRowsPerRun: 200, maxReadRows: 5000,
maxLeads: 5000, maxRuntimeMs: 240000, dryRun: true
};
var CRM_INBOX = ['submission_id', 'email', 'name', 'submitted_at', 'source'];
var CRM_LEADS = ['lead_id', 'email', 'name', 'submitted_at_utc', 'source', 'submission_id'];
function crmLog(event, fields) {
// Only codes, counts and source row numbers. No contact values in logs.
console.log(JSON.stringify(Object.assign({project: 'synthetic-crm', event: event}, fields || {})));
}
function crmEmail(value) { return String(value || '').trim().toLowerCase(); }
function crmText(value) {
var text = String(value == null ? '' : value).trim();
return /^[=+\-@\t\r]/.test(text) ? "'" + text : text;
}
function crmDate(value, timezone) {
if (value instanceof Date && !isNaN(value.getTime())) {
return Utilities.formatDate(value, 'UTC', "yyyy-MM-dd'T'HH:mm:ss'Z'");
}
var text = String(value).trim();
var parts = /^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2}):(\d{2})(Z|[+-]\d{2}:\d{2})?$/.exec(text);
if (!parts) throw new Error('DATE_FORMAT');
var year = +parts[1], month = +parts[2], day = +parts[3];
if (year < 2000 || month < 1 || month > 12 || day < 1 ||
day > new Date(Date.UTC(year, month, 0)).getUTCDate() ||
+parts[4] > 23 || +parts[5] > 59 || +parts[6] > 59) throw new Error('DATE_INVALID');
var date;
if (parts[7]) {
if (parts[7] !== 'Z' && (+parts[7].slice(1, 3) > 23 || +parts[7].slice(4, 6) > 59)) throw new Error('DATE_OFFSET');
date = new Date(text.replace(' ', 'T'));
} else {
// Explicit source timezone; never use the host locale or script default.
date = Utilities.parseDate(text.replace('T', ' '), timezone, 'yyyy-MM-dd HH:mm:ss');
if (Utilities.formatDate(date, timezone, 'yyyy-MM-dd HH:mm:ss') !== text.replace('T', ' ')) throw new Error('DATE_INVALID');
}
if (isNaN(date.getTime())) throw new Error('DATE_INVALID');
return Utilities.formatDate(date, 'UTC', "yyyy-MM-dd'T'HH:mm:ss'Z'");
}
function crmHeader(sheet, expected) {
var header = sheet.getRange(1, 1, 1, expected.length).getValues()[0];
if (JSON.stringify(header) !== JSON.stringify(expected)) throw new Error('SCHEMA_MISMATCH:' + sheet.getName());
}
function repairCrm(options) {
var cfg = Object.assign({}, CRM_DEFAULTS, options || {});
['batchSize', 'maxRowsPerRun', 'maxReadRows', 'maxLeads', 'maxRuntimeMs'].forEach(function(key) {
if (!Number.isInteger(cfg[key]) || cfg[key] < 1) throw new Error('CONFIG_INVALID:' + key);
});
if (typeof cfg.dryRun !== 'boolean') throw new Error('CONFIG_INVALID:dryRun');
var lock = LockService.getScriptLock();
if (!lock.tryLock(1000)) { crmLog('LOCK_BUSY'); return {status: 'locked'}; }
var summary = {status: 'complete', dryRun: cfg.dryRun, scanned: 0, inserted: 0, duplicates: 0, rejected: 0, batches: 0};
var started = Date.now();
try {
var book = SpreadsheetApp.getActiveSpreadsheet();
var inbox = book.getSheetByName(cfg.inboxSheet), leads = book.getSheetByName(cfg.leadsSheet);
if (!inbox || !leads) throw new Error('SHEET_MISSING');
crmHeader(inbox, CRM_INBOX); crmHeader(leads, CRM_LEADS);
var total = Math.max(0, inbox.getLastRow() - 1), leadCount = Math.max(0, leads.getLastRow() - 1);
// Bounded reads. Large migrations need a separate scoped design.
if (total > cfg.maxReadRows || leadCount > cfg.maxLeads) throw new Error('SIZE_LIMIT');
var existing = leadCount ? leads.getRange(2, 1, leadCount, CRM_LEADS.length).getValues() : [];
var emails = Object.create(null), submissions = Object.create(null);
existing.forEach(function(row) {
var email = crmEmail(row[1]), sid = String(row[5]);
if (!email || !sid || emails[email] || submissions[sid]) throw new Error('EXISTING_DUPLICATE_OR_INVALID');
emails[email] = true; submissions[sid] = email;
});
var props = PropertiesService.getScriptProperties();
var checkpointKey = 'synthetic_crm_cursor_v1';
var cursor = Number(props.getProperty(checkpointKey) || 0);
if (!Number.isInteger(cursor) || cursor < 0 || cursor > total) throw new Error('CURSOR_INVALID');
while (cursor < total && summary.scanned < cfg.maxRowsPerRun) {
if (Date.now() - started >= cfg.maxRuntimeMs) { summary.status = 'deferred'; break; }
var size = Math.min(cfg.batchSize, total - cursor, cfg.maxRowsPerRun - summary.scanned);
var rows = inbox.getRange(cursor + 2, 1, size, CRM_INBOX.length).getValues();
var pending = [];
rows.forEach(function(row, index) {
var rowNumber = cursor + index + 2;
var sid = String(row[0] || '').trim(), email = crmEmail(row[1]);
try {
if (!/^[A-Za-z0-9_-]{1,80}$/.test(sid) || !/^[A-Za-z0-9_][^\s@]*@[^\s@]+\.[^\s@]+$/.test(email) || !String(row[2]).trim()) throw new Error('REQUIRED_FIELD');
if (submissions[sid] && submissions[sid] !== email) throw new Error('SUBMISSION_CONFLICT');
var date = crmDate(row[3], cfg.sourceTimezone);
if (emails[email] || submissions[sid]) { summary.duplicates++; return; }
if (leadCount + pending.length >= cfg.maxLeads) throw new Error('CAPACITY_LIMIT');
pending.push(['lead_' + sid, crmText(email), crmText(row[2]), date, crmText(row[4]), sid]);
emails[email] = true; submissions[sid] = email;
} catch (error) {
// Row-level validation only. Service errors are never swallowed here.
summary.rejected++; crmLog('ROW_REJECTED', {row: rowNumber, code: String(error.message).split(':')[0]});
}
});
if (pending.length && !cfg.dryRun) {
// A:F is owned by this integration. Never clear, sort, or rewrite G onward.
leads.getRange(leads.getLastRow() + 1, 1, pending.length, CRM_LEADS.length).setValues(pending);
SpreadsheetApp.flush();
}
// Commit cursor only after the batch write. If this fails, replay scans Leads.
var next = cursor + size;
if (!cfg.dryRun) props.setProperty(checkpointKey, String(next));
summary.scanned += size; summary.inserted += pending.length; summary.batches++;
leadCount += pending.length; cursor = next;
crmLog(cfg.dryRun ? 'BATCH_PLANNED' : 'BATCH_COMMITTED', {throughRow: cursor + 1, inserted: pending.length});
}
if (cursor < total) summary.status = 'deferred';
summary.nextCursor = cursor;
crmLog('RUN_FINISHED', summary);
return summary;
} catch (error) {
crmLog('RUN_FAILED', {code: String(error.message).split(':')[0]});
throw error;
} finally { lock.releaseLock(); }
}
function runCrmDryRun() { return repairCrm({dryRun: true}); }
function runCrmRepair() { return repairCrm({dryRun: false}); }