Connect a Cosmic Agent to Google Sheets

Letting an agent write to a Google Sheet is a small capability with a surprisingly sharp setup. The auth is a service account, the API is public and well documented, and the create payload is three fields. The working version also calls a different Google API than the task description implies.

This is the setup end to end, written so you can follow it with your own Google account and your own Cosmic agent. If a step fails on you, the Troubleshooting section at the end covers what this path actually produces.

What you will have at the end

An agent that can:

• Create a new spreadsheet in a location your team owns
• Write multiple tabs into it in one pass, some tabular, some prose
• Share it with a human reviewer
• Read that spreadsheet back later, including whatever the human changed

That last item is what makes this worth wiring up. A one-way export is a file. A sheet the agent can re-read is a two-way surface between a run and a person.

You need a Google account that can create a service account and a Shared Drive, which in practice means Google Workspace rather than a personal Gmail account. You also need a Cosmic agent with the API request capability enabled.

Step 1: Set up the Google side

Four things, in this order, in the Google Cloud console.

1. Create a project, or pick an existing one. The API enablement and the service account below both happen inside this project.

2. Enable both APIs. Google Sheets API and Google Drive API. Both, because the call that actually creates the file is a Drive call.

3. Create a service account and download its JSON key. IAM and Admin, Service Accounts, Create Service Account, then Keys, Add Key, JSON. The file you get contains a client_email, a private_key, and the project identifiers. Treat it like a password. It is one.

4. Create a Shared Drive, then add the service account to it as a member. Google Drive, Shared drives, Create shared drive. Open Manage members, paste the service account's client_email, and grant it Content manager or Manager. Copy the Shared Drive ID out of the URL when you open the drive: it is the segment after /drive/folders/.

Step 2: Set up the Cosmic side

Three pieces, all of which live in your own Cosmic account.

Store the key as a secret. Ask your agent to save the service account JSON with manage_secrets. It gets stored encrypted and referenced by id, so it never appears in a prompt, a log, or a saved request body. This is the part people get wrong by pasting a private key into an agent instruction and hoping.

Save the Google calls as endpoints. Your agent can register saved API endpoints with default headers, so the Drive create, the Sheets batch update, the values write, and the read-back each become a named endpoint instead of a URL it has to reconstruct from memory every run. Reference the token in the default Authorization header as a secret placeholder rather than a literal.

Call them with api_request. From there the agent makes plain HTTPS calls to googleapis.com. There is no Cosmic-side Google integration doing anything clever underneath. Every call in the rest of this post is a call you could paste into curl.

One honest rough edge before you start. A service account authenticates by signing a JWT with RS256 and exchanging it at https://oauth2.googleapis.com/token for an access token that lasts an hour. The exchange is a plain HTTP POST your agent can make. The RS256 signing is not something an agent does with an HTTP tool alone. Two practical ways through it:

• For following this walkthrough: mint a token once from your own machine and store that token as a secret. gcloud auth print-access-token --impersonate-service-account=YOUR_SERVICE_ACCOUNT_EMAIL does it in one line, as does a five-line Node script using google-auth-library. It expires in an hour, which is plenty to work through these steps.
• For anything running on a schedule: put the signing in a small endpoint you own, have it return a fresh access token, and register that as one more saved endpoint the agent calls before the Google calls. Store its shared secret with manage_secrets the same way.

Request both scopes on every token, not just the one matching the call you think you are making:

https://www.googleapis.com/auth/spreadsheets
https://www.googleapis.com/auth/drive

Creation happens through Drive and writing happens through Sheets, so a token carrying both keeps a single token path across every call in this post. The broader scope is acceptable here because the identity holding it is a service account whose only access is the one Shared Drive you added it to.

Step 3: Create the file through the Drive API

Create the spreadsheet with a Drive call rather than sheets.spreadsheets.create, because a service account has no Drive storage quota of its own and therefore has to create the file inside a Shared Drive it has been added to as a member.

This is the call:

POST https://www.googleapis.com/drive/v3/files?supportsAllDrives=true&fields=id,webViewLink
Authorization: Bearer <token>
Content-Type: application/json

{
  "name": "Weekly content report",
  "mimeType": "application/vnd.google-apps.spreadsheet",
  "parents": ["YOUR_SHARED_DRIVE_ID"]
}

The response gives you an id, which is the spreadsheet id every later Sheets call uses, and a webViewLink, which is the URL you hand to a human.

Three parts of that request carry weight. mimeType is what makes Drive mint a native Google Sheets file the Sheets API can address by id, rather than an opaque blob. parents assigns the file to the Shared Drive, so the Drive owns it and the service account is simply the thing that created it. supportsAllDrives=true is required on every Drive call that touches a file in a Shared Drive, including this one and the permissions call below.

Step 4: Write multiple tabs, then read the edits back

The first tab exists as soon as the file does. Every additional tab is two calls.

Add the sheet:

POST https://sheets.googleapis.com/v4/spreadsheets/<id>:batchUpdate
Authorization: Bearer <token>
Content-Type: application/json

{
  "requests": [
    { "addSheet": { "properties": { "title": "Code snippets" } } }
  ]
}

Then write values into it:

PUT https://sheets.googleapis.com/v4/spreadsheets/<id>/values/Code%20snippets!A1?valueInputOption=RAW
Authorization: Bearer <token>
Content-Type: application/json

{
  "values": [["Slug", "Status"], ["pricing", "live"]]
}

values is an array of rows, each row an array of cells. For narrative content rather than a table, split the prose on blank lines and make each paragraph a single-cell row, which puts one paragraph per row in column A.

Two practical notes. Sheet names with spaces have to be URL-encoded in the range, which is the Code%20snippets!A1 above. And when you are writing a dozen tabs, collect per-tab failures rather than aborting the whole job on the first bad one. A partial write that reports what it could not do beats losing eleven good tabs because the twelfth had an invalid name.

Share it with a human using the Drive permissions endpoint:

POST https://www.googleapis.com/drive/v3/files/<id>/permissions?supportsAllDrives=true&sendNotificationEmail=false
Authorization: Bearer <token>
Content-Type: application/json

{ "role": "writer", "type": "user", "emailAddress": "[e-mail]" }

Note supportsAllDrives=true again. It is required on this call for the same reason it was required on the create.

Then read the whole thing back, including whatever the human changed:

GET https://sheets.googleapis.com/v4/spreadsheets/<id>?includeGridData=true&fields=properties.title,sheets(properties.title,data.rowData.values.formattedValue)
Authorization: Bearer <token>

The narrow fields mask is the detail worth copying. Without it, includeGridData=true returns the entire grid including every formatting property on every cell, and the payload gets very large very fast on a sheet a human has been editing for a week. With the mask you get titles and formattedValue strings and nothing else. Normalize missing cells to an empty string on your side so whatever consumes this never has to null-check a cell.

That read-back closes the loop. Your agent wrote a draft, a person edited it in a tool they already had open with no new login and no new app, and the next run can see exactly what they left behind.

Three things this makes possible

Creating a file is a small capability with a large operational consequence attached. The destination has to be a Shared Drive the team owns, because the service account cannot own anything itself. That same constraint shows up in any content operation spanning many properties: one shared credential writing into one owned destination, rather than a separate identity and a separate orphaned folder per property.

1. A draft handoff a human can actually edit

An agent writes a draft into a sheet and shares it with a reviewer. The reviewer edits in place. A later run reads the spreadsheet back and picks up exactly what the human left behind. The read-back is what turns the sheet into a two-way surface instead of an export.

2. Structured exports that are not one flat table

Most real reports have more than one shape in them. A content audit has a table of pages with statuses, and it also has a pile of prose that does not fit in a grid. Multi-tab writing handles both in one pass: rows for the tabular tab, paragraph-per-row for the narrative tab. Collecting per-tab failures means a twelve-tab export that hits one bad tab name still delivers eleven tabs and tells you which one it dropped.

3. One credential across many properties

This is the use worth the most attention, and it is where the Shared Drive constraint stops being an annoyance and starts being the point.

A team running five to twenty distinct web properties has the same reporting job repeated per property: pull what changed, write it somewhere a human will actually read, keep the history. The naive version gives every property its own credential, its own destination folder, and its own half-remembered setup. Six months later nobody knows which service account owns which folder, and the person who configured property nine has left the company.

Creating through Drive with an explicit parents forces the better shape by default. The file is owned by a Shared Drive from the moment it exists. Access is governed by the Drive permissions the team already maintains, not by whoever happens to hold the key. Removing someone from the Drive removes them from every sheet the agent has ever written, across every property, without touching the agent or rotating anything. Adding a property means passing a different parent folder id on the request and provisioning no new identity at all.

The read-back compounds this. Once each sheet is a real addressable destination with a stable id, a run next week can read what a human changed this week and act on it, which holds across properties and across time rather than only within a single job. If your team is already running a dozen properties, the same consolidation argument applies one level up to the content layer itself: Workspaces puts every brand on one bill and one team.

Where this stops is worth naming plainly. The capability is that an agent can create, write to, share, and read a spreadsheet in a location the team owns. What gets built on top of it, whether per-property reporting, a review queue, or a staging area for bulk import, is a workflow decision you make. The setup guarantees the destination is owned by the team and reachable by the next run, and deliberately assumes nothing beyond that.

If you are wiring agents into systems that write, the same question applies to your content layer: what exactly is the credential allowed to do, and can you verify it rather than assume it. We wrote up the Cosmic answer in AI Agent Write Access: The 4 Real Controls You Get With Your CMS, including the two controls we do not ship yet. The broader picture of agents reading and writing structured content is on the Cosmic for AI teams page, and you can start on the Free plan, no credit card required, and give an agent its first saved endpoint in about ten minutes.

Troubleshooting

403 PERMISSION_DENIED on POST /v4/spreadsheets. The service account has no Drive storage of its own, so it cannot own the file that call is asking Google to create. Create the spreadsheet through the Drive API instead, into a Shared Drive the service account has been added to as a member, and pass supportsAllDrives=true.

A reviewer cannot open the sheet. The file inherits the permissions of the Shared Drive it was created in, so anyone who is not a member of that Drive has no access until you share the file explicitly. Grant it with the Drive permissions call in Step 4.

A write lands somewhere other than the tab you expected. The tab name in the range has to match a sheet that already exists, and a range with no tab name goes to the first sheet in the file. Add the tab with batchUpdate before writing to it, and URL-encode any spaces in the name.