Convert Google Sheets into JSON APIs or Database

Published at

1/10/2025

Sheet-Flow 📊

Sheet-Flow transforms your Google Sheets into powerful, production-ready databases with a RESTful API interface. Built for modern applications, it provides enterprise-grade features while maintaining the simplicity and flexibility of spreadsheets.

🌟 Key Features

Core Functionality

🔄 Real-Time Sync: Bi-directional synchronization between your API and spreadsheets
🔐 Enterprise-Grade Security: Row-level access control, API key authentication, and rate limiting
🚀 High Performance: Intelligent caching and connection pooling for optimal performance
📦 Type Safety: Full TypeScript support with automatic type inference from sheet headers

Advanced Features

🔍 Advanced Querying
- Complex filters and search operations
- Pagination and sorting
- Relationship support between sheets
- Aggregation functions
🎯 Data Validation
- Schema validation using Joi
- Custom validation rules
- Data transformation hooks
🔌 Integration Features
- Webhooks for real-time updates
- Event system for data changes
- Custom middleware support
- Batch operations
🛠 Developer Experience
- Auto-generated TypeScript types
- Comprehensive error handling
- Detailed logging and monitoring
- OpenAPI/Swagger documentation

📚 Quick Start

Installation

npm install @sh20raj/sheet-flow

Basic Usage

import { SheetFlow } from '@sh20raj/sheet-flow';

// Initialize SheetFlow
const sheetflow = new SheetFlow({
  credentials: {
    client_email: process.env.GOOGLE_CLIENT_EMAIL,
    private_key: process.env.GOOGLE_PRIVATE_KEY,
  },
  spreadsheetId: 'your-spreadsheet-id'
});

// Define your schema (optional)
const userSchema = {
  name: 'string:required',
  email: 'string:email:required',
  age: 'number:min(0)',
};

// Create a table
const Users = sheetflow.defineTable('Users', {
  schema: userSchema,
  timestamps: true, // Adds createdAt and updatedAt
});

// CRUD Operations
async function examples() {
  // Create
  const newUser = await Users.create({
    name: 'John Doe',
    email: '[email protected]',
    age: 25
  });

  // Read with filtering
  const adults = await Users.find({
    where: {
      age: { $gte: 18 }
    },
    sort: { name: 'asc' },
    limit: 10
  });

  // Update
  await Users.update(
    { age: { $lt: 18 } },
    { status: 'minor' }
  );

  // Delete
  await Users.delete({
    email: '[email protected]'
  });
}

🔧 Advanced Configuration

const config: SheetFlowConfig = {
  credentials: {
    client_email: process.env.GOOGLE_CLIENT_EMAIL,
    private_key: process.env.GOOGLE_PRIVATE_KEY,
  },
  spreadsheetId: 'your-spreadsheet-id',
  options: {
    cache: {
      enabled: true,
      ttl: 60000, // 1 minute
    },
    sync: {
      interval: 5000, // 5 seconds
      strategy: 'optimistic',
    },
    security: {
      encryption: {
        enabled: true,
        fields: ['email', 'phone'],
      },
      rateLimit: {
        windowMs: 15 * 60 * 1000, // 15 minutes
        max: 100, // limit each IP to 100 requests per windowMs
      },
    },
    logging: {
      level: 'info',
      format: 'json',
    },
  },
};

🔐 Authentication & Security

API Key Authentication

import { SheetFlow, auth } from '@sh20raj/sheet-flow';

const app = express();

// Add authentication middleware
app.use(auth.apiKey({
  header: 'X-API-Key',
  keys: ['your-api-key'],
}));

Row-Level Security

const Users = sheetflow.defineTable('Users', {
  schema: userSchema,
  security: {
    policies: {
      read: (user, row) => user.id === row.userId || user.role === 'admin',
      write: (user, row) => user.role === 'admin',
    },
  },
});

🎯 Event Handling

// Subscribe to events
Users.on('beforeCreate', async (data) => {
  // Validate or transform data before creation
  data.createdBy = currentUser.id;
});

Users.on('afterUpdate', async (oldData, newData) => {
  // Trigger webhooks or other side effects
  await notifyWebhooks({
    event: 'user.updated',
    data: { old: oldData, new: newData },
  });
});

📊 Relationships & Joins

const Orders = sheetflow.defineTable('Orders', {
  schema: orderSchema,
  relationships: {
    user: {
      type: 'belongsTo',
      table: 'Users',
      foreignKey: 'userId',
    },
  },
});

// Query with joins
const ordersWithUsers = await Orders.find({
  include: ['user'],
  where: {
    'user.country': 'USA',
  },
});

🔍 Advanced Queries

// Complex filtering
const results = await Users.find({
  where: {
    $or: [
      { age: { $gt: 18 } },
      { status: 'approved' },
    ],
    country: { $in: ['USA', 'Canada'] },
    lastLogin: { $gte: new Date('2023-01-01') },
  },
  select: ['id', 'name', 'email'],
  sort: { age: 'desc' },
  limit: 20,
  offset: 0,
});

// Aggregations
const stats = await Users.aggregate({
  $group: {
    _id: '$country',
    avgAge: { $avg: '$age' },
    total: { $count: true },
  },
  having: {
    total: { $gt: 100 },
  },
});

🚨 Error Handling

try {
  await Users.create({
    name: 'John',
    email: 'invalid-email',
  });
} catch (error) {
  if (error instanceof SheetFlowValidationError) {
    console.error('Validation failed:', error.details);
  } else if (error instanceof SheetFlowConnectionError) {
    console.error('Connection failed:', error.message);
  }
}

📈 Monitoring & Logging

// Custom logger
sheetflow.setLogger({
  info: (msg, meta) => winston.info(msg, meta),
  error: (msg, meta) => winston.error(msg, meta),
});

// Monitor performance
sheetflow.on('query', (stats) => {
  console.log(`Query took ${stats.duration}ms`);
});

🔄 Migration Tools

import { migrate } from '@sh20raj/sheet-flow/tools';

// Create a migration
const migration = {
  up: async (sheet) => {
    await sheet.addColumn('status', { type: 'string', default: 'active' });
    await sheet.renameColumn('userName', 'fullName');
  },
  down: async (sheet) => {
    await sheet.removeColumn('status');
    await sheet.renameColumn('fullName', 'userName');
  },
};

// Run migrations
await migrate.up();

🧪 Testing

import { createTestClient } from '@sh20raj/sheet-flow/testing';

describe('User API', () => {
  let client;

  beforeEach(() => {
    client = createTestClient();
  });

  it('should create a user', async () => {
    const user = await client.Users.create({
      name: 'Test User',
      email: '[email protected]',
    });
    expect(user.id).toBeDefined();
  });
});

📝 Contributing

We welcome contributions! Please see our Contributing Guide for details.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🙋‍♂️ Support

Made with ❤️ by the Sheet-Flow Team

dev-resources.site