@kylebrodeur/sheets-loader

Lightweight TypeScript library for loading and caching data from Google Sheets. Supports both service account (server-to-server) and OAuth2 (user credentials) authentication.

Installation

npm install @kylebrodeur/sheets-loader
# or
pnpm add @kylebrodeur/sheets-loader

Quick Start

import { SheetsLoader } from '@kylebrodeur/sheets-loader';

// Service account (server-to-server)
const loader = new SheetsLoader({
  auth: {
    credentials: {
      client_email: process.env.SA_EMAIL,
      private_key: process.env.SA_KEY,
    },
  },
});

const rows = await loader.load('YOUR_SPREADSHEET_ID', 'Sheet1!A1:B10');
console.log(rows); // string[][]

Authentication

Service Account

Best for server-side, automated workflows. Create a service account in the Google Cloud Console, share the sheet with the service account email, and pass the credentials:

const loader = new SheetsLoader({
  auth: {
    credentials: {
      client_email: process.env.SA_EMAIL,
      private_key: process.env.SA_KEY,
    },
  },
});

You can also pass a path to a credentials JSON file:

const loader = new SheetsLoader({
  auth: { credentials: './service-account.json' },
});

OAuth2 (User Credentials)

For apps where users authorize access to their own sheets:

import { OAuth2Client } from 'google-auth-library';
import { SheetsLoader } from '@kylebrodeur/sheets-loader';

const oAuth2Client = new OAuth2Client(
  process.env.CLIENT_ID,
  process.env.CLIENT_SECRET,
  process.env.REDIRECT_URI,
);
oAuth2Client.setCredentials({ refresh_token: process.env.REFRESH_TOKEN });

const loader = new SheetsLoader({ authClient: oAuth2Client });
const rows = await loader.load('SPREADSHEET_ID', 'Sheet1!A1:C100');

Full Authorization Code Flow

// 1. Generate a consent URL for the user
const authUrl = oAuth2Client.generateAuthUrl({
  access_type: 'offline',
  scope: ['https://www.googleapis.com/auth/spreadsheets.readonly'],
});
console.log('Authorize this app:', authUrl);

// 2. Exchange the code the user pastes back
const { tokens } = await oAuth2Client.getToken(code);
oAuth2Client.setCredentials(tokens);
// Persist tokens securely — never commit them to source control

// 3. Use the client
const loader = new SheetsLoader({ authClient: oAuth2Client });

See docs/oauth.md for endpoint details and token persistence guidance.

API

new SheetsLoader(config?)

| Option | Type | Description | |--------|------|-------------| | auth | AuthConfig | Auth credentials or file path | | authClient | JWT \| OAuth2Client | Pre-built auth client (takes precedence over auth) | | cacheTTL | number | Cache TTL in seconds (default: 300) |

loader.load(sheetId, range): Promise<string[][]>

Fetches values for the given range. Results are cached for cacheTTL seconds.

loader.loadWithHeaders(sheetId, range): Promise<Record<string, string>[]>

Like load(), but treats the first row as column headers and returns an array of objects keyed by those headers. Empty header cells are skipped.

const rows = await loader.loadWithHeaders('SPREADSHEET_ID', 'Sheet1!A1:C100');
// rows[0] → { 'First Name': 'Alice', 'Email': '[email protected]', 'Age': '30' }

loader.loadAndMap<T>(sheetId, range, mapper): Promise<T[]>

Fetches values and maps each row through a mapper function:

type Product = { id: string; name: string; price: number };

const products = await loader.loadAndMap(
  'SPREADSHEET_ID',
  'Products!A2:C100',
  (row): Product => ({
    id: row[0],
    name: row[1] || 'Unknown',
    price: parseFloat(row[2]) || 0,
  }),
);

Error Handling

import { AuthError, SheetNotFoundError, FetchError } from '@kylebrodeur/sheets-loader';

try {
  const rows = await loader.load(sheetId, range);
} catch (err) {
  if (err instanceof AuthError) { /* bad credentials */ }
  if (err instanceof SheetNotFoundError) { /* sheet ID not found */ }
  if (err instanceof FetchError) { /* network / API error */ }
}

Type-Safe Column Mapping

sheets-loader ships with MappedSheetsLoader, which integrates @kylebrodeur/type-safe-mapping to rename sheet column headers to your internal model fields with full TypeScript inference — no manual string indexing.

import { SheetsLoader, MappedSheetsLoader } from '@kylebrodeur/sheets-loader';
import type { MappingDefinition } from '@kylebrodeur/type-safe-mapping';

// TSource must include an index signature because mapped field names
// (e.g. 'id') must also satisfy keyof TSource at the type level.
type ProductRow = {
  'Product ID': string;
  'Product Name': string;
  'Unit Price': string;
  [key: string]: string;
};

const mapping = {
  'Product ID':   'id',
  'Product Name': 'name',
  'Unit Price':   'unitPrice',
} as const satisfies MappingDefinition<ProductRow>;

class ProductLoader extends MappedSheetsLoader<ProductRow, typeof mapping> {
  protected fieldMapping = mapping;
}

const loader = new ProductLoader(new SheetsLoader({ auth: { credentials: './sa.json' } }));

// products: { id: string; name: string; unitPrice: string }[]
const products = await loader.loadMapped('SPREADSHEET_ID', 'Products!A1:C500');

See examples/mapped-loader.ts for the full runnable example.

Examples

See the examples/ directory for runnable TypeScript scripts:

• mapped-loader.ts — type-safe column renaming with MappedSheetsLoader

See the docs/ directory for deeper guides:

• docs/oauth.md — OAuth2 authorization code flow and token management
• docs/post-processing.md — transforming and validating loaded rows

Testing

pnpm test

Unit tests mock getAuthClient and fetchValues to avoid real network calls. Coverage includes caching behavior, retry logic, and all error types.

License

MIT