sheetsql

0.1.7 verified Thu Apr 23 auth: no javascript

sheetsql is a JavaScript/TypeScript library (currently at version 0.1.7) that enables developers to interact with Google Spreadsheets as if they were a simple database. It provides a programmatic API for performing CRUD (Create, Read, Update, Delete) operations on spreadsheet data, effectively turning a Google Sheet into a 'single source of truth' for non-technical users. This approach is particularly useful for scenarios where a full-fledged Content Management System (CMS) is overkill, but programmatic access to frequently updated spreadsheet data is required. The library is currently in an early development stage, indicated by its low version number, suggesting that future releases might introduce breaking changes. It differentiates itself by offering a direct, code-driven interface to Google Sheets data without requiring intermediate database synchronization, simplifying workflows for data managed primarily by business users.

Warnings

gotcha Data retrieved from and written to Google Spreadsheets via sheetsql are always handled as strings. Users are responsible for explicitly converting data types (e.g., to numbers, booleans, dates) in their application code when reading, and ensuring data is correctly stringified when writing. ↓

fix Implement explicit type conversion logic in your application code (e.g., `parseInt(doc.age)`, `String(myNumber)`).

gotcha Proper authentication with Google Sheets requires a correctly configured Google Cloud Service Account. This involves creating the service account, downloading its JSON key file, and crucially, sharing the target Google Spreadsheet with the service account's email address (with editor permissions). Misconfiguration in any of these steps will lead to authentication failures. ↓

fix Refer to the Google Cloud documentation for creating a Service Account and ensure the service account email has 'Editor' access to your Google Spreadsheet.

gotcha sheetsql internally uses the `googleapis` library, which relies on standard `HTTP_PROXY`/`HTTPS_PROXY` environment variables for proxying requests. If only Google API requests need to be proxied, and other network requests in the application should bypass the proxy, users must explicitly set `NO_PROXY=*` to prevent unintended global proxying. ↓

fix Set environment variables like `HTTP_PROXY=http://your.proxy.com:8080` and, if necessary, `NO_PROXY=*` before running your application.

Install

npm install sheetsql

yarn add sheetsql

pnpm add sheetsql

Imports

Database
wrong
```
const Database = require('sheetsql')
```
correct
```
import { Database } from 'sheetsql'
```
sheetsql ships with TypeScript types and is best used in an ESM context. While CommonJS require might work via transpilation, ESM import is the idiomatic usage.

Quickstart

Demonstrates how to initialize a `Database` instance with a Google Service Account key, load data from a spreadsheet, and perform basic CRUD operations (insert, find, update, remove).

import { Database } from 'sheetsql';
import * as path from 'path';

// Ensure your service account key file exists at this path.
// For production, consider loading the key from environment variables or a secure secret management system.
const keyFilePath = path.resolve('./google-serviceaccount.json');

const runExample = async () => {
  const db = new Database({
    db: '1ya2Tl2ev9M80xYwspv7FJaoWq0oVOMBk3VF0f0MXv2s', // Replace with your actual Google Spreadsheet ID
    table: 'Sheet1', // optional, default = Sheet1
    keyFile: keyFilePath,
    cacheTimeoutMs: 5000 // optional, default = 5000
  });

  // Load schema and data from google spreadsheet
  await db.load();
  console.log('Database loaded.');

  // Insert multiple documents
  let docs = await db.insert([
    {
      name: 'joway',
      age: 18
    },
    {
      name: 'alice',
      age: 25
    }
  ]);
  console.log('Inserted documents:', docs);

  // Find documents and update them
  docs = await db.update(
    {
      name: 'joway'
    },
    {
      age: 100
    }
  );
  console.log('Updated documents:', docs);

  // Find documents
  docs = await db.find({
    name: 'joway'
  });
  console.log('Found documents:', docs);

  // Find all documents
  docs = await db.find({});
  console.log('All documents:', docs);

  // Find documents and remove them
  docs = await db.remove({
    name: 'alice'
  });
  console.log('Removed documents:', docs);
};

runExample().catch(console.error);