crm-setup

CRM Setup

One-time CRM initialization skill. Reads agency.config.json, creates every Google Sheet tab with the correct column headers, and provides the Google Apps Script webhook code for deployment. Run this once after /agency-setup.

Prerequisites

• agency.config.json at repo root (generated by /agency-setup)
• A Google Sheet (the crm.sheet_id in your config)
• Google account with edit access to that sheet

Phase 0: Intake

1. Read agency.config.json from the project root.
2. Extract:
   • crm.sheet_id -- the Google Sheet ID
   • crm.webhook_url -- the Google Apps Script webhook endpoint (may be empty)
   • crm.tabs -- map of logical tab names to actual sheet tab names
   • tools.crm.access -- execution method: webhook | api | browser
3. Store these values for use in subsequent phases.

Phase 1: Validate Config

Check that the config has everything needed:

1. crm.sheet_id must be a non-empty string. If missing or empty: stop and tell the user to run /agency-setup first, or manually add crm.sheet_id to agency.config.json.
2. crm.tabs must be a non-empty object with at least one tab mapping. If missing or empty: stop and tell the user to run /agency-setup first.
3. crm.webhook_url -- note whether this is set. If empty, Phase 3 will handle deployment.

Report validation status:

Config Validation:
- sheet_id: OK (1YPMlou...)
- webhook_url: OK | MISSING (will deploy in Phase 3)
- tabs: OK (8 tabs configured)

If crm.sheet_id or crm.tabs are missing, halt and direct the user to /agency-setup.

Phase 2: Create Tabs

For each logical tab in crm.tabs, create the tab with the appropriate headers. Use the webhook if crm.webhook_url is set, otherwise provide manual instructions.

Tab Headers

pipeline (resolved tab name from crm.tabs.pipeline):

["Date", "Company", "Website", "Contact", "Title", "Email", "LinkedIn", "Phone", "Platform", "Signal_Type", "Score", "Tier", "Stage", "Cadence_Day", "Last_Action", "Last_Action_Date", "Next_Action", "Next_Action_Date", "Response_Received", "Response_Summary", "Notes", "Created_At"]

hawk_leads (resolved tab name from crm.tabs.hawk_leads):

["Date", "Company", "Platform", "URL", "Budget", "Description", "Urgency", "Score", "Market", "Contact", "Status"]

researched_leads (resolved tab name from crm.tabs.researched_leads):

["Date", "Company", "Website", "Industry", "Business_Model", "Tech_Platform", "Theme", "Team_Size", "Key_Person", "Key_Person_Title", "Pain_Points", "CRO_Score", "Personalization_Hooks", "Researched_At"]

outreach_log (resolved tab name from crm.tabs.outreach_log):

["Date", "Company", "Contact", "Channel", "Message_Type", "Tier", "Subject", "Cadence_Day", "Status", "Sent_At"]

email_drafts (resolved tab name from crm.tabs.email_drafts):

["Date", "Company", "Contact", "Subject", "Body", "Framework", "Tier", "Personalization_Points", "Status", "Created_At"]

calling (resolved tab name from crm.tabs.calling):

["Date", "Company", "Contact", "Title", "Phone", "Email", "LinkedIn", "Call_Purpose", "Talking_Points", "Lead_Stage", "Call_Status", "Call_Notes"]

dashboard (resolved tab name from crm.tabs.dashboard):

["Metric", "Value", "Date", "Notes"]

inbound_leads (resolved tab name from crm.tabs.inbound_leads):

["Date", "Source", "Company", "Contact", "Email", "Description", "Status", "Score", "Notes"]

Execution: Via Webhook

If crm.webhook_url is set, create each tab using the create_tab action:

curl -s -X POST "{{crm.webhook_url}}" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "create_tab",
    "sheet": "{{resolved_tab_name}}",
    "headers": {{headers_array}}
  }'

Send one request per tab. Wait 1 second between requests to avoid Google rate limits.

After each request, check the response for errors. Log the result:

Creating tabs:
  [OK] Pipeline (22 columns)
  [OK] Hawk Leads (11 columns)
  [OK] Researched Leads (14 columns)
  [OK] Outreach CRM (10 columns)
  [OK] Email Drafts (10 columns)
  [OK] Call Today (12 columns)
  [OK] Dashboard (4 columns)
  [OK] Inbound Leads (9 columns)

If a tab already exists, the webhook will return a message indicating it exists. That is fine; log it as [EXISTS] and move on.

Execution: Without Webhook

If crm.webhook_url is not set, skip tab creation and proceed to Phase 3 to deploy the webhook first. After the webhook is deployed, return to this phase.

Phase 3: Deploy Webhook

If crm.webhook_url is not set or the user wants to redeploy, provide the Google Apps Script code.

Tell the user:

"Your CRM needs a webhook to receive data from the skills. Here is the Google Apps Script to deploy:

1. Open your Google Sheet: https://docs.google.com/spreadsheets/d/{{crm.sheet_id}}/edit
2. Go to Extensions > Apps Script
3. Delete any existing code in Code.gs
4. Paste the code below
5. Click Deploy > New deployment
6. Select type: Web app
7. Set 'Execute as': Me
8. Set 'Who has access': Anyone
9. Click Deploy and copy the URL
10. Add the URL to agency.config.json as crm.webhook_url"

Google Apps Script Code

function doPost(e) {
  try {
    var payload = JSON.parse(e.postData.contents);
    var action = payload.action || "append";
    var sheetName = payload.sheet;

    if (!sheetName) {
      return jsonResponse({ error: "Missing 'sheet' parameter" });
    }

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    switch (action) {
      case "append":
        return handleAppend(ss, sheetName, payload);
      case "read":
        return handleRead(ss, sheetName, payload);
      case "update":
        return handleUpdate(ss, sheetName, payload);
      case "create_tab":
        return handleCreateTab(ss, sheetName, payload);
      default:
        return jsonResponse({ error: "Unknown action: " + action });
    }
  } catch (err) {
    return jsonResponse({ error: err.toString() });
  }
}

function handleAppend(ss, sheetName, payload) {
  var sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    // Auto-create the tab if it does not exist
    sheet = ss.insertSheet(sheetName);
    if (payload.headers && payload.headers.length > 0) {
      sheet.getRange(1, 1, 1, payload.headers.length).setValues([payload.headers]);
      sheet.getRange(1, 1, 1, payload.headers.length).setFontWeight("bold");
    }
  }

  var headers = payload.headers;
  var row = payload.row;

  if (!headers || !row) {
    return jsonResponse({ error: "Missing 'headers' or 'row' for append" });
  }

  // Get existing headers from the sheet
  var existingHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  // Build the row in the correct column order
  var orderedRow = [];
  for (var i = 0; i < existingHeaders.length; i++) {
    var colIndex = headers.indexOf(existingHeaders[i]);
    if (colIndex !== -1) {
      orderedRow.push(row[colIndex]);
    } else {
      orderedRow.push("");
    }
  }

  // Append any new columns that do not exist yet
  for (var j = 0; j < headers.length; j++) {
    if (existingHeaders.indexOf(headers[j]) === -1) {
      existingHeaders.push(headers[j]);
      orderedRow.push(row[j]);
      sheet.getRange(1, existingHeaders.length).setValue(headers[j]).setFontWeight("bold");
    }
  }

  var nextRow = sheet.getLastRow() + 1;
  sheet.getRange(nextRow, 1, 1, orderedRow.length).setValues([orderedRow]);

  return jsonResponse({
    status: "ok",
    action: "append",
    sheet: sheetName,
    row_number: nextRow
  });
}

function handleRead(ss, sheetName, payload) {
  var sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    return jsonResponse({ error: "Sheet not found: " + sheetName });
  }

  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  if (lastRow < 1 || lastCol < 1) {
    return jsonResponse({ status: "ok", action: "read", sheet: sheetName, headers: [], rows: [] });
  }

  var data = sheet.getRange(1, 1, lastRow, lastCol).getValues();
  var headers = data[0];
  var rows = [];

  var filters = payload.filters || null;

  for (var i = 1; i < data.length; i++) {
    var rowObj = {};
    var include = true;

    for (var j = 0; j < headers.length; j++) {
      rowObj[headers[j]] = data[i][j];
    }

    // Apply filters if provided
    if (filters && filters.column && filters.value) {
      var filterCol = filters.column;
      var filterVal = String(filters.value).toLowerCase();
      var cellVal = String(rowObj[filterCol] || "").toLowerCase();

      if (cellVal.indexOf(filterVal) === -1) {
        include = false;
      }
    }

    if (include) {
      rows.push(rowObj);
    }
  }

  // Apply limit if provided
  var limit = payload.limit || 0;
  if (limit > 0 && rows.length > limit) {
    rows = rows.slice(0, limit);
  }

  return jsonResponse({
    status: "ok",
    action: "read",
    sheet: sheetName,
    headers: headers,
    total_rows: rows.length,
    rows: rows
  });
}

function handleUpdate(ss, sheetName, payload) {
  var sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    return jsonResponse({ error: "Sheet not found: " + sheetName });
  }

  var matchColumn = payload.match_column;
  var matchValue = payload.match_value;
  var updates = payload.updates;

  if (!matchColumn || !matchValue || !updates) {
    return jsonResponse({ error: "Missing 'match_column', 'match_value', or 'updates'" });
  }

  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  var data = sheet.getRange(1, 1, lastRow, lastCol).getValues();
  var headers = data[0];

  var matchColIndex = headers.indexOf(matchColumn);
  if (matchColIndex === -1) {
    return jsonResponse({ error: "Column not found: " + matchColumn });
  }

  var updatedCount = 0;

  for (var i = 1; i < data.length; i++) {
    if (String(data[i][matchColIndex]).toLowerCase() === String(matchValue).toLowerCase()) {
      for (var key in updates) {
        var colIndex = headers.indexOf(key);
        if (colIndex !== -1) {
          sheet.getRange(i + 1, colIndex + 1).setValue(updates[key]);
        }
      }
      updatedCount++;
    }
  }

  return jsonResponse({
    status: "ok",
    action: "update",
    sheet: sheetName,
    match_column: matchColumn,
    match_value: matchValue,
    rows_updated: updatedCount
  });
}

function handleCreateTab(ss, sheetName, payload) {
  var existing = ss.getSheetByName(sheetName);

  if (existing) {
    return jsonResponse({
      status: "ok",
      action: "create_tab",
      sheet: sheetName,
      message: "Tab already exists"
    });
  }

  var sheet = ss.insertSheet(sheetName);
  var headers = payload.headers || [];

  if (headers.length > 0) {
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.getRange(1, 1, 1, headers.length).setFontWeight("bold");
    sheet.setFrozenRows(1);
  }

  return jsonResponse({
    status: "ok",
    action: "create_tab",
    sheet: sheetName,
    columns: headers.length,
    message: "Tab created"
  });
}

function jsonResponse(data) {
  return ContentService
    .createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

function doGet(e) {
  return jsonResponse({
    status: "ok",
    message: "B2B GTM CRM Webhook is running. Use POST to interact."
  });
}

After the user deploys and provides the URL:

1. Update agency.config.json by setting crm.webhook_url to the new URL.
2. Return to Phase 2 to create all tabs.

Phase 4: Verify

Test each tab with a READ operation to confirm it exists and has the correct headers.

For each tab in crm.tabs:

curl -s -X POST "{{crm.webhook_url}}" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "read",
    "sheet": "{{resolved_tab_name}}",
    "limit": 1
  }'

Check the response:

• If status: "ok" and headers array matches the expected columns: mark as VERIFIED.
• If error: "Sheet not found": mark as MISSING and attempt to re-create.
• If headers do not match: mark as HEADERS_MISMATCH and report which columns differ.

Report:

CRM Verification:
  [VERIFIED] Pipeline -- 22 columns
  [VERIFIED] Hawk Leads -- 11 columns
  [VERIFIED] Researched Leads -- 14 columns
  [VERIFIED] Outreach CRM -- 10 columns
  [VERIFIED] Email Drafts -- 10 columns
  [VERIFIED] Call Today -- 12 columns
  [VERIFIED] Dashboard -- 4 columns
  [VERIFIED] Inbound Leads -- 9 columns

Phase 5: Output

Provide a summary:

CRM Setup Complete
------------------
Sheet: https://docs.google.com/spreadsheets/d/{{crm.sheet_id}}/edit
Webhook: {{crm.webhook_url}}
Tabs created: 8/8
All verified: Yes/No

Tab Summary:
  Pipeline .......... 22 columns (lead tracking, cadence, stages)
  Hawk Leads ........ 11 columns (hot signal leads)
  Researched Leads .. 14 columns (company research data)
  Outreach CRM ...... 10 columns (outreach activity log)
  Email Drafts ...... 10 columns (draft emails for review)
  Call Today ........ 12 columns (daily call sheet)
  Dashboard ......... 4 columns  (KPI metrics)
  Inbound Leads ..... 9 columns  (inbound lead capture)

Next steps:
  1. Run /signal-scanner to find your first leads
  2. Run /apollo-lead-finder to prospect on Apollo.io
  3. Run /lead-enrichment-pipeline to research and enrich leads
  4. Run /outreach-draft-pipeline to generate personalized outreach

Example Usage

Trigger phrases:

• "Set up my CRM"
• "Initialize the CRM tabs"
• "Create CRM tabs"
• "Deploy CRM webhook"
• "Run CRM setup"

User: Set up my CRM
Assistant: [reads agency.config.json, validates config, creates 8 tabs via webhook, verifies each tab, reports summary]

User: I need to deploy the webhook
Assistant: [provides the Apps Script code, walks through deployment steps, updates config with URL]