LW Google Sheets

An optimized Google Sheets API wrapper with connection pooling, retry logic, and enhanced error handling.

Features

• 🚀 Connection Pooling: Reuses authentication clients for better performance
• 🔄 Automatic Retry: Built-in retry logic with exponential backoff
• 🛡️ Enhanced Error Handling: Custom error types with detailed error codes
• 📝 TypeScript Support: Full TypeScript support with comprehensive types
• 🎯 Multiple Operations: Read, write, append, clear, and get spreadsheet info
• 🔧 Flexible Auth: Support for both credential files and credential objects
• 🔇 Silent Mode: Optional silent operation for production environments

Installation

npm install lw-google-sheet

Quick Start

import { getSheetData, writeSheetData } from "lw-google-sheet";

// Read data from a sheet
const data = await getSheetData({
  credentialsPath: "./credentials.json",
  sheetId: "your-sheet-id",
  range: "A1:C10",
});

// Write data to a sheet
await writeSheetData({
  credentialsPath: "./credentials.json",
  sheetId: "your-sheet-id",
  range: "A1:C3",
  values: [
    ["Name", "Age", "City"],
    ["John", "25", "New York"],
    ["Jane", "30", "San Francisco"],
  ],
});

API Reference

Configuration

All functions accept a configuration object with the following properties:

interface SheetConfig {
  credentialsPath?: string; // Path to Google credentials JSON file
  credentials?: any; // Credentials object (alternative to credentialsPath)
  sheetId: string; // Google Sheets ID
  namedRange?: string; // Named range (optional)
  range: string; // Cell range (e.g., 'A1:C10')
  sheetIndexFallback?: number; // Fallback sheet index (default: 0)
  silent?: boolean; // Suppress console output (default: false)
  retryAttempts?: number; // Number of retry attempts (default: 3)
  retryDelay?: number; // Initial retry delay in ms (default: 1000)
}

Functions

getSheetData(config: SheetConfig): Promise<string[][]>

Reads data from a Google Sheet.

const data = await getSheetData({
  credentialsPath: "./credentials.json",
  sheetId: "1ABC123...",
  range: "A1:E10",
  namedRange: "DataRange", // Optional
  retryAttempts: 5,
  silent: true,
});

writeSheetData(config: WriteConfig): Promise<void>

Writes data to a Google Sheet (overwrites existing data).

await writeSheetData({
  credentialsPath: "./credentials.json",
  sheetId: "1ABC123...",
  range: "A1:C3",
  values: [
    ["Header 1", "Header 2", "Header 3"],
    ["Value 1", "Value 2", "Value 3"],
    ["Value 4", "Value 5", "Value 6"],
  ],
  valueInputOption: "USER_ENTERED", // or 'RAW'
});

appendSheetData(config: WriteConfig): Promise<void>

Appends data to a Google Sheet.

await appendSheetData({
  credentialsPath: "./credentials.json",
  sheetId: "1ABC123...",
  range: "A1:C1",
  values: [["New", "Row", "Data"]],
});

clearSheetData(config: SheetConfig): Promise<void>

Clears data from a Google Sheet range.

await clearSheetData({
  credentialsPath: "./credentials.json",
  sheetId: "1ABC123...",
  range: "A1:Z100",
});

getSpreadsheetInfo(config): Promise<SpreadsheetInfo>

Gets metadata about a spreadsheet and its sheets.

const info = await getSpreadsheetInfo({
  credentialsPath: "./credentials.json",
  sheetId: "1ABC123...",
});

console.log(info.title); // Spreadsheet title
console.log(info.sheets); // Array of sheet metadata

cleanupConnections(maxAge?: number): void

Manually cleanup cached connections (optional, happens automatically).

// Cleanup connections older than 30 minutes
cleanupConnections(30 * 60 * 1000);

Error Handling

The library throws GoogleSheetError instances with specific error codes:

import { getSheetData, GoogleSheetError } from "lw-google-sheet";

try {
  const data = await getSheetData(config);
} catch (error) {
  if (error instanceof GoogleSheetError) {
    console.log("Error code:", error.code);
    console.log("Message:", error.message);
    console.log("Original error:", error.originalError);
  }
}

Common error codes:

• INVALID_CONFIG: Configuration validation failed
• SHEET_NOT_FOUND: Specified sheet index not found
• INVALID_DATA: Data validation failed

Authentication

You can authenticate using either a credentials file or a credentials object:

Using Credentials File

const config = {
  credentialsPath: "./path/to/credentials.json",
  sheetId: "your-sheet-id",
  range: "A1:C10",
};

Using Credentials Object

const config = {
  credentials: {
    type: "service_account",
    project_id: "your-project-id",
    private_key_id: "key-id",
    private_key: "your-private-key",
    client_email: "[email protected]",
    // ... other credential fields
  },
  sheetId: "your-sheet-id",
  range: "A1:C10",
};

Performance Optimizations

1. Connection Pooling: Authentication clients are cached and reused
2. Automatic Cleanup: Old connections are automatically cleaned up
3. Retry Logic: Failed requests are automatically retried with exponential backoff
4. Efficient Fallbacks: Smart fallback to sheet index when named ranges fail

License

ISC