Google Sheet API

Overview

API Plugin's Google Sheets API transforms your spreadsheets into dynamic JSON endpoints. Get instant API access to your Google Sheets data without any coding or complex setup.

Key benefits include:

• Instant JSON API from any Google Sheet
• Real-time data synchronization
• Secure access control
• Simple query parameters
• Multiple response formats
• Automatic caching
• No coding required

How to Setup

We assume you have signed up on API Plugin and are logged into the dashboard.

To set up the Google Sheets API:

1. Go to the marketplace
2. Create a new API
3. Select Google Sheets from the list
4. Click "Connect with Google"
5. Select the sheets you want to expose as API

After authorizing access, you can start using your sheets as APIs immediately.

Sheet Preparation

For best results, structure your sheet with these guidelines:

1. First Row Headers

   • Use clear column names
   • Avoid spaces (use_underscores)
   • Keep names unique
   • Use simple characters

2. Data Formatting

   • Keep data types consistent
   • Use standard date formats
   • Avoid merged cells
   • Remove empty rows

Example Sheet Structure:

| id | product_name | price | stock | last_updated    |
|----|-------------|-------|-------|-----------------|
| 1  | Product A   | 29.99 | 100   | 2024-03-18     |
| 2  | Product B   | 49.99 | 50    | 2024-03-18     |

Available Endpoints

1. Get Rows

GET /v1/{appId}/{token}

Query Parameters:

• tabId (required): Your Sheet Tab name (e.g., Sheet1)
• row_id: Get a specific row by ID
• perPage: Number of records per request
• page: Page number for pagination
• filterBy: Column key to filter results
• filterValue: Value to filter by
• filterType: Filter method (equal, contain, greater, less)
• valueRenderOption: How values should be rendered (FORMATTED_VALUE, UNFORMATTED_VALUE, FORMULA)

2. Add New Rows

POST /v1/{appId}/{token}

Parameters:

• tabId (required): Your Sheet Tab name
• Request Body: Array of objects representing rows

Example Request Body:

[
    {
        "Name": "John Doe",
        "Age": "30",
        "Email": "john@example.com"
    },
    {
        "Name": "Jane Smith",
        "Age": "25",
        "Email": "jane@example.com"
    }
]

3. Update Row

PUT /v1/{appId}/{token}

Parameters:

• tabId (required): Your Sheet Tab name
• Request Body: Object containing row_id and updated values

Example Request Body:

{
    "row_id": 3,
    "name": "API Plugin v2",
    "email": "hello@apiplugin.io"
}

4. Delete Row

DELETE /v1/{appId}/{token}

Query Parameters:

• tabId (required): Your Sheet Tab name
• row_id (required): Row ID to delete

5. Search Rows

GET /v1/{appId}/{token}/search

Query Parameters:

• tabId (required): Your Sheet Tab name
• searchKey (required): Column to search in
• searchValue (required): Value to search for
• searchType: Search method (equal, contain, greater, less)
• valueRenderOption: Value rendering option (FORMATTED_VALUE, UNFORMATTED_VALUE, FORMULA)

Example Requests

1. Get All Rows from Sheet1

GET /v1/{appId}/{token}?tabId=Sheet1

2. Get Filtered Rows

GET /v1/{appId}/{token}?tabId=Sheet1&filterBy=Status&filterValue=Active&filterType=equal

3. Search for Records

GET /v1/{appId}/{token}/search?tabId=Sheet1&searchKey=Email&searchValue=@example.com&searchType=contain

4. Add Multiple Rows

POST /v1/{appId}/{token}?tabId=Sheet1
Content-Type: application/json

[
    {
        "Name": "John Doe",
        "Email": "john@example.com"
    }
]

5. Update a Row

PUT /v1/{appId}/{token}?tabId=Sheet1
Content-Type: application/json

{
    "row_id": 5,
    "Name": "Updated Name",
    "Email": "updated@example.com"
}

6. Delete a Row

DELETE /v1/{appId}/{token}?tabId=Sheet1&row_id=5