Sheet Structure Best Practices

SheetsAPI reads your Google Sheet exactly as it is structured. A well-organised sheet makes your API responses predictable, your queries readable, and your application code simpler.

Header row requirements

The first row of each sheet must contain column headers. SheetsAPI uses these as the keys in every JSON response object.

| name       | price  | category | in_stock |
|------------|--------|----------|----------|
| Widget Pro | 49.99  | Widgets  | TRUE     |
| Gadget X   | 129.00 | Gadgets  | FALSE    |

Response:

{
  "data": [
    {
      "name": "Widget Pro",
      "price": "49.99",
      "category": "Widgets",
      "in_stock": "TRUE"
    }
  ]
}

Rules for header names:

• Headers are case-sensitive - Name and name are different keys
• Spaces are preserved - product name becomes the key "product name"
• Prefer snake_case or camelCase to avoid quoting in JavaScript
• Avoid special characters (,, ., [, ]) - they make accessing properties awkward

Column naming conventions

Always include an id column

Add an id column as the first data column. Use it to look up individual rows:

GET /api/spreadsheets/YOUR_USER_KEY/Products?search=id:prod_001

Good id formats:

Generate UUIDs in bulk from the UUID generator tool.

Date and time columns

Store dates in ISO 8601 format (yyyy-mm-dd) for reliable parsing across languages:

| created_at          | updated_at          |
|---------------------|---------------------|
| 2024-01-15          | 2024-03-20          |
| 2024-01-16T09:30:00 | 2024-03-21T14:00:00 |

Format the column in Google Sheets: Format → Number → More formats → enter yyyy-mm-dd.

Avoid locale-specific formats (1/15/24, 15-Jan-24) - they parse differently across environments and break new Date(value).

Boolean columns

Use checkbox cells (Insert → Checkbox) for boolean values. They appear as TRUE or FALSE in the API response and are visually clear in the sheet.

const isActive = row.is_active === "TRUE";

Alternatively, use the text values "yes"/"no" or "1"/"0" and document your convention consistently.

Multi-value columns (tags, categories)

Google Sheets doesn't have arrays. Store multiple values as comma-separated strings and parse them in your application:

| tags                        |
|-----------------------------|
| typescript,react,next.js    |
| python,fastapi,postgres      |

const tags = row.tags ? row.tags.split(",").map((t) => t.trim()) : [];

Separate concerns into separate sheets

Each sheet in a spreadsheet is its own endpoint. Use separate sheets for separate entities:

/api/spreadsheets/YOUR_USER_KEY/Products    → products sheet
/api/spreadsheets/YOUR_USER_KEY/Orders      → orders sheet
/api/spreadsheets/YOUR_USER_KEY/Customers   → customers sheet

Avoid putting multiple logical tables in one sheet. Sheet names appear verbatim in the URL - use PascalCase (Products, BlogPosts) or kebab-case (blog-posts) consistently.

Keep data rows contiguous

SheetsAPI reads rows from the header row downward until it finds an empty row. To avoid early truncation:

• Do not leave blank rows in the middle of your data
• Delete unused rows at the bottom of the sheet (right-click → Delete rows)
• Do not use rows above row 1 for titles or notes - put them in a separate sheet or a note on the cell

Limit sheet width

Wide sheets with many columns are slower to read and produce large JSON payloads. Use the fields parameter to return only the columns you need:

GET /api/spreadsheets/YOUR_USER_KEY/Products?fields=id,name,price

Response will only include id, name, and price - ignoring all other columns.

Use a staging sheet for imports

When importing data from CSV or another system, import into a temporary _import sheet first, verify the data, then copy rows into the live sheet. This prevents partially valid data from appearing in your API.

Sheet size limits

SheetsAPI performs best with sheets under 100,000 rows. For larger datasets, split into multiple sheets or use a purpose-built database for the hot path and sync to Sheets for reporting.

Checklist

• First row is a header row with descriptive column names
• Column names use snake_case or camelCase (no spaces or special characters)
• An id column exists for per-row lookups
• Date columns use ISO 8601 format (yyyy-mm-dd)
• Boolean columns use checkboxes
• No blank rows in the middle of data
• Each logical entity lives in its own sheet
• Sheet name is URL-safe (no spaces, special characters)