googlesheet-rest-client

A lightweight, fully typed TypeScript client library for consuming Google Sheets REST API (Google Apps Script). Designed with first-class support for Astro JS, modern frontend frameworks (Next.js, Remix, SvelteKit), and Node.js.

✨ Features

• 🔒 Type-Safe: Full TypeScript generics support for strongly typed data models (client.from<User>('users')).
• ⚡ Zero External Runtime Dependencies: Built entirely on standard fetch API.
• 🚀 Astro JS Ready: Works out of the box with Astro SSR, Server Endpoints (src/pages/api/), and static .astro frontmatters.
• 🔄 Relational Queries: Fetch nested child relations using .find({ include: ['posts'] }).
• 🔍 Fluent API: Clean, chainable syntax inspired by modern ORMs and Supabase.
• 📦 Dual Module: Pre-bundled with ESM, CommonJS, and TypeScript definitions (.d.ts).
• 🔓 100% Free & Open-Source: Unrestricted access to your self-hosted Google Sheets REST backend.

📦 Installation

npm install googlesheet-rest-client

⚙️ Configuration (.env)

Add the following environment variables to your .env file:

SHEET_URL=https://script.google.com/macros/s/AKfycbycW46jWjtwiQrGf7xXgubbMYEY2aCgDpMnz_g8mnVJk-Wux0BtsHxEDO0gVWRbRZO3yA/exec
SHEET_API_KEY=NANONANO_API_KEY
NANONANO_SPREADSHEET_ID=18SccKuB73LsrujcMc8qopKAtiDcPTQ7Aqk3O4wvyASI

SheetClient automatically detects SHEET_URL, SHEET_API_KEY (or NANONANO_API_KEY), and NANONANO_SPREADSHEET_ID from process.env or import.meta.env.

🔒 Service Account Sharing

To keep spreadsheets 100% private, share your Google Sheet with:

nanonano-service@sheets-rest-api-marketplace.iam.gserviceaccount.com

Role: Editor. (Also exported as NANONANO_SERVICE_ACCOUNT_EMAIL in the SDK).

🚀 Astro JS Integration Guide

1. Initialize Client (src/lib/sheets.ts)

Create a helper file to instantiate and export your client:

// src/lib/sheets.ts
import { createClient } from 'googlesheet-rest-client';

// Automatically reads SHEET_URL and SHEET_API_KEY from .env
export const sheets = createClient();

(You can also pass explicit options if needed: createClient({ url: '...', apiKey: '...' }))

2. Fetch Data in Astro Component (src/pages/users.astro)

In your .astro frontmatter, fetch data directly on the server:

---
import Layout from '../layouts/Layout.astro';
import { sheets } from '../lib/sheets';

interface User {
  id: string;
  name: string;
  email: string;
  status: string;
}

// Fetch users with pagination, filter, and relational joins
const { data: users, pagination } = await sheets.from<User>('users').find({
  limit: 20,
  filter: { status: 'active' },
  include: ['posts'] // Join child table 'posts' where post.userId = user.id
});
---

<Layout title="Users List">
  <main>
    <h1>Active Users ({pagination.total})</h1>
    <ul>
      {users.map((user) => (
        <li>
          <strong>{user.name}</strong> - {user.email}
        </li>
      ))}
    </ul>
  </main>
</Layout>

3. Build Astro API Endpoints (src/pages/api/users.ts)

Handle server-side API requests (e.g. form submissions, JSON API):

// src/pages/api/users.ts
import type { APIRoute } from 'astro';
import { sheets } from '../../lib/sheets';

// GET /api/users
export const GET: APIRoute = async ({ url }) => {
  try {
    const limit = Number(url.searchParams.get('limit')) || 20;
    const result = await sheets.from('users').find({ limit });

    return new Response(JSON.stringify(result), {
      status: 200,
      headers: { 'Content-Type': 'application/json' },
    });
  } catch (error: any) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: error.status || 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }
};

// POST /api/users
export const POST: APIRoute = async ({ request }) => {
  try {
    const body = await request.json();
    const newUser = await sheets.from('users').create(body);

    return new Response(JSON.stringify(newUser), {
      status: 201,
      headers: { 'Content-Type': 'application/json' },
    });
  } catch (error: any) {
    return new Response(JSON.stringify({ error: error.message }), {
      status: error.status || 500,
      headers: { 'Content-Type': 'application/json' },
    });
  }
};

📖 API Reference & Examples

1. find(options) - Query / List Records

const { data, pagination } = await client.from('products').find({
  limit: 50,          // Limit number of records (default: 50)
  offset: 0,          // Skip number of records (default: 0)
  filter: {           // Exact match column filters
    category: 'Electronics',
    in_stock: 'true'
  },
  include: ['reviews'] // Join related sheet table
});

console.log(pagination.hasMore); // boolean
console.log(pagination.total);   // total matching records count

2. findById(id, include?) - Get Single Record

const user = await client.from('users').findById('123', ['posts', 'profile']);

3. create(data) - Insert New Record

const newProduct = await client.from('products').create({
  name: 'Mechanical Keyboard',
  price: 99.99,
  category: 'Electronics'
});

console.log(newProduct.id); // ID auto-assigned by Google Sheets backend

4. update(id, data) - Update Record

const updated = await client.from('products').update('123', {
  price: 89.99
});

5. delete(id) - Delete Record

const result = await client.from('products').delete('123');
// result: { success: true, deleted_id: '123' }

🚨 Error Handling

The library provides structured error classes for HTTP response status codes:

import {
  SheetClient,
  AuthenticationError,
  NotFoundError,
  RateLimitError,
  SheetApiError
} from 'googlesheet-rest-client';

try {
  const item = await client.from('items').findById('non-existent-id');
} catch (error) {
  if (error instanceof NotFoundError) {
    console.error('Item not found in sheet!');
  } else if (error instanceof RateLimitError) {
    console.error('Rate limit reached (100 req/min). Backing off...');
  } else if (error instanceof AuthenticationError) {
    console.error('Invalid API Key.');
  } else if (error instanceof SheetApiError) {
    console.error(`API Error (${error.status}): ${error.message}`);
  }
}

🛠 Building the Package

cd packages/googlesheet-rest-client
npm install
npm run build
npm test

📄 License

MIT © 2026 Ronnie Aban