Crm Setup

---
name: crm-setup
description: >
  One-time CRM initialization. Reads agency.config.json, creates Google Sheet
  tabs with correct headers, and deploys the webhook Apps Script. Run this
  once after agency-setup to bootstrap your CRM.
tags: [onboarding, crm, google-sheets, 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`):

```json
["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`):

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

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

```json
["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`):

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

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

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

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

```json
["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`):

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

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

```json
["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:

```bash
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

```javascript
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`:

```bash
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]
```