Google Sheets

The io.b3os/google-sheets pack: 7 actions with call snippets, fixture outputs, connection setup and error codes, generated from version 1.0.0.

io.b3os/google-sheets 1.0.0 · spreadsheets, productivity · MIT

Read and write Google Sheets with your own Google OAuth token: spreadsheet metadata and tabs, values of one or several ranges, appending rows, updating and clearing ranges, and creating spreadsheets. Uses the Sheets API v4. Needs the spreadsheets scope (or spreadsheets.readonly for the read actions) from your own OAuth client.

Every call's result carries upstream_status and timing: validate_ms, inject_ms, render_ms, upstream_ms, map_ms and total_ms. upstream is the service's time; the others are B3OS's own (call actions). The snippets below show no timing values, because a fixture records none.

Connection

google, type oauth2: Google account (Sheets).

An OAuth access token for your Google account from your own Google Cloud OAuth client, with the scopes listed here. Store the refresh_token, client_id and client_secret with it so the vault can refresh the token (access tokens last an hour); B3OS never uses a Google client of its own.

How to get one: developers.google.com.

Scopes the pack's actions use: https://www.googleapis.com/auth/spreadsheets. The manifest names them per connection, not per action.

The credential is sent only to sheets.googleapis.com.

Store the credential once as a vault connection for this pack and key, then name its id in a call's connections. Leave the key out and B3OS uses the project's newest connection for the pack and key without a subject (connections).

Hosts it reaches: sheets.googleapis.com.

Actions

Action	Effect	MCP tool	Title
`get-spreadsheet`	read	`google-sheets__get-spreadsheet`	Get spreadsheet
`get-values`	read	`google-sheets__get-values`	Get values
`batch-get-values`	read	`google-sheets__batch-get-values`	Get values of several ranges
`append-rows`	write	`google-sheets__append-rows`	Append rows
`update-values`	write	`google-sheets__update-values`	Update values
`clear-values`	write	`google-sheets__clear-values`	Clear values
`create-spreadsheet`	write	`google-sheets__create-spreadsheet`	Create spreadsheet

`get-spreadsheet`

Get spreadsheet. A spreadsheet's title, locale, time zone, URL and tabs (sheet id, title, index, row and column count). Use the tab titles in ranges.

Ref io.b3os/google-sheets/get-spreadsheet@1.0.0 · effect read · connection google · MCP tool google-sheets__get-spreadsheet

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/get-spreadsheet@1",      "input": {"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"},      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/get-spreadsheet@1",  input: { spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms" },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },});console.log(result.output);

python
from b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/get-spreadsheet@1",    "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",    },    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},})print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__get-spreadsheet",    "arguments": {"spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"}  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__get-spreadsheet with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Get spreadsheet (documented response shape, example values). From the bundle's fixtures/get-spreadsheet.json, not a live call.

json
{  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",  "title": "Q4 pipeline",  "locale": "en_US",  "time_zone": "America/Los_Angeles",  "url": "https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit",  "sheets": [    {      "sheet_id": 0,      "title": "Deals",      "index": 0,      "row_count": 1000,      "column_count": 26    },    {      "sheet_id": 1522233,      "title": "Summary",      "index": 1,      "row_count": 100,      "column_count": 10    }  ]}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

`get-values`

Get values. Cell values of one range as rows (or columns with major_dimension COLUMNS). Formatted values by default (as shown in the UI); UNFORMATTED_VALUE gives raw numbers and FORMULA the formulas. Trailing empty rows and cells are left out.

Ref io.b3os/google-sheets/get-values@1.0.0 · effect read · connection google · MCP tool google-sheets__get-values

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/get-values@1",      "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Deals!A1:C3"      },      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/get-values@1",  input: {    spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",    range: "Deals!A1:C3",  },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },});console.log(result.output);

python
from b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/get-values@1",    "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Deals!A1:C3",    },    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},})print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__get-values",    "arguments": {      "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",      "range": "Deals!A1:C3"    }  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__get-values with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Get values (documented response shape, example values). From the bundle's fixtures/get-values.json, not a live call.

json
{  "range": "Deals!A1:C3",  "major_dimension": "ROWS",  "values": [    ["Company", "Stage", "Amount"],    ["Acme", "Proposal", "$12,000"],    ["Globex", "Won", "$48,500"]  ]}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

`batch-get-values`

Get values of several ranges. Cell values of up to 50 ranges in one call, in the order given, each with its resolved range and rows.

Ref io.b3os/google-sheets/batch-get-values@1.0.0 · effect read · connection google · MCP tool google-sheets__batch-get-values

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/batch-get-values@1",      "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "ranges": ["Deals!A1:A3", "Summary!B2"]      },      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/batch-get-values@1",  input: {    spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",    ranges: ["Deals!A1:A3", "Summary!B2"],  },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },});console.log(result.output);

python
from b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/batch-get-values@1",    "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "ranges": ["Deals!A1:A3", "Summary!B2"],    },    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},})print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__batch-get-values",    "arguments": {      "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",      "ranges": ["Deals!A1:A3", "Summary!B2"]    }  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__batch-get-values with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Get values of several ranges (documented response shape, example values). From the bundle's fixtures/batch-get-values.json, not a live call.

json
{  "value_ranges": [    {      "range": "Deals!A1:A3",      "values": [["Company"], ["Acme"], ["Globex"]]    },    {"range": "Summary!B2", "values": [["$60,500"]]}  ]}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

`append-rows`

Append rows. Append rows after the last row of data in a range's table (for example Sheet1!A:D), inserting new rows so nothing is overwritten. Returns the range written. Not idempotent: calling it twice appends twice.

Ref io.b3os/google-sheets/append-rows@1.0.0 · effect write · connection google · MCP tool google-sheets__append-rows

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Idempotency-Key: 7c1e2f6a-1d43-4a8e-b2a5-3f0c9d5e8b11" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/append-rows@1",      "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Deals!A:C",        "values": [["Initech", "Discovery", 9000]]      },      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/append-rows@1",  input: {    spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",    range: "Deals!A:C",    values: [["Initech", "Discovery", 9000]],  },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },}, { idempotencyKey: crypto.randomUUID() });console.log(result.output);

python
import uuidfrom b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/append-rows@1",    "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Deals!A:C",        "values": [["Initech", "Discovery", 9000]],    },    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},}, idempotency_key=str(uuid.uuid4()))print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__append-rows",    "arguments": {      "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",      "range": "Deals!A:C",      "values": [["Initech", "Discovery", 9000]]    }  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__append-rows with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Append rows (documented response shape, example values). From the bundle's fixtures/append-rows.json, not a live call.

json
{  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",  "updated_range": "Deals!A4:C4",  "updated_rows": 1,  "updated_columns": 3,  "updated_cells": 3}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

`update-values`

Update values. Overwrite the cells of a range with the given rows, starting at its top-left cell. Cells outside the given rows keep their values.

Ref io.b3os/google-sheets/update-values@1.0.0 · effect write · connection google · MCP tool google-sheets__update-values

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Idempotency-Key: 7c1e2f6a-1d43-4a8e-b2a5-3f0c9d5e8b11" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/update-values@1",      "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Deals!B2",        "values": [["Won"]]      },      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/update-values@1",  input: {    spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",    range: "Deals!B2",    values: [["Won"]],  },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },}, { idempotencyKey: crypto.randomUUID() });console.log(result.output);

python
import uuidfrom b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/update-values@1",    "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Deals!B2",        "values": [["Won"]],    },    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},}, idempotency_key=str(uuid.uuid4()))print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__update-values",    "arguments": {      "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",      "range": "Deals!B2",      "values": [["Won"]]    }  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__update-values with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Update values (documented response shape, example values). From the bundle's fixtures/update-values.json, not a live call.

json
{  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",  "updated_range": "Deals!B2",  "updated_rows": 1,  "updated_columns": 1,  "updated_cells": 1}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

`clear-values`

Clear values. Clear the values (not the formatting) of a range.

Ref io.b3os/google-sheets/clear-values@1.0.0 · effect write · connection google · MCP tool google-sheets__clear-values

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Idempotency-Key: 7c1e2f6a-1d43-4a8e-b2a5-3f0c9d5e8b11" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/clear-values@1",      "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Summary!A1:Z100"      },      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/clear-values@1",  input: {    spreadsheet_id: "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",    range: "Summary!A1:Z100",  },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },}, { idempotencyKey: crypto.randomUUID() });console.log(result.output);

python
import uuidfrom b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/clear-values@1",    "input": {        "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",        "range": "Summary!A1:Z100",    },    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},}, idempotency_key=str(uuid.uuid4()))print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__clear-values",    "arguments": {      "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",      "range": "Summary!A1:Z100"    }  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__clear-values with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Clear values (documented response shape, example values). From the bundle's fixtures/clear-values.json, not a live call.

json
{  "spreadsheet_id": "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms",  "cleared_range": "Summary!A1:Z100"}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

`create-spreadsheet`

Create spreadsheet. Create an empty spreadsheet with a title (and optionally named first tab) in the user's Drive. Returns its id and URL. Not idempotent.

Ref io.b3os/google-sheets/create-spreadsheet@1.0.0 · effect write · connection google · MCP tool google-sheets__create-spreadsheet

bash
curl -s https://beta-api.b3os.org/v1/actions/call \  -H "Authorization: Bearer $B3OS_API_KEY" \  -H "Idempotency-Key: 7c1e2f6a-1d43-4a8e-b2a5-3f0c9d5e8b11" \  -H "Content-Type: application/json" \  -d '{      "ref": "io.b3os/google-sheets/create-spreadsheet@1",      "input": {"title": "Launch tracker"},      "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"}    }'

ts
import { B3OS } from "@b3os/sdk";const b3os = new B3OS();const result = await b3os.actions.call({  ref: "io.b3os/google-sheets/create-spreadsheet@1",  input: { title: "Launch tracker" },  connections: { google: "conn_01m5t6cbgdjs1ketgcqwa2sne8" },}, { idempotencyKey: crypto.randomUUID() });console.log(result.output);

python
import uuidfrom b3os import B3OSb3os = B3OS()result = b3os.actions.call({    "ref": "io.b3os/google-sheets/create-spreadsheet@1",    "input": {"title": "Launch tracker"},    "connections": {"google": "conn_01m5t6cbgdjs1ketgcqwa2sne8"},}, idempotency_key=str(uuid.uuid4()))print(result["output"])

json
{  "method": "tools/call",  "params": {    "name": "google-sheets__create-spreadsheet",    "arguments": {"title": "Launch tracker"}  }}

Expose the pack as tools with --packs google-sheets on the local server or ?packs=google-sheets on the remote URL, and the agent calls google-sheets__create-spreadsheet with the action's input. The tool uses your newest connection for each key without a subject. Without exposing the pack, actions_call takes the same input with ref.

Output, labeled fixture: Create spreadsheet (documented response shape, example values). From the bundle's fixtures/create-spreadsheet.json, not a live call.

json
{  "spreadsheet_id": "1q2W3e4R5t6Y7u8I9o0PaSdFgHjKlZxCvBnM-_12345",  "url": "https://docs.google.com/spreadsheets/d/1q2W3e4R5t6Y7u8I9o0PaSdFgHjKlZxCvBnM-_12345/edit"}

When the service answers with an error, the call fails with upstream_error and pack_error.code is one of:

`pack_error.code`	Service status	Retryable	Message
`not_found`	404	no	built from the service's response
`connection_unauthorized`	401	no	Google rejected the access token (expired or revoked); refresh or reconnect it.
`rate_limited`	429, 403	yes	built from the service's response
`forbidden`	403	no	built from the service's response
`invalid_request`	400	no	built from the service's response
`conflict`	409	no	built from the service's response

Was this page helpful?

Previous Google Drive

Next Hacker News