{"id":17361,"library":"sheets-database","title":"Google Sheets Database","description":"sheets-database is a JavaScript/TypeScript library designed to enable developers to use Google Sheets as a lightweight, no-cost database or content management system. Currently at version 1.0.4, it provides a simple and intuitive API for common database operations like creating, reading, updating, and deleting (CRUD) tables and their entries. The library offers multiple authentication methods, including Service Account, OAuth, Access Token, and API Key, and includes features to optimize memory and network usage for suitable use cases. While it handles internal synchronization with Google Sheets, it is explicitly positioned for small applications and datasets, warning against its use for applications with many entries or complex joins where traditional databases are more appropriate. Release cadence appears to be minor patches within the 1.0.x range, indicating stability and ongoing maintenance.","status":"active","version":"1.0.4","language":"javascript","source_language":"en","source_url":"https://github.com/rahul-jha98/sheets-database","tags":["javascript","google","sheets","database","db","google sheets","spreadsheets","spreadsheet","typescript"],"install":[{"cmd":"npm install sheets-database","lang":"bash","label":"npm"},{"cmd":"yarn add sheets-database","lang":"bash","label":"yarn"},{"cmd":"pnpm add sheets-database","lang":"bash","label":"pnpm"}],"dependencies":[],"imports":[{"note":"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.","wrong":"const { SheetDatabase } = require('sheets-database');","symbol":"SheetDatabase","correct":"import { SheetDatabase } from 'sheets-database';"},{"note":"Authentication methods are async and must be awaited. Access the methods via the SheetDatabase instance, not as direct imports.","wrong":"db.useServiceAccount({...}); // Missing await","symbol":"Auth Options (e.g., useServiceAccount)","correct":"await db.useServiceAccount({...});"}],"quickstart":{"code":"import { SheetDatabase } from 'sheets-database';\n\n// Initialize the Database with doc ID (long id in the sheets URL)\nconst db = new SheetDatabase(process.env.GOOGLE_SHEET_ID ?? '');\n\nasync function run() {\n  // Initialize Auth using a Service Account for robust server-side access.\n  // Ensure GOOGLE_SERVICE_ACCOUNT_EMAIL and GOOGLE_PRIVATE_KEY are set in your environment variables.\n  // The private key may need newline characters replaced if loaded from a single-line environment variable.\n  await db.useServiceAccount({\n    client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL ?? '',\n    private_key: (process.env.GOOGLE_PRIVATE_KEY ?? '').replace(/\\\\n/g, '\\n'),\n  });\n\n  await db.sync(); // Connects with the sheet and fetches initial data\n\n  // ADDING TABLES\n  const table1 = await db.addTable('products', ['id', 'name', 'price']);\n  console.log('Table \"products\" added.');\n\n  // Insert some data\n  await table1.insertOne({ 'id': 1, 'name': 'Laptop', 'price': 1200 });\n  await table1.insert([\n    { 'id': 2, 'name': 'Mouse', 'price': 25 },\n    ['3', 'Keyboard', 75]\n  ]);\n  console.log('Data inserted into products table:', table1.getData());\n\n  // RENAMING TABLES\n  await table1.rename('inventory');\n  console.log('Table renamed to \"inventory\".');\n\n  // Update entries\n  await db.inventory.updateRowsWhere(\n    (currentData) => currentData.name === 'Laptop',\n    (data) => ({ ...data, price: 1250 })\n  );\n  console.log('Updated inventory data:', db.inventory.getData());\n\n  // DELETING TABLES (Commented out to prevent accidental deletion in quickstart)\n  // await db.inventory.drop();\n  // console.log('Table \"inventory\" dropped.');\n}\n\nrun().catch(console.error);","lang":"typescript","description":"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."},"warnings":[{"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.","message":"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`).","severity":"gotcha","affected_versions":">=1.0.0"},{"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).","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"},{"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.","message":"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.","severity":"gotcha","affected_versions":">=1.0.0"}],"env_vars":null,"last_verified":"2026-04-22T00:00:00.000Z","next_check":"2026-07-21T00:00:00.000Z","problems":[{"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.","cause":"Incorrect or expired Google API credentials (e.g., Service Account private key, OAuth token).","error":"Error: Invalid Credentials"},{"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.","cause":"The provided Google Sheet ID is incorrect, or the authenticated account does not have permission to view it.","error":"Error: The requested document was not found."},{"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.","cause":"Using `await` outside an `async` function or a module context (when `\"type\": \"module\"` is set in `package.json`) in Node.js.","error":"SyntaxError: await is only valid in async functions and the top level bodies of modules"},{"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).","cause":"The authenticated Google account lacks the necessary permissions (e.g., read, write) for the target Google Sheet.","error":"Error: You do not have permission to access this spreadsheet."}],"ecosystem":"npm","meta_description":null}