feat: @happyvertical/sheets - Unified spreadsheet API package

[willgriffin]

Summary

Create a new @happyvertical/sheets package providing a unified API for reading/writing spreadsheets across multiple providers using the factory pattern with adapters.

Motivation

Magnateos needs to sync data with external spreadsheets (Google Sheets, Smartsheet, Excel Online) for:

• Importing historical transaction data from user spreadsheets
• Exporting reports to accountant-friendly formats
• Bidirectional sync where teams edit in spreadsheets

This should be a standalone SDK package (like @happyvertical/files) that can be used by any project, not just Magnateos.

Proposed API

Factory Pattern

import { SheetsClient } from '@happyvertical/sheets';

// Google Sheets
const googleSheets = SheetsClient.create({
  provider: 'google-sheets',
  credentials: { type: 'service-account', keyFile: './service-account.json' }
});

// Smartsheet
const smartsheet = SheetsClient.create({
  provider: 'smartsheet',
  apiKey: process.env.SMARTSHEET_API_KEY
});

// Excel Online (Microsoft 365)
const excel = SheetsClient.create({
  provider: 'excel-online',
  oauth: { clientId: '...', clientSecret: '...', refreshToken: '...' }
});

// Local Excel/CSV files
const localExcel = SheetsClient.create({
  provider: 'local',
  filePath: './data.xlsx'
});

Core Interface

interface SheetsClient {
  // Discovery
  listSpreadsheets(): Promise<Spreadsheet[]>;
  getSpreadsheet(id: string): Promise<Spreadsheet>;
  listSheets(spreadsheetId: string): Promise<Sheet[]>;

  // Schema
  getColumns(spreadsheetId: string, sheetId: string): Promise<Column[]>;

  // Read operations
  getRange(spreadsheetId: string, sheetId: string, range: string): Promise<CellValue[][]>;
  getRows(spreadsheetId: string, sheetId: string, options?: GetRowsOptions): Promise<Row[]>;
  getRow(spreadsheetId: string, sheetId: string, rowId: string): Promise<Row>;

  // Write operations
  setRange(spreadsheetId: string, sheetId: string, range: string, values: CellValue[][]): Promise<void>;
  appendRows(spreadsheetId: string, sheetId: string, rows: RowData[]): Promise<Row[]>;
  updateRow(spreadsheetId: string, sheetId: string, rowId: string, data: RowData): Promise<Row>;
  deleteRow(spreadsheetId: string, sheetId: string, rowId: string): Promise<void>;

  // Batch operations
  batchUpdate(spreadsheetId: string, operations: BatchOperation[]): Promise<BatchResult>;

  // Watch (for providers that support webhooks/push)
  watch?(spreadsheetId: string, callback: ChangeCallback): Unsubscribe;

  // Provider capabilities
  getProviderCapabilities(): ProviderCapabilities;
}

interface ProviderCapabilities {
  supportsWatch: boolean;
  supportsFormulas: boolean;
  supportsBatchOperations: boolean;
  maxRowsPerRequest: number;
  supportsRowIds: boolean;
}

type CellValue = string | number | boolean | Date | null;

interface Row {
  id: string;
  rowNumber: number;
  cells: Record<string, CellValue>;
  modifiedAt?: Date;
}

interface Column {
  id: string;
  name: string;
  letter?: string;
  type: 'text' | 'number' | 'date' | 'boolean' | 'formula' | 'unknown';
}

Adapters to Implement

Provider	API	Priority	Notes
google-sheets	Google Sheets API v4	High	Watch via webhooks, formulas
smartsheet	Smartsheet API	Medium	Watch via webhooks, row IDs
excel-online	Microsoft Graph API	Medium	Watch via subscriptions
local-excel	xlsx/exceljs package	High	Read/write .xlsx files
csv	Built-in	High	Simple CSV read/write
airtable	Airtable API	Low	Strong typing, row IDs

Package Structure

packages/sheets/
├── src/
│   ├── index.ts
│   ├── client.ts              # SheetsClient factory
│   ├── types.ts               # Shared types
│   ├── adapters/
│   │   ├── google-sheets.ts
│   │   ├── smartsheet.ts
│   │   ├── excel-online.ts
│   │   ├── local-excel.ts
│   │   ├── csv.ts
│   │   └── airtable.ts
│   └── utils/
│       ├── range-parser.ts    # A1 notation parsing
│       └── type-detection.ts  # Cell type inference
├── package.json
├── tsconfig.json
└── README.md

Dependencies

• googleapis - Google Sheets API client
• smartsheet - Smartsheet SDK
• @microsoft/microsoft-graph-client - Excel Online
• xlsx or exceljs - Local Excel files
• csv-parse / csv-stringify - CSV handling

Related

• Downstream: smrt-sheets package in SMRT repo
• Consumer: Magnateos.com spreadsheet integration feature
• See Magnateos PRD for full requirements