Google Sheets - Charter Docs

17 toolsOAuth

gsheets_example.py

import asyncio

from charter.auth import EnvTokenProvider
from charter.packs import gsheets

credentials = EnvTokenProvider("GOOGLE_ACCESS_TOKEN")
gsheets.configure(credentials)


async def main():
    rows = await gsheets.spreadsheets_values_get.ainvoke(
        spreadsheet_id="1abc", range="Sheet1!A1:D20"
    )


asyncio.run(main())

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

Before the first call, Google Sheets needs a Google OAuth client. Google issues no API key for it: credentials come only from an app you register in the Cloud console. Set up Google walks through every screen — about ten minutes, done once, and the same client serves all six Google packs. This pack takes a Google OAuth bearer token, and configure() is optional when a renewable grant or $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 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 re-reads the variable on every call, so a token rotated beside the process is picked up without a restart; StaticTokenProvider takes one you already hold as a string. Neither renews anything, so the calls stop when the token expires.

gsheets_script.py

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 turns a client registration and a stored refresh token into an access token, and renews it before it lapses.

gsheets_agent.py

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? Set up Google is the one-time consent flow that hands you one.

Many end users

For a product whose users each connect their own account. SubjectProvider builds one credential per user through a factory you write, and use_subject names the user a call acts for. Your users’ accounts is the consent route inside your app; serving many users is the per-subject cache and its eviction. The client is handed a function that reads the stored grant, not a copy of the refresh token. A server runs several workers, and a copy goes stale the first time another worker refreshes: More than one process.

gsheets_server.py

from functools import partial

from charter.auth import OAuth2Client, SubjectProvider, TokenGrant, 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:
    async def stored_grant() -> TokenGrant:  # read again whenever the token is due
        row = await db.grants.get(user_id, "google")
        return TokenGrant(
            access_token=row.access_token,
            refresh_token=row.refresh_token,
            expires_at=row.expires_at,
        )

    return OAuth2Client(
        GOOGLE,
        client_id=os.environ["GOOGLE_CLIENT_ID"],
        client_secret=os.environ["GOOGLE_CLIENT_SECRET"],
        refresh_token=stored_grant,
        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 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.

from charter import oauth_tool_factory

gsheets_api_client = oauth_tool_factory(
    base_url="https://sheets.googleapis.com/",
    provider="google",
    credential_provider=credentials,
    pack="gsheets",
    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,
)

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. A pack takes it through configure(); a client you build takes the same object as credential_provider, and has no configure() of its own.

Tools

Each is a Tool, called with ainvoke as in the snippet above. The name links to its parameters, its response and what it costs.

Valuesspreadsheets_values_getGETReturns a range of values from a spreadsheet.spreadsheets_values_updatePUTSets values in a range of a spreadsheet.spreadsheets_values_appendPOSTAppends values to a spreadsheet.spreadsheets_values_clearPOSTClears values from a spreadsheet.spreadsheets_values_batch_getGETReturns one or more ranges of values from a spreadsheet.spreadsheets_values_batch_updatePOSTSets values in one or more ranges of a spreadsheet.spreadsheets_values_batch_clearPOSTClears one or more ranges of values from a spreadsheet.values_batch_get_by_data_filterPOSTReturns one or more ranges of values that match the specified data filters.values_batch_update_by_data_filterPOSTSets values in one or more ranges of a spreadsheet.values_batch_clear_by_data_filterPOSTClears one or more ranges of values from a spreadsheet.Spreadsheetsspreadsheets_createPOSTCreate a new spreadsheet.spreadsheets_getGETGet spreadsheet metadata and structure.spreadsheets_batch_updatePOSTApply a list of updates to a spreadsheet.spreadsheets_sheets_copy_toPOSTCopy a single sheet from one spreadsheet to another.spreadsheets_get_by_data_filterPOSTGet a spreadsheet, selecting which ranges to return with DataFilters (an A1 range, a GridRange, or developer metadata).Developer metadataspreadsheets_developer_metadata_getGETGet one developer metadata entry by its spreadsheet-scoped ID.spreadsheets_developer_metadata_searchPOSTFind developer metadata matching one or more DataFilters.

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:

gsheets_append_rows.py

import asyncio


async def main():
    await gsheets.spreadsheets_values_append.ainvoke(
        spreadsheet_id="1abc",
        range="Sheet1!A1",
        value_input_option="USER_ENTERED",
        value_range={"values": [["Ada", 36], ["Grace", 45]]},
    )


asyncio.run(main())

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.

Gotchas

value_input_option decides whether a formula is a formula

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.

Append writes after the last row of a detected table, not at the range

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.

Nothing in this pack pages

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.

Range is a path parameter, so it is URL-encoded

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.

spreadsheets_create makes the shell, not the contents

Its one argument is spreadsheet, the same resource spreadsheets_get returns, narrowed to the spreadsheet’s properties and each sheet’s properties: a title, a locale, a time zone and the tabs it starts with. There is no flat title shortcut. Values go in afterwards with the values tools, and formatting with spreadsheets_batch_update. The whole resource, initial grid data and charts included, is 31,961 tokens of schema against 2,532, so it is on gsheets.spreadsheets_create_full, which TOOLS leaves out.

Google — consent screen, scopes, refresh
Transforms — proto_json and field_mask in detail

​Authenticating

​A token you hold

​One account, refreshed

​Many end users

​The client

​Tools

​The two transforms

​Gotchas

​Related

Authenticating

A token you hold

One account, refreshed

Many end users

The client

Tools

The two transforms

Gotchas

Related