A lightweight REST API microservice that turns Google Sheets into a backend database with full CRUD operations.

Use Google Spreadsheets as a simple database for your applications. Perfect for prototypes, small projects, internal tools, and MVPs where you need a quick backend without setting up a full database.

• Features
• Prerequisites
• Installation
  • Docker (Recommended)
  • Heroku
  • Local Development
• Configuration
  • Google Cloud Setup
  • Environment Variables
• API Reference
  • List Spreadsheets
  • List Sheets
  • Query Data
  • Get Single Row
  • Insert Rows
  • Update Rows
  • Delete Rows
  • Health Check
• Security
• Rate Limits & Quotas
• Examples
• Contributing
• License

• Full CRUD Operations - Create, Read, Update, and Delete data in Google Sheets via REST API
• Pagination Support - Built-in pagination for large datasets
• Dynamic Columns - Automatically adds new columns when inserting data with new fields
• Smart Type Detection - Automatically converts values to appropriate types (boolean, integer, float)
• Multiple Deployment Options - Deploy via Docker, Heroku, or run locally
• API Key Protection - Secure your endpoints with API key authentication
• Health Checks - Built-in health check endpoints for monitoring
• Lightweight - Minimal dependencies, fast startup time

Before you begin, ensure you have the following:

• Node.js >= 18.0.0 (for local development)
• Docker (for containerized deployment)
• Google Cloud Platform Account with:
  • Google Sheets API enabled
  • Google Drive API enabled
  • Service Account with JSON credentials

The fastest way to get started is using Docker:

# Pull and run the container
docker run -p 8080:80 -e GOOGLE_CREDENTIALS=$GOOGLE_CREDENTIALS melalj/gsheet-api

# With API key protection
docker run -p 8080:80 \
  -e GOOGLE_CREDENTIALS=$GOOGLE_CREDENTIALS \
  -e PRIVATE_API_KEY=your-secret-key \
  melalj/gsheet-api

Deploy instantly with one click:

Set the GOOGLE_CREDENTIALS config var in your Heroku app settings.

# Clone the repository
git clone https://github.com/melalj/gsheet-api.git
cd gsheet-api

# Install dependencies
npm install

# Create .env file with your credentials (see Configuration section)
cp .env.example .env

# Start the server
npm start

The API will be available at http://localhost:80/ (customize with PORT environment variable).

1. Create a Service Account

   • Go to the Service Accounts page in Google Cloud Console
   • Select your project (or create a new one)
   • Click Create Service Account
   • Enter a name and description
   • Grant the role Editor (or appropriate permissions)
   • Click Done

2. Generate JSON Key

   • Click on the created service account
   • Go to the Keys tab
   • Click Add Key > Create new key
   • Select JSON format
   • Download and save as credentials.json

3. Enable Required APIs

   • Enable Google Drive API
   • Enable Google Sheets API

4. Generate Base64 Credentials

   # On Linux/macOS
   export GOOGLE_CREDENTIALS=$(base64 -w 0 credentials.json)
   
   # On macOS (alternative)
   export GOOGLE_CREDENTIALS=$(base64 credentials.json)
   
   # On Windows (PowerShell)
   $GOOGLE_CREDENTIALS = [Convert]::ToBase64String([IO.File]::ReadAllBytes("credentials.json"))

5. Share Your Spreadsheet

   • Open your Google Spreadsheet
   • Click Share
   • Add the service account email (found in credentials.json as client_email)
   • Grant Editor access

GOOGLE_CREDENTIALS=eyJ0eXBlIjoic2VydmljZV9hY2NvdW50Ii...
PORT=3000
PRIVATE_API_KEY=your-secret-api-key
NODE_ENV=production

Returns all spreadsheets accessible by the service account.

GET /

[
  {
    "id": "1MNXlNRwbUo4-qbTCdBZGW3Q8sq7pUDov-2ElTFOA0wo",
    "name": "My Spreadsheet",
    "modifiedTime": "2024-01-15T10:30:00.000Z"
  }
]

Returns all sheets within a spreadsheet.

GET /:spreadsheetId

[
  {
    "title": "Sheet1",
    "sheetId": 0,
    "rowCount": 1000,
    "columnCount": 26
  },
  {
    "title": "Sheet2",
    "sheetId": 123456789,
    "rowCount": 500,
    "columnCount": 10
  }
]

Retrieves data from a specific sheet with pagination support.

GET /:spreadsheetId/:sheetName

{
  "columns": {
    "name": "Sheet1!A",
    "email": "Sheet1!B",
    "age": "Sheet1!C"
  },
  "pagination": {
    "haveNext": true,
    "totalItems": 2500,
    "range": "Sheet1!A2:DD1001"
  },
  "data": [
    {
      "name": "John Doe",
      "email": "john@example.com",
      "age": 30
    },
    {
      "name": "Jane Smith",
      "email": "jane@example.com",
      "age": 25
    }
  ]
}

Retrieves a specific row by row number.

GET /:spreadsheetId/:sheetName/:rowNumber

{
  "rowNumber": 3,
  "name": "Jane Smith",
  "email": "jane@example.com",
  "age": 25
}

Appends new rows to a sheet. Automatically creates new columns if fields don't exist.

POST /:spreadsheetId/:sheetName

[
  { "name": "Alice", "email": "alice@example.com", "age": 28 },
  { "name": "Bob", "email": "bob@example.com", "age": 32 }
]

{
  "insertedRows": 2
}

Updates specific rows in a sheet. Automatically creates new columns if fields don't exist.

PUT /:spreadsheetId/:sheetName

Object where keys are row numbers and values are the fields to update:

{
  "3": { "email": "newemail@example.com" },
  "5": { "name": "Updated Name", "age": 35 }
}

[
  {
    "spreadsheetId": "1MNXlNRwbUo4-qbTCdBZGW3Q8sq7pUDov-2ElTFOA0wo",
    "updatedRange": "Sheet1!B3",
    "updatedRows": 1,
    "updatedColumns": 1,
    "updatedCells": 1
  },
  {
    "spreadsheetId": "1MNXlNRwbUo4-qbTCdBZGW3Q8sq7pUDov-2ElTFOA0wo",
    "updatedRange": "Sheet1!A5:C5",
    "updatedRows": 1,
    "updatedColumns": 2,
    "updatedCells": 2
  }
]

Deletes specific rows from a sheet.

DELETE /:spreadsheetId/:sheetName

Array of row numbers to delete:

[3, 5, 7]

{
  "deletedRows": 3
}

Check if the API is running.

GET /health
GET /~health

{
  "status": "ok"
}

Protect your endpoints using one of the following methods:

Set the PRIVATE_API_KEY environment variable and include the key in requests:

curl -H "X-Private-Api-Key: your-secret-key" http://localhost:8080/

Set the PRIVATE_API_KEY_QUERY environment variable and include the key in the URL:

curl "http://localhost:8080/?key=your-secret-key"

1. Always use HTTPS in production
2. Use strong, random API keys (at least 32 characters)
3. Limit service account permissions to only necessary spreadsheets
4. Regularly rotate credentials and API keys
5. Monitor API usage for suspicious activity

This API is subject to Google Sheets API quotas:

Note: Read and write operations have separate quotas.

• Implement caching for frequently accessed data
• Use batch operations when possible
• Consider rate limiting on your end for high-traffic applications
• Monitor your Google Cloud Console for quota usage

// List all spreadsheets
const response = await fetch('http://localhost:8080/', {
  headers: {
    'X-Private-Api-Key': 'your-api-key'
  }
});
const spreadsheets = await response.json();

// Get data from a sheet
const data = await fetch(
  'http://localhost:8080/SPREADSHEET_ID/Sheet1?perPage=100'
).then(r => r.json());

// Insert new rows
await fetch('http://localhost:8080/SPREADSHEET_ID/Sheet1', {
  method: 'POST',
  headers: {
    'Content-Type': 'application/json',
    'X-Private-Api-Key': 'your-api-key'
  },
  body: JSON.stringify([
    { name: 'New User', email: 'user@example.com' }
  ])
});

# List spreadsheets
curl -H "X-Private-Api-Key: your-key" http://localhost:8080/

# Get data with pagination
curl "http://localhost:8080/SPREADSHEET_ID/Sheet1?perPage=50&offset=2"

# Insert data
curl -X POST http://localhost:8080/SPREADSHEET_ID/Sheet1 \
  -H "Content-Type: application/json" \
  -d '[{"name": "Test", "email": "test@example.com"}]'

# Update data
curl -X PUT http://localhost:8080/SPREADSHEET_ID/Sheet1 \
  -H "Content-Type: application/json" \
  -d '{"3": {"name": "Updated Name"}}'

# Delete rows
curl -X DELETE http://localhost:8080/SPREADSHEET_ID/Sheet1 \
  -H "Content-Type: application/json" \
  -d '[3, 4, 5]'

import requests

BASE_URL = 'http://localhost:8080'
HEADERS = {'X-Private-Api-Key': 'your-api-key'}

# Get all spreadsheets
spreadsheets = requests.get(BASE_URL, headers=HEADERS).json()

# Get data from sheet
data = requests.get(
    f'{BASE_URL}/SPREADSHEET_ID/Sheet1',
    params={'perPage': 100},
    headers=HEADERS
).json()

# Insert rows
requests.post(
    f'{BASE_URL}/SPREADSHEET_ID/Sheet1',
    json=[{'name': 'Alice', 'email': 'alice@example.com'}],
    headers=HEADERS
)

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

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Make your changes
4. Run linting (npm run lint)
5. Commit your changes (git commit -m 'Add amazing feature')
6. Push to the branch (git push origin feature/amazing-feature)
7. Open a Pull Request

Please read our Code of Conduct before contributing.

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

• Built with Express.js
• Powered by Google Sheets API
• Inspired by the need for simple, quick backends

Made with love by melalj and contributors