A Google Sheets Apps Script to connect to the Bridge API (brdg.app)

extension.js

/**
 * Bridge.app Investor List Enrichment Script
 *
 * This Google Apps Script enriches your fundraising CRM spreadsheet
 * with intro path data from Bridge (brdg.app).
 *
 * SETUP:
 * 1. Get your Bridge API key from https://brdg.app/account > API > Copy API Key
 * 2. In your Google Sheet, go to Extensions > Apps Script
 * 3. Paste this entire script
 * 4. Update the CONFIG section below with your API key
 * 5. Run enrichInvestorList() or use the custom menu
 *
 * SPREADSHEET REQUIREMENTS:
 * - Column A: Investor Name
 * - Column B: Fund
 * - Column G: Intro Path (this gets updated)
 * - Header row in row 1
 */

// ============ CONFIG ============
const CONFIG = {
  BRIDGE_API_KEY: 'YOUR_API_KEY_HERE', // Get from https://brdg.app/account
  BRIDGE_API_BASE: 'https://api.brdg.app',

  // Column positions (1-indexed)
  COL_INVESTOR_NAME: 1,  // A
  COL_FUND: 2,           // B
  COL_INTRO_PATH: 7,     // G

  // Rate limiting (Bridge allows 1 req/sec, 20 req/min)
  DELAY_BETWEEN_REQUESTS_MS: 1500,

  // Start row (skip header)
  START_ROW: 2
};

// ============ MENU ============
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Bridge Enrichment')
    .addItem('Enrich All Investors', 'enrichInvestorList')
    .addItem('Enrich Selected Rows', 'enrichSelectedRows')
    .addItem('Enrich Empty Intro Paths Only', 'enrichEmptyOnly')
    .addSeparator()
    .addItem('Test API Connection', 'testApiConnection')
    .addToUi();
}

// ============ MAIN FUNCTIONS ============

/**
 * Enriches all investor rows with intro path data from Bridge
 */
function enrichInvestorList() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();

  if (lastRow < CONFIG.START_ROW) {
    SpreadsheetApp.getUi().alert('No data found in spreadsheet.');
    return;
  }

  const range = sheet.getRange(CONFIG.START_ROW, 1, lastRow - CONFIG.START_ROW + 1, CONFIG.COL_INTRO_PATH);
  const data = range.getValues();

  let enrichedCount = 0;
  let errorCount = 0;

  for (let i = 0; i < data.length; i++) {
    const investorName = data[i][CONFIG.COL_INVESTOR_NAME - 1];
    const fundName = data[i][CONFIG.COL_FUND - 1];

    if (!investorName && !fundName) continue;

    try {
      const introPath = getIntroPath(investorName, fundName);

      // Update the Intro Path column
      sheet.getRange(CONFIG.START_ROW + i, CONFIG.COL_INTRO_PATH).setValue(introPath);
      enrichedCount++;

      // Rate limiting
      Utilities.sleep(CONFIG.DELAY_BETWEEN_REQUESTS_MS);

    } catch (error) {
      console.error(`Error enriching row ${CONFIG.START_ROW + i}: ${error.message}`);
      errorCount++;
    }
  }

  SpreadsheetApp.getUi().alert(
    `Enrichment complete!\n\nEnriched: ${enrichedCount} rows\nErrors: ${errorCount} rows`
  );
}

/**
 * Enriches only rows with empty Intro Path column
 */
function enrichEmptyOnly() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();

  if (lastRow < CONFIG.START_ROW) {
    SpreadsheetApp.getUi().alert('No data found in spreadsheet.');
    return;
  }

  const range = sheet.getRange(CONFIG.START_ROW, 1, lastRow - CONFIG.START_ROW + 1, CONFIG.COL_INTRO_PATH);
  const data = range.getValues();

  let enrichedCount = 0;

  for (let i = 0; i < data.length; i++) {
    const investorName = data[i][CONFIG.COL_INVESTOR_NAME - 1];
    const fundName = data[i][CONFIG.COL_FUND - 1];
    const currentIntroPath = data[i][CONFIG.COL_INTRO_PATH - 1];

    // Skip if already has intro path or no investor data
    if (currentIntroPath || (!investorName && !fundName)) continue;

    try {
      const introPath = getIntroPath(investorName, fundName);
      sheet.getRange(CONFIG.START_ROW + i, CONFIG.COL_INTRO_PATH).setValue(introPath);
      enrichedCount++;

      Utilities.sleep(CONFIG.DELAY_BETWEEN_REQUESTS_MS);

    } catch (error) {
      console.error(`Error enriching row ${CONFIG.START_ROW + i}: ${error.message}`);
    }
  }

  SpreadsheetApp.getUi().alert(`Enriched ${enrichedCount} empty rows.`);
}

/**
 * Enriches only the currently selected rows
 */
function enrichSelectedRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const selection = sheet.getActiveRange();
  const startRow = selection.getRow();
  const numRows = selection.getNumRows();

  if (startRow < CONFIG.START_ROW) {
    SpreadsheetApp.getUi().alert('Please select data rows (not the header).');
    return;
  }

  let enrichedCount = 0;

  for (let i = 0; i < numRows; i++) {
    const row = startRow + i;
    const investorName = sheet.getRange(row, CONFIG.COL_INVESTOR_NAME).getValue();
    const fundName = sheet.getRange(row, CONFIG.COL_FUND).getValue();

    if (!investorName && !fundName) continue;

    try {
      const introPath = getIntroPath(investorName, fundName);
      sheet.getRange(row, CONFIG.COL_INTRO_PATH).setValue(introPath);
      enrichedCount++;

      Utilities.sleep(CONFIG.DELAY_BETWEEN_REQUESTS_MS);

    } catch (error) {
      console.error(`Error enriching row ${row}: ${error.message}`);
    }
  }

  SpreadsheetApp.getUi().alert(`Enriched ${enrichedCount} selected rows.`);
}

// ============ API FUNCTIONS ============

/**
 * Gets intro path information from Bridge API
 * @param {string} investorName - Name of the investor
 * @param {string} fundName - Name of the fund/company
 * @returns {string} Formatted intro path string
 */
function getIntroPath(investorName, fundName) {
  // Try searching by name first, then by company domain
  let results = [];

  // Search by investor name
  if (investorName) {
    const nameResults = searchIntropathByName(investorName);
    if (nameResults && nameResults.length > 0) {
      results = results.concat(nameResults);
    }
  }

  // Search by fund/company domain
  if (fundName) {
    const domain = extractDomain(fundName);
    if (domain) {
      const domainResults = searchIntropathByDomain(domain);
      if (domainResults && domainResults.length > 0) {
        results = results.concat(domainResults);
      }
    }
  }

  if (results.length === 0) {
    return 'No connections found';
  }

  // Format the results
  return formatIntroPathResults(results);
}

/**
 * Searches Bridge API for intro paths by name
 */
function searchIntropathByName(name) {
  const url = `${CONFIG.BRIDGE_API_BASE}/api/v4/search/intropath_counts?name=${encodeURIComponent(name)}`;
  return makeApiRequest(url);
}

/**
 * Searches Bridge API for intro paths by domain
 */
function searchIntropathByDomain(domain) {
  const url = `${CONFIG.BRIDGE_API_BASE}/api/v4/search/intropath_counts?domain=${encodeURIComponent(domain)}`;
  return makeApiRequest(url);
}

/**
 * Makes authenticated request to Bridge API
 */
function makeApiRequest(url) {
  const options = {
    method: 'GET',
    headers: {
      'Authorization': `Bearer ${CONFIG.BRIDGE_API_KEY}`,
      'Accept': 'application/json'
    },
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const statusCode = response.getResponseCode();

    if (statusCode !== 200) {
      console.error(`API error: ${statusCode} - ${response.getContentText()}`);
      return null;
    }

    const data = JSON.parse(response.getContentText());
    return data.people || [];

  } catch (error) {
    console.error(`Request failed: ${error.message}`);
    return null;
  }
}

/**
 * Formats intro path results into a readable string
 */
function formatIntroPathResults(results) {
  // Sort by intropath_count (closest connections first)
  results.sort((a, b) => (a.intropath_count || 99) - (b.intropath_count || 99));

  // Take top 3 connections
  const topResults = results.slice(0, 3);

  const formatted = topResults.map(person => {
    const name = person.name || 'Unknown';
    const company = person.company || '';
    const degree = person.degree_of_connection || person.intropath_count || '?';
    const introCount = person.intropath_count || 0;

    if (introCount > 0) {
      return `${name}${company ? ' (' + company + ')' : ''} - ${introCount} paths`;
    }
    return `${name}${company ? ' (' + company + ')' : ''} - ${degree}° connection`;
  });

  return formatted.join(' | ');
}

/**
 * Extracts domain from fund name (basic implementation)
 * Handles common patterns like "Sequoia Capital" -> "sequoiacap.com"
 */
function extractDomain(fundName) {
  if (!fundName) return null;

  // Common fund domain mappings (add more as needed)
  const knownMappings = {
    'sequoia capital': 'sequoiacap.com',
    'sequoia': 'sequoiacap.com',
    'andreessen horowitz': 'a16z.com',
    'a16z': 'a16z.com',
    'accel': 'accel.com',
    'frontline ventures': 'frontline.vc',
    'point nine': 'pointnine.com',
    'point nine capital': 'pointnine.com',
    'index ventures': 'indexventures.com',
    'balderton': 'balderton.com',
    'balderton capital': 'balderton.com',
    'localglobe': 'localglobe.vc',
    'cherry ventures': 'cherry.vc',
    'act venture capital': 'actventure.capital',
    'atlantic bridge': 'abven.com'
  };

  const normalized = fundName.toLowerCase().trim();

  if (knownMappings[normalized]) {
    return knownMappings[normalized];
  }

  // Try to construct domain from name (remove spaces, add .com)
  const simpleDomain = normalized.replace(/\s+/g, '') + '.com';
  return simpleDomain;
}

// ============ UTILITY FUNCTIONS ============

/**
 * Tests the API connection
 */
function testApiConnection() {
  if (CONFIG.BRIDGE_API_KEY === 'YOUR_API_KEY_HERE') {
    SpreadsheetApp.getUi().alert('Please set your Bridge API key in the CONFIG section of the script.');
    return;
  }

  const url = `${CONFIG.BRIDGE_API_BASE}/api/v4/search/intropath_counts?domain=stripe.com`;
  const options = {
    method: 'GET',
    headers: {
      'Authorization': `Bearer ${CONFIG.BRIDGE_API_KEY}`,
      'Accept': 'application/json'
    },
    muteHttpExceptions: true
  };

  try {
    const response = UrlFetchApp.fetch(url, options);
    const statusCode = response.getResponseCode();

    if (statusCode === 200) {
      SpreadsheetApp.getUi().alert('API connection successful! You\'re ready to enrich your investor list.');
    } else if (statusCode === 401) {
      SpreadsheetApp.getUi().alert('API key invalid. Please check your Bridge API key.');
    } else {
      SpreadsheetApp.getUi().alert(`API returned status ${statusCode}. Check the console for details.`);
    }

  } catch (error) {
    SpreadsheetApp.getUi().alert(`Connection failed: ${error.message}`);
  }
}