Sheetgoose

Use Google Sheets as a database with Mongoose-like syntax. Works in both browser and Node.js environments.

Installation

npm install sheetgoose

Quick Start

Node.js (Service Account)

import { Sheetgoose, Schema } from 'sheetgoose';

const db = await Sheetgoose.connect({
  spreadsheetId: 'YOUR_SPREADSHEET_ID',
  auth: {
    mode: 'serviceAccount',
    credentials: {
      client_email: '[email protected]',
      private_key: '-----BEGIN PRIVATE KEY-----\n...\n-----END PRIVATE KEY-----\n'
    }
  }
});

const userSchema = new Schema({
  name: { type: 'string', required: true },
  email: { type: 'string', required: true },
  age: { type: 'number' }
});

const User = db.model('Users', userSchema);

// Create
await User.create({ name: 'John', email: '[email protected]', age: 30 });

// Find
const users = await User.find({ age: { $gte: 25 } });

// Update
await User.updateOne({ email: '[email protected]' }, { $set: { age: 31 } });

// Delete
await User.deleteOne({ email: '[email protected]' });

db.disconnect();

Browser (OAuth)

import { Sheetgoose, Schema } from 'sheetgoose';

const db = await Sheetgoose.connect({
  spreadsheetId: 'YOUR_SPREADSHEET_ID',
  auth: {
    mode: 'oauth',
    clientId: 'YOUR_CLIENT_ID.apps.googleusercontent.com'
  }
});

// Use the same API as Node.js

Features

• 🚀 Mongoose-like API - Familiar syntax for MongoDB users
• 🌐 Universal - Works in browser and Node.js
• 🔐 Multiple Auth Methods - OAuth, Service Account, Refresh Token, Access Token
• 📝 Type Safety - Full TypeScript support
• ⚡ Caching - Built-in caching for better performance
• 🔍 Query Operators - $gt, $gte, $lt, $lte, $in, $regex
• 📊 Schema Validation - Runtime validation with custom validators
• 🔄 Real-time Sync - Watch for changes with watch()
• 🎯 Version Control - Optimistic locking with _version

Authentication Methods

Service Account (Node.js)

auth: {
  mode: 'serviceAccount',
  credentials: {
    client_email: '[email protected]',
    private_key: '-----BEGIN PRIVATE KEY-----\n...'
  }
}

OAuth (Browser)

auth: {
  mode: 'oauth',
  clientId: 'YOUR_CLIENT_ID.apps.googleusercontent.com'
}

Refresh Token (Node.js)

auth: {
  mode: 'refreshToken',
  clientId: 'YOUR_CLIENT_ID',
  clientSecret: 'YOUR_SECRET',
  refreshToken: 'YOUR_REFRESH_TOKEN'
}

Access Token (Browser/Node.js)

auth: {
  mode: 'token',
  accessToken: 'YOUR_ACCESS_TOKEN'
}

Schema Definition

const schema = new Schema({
  name: { 
    type: 'string', 
    required: true 
  },
  age: { 
    type: 'number',
    validate: (val) => val >= 0 || 'Age must be positive'
  },
  email: { 
    type: 'string',
    validate: (val) => /\S+@\S+/.test(val) || 'Invalid email'
  },
  active: { 
    type: 'boolean', 
    default: true 
  },
  metadata: { 
    type: 'json' 
  },
  createdAt: { 
    type: 'date' 
  }
}, { 
  timestamps: true // Adds createdAt/updatedAt automatically
});

Query Examples

// Basic find
await User.find({ active: true });

// Operators
await User.find({ age: { $gte: 18, $lt: 65 } });
await User.find({ role: { $in: ['admin', 'moderator'] } });
await User.find({ name: { $regex: '^John', $options: 'i' } });

// Sorting and limiting
await User.find().sort('-age').limit(10);
await User.find().sort('name').limit(5);

// Field selection
await User.find().select(['name', 'email']);

// Find one
await User.findOne({ email: '[email protected]' });

Update Operations

// Set fields
await User.updateOne({ _id: '123' }, { 
  $set: { age: 30, active: true } 
});

// Increment
await User.updateOne({ _id: '123' }, { 
  $inc: { loginCount: 1 } 
});

// Unset (remove fields)
await User.updateOne({ _id: '123' }, { 
  $unset: { tempField: true } 
});

Watch for Changes

const stopWatching = User.watch(
  { active: true }, 
  (event) => {
    console.log(event.type); // 'insert', 'update', 'delete'
    console.log(event.doc);
  },
  { intervalMs: 5000 }
);

// Stop watching
stopWatching();

Caching

// Enable caching with 30-second staleness
db.setCacheOptions({ 
  enabled: true, 
  staleMs: 30000 
});

// Clear cache manually
db.clearCache();
User.clearCache();

Setup Guide

1. Enable Google Sheets API

• Go to Google Cloud Console
• Enable Google Sheets API

2. Create Credentials

For Node.js (Service Account):

• Create Service Account
• Download JSON key
• Share spreadsheet with service account email

For Browser (OAuth):

• Create OAuth 2.0 Client ID
• Add authorized JavaScript origins
• Use Client ID in your app

3. Share Spreadsheet

Share your Google Sheet with:

• Service account email (Node.js)
• Your Google account (Browser OAuth)

Give "Editor" permissions.

TypeScript Support

Full TypeScript definitions included:

import { Sheetgoose, Schema, Model, Document } from 'sheetgoose';

interface User extends Document {
  name: string;
  email: string;
  age?: number;
}

const schema = new Schema({ /* ... */ });
const User: Model = db.model('Users', schema);

Error Handling

import { 
  SheetgooseError,
  SheetgooseAuthError,
  SheetgooseValidationError,
  SheetgooseNotFoundError 
} from 'sheetgoose';

try {
  await User.create({ /* invalid data */ });
} catch (error) {
  if (error instanceof SheetgooseValidationError) {
    console.log(error.errors); // Field-specific errors
  }
}

API Reference

Sheetgoose

• Sheetgoose.connect(config) - Connect to spreadsheet
• model(name, schema) - Create/get model
• setCacheOptions(options) - Configure caching
• clearCache() - Clear all cache
• disconnect() - Cleanup and disconnect

Model

• create(doc) - Create single document
• createMany(docs) - Create multiple documents
• find(filter) - Find documents
• findOne(filter) - Find single document
• updateOne(filter, operations) - Update document
• deleteOne(filter) - Delete document
• watch(filter, callback, options) - Watch for changes

Query

• sort(field) - Sort results ('field' or '-field')
• limit(n) - Limit results
• select(fields) - Select specific fields
• exec() - Execute query
• findOne() - Get first result

License

MIT

Contributing

Issues and PRs welcome at github.com/WaelHoury/sheetgoose