> ## Documentation Index
> Fetch the complete documentation index at: https://docs.r28.ai/charter/llms.txt
> Use this file to discover all available pages before exploring further.
# Google Sheets
> Read, write, append and clear ranges, create a spreadsheet, and edit its structure.
```python gsheets_example.py {7} theme={null}
from charter.auth import EnvTokenProvider
from charter.packs import gsheets
credentials = EnvTokenProvider("GOOGLE_ACCESS_TOKEN")
gsheets.configure(credentials)
rows = await gsheets.spreadsheets_values_get.ainvoke(
spreadsheet_id="1abc", range="Sheet1!A1:D20"
)
```
Read a range, write or append rows, clear cells, create a spreadsheet, read its
structure, and apply structural or formatting edits in a batch. Ranges are A1
notation — `Sheet1!A1:C10` — and on get, update, append and clear they travel in
the URL path.
Sheets speaks protobuf JSON, which is a poor thing to hand a model. Two `Format`
markers absorb that: the model sends a plain 2-D array and a list of field paths,
and the API receives the encodings it expects.
## Authenticating
This pack takes a Google OAuth bearer token, and [`configure()`](/charter/charter/reference/configuration#configure) is optional when `$GOOGLE_ACCESS_TOKEN` is set. Which credential provider you hand it depends on whose account the calls run as.
Refer to [Google's provider page](/charter/charter/auth/providers/google) for the "GOOGLE" constant the snippets below name, the scopes these 17 tools ask for, and this server's refresh behaviour.
### A token you hold
For a script, or a notebook. [`EnvTokenProvider`](/charter/charter/reference/credentials#envtokenprovider) re-reads the variable on every call, so a token rotated beside the process is picked up without a restart; [`StaticTokenProvider`](/charter/charter/reference/credentials#statictokenprovider) takes one you already hold as a string. Neither renews anything, so the calls stop when the token expires.
```python gsheets_script.py {4} theme={null}
from charter.auth import EnvTokenProvider
from charter.packs import gsheets
credentials = EnvTokenProvider("GOOGLE_ACCESS_TOKEN")
gsheets.configure(credentials)
```
### One account, refreshed
For an agent or a server acting as you. [`OAuth2Client`](/charter/charter/reference/oauth#oauth2client) turns a client registration and a stored refresh token into an access token, and [renews it](/charter/charter/auth/authorization-servers#what-the-cache-does-precisely) before it lapses.
```python gsheets_agent.py {8} theme={null}
from charter.auth import OAuth2Client
from charter.packs import gsheets
credentials = OAuth2Client(
GOOGLE,
client_id=os.environ["GOOGLE_CLIENT_ID"],
client_secret=os.environ["GOOGLE_CLIENT_SECRET"],
refresh_token=os.environ["GOOGLE_REFRESH_TOKEN"],
)
gsheets.configure(credentials)
```
No refresh token yet? [Your own account](/charter/charter/auth/your-own-account) is the one-time consent flow that hands you one.
### Many end users
For a product whose users each connect their own account. [`SubjectProvider`](/charter/charter/reference/credentials#subjectprovider) builds one credential per user through a factory you write, and [`use_subject`](/charter/charter/reference/credentials#use_subject) names the user a call acts for. [Your users' accounts](/charter/charter/auth/oauth-flow) is the consent route inside your app; [serving many users](/charter/charter/auth/authorization-servers#serving-many-users) is the per-subject cache and its eviction.
```python gsheets_server.py {17,21} theme={null}
from functools import partial
from charter.auth import OAuth2Client, SubjectProvider, use_subject
from charter.packs import gsheets
# Yours to write: a user id in, that user's credential out.
async def for_user(user_id: str) -> OAuth2Client:
grant = await db.grants.get(user_id, "google")
return OAuth2Client(
GOOGLE,
client_id=os.environ["GOOGLE_CLIENT_ID"],
client_secret=os.environ["GOOGLE_CLIENT_SECRET"],
refresh_token=grant.refresh_token,
on_refresh=partial(save_to_db, user_id),
)
credentials = SubjectProvider(for_user)
gsheets.configure(credentials)
# Per request: whose grant the tools use.
with use_subject(request.user_id):
... # your agent runs here
```
The scope covers every spreadsheet the account can open; Sheets has no
per-file scope, so narrowing access is a matter of which account you delegate,
not which scope you ask for.
## The client
[`oauth_tool_factory`](/charter/charter/reference/factories#oauth_tool_factory) is the whole client: a thin wrapper over `httpx` that attaches your token and these endpoint constants to each request. `google-api-python-client` and `google-auth` do not enter your dependency tree.
```python Without the pack theme={null}
from charter import oauth_tool_factory
gsheets_api_client = oauth_tool_factory(
base_url="https://sheets.googleapis.com/",
provider="google",
credential_provider=credentials,
scopes=["https://www.googleapis.com/auth/spreadsheets"],
body_format="json",
query_format="repeat",
body_case="camel",
query_case="camel",
path_case="snake",
# this API reports failure with an HTTP status code
envelope=None,
)
```
```python With the pack theme={null}
from charter.packs import gsheets
gsheets.configure(credentials)
# The base URL, the casing, the envelope and the pagination are
# already declared. 17 tools, ready to hand to a model:
tools = gsheets.TOOLS
```
`credentials` is whichever of the three you built in [Authenticating](#authenticating). A pack takes it through [`configure()`](/charter/charter/reference/configuration#configure); a client you build takes the same object as `credential_provider`, and has no `configure()` of its own.
## Tools
Each is a [`Tool`](/charter/charter/reference/tool), called with
[`ainvoke`](/charter/charter/reference/tool#tool-ainvoke) as in the snippet above. The name
links to its parameters, its response and what it costs.
Every tool declares `quota_cost` 1. Sheets meters requests per minute per user
rather than charging different amounts per call, so a uniform 1 is the honest
number; the link in the wire table is where the real limits live.
## The two transforms
`ValueRange.values` is marked `Format("proto_json")`. The model sends what anyone
would write by hand:
```python gsheets_append_rows.py theme={null}
await gsheets.spreadsheets_values_append.ainvoke(
spreadsheet_id="1abc",
range="Sheet1!A1",
value_input_option="USER_ENTERED",
value_range={"values": [["Ada", 36], ["Grace", 45]]},
)
```
and the transform produces the protobuf-JSON encoding Sheets requires on the way
out, and reverses it on the way back.
`fields` on the `batchUpdate` requests is marked `Format("field_mask")`. A
`FieldMask` is a comma-joined string on the wire and a list of paths in the
schema, so the model sends `["userEnteredValue", "userEnteredFormat"]` and Sheets
receives `userEnteredValue,userEnteredFormat`. See [transforms](/charter/charter/tools/transforms).
## Gotchas
`RAW` writes `=SUM(A1:A2)` as the literal nine characters. `USER_ENTERED`
parses it the way typing it into the cell would — which also means `1/2`
becomes a date and a leading `+` becomes a formula. The argument is required
on `spreadsheets_values_append`, `spreadsheets_values_update` and the batch
writes, deliberately: there is no safe default, and the wrong one is silent.
The `range` you pass to `spreadsheets_values_append` is a *search hint*:
Sheets finds the table that overlaps it and appends below that table's last
row. Passing `Sheet1!A1` does not mean "write at A1". To write at an exact
address, use `spreadsheets_values_update`.
Sheets has no cursor. A large sheet is read by asking for a narrower A1
range, which is why no tool here declares a `Pagination` — and why
`next_page_args` on these tools would have nothing to return.
`Sheet1!A1:C10` goes into the path, and a sheet name containing a space or an
apostrophe needs Sheets' own quoting — `'My Sheet'!A1:C10`. The escaping for
the URL is handled; the quoting for Sheets' grammar is yours.
Its one argument is `spreadsheet`, the same resource `spreadsheets_get`
returns. Titles, sheet tabs and initial data all go inside it; there is no
flat `title` shortcut.
## Related
* [Google](/charter/charter/auth/providers/google) — consent screen, scopes, refresh
* [Transforms](/charter/charter/tools/transforms) — `proto_json` and `field_mask` in detail