> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coreflux.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Google Sheets

> Client-mode REST API connector for a curated Google Sheets API v4 slice: get/append/update values, batchUpdate, batchGet, and spreadsheet metadata

Technical Preview

## Overview

A client-mode connector for a **curated** slice of [Google Sheets API v4](https://developers.google.com/sheets/api/reference/rest) under `https://sheets.googleapis.com`:

| Catalog connector event | Entity / path | Notes |
| - | - | - |
| GET values/\{range} | `GET /v4/spreadsheets/{spreadsheetId}/values/{range}` | A1 notation — Bearer OAuth / SA token |
| POST values/\{range}:append | `POST /v4/spreadsheets/{spreadsheetId}/values/{range}:append` | One row (or more) per MQTT message — `valueInputOption` required |
| PUT values/\{range} | `PUT /v4/spreadsheets/{spreadsheetId}/values/{range}` | Live cell / overwrite — `valueInputOption` required |
| values.batchUpdate | `POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdate` | Multi-range write — body `valueInputOption` + `data[]` |
| (supporting) values.batchGet | `GET /v4/spreadsheets/{spreadsheetId}/values:batchGet?{ranges_query}` | Multi-range read |
| (supporting) spreadsheets.get | `GET /v4/spreadsheets/{spreadsheetId}` | Metadata / auth smoke |

Default `BASE_ADDRESS` is `https://sheets.googleapis.com` (no trailing slash). Topics use `storage/google-sheets/<object>/<verb>`.

See the base guide: [REST API Connectors](/latest/lot-language/routes/rest-api-routes).

> [!NOTE]
>
> **A1 encoding:** put the sheet range in `{value.json.range}`. URL-encode `!` as `%21` and `:` as `%3A` if the publisher or broker does not encode path segments (example: `Sheet1!A1:D10` → `Sheet1%21A1%3AD10`).

> [!NOTE]
>
> **valueInputOption:** Append and Update require query `valueInputOption=RAW` or `USER_ENTERED`. BatchUpdate places `valueInputOption` in the JSON body with `data` (array of ValueRange).

> [!NOTE]
>
> Prefer Hub secrets for access tokens (`GET SECRET "GOOGLE_SHEETS_TOKEN"` or `"GOOGLE_ACCESS_TOKEN"`). Scope: `https://www.googleapis.com/auth/spreadsheets`. This template has not been verified against a live Google Cloud project. Status remains blocked until an authenticated live smoke succeeds.

## Basic Syntax

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
# Coreflux REST_API template — Google Sheets API v4
# Auth: Bearer {value.json.api_token}
# BASE_ADDRESS: https://sheets.googleapis.com

DEFINE ROUTE GoogleSheetsRestApi WITH TYPE REST_API
    ADD REST_API_CONFIG
        WITH BASE_ADDRESS "https://sheets.googleapis.com"
        WITH ENABLE_CLIENT "true"
        WITH USE_SSL "true"
        WITH REQUEST_TIMEOUT '60'
        WITH DEFAULT_CONTENT_TYPE "application/json"
    ADD EVENT Getvalues
        WITH SOURCE_TOPIC "storage/google-sheets/values/get"
        WITH DESTINATION_TOPIC "storage/google-sheets/values/get/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/v4/spreadsheets/{value.json.spreadsheetId}/values/{value.json.range}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "Authorization" "Bearer {value.json.api_token}"
    ADD EVENT Appendvalues
        WITH SOURCE_TOPIC "storage/google-sheets/values/append"
        WITH DESTINATION_TOPIC "storage/google-sheets/values/append/response"
        WITH METHOD "POST"
        WITH ENDPOINT "/v4/spreadsheets/{value.json.spreadsheetId}/values/{value.json.range}:append?valueInputOption={value.json.valueInputOption}&insertDataOption={value.json.insertDataOption}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "Content-Type" "application/json"
            ADD "Authorization" "Bearer {value.json.api_token}"
        WITH BODY "{payload}"
    ADD EVENT Updatevalues
        WITH SOURCE_TOPIC "storage/google-sheets/values/update"
        WITH DESTINATION_TOPIC "storage/google-sheets/values/update/response"
        WITH METHOD "PUT"
        WITH ENDPOINT "/v4/spreadsheets/{value.json.spreadsheetId}/values/{value.json.range}?valueInputOption={value.json.valueInputOption}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "Content-Type" "application/json"
            ADD "Authorization" "Bearer {value.json.api_token}"
        WITH BODY "{payload}"
    ADD EVENT Batchupdatevalues
        WITH SOURCE_TOPIC "storage/google-sheets/values/batch-update"
        WITH DESTINATION_TOPIC "storage/google-sheets/values/batch-update/response"
        WITH METHOD "POST"
        WITH ENDPOINT "/v4/spreadsheets/{value.json.spreadsheetId}/values:batchUpdate"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "Content-Type" "application/json"
            ADD "Authorization" "Bearer {value.json.api_token}"
        WITH BODY "{payload}"
    ADD EVENT Batchgetvalues
        WITH SOURCE_TOPIC "storage/google-sheets/values/batch-get"
        WITH DESTINATION_TOPIC "storage/google-sheets/values/batch-get/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/v4/spreadsheets/{value.json.spreadsheetId}/values:batchGet?{value.json.ranges_query}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "Authorization" "Bearer {value.json.api_token}"
    ADD EVENT Getspreadsheet
        WITH SOURCE_TOPIC "storage/google-sheets/spreadsheet/get"
        WITH DESTINATION_TOPIC "storage/google-sheets/spreadsheet/get/response"
        WITH METHOD "GET"
        WITH ENDPOINT "/v4/spreadsheets/{value.json.spreadsheetId}"
        WITH HEADERS
            ADD "Accept" "application/json"
            ADD "Authorization" "Bearer {value.json.api_token}"
```

## Auth

1. Enable **Google Sheets API** on a Google Cloud project.
2. Obtain an OAuth 2.0 user access token **or** a service-account access token (share the target spreadsheet with the service-account email).
3. Scope: `https://www.googleapis.com/auth/spreadsheets` (read/write). Use `.../spreadsheets.readonly` only if you stick to GET events.
4. Pass `api_token` on each MQTT payload → `Authorization: Bearer {value.json.api_token}`. Prefer Hub secrets / `GET SECRET "GOOGLE_SHEETS_TOKEN"` or `"GOOGLE_ACCESS_TOKEN"`. Never commit real tokens.

## MQTT topics

| Event | Source topic | Response topic |
| - | - | - |
| Getvalues | `storage/google-sheets/values/get` | `.../get/response` |
| Appendvalues | `storage/google-sheets/values/append` | `.../append/response` |
| Updatevalues | `storage/google-sheets/values/update` | `.../update/response` |
| Batchupdatevalues | `storage/google-sheets/values/batch-update` | `.../batch-update/response` |
| Batchgetvalues | `storage/google-sheets/values/batch-get` | `.../batch-get/response` |
| Getspreadsheet | `storage/google-sheets/spreadsheet/get` | `.../get/response` |

## Example payloads

**Append telemetry row** (body is `{payload}` — include Sheets `values` array):

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
{
  "api_token": "YOUR_OAUTH_OR_SA_ACCESS_TOKEN",
  "spreadsheetId": "YOUR_SPREADSHEET_ID",
  "range": "Sheet1%21A%3AA",
  "valueInputOption": "USER_ENTERED",
  "insertDataOption": "INSERT_ROWS",
  "values": [["2026-10-04T05:00:00Z", "line-a-sensor-01", 72.5, "OK"]]
}
```

**Update live cell:**

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
{
  "api_token": "YOUR_OAUTH_OR_SA_ACCESS_TOKEN",
  "spreadsheetId": "YOUR_SPREADSHEET_ID",
  "range": "Sheet1%21B2",
  "valueInputOption": "USER_ENTERED",
  "values": [["OK"]]
}
```

**Batch get** (`ranges_query` is the full query string after `?`):

```json theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
{
  "api_token": "YOUR_OAUTH_OR_SA_ACCESS_TOKEN",
  "spreadsheetId": "YOUR_SPREADSHEET_ID",
  "ranges_query": "ranges=Sheet1%21A1%3AB2&ranges=Sheet1%21D1%3AE2"
}
```

## Quotas and limits

Google Sheets API enforces [per-project quotas](https://developers.google.com/sheets/api/limits). Burst append/update from many devices can hit write quotas — batch or throttle at the edge when possible.

## Deploy notes

* Deploy **only** `GoogleSheetsRestApi.lot` with `-addRoute` / Hub deploy. Do **not** deploy `hub-metadata.lot`.
* Hub listing uses `hub-metadata.lot` (`WITH IMAGE`, `WITH STATUS "beta"`, doclink).
* Suggested branches (Hugo pushes): `feat/rest-api-route-google-sheets`, `feat/docs-rest-api-google-sheets`.

## Related

* [REST API Connectors](/latest/lot-language/routes/rest-api-routes)
* [REST API Apps catalog](/latest/lot-language/routes/rest-api-apps)
* [Google Sheets API reference](https://developers.google.com/sheets/api/reference/rest)
* Discovery: `https://sheets.googleapis.com/$discovery/rest?version=v4`


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.