Google Sheets Connector Guide

The Google Sheets connector helps Tealfabric workflows read and write spreadsheet data using OAuth2-secured Google APIs. This guide covers practical setup, common read/write patterns, and reliability practices so you can use Sheets as a dependable operational data surface.

When to use this connector

Use this connector when workflows need to publish tabular output, read user-maintained reference data, or maintain operational trackers in spreadsheets. It is especially useful for lightweight reporting, handoff queues, and business-owned lists that must stay synchronized with automated processes. Defining stable ranges and header structure up front reduces mapping errors over time.

Prerequisites

Before creating the integration, enable the Google Sheets API in your Google Cloud project and configure OAuth credentials for your environment. Confirm that your integration has access to the target spreadsheet and that your scopes match the intended read/write operations. Stable permission setup is the most important prerequisite for predictable runs.

Configuration reference

Connector credentials are stored in integration configuration and reused for runtime token management. Keep secrets out of code and rotate tokens according to your security policy.

• client_id (required for test): OAuth2 client ID from Google Cloud.
• client_secret (required for test): OAuth2 client secret paired with the client ID.
• redirect_uri (required for test): OAuth redirect URI configured in Google Cloud.
• access_token (required for runtime operations): Current access token; complete the OAuth2 authorization flow first.
• refresh_token (optional): Refresh token used for automatic renewal on HTTP 401.
• scope (optional): OAuth scopes for Sheets operations.
• timeout_seconds (optional): Timeout for API requests (default 30).

test validates OAuth app configuration (client_id, client_secret, redirect_uri) and returns Configuration validation failed when incomplete. send, receive, sync, and batch require a valid access_token but do not re-validate OAuth app credentials on every call.

Write sheet ranges (send)

The most common pattern is writing data to a known range. Use method: "APPEND" to insert rows instead of overwriting.

const baseUrl = "https://api.example.com/api/v1";
const tenantId = "<TENANT_ID>";
const apiKey = "<API_KEY>";

async function writeOrderSummary(integrationId: string, spreadsheetId: string) {
  const response = await fetch(
    `${baseUrl}/integrations/${encodeURIComponent(integrationId)}/execute`,
    {
      method: "POST",
      headers: {
        "X-API-Key": apiKey,
        "X-Tenant-ID": tenantId,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        operation: "send",
        spreadsheet_id: spreadsheetId,
        range: "Orders!A1:C2",
        values: [
          ["Order", "Amount", "Status"],
          ["A-100", "49.00", "Paid"],
        ],
      }),
    }
  );

  if (!response.ok) throw new Error(`Request failed: ${response.status}`);
  const payload = await response.json();
  if (!payload.success) throw new Error(payload.error?.message ?? "Send failed");
  return payload.data;
}

curl -X POST "https://api.example.com/api/v1/integrations/<INTEGRATION_ID>/execute" \
  -H "X-API-Key: <API_KEY>" \
  -H "X-Tenant-ID: <TENANT_ID>" \
  -H "Content-Type: application/json" \
  -d '{
    "operation": "send",
    "spreadsheet_id": "<SPREADSHEET_ID>",
    "range": "Orders!A1:C2",
    "values": [
      ["Order", "Amount", "Status"],
      ["A-100", "49.00", "Paid"]
    ]
  }'

{
  "success": true,
  "data": {
    "message_count": 6,
    "data": {
      "spreadsheetId": "<SPREADSHEET_ID>",
      "updatedRange": "Orders!A1:C2",
      "updatedRows": 2,
      "updatedColumns": 3,
      "updatedCells": 6
    },
    "response": {
      "spreadsheetId": "<SPREADSHEET_ID>",
      "updatedRange": "Orders!A1:C2",
      "updatedRows": 2,
      "updatedColumns": 3,
      "updatedCells": 6
    }
  }
}

Retrieve range data (receive)

Use receive to fetch range values for validation, branching, or export workflows. Rows are returned in data.data as a 2D array from the Sheets API values field.

curl -X POST "https://api.example.com/api/v1/integrations/<INTEGRATION_ID>/execute" \
  -H "X-API-Key: <API_KEY>" \
  -H "X-Tenant-ID: <TENANT_ID>" \
  -H "Content-Type: application/json" \
  -d '{
    "operation": "receive",
    "spreadsheet_id": "<SPREADSHEET_ID>",
    "range": "Orders!A1:C100"
  }'

{
  "success": true,
  "data": {
    "message_count": 2,
    "data": [
      ["Order", "Amount", "Status"],
      ["A-100", "49.00", "Paid"]
    ],
    "total_size": 2
  }
}

Bidirectional sync (sync)

sync runs optional outbound (send) and/or inbound (receive) legs. Empty outbound/inbound objects are skipped (PHP empty() semantics).

{
  "operation": "sync",
  "outbound": {
    "spreadsheet_id": "<SPREADSHEET_ID>",
    "range": "Orders!A2:C2",
    "values": [["B-200", "12.00", "Pending"]]
  },
  "inbound": {
    "spreadsheet_id": "<SPREADSHEET_ID>",
    "range": "Orders!A1:C100"
  }
}

Batch operations (batch)

batch accepts operations or items with operation/type of send or receive. Unsupported operation types are rejected per entry; other entries continue.

{
  "operation": "batch",
  "operations": [
    {
      "operation": "send",
      "data": {
        "spreadsheet_id": "<SPREADSHEET_ID>",
        "range": "Orders!A3:C3",
        "values": [["C-300", "8.00", "Paid"]]
      }
    },
    {
      "operation": "receive",
      "data": {
        "spreadsheet_id": "<SPREADSHEET_ID>",
        "range": "Orders!A1:C10"
      }
    }
  ]
}

Validate connection (test)

Run test after token refresh changes, spreadsheet permission updates, or integration migrations. The probe calls GET spreadsheets?fields=spreadsheets.properties.title.

curl -X POST "https://api.example.com/api/v1/integrations/<INTEGRATION_ID>/execute" \
  -H "X-API-Key: <API_KEY>" \
  -H "X-Tenant-ID: <TENANT_ID>" \
  -H "Content-Type: application/json" \
  -d '{
    "operation": "test"
  }'

{
  "success": true,
  "data": {
    "message": "Google Sheets connection test successful",
    "details": {
      "api_base_url": "https://sheets.googleapis.com/v4"
    }
  }
}

Rate limiting and token refresh

The connector enforces a local 500-requests-per-100-seconds throttle (aligned with Google Sheets quota windows). On HTTP 401 with a configured refresh_token, the connector refreshes the access token once and retries the request. Refreshed tokens are used for the current request path but are not persisted back to integration secret storage unless the platform write-back layer is wired.

Additional references

For endpoint and scope details, use the official Google Sheets API documentation and Google OAuth documentation. Keeping ranges, headers, and scope decisions documented alongside workflow logic improves long-term maintainability.