Google Sheets

Technical Preview

Overview

A client-mode connector for a curated slice of Google Sheets API v4 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.

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).

valueInputOption: Append and Update require query valueInputOption=RAW or USER_ENTERED. BatchUpdate places valueInputOption in the JSON body with data (array of ValueRange).

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

# 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

Enable Google Sheets API on a Google Cloud project.
Obtain an OAuth 2.0 user access token or a service-account access token (share the target spreadsheet with the service-account email).
Scope: https://www.googleapis.com/auth/spreadsheets (read/write). Use .../spreadsheets.readonly only if you stick to GET events.
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):

{
  "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:

{
  "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 ?):

{
  "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. 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.

REST API Connectors
REST API Apps catalog
Google Sheets API reference
Discovery: https://sheets.googleapis.com/$discovery/rest?version=v4

Quick Start

Coreflux MQTT Broker

LoT Language

Coreflux HUB

VS Code - LoT Notebooks

Coreflux Portal

Using AI with Coreflux

Product Updates

Overview

Basic Syntax

Auth

MQTT topics

Example payloads

Quotas and limits

Deploy notes

​Overview

​Basic Syntax

​Auth

​MQTT topics

​Example payloads

​Quotas and limits

​Deploy notes

​Related

Overview

Basic Syntax

Auth

MQTT topics

Example payloads

Quotas and limits

Deploy notes

Related