ZZZ Digital Lab

Back to Google Sheets CRM repair

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}); }