Google Sheets Database

Common errors

• Error: Invalid Credentials

  cause Incorrect or expired Google API credentials (e.g., Service Account private key, OAuth token).
  fix Double-check your `GOOGLE_SERVICE_ACCOUNT_EMAIL` and `GOOGLE_PRIVATE_KEY` environment variables. Ensure the private key correctly handles newline characters (e.g., replace `\n` with `\n`). Regenerate credentials if necessary.

• Error: The requested document was not found.

  cause The provided Google Sheet ID is incorrect, or the authenticated account does not have permission to view it.
  fix Verify the `GOOGLE_SHEET_ID` environmental variable or literal string matches the ID from the Google Sheet URL. Confirm the authenticated Google account (Service Account or OAuth) has at least read access to the sheet.

• SyntaxError: await is only valid in async functions and the top level bodies of modules

  cause Using `await` outside an `async` function or a module context (when `"type": "module"` is set in `package.json`) in Node.js.
  fix Wrap all `await` calls within an `async` function, or configure your Node.js project to use ES Modules by adding `"type": "module"` to your `package.json` file and using `.mjs` or `.js` files when appropriate.

• Error: You do not have permission to access this spreadsheet.

  cause The authenticated Google account lacks the necessary permissions (e.g., read, write) for the target Google Sheet.
  fix Share the Google Sheet directly with the email address of your Service Account or the Google account used for OAuth, granting appropriate permissions (e.g., Editor for write access).

Warnings

• gotcha The package examples often use top-level `await` for brevity. This feature is not enabled by default in most Node.js environments and requires wrapping `await` calls in an `async` function or configuring Node.js for ES Modules (`"type": "module"` in `package.json`).
  Fix: Wrap top-level `await` calls in an `async` function and invoke it, or configure your project for ES Modules and ensure your script is treated as such.
• gotcha This library is explicitly designed for small applications and datasets. It is not suitable as a replacement for traditional databases in scenarios involving many entries, complex queries, or frequent, high-volume operations due to Google Sheets API limitations and performance characteristics.
  Fix: Evaluate your application's data scale and query complexity. For larger datasets or relational needs, consider a dedicated database solution (e.g., PostgreSQL, MongoDB).
• gotcha When using a Service Account for authentication, ensure that the Google Service Account has editor permissions on the specific Google Sheet document you are trying to access. Common issues arise from insufficient sharing permissions on the sheet itself.
  Fix: Share your Google Sheet with the `client_email` of your Service Account, granting editor access. Verify the Service Account JSON keys are correctly configured and accessible to your application.
• gotcha Google Sheets API imposes rate limits. Excessive read/write operations within a short period can lead to `429 Too Many Requests` errors. While the library might have some internal optimizations, developers should be mindful of their usage patterns.
  Fix: Implement rate limiting or exponential backoff in your application logic for operations that might trigger high API usage. Optimize data fetching to retrieve only necessary data and batch writes where possible.

Install

• npm install sheets-database npm
• yarn add sheets-database yarn
• pnpm add sheets-database pnpm

Imports

• SheetDatabase
  wrong:

  const { SheetDatabase } = require('sheets-database');

  correct:

  import { SheetDatabase } from 'sheets-database';

  While CommonJS `require` is shown in some examples, ES modules with TypeScript are generally preferred for modern Node.js and frontend projects. The package ships with TypeScript types.
• Auth Options (e.g., useServiceAccount)
  wrong:

  db.useServiceAccount({...}); // Missing await

  correct:

  await db.useServiceAccount({...});

  Authentication methods are async and must be awaited. Access the methods via the SheetDatabase instance, not as direct imports.

Quickstart

This quickstart initializes a SheetDatabase instance, authenticates using a Google Service Account (best practice for backend), synchronizes with the sheet, adds a new table, inserts and updates data, and demonstrates renaming a table. It includes robust environment variable handling for credentials.

import { SheetDatabase } from 'sheets-database';

// Initialize the Database with doc ID (long id in the sheets URL)
const db = new SheetDatabase(process.env.GOOGLE_SHEET_ID ?? '');

async function run() {
  // Initialize Auth using a Service Account for robust server-side access.
  // Ensure GOOGLE_SERVICE_ACCOUNT_EMAIL and GOOGLE_PRIVATE_KEY are set in your environment variables.
  // The private key may need newline characters replaced if loaded from a single-line environment variable.
  await db.useServiceAccount({
    client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL ?? '',
    private_key: (process.env.GOOGLE_PRIVATE_KEY ?? '').replace(/\\n/g, '\n'),
  });

  await db.sync(); // Connects with the sheet and fetches initial data

  // ADDING TABLES
  const table1 = await db.addTable('products', ['id', 'name', 'price']);
  console.log('Table "products" added.');

  // Insert some data
  await table1.insertOne({ 'id': 1, 'name': 'Laptop', 'price': 1200 });
  await table1.insert([
    { 'id': 2, 'name': 'Mouse', 'price': 25 },
    ['3', 'Keyboard', 75]
  ]);
  console.log('Data inserted into products table:', table1.getData());

  // RENAMING TABLES
  await table1.rename('inventory');
  console.log('Table renamed to "inventory".');

  // Update entries
  await db.inventory.updateRowsWhere(
    (currentData) => currentData.name === 'Laptop',
    (data) => ({ ...data, price: 1250 })
  );
  console.log('Updated inventory data:', db.inventory.getData());

  // DELETING TABLES (Commented out to prevent accidental deletion in quickstart)
  // await db.inventory.drop();
  // console.log('Table "inventory" dropped.');
}

run().catch(console.error);