DOCSEARCHbyAlgolia
Submit Docs

opensheet

repository·main·Indexed 21 days ago

https://github.com/benborgers/opensheet

A hosted API and Bun server that converts Google Sheets into JSON data, allowing spreadsheets to be used as a data source for web applications. It supports accessing tabs by name or index, raw data mode via query parameters, and 30-second response caching. The project can be self-hosted with Redis, PostgreSQL, and a Google Sheets API key.

Tokens
1K
Snippets
4
Records
6
Agent score
24%

What's inside opensheet

  1. Understand opensheet caching behavior

    main
    API responses are cached for 30 seconds to improve performance and prevent hitting Google Sheets' rate limits. Note that new edits made to a Google Sheet may take up to 30 seconds to appear in the API response.

  2. Use the opensheet API to get Google Sheets as JSON

    main

    The opensheet API converts Google Sheets into JSON data. To use it, ensure your Google Sheet meets these requirements:

    1. Header Row: The first row must contain headers.
    2. Link Sharing: Link sharing must be enabled so that anyone with the link can view the sheet.

    URL Format

    https://opensheet.elk.sh/<spreadsheet_id>/<tab_name_or_number>

    Accessing Tabs

    • By Name: Use the tab name (e.g., Test+Sheet).
    • By Index: Use the tab number (starting at 1).

    Raw Data Mode

    By default, the API returns formatted data (numbers, dates, etc.). To get the underlying raw values, append the ?raw=true query parameter.

    https://opensheet.elk.sh/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/Test+Sheet

// To get raw, unformatted data:
https://opensheet.elk.sh/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/Test+Sheet?raw=true

// To get the first tab by index:
https://opensheet.elk.sh/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/1

  4. Self-host opensheet

    main

    If you wish to host your own instance of opensheet, note that it is a Bun server. You will need to provide the following infrastructure and configuration:

    Requirements

    1. Redis: Used for caching.
    2. PostgreSQL: Used for analytics.
    3. Google Sheets API Key:
      • Create a project in the Google Cloud Console.
      • Enable the "Google Sheets API".
      • Create an API key under "Credentials".

    Environment Variables

    Set the following variable in your environment:

    • GOOGLE_API_KEY

  5. Fetch Google Sheet data via OpenSheet API

    main

    OpenSheet provides a RESTful interface to fetch Google Sheets data as JSON. The API converts spreadsheet rows into an array of objects where the keys are derived from the first row (the header row) of the sheet.

    URL Structure

    /:id/:sheet

    • id: The Google Spreadsheet ID.
    • sheet: Either the name of the sheet (string) or the index of the sheet (1-based integer).

    Query Parameters

    ParameterTypeDescription
    rawtrue or falseIf set to true, the API uses Google's UNFORMATTED_VALUE render option, returning raw data instead of formatted strings.

    Response Format

    Returns a JSON array of objects:

    [
  { "Header1": "Value1", "Header2": "Value2" },
  { "Header1": "Value3", "Header2": "Value4" }
]
    // Example: Fetching sheet index 1 with raw values
// URL: https://your-opensheet-instance.com/SPREADSHEET_ID/1?raw=true

// Expected Response:
[
  {
    "Name": "John Doe",
    "Email": "john@example.com"
  },
  {
    "Name": "Jane Smith",
    "Email": "jane@example.com"
  }
]

  6. Handle OpenSheet API errors

    main

    If a request fails (e.g., invalid sheet index, invalid query parameters, or Google API errors), OpenSheet returns a JSON error response with a non-200 status code.

    Error Response Schema

    {
  "error": "Error message string",
  "documentation": "https://github.com/benborgers/opensheet#readme"
}
    {
  "error": "For this API, sheet numbers start at 1",
  "documentation": "https://github.com/benborgers/opensheet#readme"
}