Apps Script v2 — Call Log + Provisioning

Replaces the original setupCallLog script. Adds 6 columns + a YE Provision menu that talks to the bridge.

Replace the script

In the Sheet → Extensions → Apps Script → delete the existing code → paste this whole file → Save → Run setCredentials first (puts the bridge key into Script Properties) → then Run setupCallLog.

/* =====================================================================
 * YE Call Log + Pilot Provisioning
 * ===================================================================== */

const BRIDGE_URL = 'https://bridge.yourevenings.com';
const SHEET_NAME = 'Call Log';

const COLS = {
  date: 1, time: 2, dialler: 3, business: 4, town: 5, mobile: 6,
  trade: 7, source: 8, outcome: 9, objection: 10, nextStep: 11,
  trialSetUp: 12, notes: 13,
  // provisioning columns (Network dropped — we don't need it, codes are universal):
  callbackName: 14, email: 15, urgent: 16,
  provisionId: 17, yeNumber: 18, status: 19,
};

const HEADERS = [
  'Date','Time','Dialler','Business','Town','Mobile','Trade','Source',
  'Outcome','Objection','Next step','Trial set up?','Notes',
  // provisioning:
  'Callback name','Email','Urgent keywords (extra)',
  'Provision ID','YE number','Status'
];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('🚀 YE Provision')
    .addItem('Send active row to Matt for approval', 'provisionActiveRow')
    .addItem('Refresh statuses', 'refreshAllStatuses')
    .addSeparator()
    .addItem('Setup / reformat sheet', 'setupCallLog')
    .addItem('Set bridge credentials', 'setCredentials')
    .addToUi();
}

function setCredentials() {
  const ui = SpreadsheetApp.getUi();
  const r = ui.prompt('Bridge INTERNAL_API_KEY', 'Paste the key (it will be stored in Script Properties, not in the sheet):', ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return;
  const key = r.getResponseText().trim();
  if (!key) return;
  PropertiesService.getScriptProperties().setProperty('BRIDGE_KEY', key);
  ui.alert('Saved. You can now use 🚀 YE Provision → Send active row to Matt.');
}

function bridgeKey_() {
  const k = PropertiesService.getScriptProperties().getProperty('BRIDGE_KEY');
  if (!k) throw new Error('Bridge key not set. Run 🚀 YE Provision → Set bridge credentials first.');
  return k;
}

function setupCallLog() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEET_NAME) || ss.getActiveSheet();
  sheet.setName(SHEET_NAME);

  sheet.getRange(1,1,1,HEADERS.length).setValues([HEADERS])
       .setFontWeight('bold').setBackground('#0a0a0a').setFontColor('#e8ff47');
  sheet.setFrozenRows(1);

  const widths = [85,60,90,180,110,130,110,120,90,200,200,100,280,140,220,260,120,140,110];
  widths.forEach((w,i) => sheet.setColumnWidth(i+1,w));

  // Outcome dropdown
  const outcomes = ['NA','VM','BO','CONV','INT','YES','OPTOUT'];
  sheet.getRange('I2:I1000').setDataValidation(
    SpreadsheetApp.newDataValidation().requireValueInList(outcomes,true)
      .setAllowInvalid(false)
      .setHelpText('NA=no answer · VM=voicemail · BO=brush-off · CONV=conversation · INT=interested · YES=trial agreed · OPTOUT=do not redial')
      .build());

  // Trial set up? dropdown
  sheet.getRange('L2:L1000').setDataValidation(
    SpreadsheetApp.newDataValidation().requireValueInList(['YES','NO'],true)
      .setAllowInvalid(false).build());

  // Status (col S = 19) — managed by script; lock with a note
  sheet.getRange('S1').setNote('Managed automatically: queued → pending → live (or failed). Do not edit.');

  // Conditional formatting
  const mkRule = (formula,bg) => SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied(formula).setBackground(bg)
    .setRanges([sheet.getRange('A2:S1000')]).build();
  sheet.setConditionalFormatRules([
    mkRule('=$I2="YES"','#d9f7d4'),
    mkRule('=$I2="OPTOUT"','#ffd9d9'),
    mkRule('=$I2="INT"','#fff4cc'),
    mkRule('=$S2="live"','#bbf7d0'),
    mkRule('=$S2="failed"','#fecaca'),
    mkRule('=$S2="pending"','#fde68a'),
  ]);

  let summary = ss.getSheetByName('Summary') || ss.insertSheet('Summary');
  summary.clear();
  summary.getRange('A1').setValue('Call log — daily summary').setFontWeight('bold').setFontSize(14);
  const rows = [
    ['Total dials','=COUNTA(\'Call Log\'!A2:A)'],
    ['Conversations (CONV+INT+YES)','=COUNTIF(\'Call Log\'!I:I,"CONV")+COUNTIF(\'Call Log\'!I:I,"INT")+COUNTIF(\'Call Log\'!I:I,"YES")'],
    ['Trials set up (YES)','=COUNTIF(\'Call Log\'!I:I,"YES")'],
    ['Pilots LIVE','=COUNTIF(\'Call Log\'!S:S,"live")'],
    ['Pilots pending approval','=COUNTIF(\'Call Log\'!S:S,"pending")'],
    ['Opt-outs','=COUNTIF(\'Call Log\'!I:I,"OPTOUT")'],
    ['',''],
    ['By outcome',''],
    ['NA','=COUNTIF(\'Call Log\'!I:I,"NA")'],
    ['VM','=COUNTIF(\'Call Log\'!I:I,"VM")'],
    ['BO','=COUNTIF(\'Call Log\'!I:I,"BO")'],
    ['CONV','=COUNTIF(\'Call Log\'!I:I,"CONV")'],
    ['INT','=COUNTIF(\'Call Log\'!I:I,"INT")'],
    ['YES','=COUNTIF(\'Call Log\'!I:I,"YES")'],
    ['OPTOUT','=COUNTIF(\'Call Log\'!I:I,"OPTOUT")'],
  ];
  summary.getRange(3,1,rows.length,2).setValues(rows);
  summary.setColumnWidth(1, 260);
  summary.setColumnWidth(2, 100);

  SpreadsheetApp.getUi().alert('Sheet ready. Use 🚀 YE Provision → Send active row to Matt after Katie marks Outcome = YES and fills callback/email/network.');
}

function provisionActiveRow() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const row = sheet.getActiveRange().getRow();
  if (row < 2) { ui.alert('Click a data row first (not the header).'); return; }
  const v = sheet.getRange(row, 1, 1, HEADERS.length).getValues()[0];

  const payload = {
    businessName: v[COLS.business - 1],
    callbackName: v[COLS.callbackName - 1],
    mobile: String(v[COLS.mobile - 1] || ''),
    email: v[COLS.email - 1],
    trade: v[COLS.trade - 1],
    town: v[COLS.town - 1],
    urgentKeywords: v[COLS.urgent - 1],
    sheetUrl: SpreadsheetApp.getActiveSpreadsheet().getUrl(),
    sheetRow: row,
  };

  const missing = ['businessName','callbackName','mobile','email'].filter(k => !payload[k]);
  if (missing.length) { ui.alert('Missing: ' + missing.join(', ') + '. Fill those cells and try again.'); return; }

  const res = UrlFetchApp.fetch(BRIDGE_URL + '/api/provision-pilot/queue', {
    method: 'post', contentType: 'application/json',
    headers: { 'x-api-key': bridgeKey_() },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true,
  });
  const code = res.getResponseCode();
  const body = JSON.parse(res.getContentText() || '{}');
  if (code !== 200 || !body.ok) {
    ui.alert('Failed (' + code + '): ' + (body.error || res.getContentText()));
    sheet.getRange(row, COLS.status).setValue('queue-failed').setNote(body.error || res.getContentText());
    return;
  }
  sheet.getRange(row, COLS.provisionId).setValue(body.id);
  sheet.getRange(row, COLS.status).setValue('pending');
  ui.alert('✅ Sent to Matt.\nProvision ID: ' + body.id + '\n\nMatt gets an email with Approve / Reject. When he approves, the YE number lands in column S and Status flips to "live". Use 🚀 YE Provision → Refresh statuses to pull updates.');
}

function refreshAllStatuses() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const last = sheet.getLastRow();
  if (last < 2) return;
  const range = sheet.getRange(2, COLS.provisionId, last - 1, COLS.status - COLS.provisionId + 1);
  const vals = range.getValues();
  let touched = 0;
  for (let i = 0; i < vals.length; i++) {
    const [pid, currentYe, currentStatus] = vals[i];
    if (!pid) continue;
    if (currentStatus === 'live' || currentStatus === 'rejected' || currentStatus === 'failed') continue;
    try {
      const res = UrlFetchApp.fetch(BRIDGE_URL + '/api/provision-pilot/status/' + encodeURIComponent(pid) + '?key=' + encodeURIComponent(bridgeKey_()), { muteHttpExceptions: true });
      const body = JSON.parse(res.getContentText() || '{}');
      if (!body.ok || !body.record) continue;
      const r = body.record;
      vals[i][1] = r.twilioNumber || currentYe || '';
      vals[i][2] = r.status || currentStatus;
      if (r.error) {
        sheet.getRange(2 + i, COLS.status).setNote(r.error);
      }
      touched++;
    } catch (e) { /* skip row */ }
  }
  range.setValues(vals);
  SpreadsheetApp.getUi().alert('Refreshed ' + touched + ' row(s).');
}

How Katie uses it

1. Cold call lands a YES. Katie fills in the row: Outcome=YES, Trial set up?=YES, plus the new columns Callback name / Email / Urgent keywords. (No need to ask their mobile carrier — the forwarding codes work on every UK network.)
2. Katie clicks the row → 🚀 YE Provision → Send active row to Matt for approval.
3. Matt gets an email instantly → taps ✅ Approve on his phone → ~30s later the bridge has:
4. Bought a UK Twilio number
5. Cloned Polly with this trade's tokens
6. Bound the number to the clone
7. Texted the trade their 3 forwarding codes
8. Katie clicks 🚀 YE Provision → Refresh statuses → column YE number + Status fill in.

What Matt sees in his email

Approve / Reject one-click buttons. On approve, he sees a confirmation page "✅ Pilot live — Bob's Plumbing is live on +44 7…. SMS sent to +44 7…"

Failure handling

• If the bridge can't buy a number / Vapi rejects the clone / SMS fails → status flips to failed, the error is in a cell note. Provision is idempotent — Matt clicks the approve link again and the bridge picks up where it left off.