Building Custom Google Sheets Functions: A Crunchbase API Integration Postmortem

Hook

The most elegant API integration code can become completely useless overnight—and this Crunchbase-to-Sheets connector is a perfect case study in why API versioning matters more than clean code.

Context

Before SaaS integration platforms and low-code tools dominated the landscape, developers building data workflows had to get creative. Google Apps Script emerged as a powerful bridge between spreadsheets and external APIs, letting analysts and non-technical users access complex data sources through familiar spreadsheet formulas.

The christinac/crunchbase-appsscript repository represents this era of integration development. It wraps the Crunchbase API—a database of startup and investment information—into custom Google Sheets functions. Instead of wrestling with OAuth flows and JSON parsing, users could simply type =cbsearch("airbnb", "total_money_raised") into a cell and get live data. The pattern was elegant: leverage Google's execution environment to handle authentication and CORS, expose domain-specific functions that hide API complexity, and let spreadsheets become living dashboards. But there's a critical problem that makes this repository a cautionary tale rather than a ready-to-use solution.

Technical Insight

The architecture demonstrates the cleanest pattern for wrapping REST APIs in Apps Script. Each custom function constructs an API URL, fetches data via UrlFetchApp, parses the JSON response, and extracts specific fields. Here's the core implementation:

function cbsearch(name, field) {
  var url = 'http://api.crunchbase.com/v1/search.js?query=' + name;
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  
  if (json.results && json.results.length > 0) {
    var permalink = json.results[0].permalink;
    var detailUrl = 'http://api.crunchbase.com/v1/company/' + permalink + '.js';
    var detailResponse = UrlFetchApp.fetch(detailUrl);
    var detailJson = JSON.parse(detailResponse.getContentText());
    return detailJson[field];
  }
  return "Not found";
}

This two-step approach—search for the company, then fetch detailed data—mirrors how you'd manually navigate the API. The function makes spreadsheet formulas work like first-class API clients. When a user types =cbsearch("stripe", "founded_year") into cell A1, Apps Script executes server-side, bypassing browser CORS restrictions that would kill a client-side fetch.

The repository also includes specialized functions for nested data structures. The cbcity function extracts city names from location objects, while cbmilestones handles arrays of milestone events. This domain-specific approach is smarter than trying to build a generic JSON parser—different Crunchbase endpoints return wildly different schemas, and handling them individually produces more reliable spreadsheet formulas.

The UrlFetchApp API is the secret weapon here. Unlike browser fetch() calls that trigger CORS preflight requests, UrlFetchApp executes on Google's servers with a different IP address and user agent. External APIs see requests coming from Google's infrastructure, not end users. This means you can access APIs that don't support CORS or require server-side API keys without exposing credentials to browsers.

The pattern extends beyond Crunchbase. Want to pull GitHub star counts into a spreadsheet? Fetch cryptocurrency prices? Query internal microservices? The same architecture works:

function githubStars(owner, repo) {
  var url = 'https://api.github.com/repos/' + owner + '/' + repo;
  var options = {
    'headers': {
      'User-Agent': 'GoogleAppsScript',
      'Authorization': 'token YOUR_GITHUB_TOKEN'
    }
  };
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  return data.stargazers_count;
}

The options parameter lets you inject headers for authentication. Apps Script's Properties Service can store API keys securely outside your code, preventing accidental commits to version control.

But here's where this repository fails catastrophically: Crunchbase shut down API v1 years ago. Every URL in this code returns 404 errors. The v3.1 API (and newer v4) requires OAuth authentication, uses different endpoints, implements rate limiting, and restructures response schemas. The beautiful simplicity of these functions is now completely non-functional.

Gotcha

The fundamental limitation isn't just the deprecated API—it's the absence of error handling and resilience patterns. When an API call fails, the function returns undefined or throws an exception that breaks the entire spreadsheet calculation chain. Cells display #ERROR! instead of graceful fallbacks. There's no retry logic for rate limits, no caching to reduce redundant requests, and no validation that required fields exist in responses before accessing them.

Modernizing this code requires significant surgery. Crunchbase API v3.1+ demands user_key authentication in query parameters or headers, implements strict rate limiting (200 requests per minute on paid plans), and returns paginated results wrapped in different JSON structures. The search endpoint now returns entity UUIDs instead of permalinks, and detail endpoints use those UUIDs in path parameters. You'd need to rewrite every function, add credential management, implement exponential backoff for rate limits, and handle pagination. The original code's simplicity—its main selling point—evaporates when you layer on production-grade error handling.

Google Sheets also has computational limits that bite hard with API-heavy workbooks. Apps Script enforces 6-minute execution timeouts and concurrent request limits. If you have 100 cells each calling cbsearch(), you're making 200 API requests (search + detail for each). Hit recalculate and you'll slam into rate limits or timeouts. The lack of caching means every spreadsheet recalculation hammers the API fresh, burning through quota and slowing down the entire workbook.

Verdict

Use if: You need to understand the fundamental pattern for wrapping REST APIs in Google Sheets custom functions, you're building a proof-of-concept integration and want a clear reference implementation to adapt, or you're maintaining legacy Apps Script code and need to see how others structured API wrappers before modern tooling existed. This repository serves as an excellent educational resource for the core technique, even though the specific implementation is defunct.

Skip if: You need working Crunchbase data in spreadsheets right now (use the official Excel add-in or a paid connector like Coupler.io instead), you're building production integrations that require reliability and error handling (start fresh with current API docs and proper architecture), or you're unfamiliar with Apps Script and API deprecation cycles (you'll waste time debugging non-functional code). The gap between this code and a modern, working solution is large enough that you're better off treating current API documentation as your starting point rather than trying to resurrect this legacy implementation.