# Let Claude edit your Google Sheets and Google Docs — setup handoff **What this is:** a one-time setup that gives Claude Code the ability to actually *write* to your Google Sheets and Google Docs (add rows, update cells, clear ranges; find and replace text, replace or insert or delete paragraphs) instead of just reading them. It takes about 15 minutes, most of which Claude does for you by driving your browser. **How to use this file:** you don't need to read the rest of it. Save it somewhere on your computer, open Claude Code, and paste this: ``` Read the file and walk me through it, doing every step you can yourself. ``` Claude will handle the rest and stop to ask you at the three points that genuinely need a human. --- ## What you'll end up with A tiny Google Apps Script web app running under your own Google account, plus a Claude Code skill that knows how to call it. After setup, you can say things like "add a row to my Q3 tracker with these values" or "update the status cell for the Acme row to Shipped" or "in my project brief, change every 2025 to 2026" and Claude does it directly. It can edit **any spreadsheet or Google Doc your Google account can already edit**, including files other people own and have shared with you as an editor. There is no per-file setup after this. **The safety design, in plain terms:** - The web app only accepts a fixed list of operations. There is no way to make it run arbitrary code. - Every request must carry a secret token that only you and your Claude Code shell know. Requests without it are rejected. - Every change is logged, with the old cell values or the old paragraph text, to a "Claude Sheets Audit" spreadsheet that appears in your Drive. Accidental overwrites are recoverable by hand. - Every Doc edit aimed at a paragraph must quote text that paragraph currently contains, or the script refuses. Find-and-replace can be told how many matches to expect and refuses the whole edit if the count differs. - Values starting with `=` are rejected unless explicitly allowed, so a write can never silently plant a formula. - Reads are capped at 20,000 cells and writes at 5,000 cells per call. - You can revoke the whole thing instantly by deleting the deployment or changing the token. **The honest tradeoff:** the web app runs with your full Sheets/Docs/Drive/Calendar authority. The operation whitelist and the token are the entire security boundary. Treat the token like a password. If that's not acceptable for your account, don't set this up. --- ## What you need first 1. **Claude Code** installed and working on your machine. 2. **Chrome, signed in to the Google account that owns the sheets and docs you want to edit**, plus the Claude in Chrome extension. Claude drives the Google setup screens through your real Chrome session, which is the part that makes this fast. 3. About 15 minutes, most of it watching. If you don't have Claude in Chrome, this still works — Claude will read you the click-by-click steps instead of clicking them. It just takes longer. ## Why Claude Code, not Claude Chat or Cowork The finished setup is a shell command. Claude Code has a real shell on your machine, so it can store your credentials in your shell profile, write the skill file into `~/.claude/skills/`, run the calls, and retry quickly when something fails. Claude Chat has no shell at all, so it can neither set this up nor use it afterward. Cowork runs in a sandboxed environment where outbound calls and your local shell profile are not reliably available, so it's a poor fit for both the setup and the day-to-day use. Set it up in Claude Code, and use it from Claude Code. --- --- # INSTRUCTIONS FOR CLAUDE Everything below is addressed to Claude Code. Follow it in order. ## Ground rules **Do as much as you can yourself.** The user should only have to act at three moments, listed in the next section. Everything else is yours: writing files, driving the browser, generating the token, editing the shell profile, and running the verification tests. **Use Claude in Chrome (`mcp__claude-in-chrome__*`) for every step on `script.google.com`.** Those pages require the user's logged-in Google session, which only their real Chrome has. The Claude Code built-in browser (`mcp__Claude_Browser__*`) will hit a sign-in wall there. If the Chrome tools are deferred, load them in ONE `ToolSearch` call: ``` ToolSearch with query "select:mcp__claude-in-chrome__tabs_context_mcp,mcp__claude-in-chrome__navigate,mcp__claude-in-chrome__computer,mcp__claude-in-chrome__read_page,mcp__claude-in-chrome__find,mcp__claude-in-chrome__form_input,mcp__claude-in-chrome__tabs_create_mcp,mcp__claude-in-chrome__javascript_tool" ``` If Claude in Chrome is unavailable, say so plainly and fall back to reading the user the exact click path for each step. Do not silently switch surfaces. **Never print, echo, or log the secret token.** Not in chat, not in a file, not in command output. It goes from `openssl` to the clipboard to two places (the Apps Script UI and the user's shell profile) without ever being displayed. **Never type the token into a browser field yourself.** Put it on the clipboard and have the user paste it. This is a hard rule, not a preference. **Confirm before you overwrite anything** in the user's shell profile or an existing `~/.claude/skills/gsheets-api/` directory. ## The three moments a human must act Tell the user these up front so they know when to pay attention: 1. **Pasting the secret token** into the Apps Script "Script property" value field (you put it on their clipboard; they press paste). 2. **Clicking through the OAuth authorization**, including the "unverified app" warning screen. Google will not let an automated click through this, and the consent window opens outside the tabs Claude can see. 3. **Confirming the write test** on a scratch spreadsheet at the end. --- --- ## Platform notes — read this before Phase 0 **The phases below are written for and tested on macOS.** On any other platform, do not assume the commands transfer. Work out the equivalents on the actual machine and verify each one empirically as you go. Detect the platform first: ```bash uname -s; grep -qi microsoft /proc/version 2>/dev/null && echo WSL; echo "(detection done)" ``` `Darwin` means macOS: follow the phases exactly as written. Anything else, follow the protocol below. ### Working out a non-macOS setup Say this to the user before you start, in your own words: the macOS path for this is tested, theirs isn't, so you'll be working out the equivalent commands on their machine and checking each one as you go. It may take longer and you may ask them to confirm things. Don't present guesses as established steps. **Work through this one step at a time, with the user.** Before each command, say what you're about to try and what you expect to happen. Run it. Show them the actual result. Confirm it did what you wanted before moving to the next step. Do not batch several unverified commands together, and do not move on from a step that didn't clearly succeed. If something fails twice, stop and reason about it with the user rather than trying a third variation. Four steps are platform-dependent. For each one the **goal** is fixed; the mechanics are yours to determine on this machine. Never assume a command worked because it exited cleanly. Prove it. **1. Getting `Code.gs` into the Apps Script editor (Phase 3).** Goal: the file arrives complete and unmangled. The preferred path in Phase 3 (setting the editor contents through Monaco with the Claude in Chrome `javascript_tool`) is platform-independent and needs no clipboard; try it first. If it is unavailable, work out how the clipboard is driven here. If you can't establish a reliable path in two attempts, stop trying and ask the user to open `~/.claude/skills/gsheets-api/apps-script/Code.gs` in their editor and press select-all then copy. That always works and costs ten seconds; it is not a failure to fall back to it. The verification is mandatory and platform-independent: checksum the editor contents against the local file as described in Phase 3, or at minimum read the editor content back and confirm the first line is `/**` and that `function doPost(e)`, `case 'set_link':` and `case 'doc_get_text':` are all present. The file also contains 42 em dashes, all inside comments and error strings. If those arrive garbled, behavior won't break, but it means the encoding path is wrong and you should switch to a manual copy rather than press on. **2. Persisting the token and URL (Phases 4 and 5).** Goal: `GSHEETS_API_TOKEN` and `GSHEETS_API_URL` are readable by **future** Claude Code Bash sessions, and the token is never displayed. Determine where this machine's shell actually reads environment variables from. Do not assume a profile file is sourced just because it exists; confirm it. If a shell profile turns out not to be reliable here, find the mechanism that is. The token rules do not change on any platform: never print or log it, never type it into a browser field yourself, and have the user paste it into the Apps Script field. **3. Reloading (Phase 6).** Goal: prove both variables are readable **in a fresh shell** before you run any of Phase 7. Whatever mechanism you used, verify with `echo "url:${GSHEETS_API_URL:+ok} token:${GSHEETS_API_TOKEN:+ok}"`. If it needs a full Claude Code restart rather than sourcing a file, say so plainly and wait for the user. **4. Browser keyboard shortcuts.** Ctrl instead of Cmd throughout. ### One hazard that is not platform guesswork The web app compares the token exactly. Any stray whitespace or carriage return picked up while moving it between programs produces `{"ok":false,"error":"unauthorized"}`, which is indistinguishable from a wrong token and will send you hunting in the wrong place. If auth fails and you believe the token is correct, check its length before suspecting anything else. This prints the length without revealing the value: ```bash printf %s "$GSHEETS_API_TOKEN" | wc -c ``` It must be exactly 48. Anything else means the value got corrupted in transit, not that the token is wrong. ### Close the loop If you work out a setup that succeeds on a platform this document doesn't cover, tell the user to send the working commands back to whoever shared this file, so the next person on that platform doesn't have to rediscover them. ## Phase 0 — Orient Ask the user, in one message: - Which Google account owns the sheets and docs they want to edit (get the full email address). This matters more than anything else in the setup, because Chrome may be signed into several accounts and the script must be created under the right one. - Whether they want the Calendar operations (`attach_to_event`, `strip_meet`). These attach Drive files to calendar events and remove Google Meet links. If they don't care, skip the advanced-service step in Phase 3 and say so; the sheet operations work fine without it. - Whether they're on a work Google Workspace account. If so, warn them now: some Workspace admins block web apps deployed with "Anyone" access. You'll find out for certain in Phase 5, and there's a fallback there. Then detect the platform (see **Platform notes** above) and check the ground state in the same call: ```bash uname -s; grep -qi microsoft /proc/version 2>/dev/null && echo WSL; ls -d ~/.claude/skills/gsheets-api 2>/dev/null; echo "GSHEETS_API_URL is ${GSHEETS_API_URL:+set}${GSHEETS_API_URL:-empty}" ``` State which platform branch you're following before you start Phase 1, so the user can correct you if it's wrong. If the directory already exists or the env var is already set, stop and ask before proceeding — they may already have this. ## Phase 1 — Write the local files Create `~/.claude/skills/gsheets-api/apps-script/` and write `Code.gs` from **Appendix A** of this document, verbatim, with one substitution: replace `YOUR_EMAIL_HERE` in `ALLOWED_CALENDARS` with the email address from Phase 0. Then write `~/.claude/skills/gsheets-api/SKILL.md` from **Appendix B**, verbatim. Confirm the files landed and the line count of `Code.gs` looks right (about 840 lines). ## Phase 2 — Create the Apps Script project Navigate Chrome to `https://script.google.com`. **Before doing anything else, verify the active Google account.** Read the page and check the account avatar or the account menu in the top right. If it isn't the address from Phase 0, stop and tell the user to switch accounts in Chrome, then retry. Creating the project under the wrong account is the single most common way this setup goes wrong, and it is not obvious later. Then: **New project**. Rename it from "Untitled project" to `gsheets-api` by clicking the title at the top left. ## Phase 3 — Paste the code **Do not type the file character by character.** It's ~840 lines and the editor auto-inserts closing brackets while typing, which will corrupt it. Set the editor contents directly through Monaco (preferred), or use the clipboard (fallback). **Preferred: Monaco `setValue` through `javascript_tool`.** The Apps Script editor exposes its Monaco editor at `window.monaco`, and the Claude in Chrome `javascript_tool` runs in the page, so the file can be set in one call with nothing retyped. Generate the call from the local file: ```bash python3 -c "import json,os; s=open(os.path.expanduser('~/.claude/skills/gsheets-api/apps-script/Code.gs'),encoding='utf-8').read(); print('window.monaco.editor.getModels()[0].setValue(' + json.dumps(s, ensure_ascii=False) + ')')" ``` Run that output as the `javascript_tool` text in the Apps Script tab (click into the editor pane first so the project is loaded). If one call is too large for the tool, split the file at function boundaries and apply the pieces as anchored replacements: for each `[old, new]` pair, assert `v.split(old).length === 2`, then `v = v.split(old).join(new)`. Use split/join rather than `String.replace`, so a `$` in the code cannot be misread as a replacement pattern. **Verify with a checksum, not by eye.** Compute the same checksum on both sides (iterate characters, `t = (t * 31 + codePoint) % 4294967296`) and require the two numbers to match. Locally: ```bash python3 - <<'PY' import os s = open(os.path.expanduser('~/.claude/skills/gsheets-api/apps-script/Code.gs'), encoding='utf-8').read() t = 0 for ch in s: t = (t * 31 + ord(ch)) % 4294967296 print(len(s), t) PY ``` In the editor, via `javascript_tool`: ``` (() => { const v = window.monaco.editor.getModels()[0].getValue(); let t = 0; for (const ch of v) { t = (t * 31 + ch.codePointAt(0)) % 4294967296; } return {len: v.length, cks: t}; })() ``` Then click once into the editor body, save with `cmd+s` / `ctrl+s`, confirm the header reads "Saved to Drive" (not "Unsaved changes"), and run the editor checksum once more. From here on, keep the local `Code.gs` byte-identical to what was saved; the same checksum check is how a future edit proves the deployed script has not drifted before overwriting it. **Fallback: the clipboard.** Immediately before pasting (not earlier — the user may copy something else in between and clobber it): ```bash LANG=en_US.UTF-8 pbcopy < ~/.claude/skills/gsheets-api/apps-script/Code.gs ``` `pbcopy` is macOS-only. On any other platform, see **Platform notes** and work out the clipboard path there, falling back to a manual copy rather than fighting it. Then in Chrome: click into the code editor pane, select all (`cmd+a` / `ctrl+a`), and paste (`cmd+v` / `ctrl+v`). **Verify what actually landed.** Read the editor content back and confirm the first line is `/**` and that the file contains `function doPost(e)`, `case 'set_link':` and `case 'doc_get_text':`, or better, run the checksum comparison above. A clipboard race here is a known failure: if the content is wrong or partial, re-copy and paste again. If the keystroke paste doesn't work at all, tell the user the code is on their clipboard and ask them to click the editor, select all, and paste manually. Save with `cmd+s` / `ctrl+s`. **If the user wants the Calendar operations** (Phase 0): in the left sidebar next to **Services**, click **+**, choose **Google Calendar API**, and leave the identifier as `Calendar`. Click Add. ## Phase 4 — Set the secret token Generate the token straight onto the clipboard, without displaying it: ```bash openssl rand -hex 24 | tr -d '\n' | pbcopy ``` Immediately write it into the user's shell profile from the clipboard, still without displaying it. **Determine the profile file once, here, and use that same file in Phases 5 and 6.** Run `echo $SHELL`: zsh means `~/.zshenv`, bash means `~/.bashrc` or `~/.bash_profile`. Tell the user which file you chose. The examples below say `~/.zshenv` because zsh is the macOS default; substitute the real one throughout if it isn't. Writing the token to a file the user's shell doesn't read is a silent failure that won't surface until their next session. **On any non-macOS platform, see Platform notes** and establish where environment variables actually persist before writing anything. ```bash printf 'export GSHEETS_API_TOKEN="%s"\n' "$(pbpaste)" >> ~/.zshenv ``` Now, in Chrome: **Project Settings** (the gear icon in the left sidebar) → scroll to **Script Properties** → **Add script property**. Type `SECRET_TOKEN` into the Property field yourself. For the Value field: click it, then **stop and ask the user to press `cmd+v` / `ctrl+v`**. Do not type or paste the token yourself. Wait for them to confirm, then click **Save script properties**. Tell them their clipboard now holds a secret and they may want to copy something else over it when you're done. ## Phase 5 — Deploy as a web app In Chrome: **Deploy** → **New deployment** → click the gear next to "Select type" → **Web app**. - **Description:** `v1` - **Execute as:** Me - **Who has access:** Anyone That "Anyone" setting is what lets a plain `curl` reach it. The token is the actual gate; requests without it are rejected. Click **Deploy**. Google will prompt for authorization. The prompt covers every Google service the code uses (Sheets, Docs, Drive, and Calendar if the advanced service was added), so one authorization here is enough; see Phase 8 for what changes if the code later gains a new service. **Hand this to the user.** Say clearly: "Google is going to ask you to authorize this script, and it will show a scary 'Google hasn't verified this app' warning. That warning is expected — this is a script you just created under your own account. Click **Advanced**, then **Go to gsheets-api (unsafe)**, then **Allow**." Wait for them to finish. Then copy the **Web app URL** (it ends in `/exec`) and store it: ```bash echo 'export GSHEETS_API_URL="PASTE_URL_HERE"' >> ~/.zshenv ``` The URL is not a secret; it's safe to handle and show. **If "Anyone" is not offered, or the deploy is blocked:** their Workspace admin has restricted web app deployment. Tell them plainly that this path won't work on that account, and give them the two real options: ask their Workspace admin to allow it, or deploy the script from a personal Google account instead and share the specific spreadsheets with that account. Don't improvise a different write path. ## Phase 6 — Load the credentials The env vars only reach a shell that starts after the profile was edited. Source them for the current session so you can test immediately: ```bash source ~/.zshenv && echo "url:${GSHEETS_API_URL:+ok} token:${GSHEETS_API_TOKEN:+ok}" ``` Use the same profile file you chose in Phase 4. Note that `source` proves the file *contains* the variables, not that the user's shell reads that file on startup. If you had any doubt about which profile file was right, confirm it in a genuinely fresh shell before trusting it. That prints `ok` for each without revealing either value. Tell the user to restart any other open Claude Code sessions. **On any non-macOS platform, `source` may not be the right mechanism at all** — see Platform notes, and confirm both variables read `ok` in a fresh shell before you continue to Phase 7. ## Phase 7 — Verify end to end Do not report success until all of these pass. A ping alone is not a working setup. **7a. Liveness and auth:** ```bash curl -sL -H 'Content-Type: application/json' -d "{\"token\":\"$GSHEETS_API_TOKEN\",\"op\":\"ping\"}" "$GSHEETS_API_URL" ``` Expect `{"ok":true,"result":{"pong":true,...}}`. **NEVER pass `-X POST`.** Apps Script answers with a 302 redirect, and `-X POST` forces POST onto the redirect, which returns Google's "Page Not Found" HTML instead of your result. `-d` alone already makes the first request a POST. This mistake looks like a broken deployment and isn't. **7b. Bad token is rejected:** ```bash curl -sL -H 'Content-Type: application/json' -d '{"token":"wrong","op":"ping"}' "$GSHEETS_API_URL" ``` Expect `{"ok":false,"error":"unauthorized"}`. If a bad token succeeds, stop — the token wasn't saved correctly in Script Properties. **7c. A real read.** Ask the user for the URL of a spreadsheet they can edit, pull the id out of it, and run `list_tabs`, then `get_headers` on one tab. Show them the result. **7d. A real write.** Ask the user to create a throwaway spreadsheet, or offer to have them make one, and confirm with them before writing. Then `append_rows` a single test row, `read_range` it back to prove it landed, and `clear_range` to clean up. Show them each result. **7e. A real Doc write.** Ask the user for the URL of a throwaway Google Doc they can edit (or to create one with a sentence or two in it), pull the id out of it, and confirm with them before writing. Then run `doc_get_text` to list its paragraphs, `doc_replace_text` with `find`, `replace`, and `expect_count: 1` on one word from the Doc, `doc_get_text` again to prove it landed, and a second `doc_replace_text` to put the original word back. Show them each result. Build these JSON payloads in Python, not by hand in the shell. If `doc_get_text` fails with "cannot open document" while `ping` works, the Documents permission was not granted: see Troubleshooting. Confirm the "Claude Sheets Audit" spreadsheet now exists in their Drive with entries for those writes, both the sheet rows and the doc edits. ## Phase 8 — Report Tell them, briefly: - It's set up and verified, and what you actually tested. - The skill lives at `~/.claude/skills/gsheets-api/`, and Claude will load it automatically when a task needs to write to a sheet or edit a doc. They can also invoke it by name. - Reads don't need this — the Google Drive connector reads sheets and docs fine, including comments. This is for writes. - Every change is logged to "Claude Sheets Audit" in their Drive, with old cell values and old paragraph text. - To revoke: delete the deployment in the Apps Script editor, or change `SECRET_TOKEN`. - If they edit `Code.gs` later: **Deploy → Manage deployments → pencil icon → Version: New version → Deploy**. The URL stays the same. Creating a *new* deployment instead gives a different URL and is a common mistake. One more trap: if the edit makes the script use a Google service it did not use before (the way Docs editing was added to this script in September 2026), redeploying does NOT ask for the new permission, and every call into that service fails with "cannot open ..." while `ping` still works. The fix is to run any function that uses the new service once from the editor's Run button and accept the authorization prompt; no new deployment is needed after that. --- ## Troubleshooting | Symptom | Cause | Fix | |---|---|---| | Response is HTML saying "Page Not Found" | `-X POST` was used, or the URL doesn't end in `/exec` | Drop `-X POST`; use `curl -sL -d '...' "$URL"` | | `{"ok":false,"error":"unauthorized"}` | Token in the shell doesn't match Script Properties | Re-check the Script Property value; regenerate and set both sides | | `{"ok":false,"error":"unknown op: ..."}` | The deployment is running an older code version | Deploy → Manage deployments → pencil → New version → Deploy | | `$GSHEETS_API_URL` is empty | Shell profile edited but not reloaded, or written to the wrong file | `source` the profile; confirm which file the shell actually reads | | `unauthorized` despite a correct-looking token | Stray whitespace or a carriage return got into the value while moving it between programs | `printf %s "$GSHEETS_API_TOKEN" \| wc -c` must print 48; if not, re-set it | | Env vars empty in a new session on a non-macOS platform | They were written somewhere this machine's shell doesn't actually read | See Platform notes: establish the real mechanism, don't assume a profile file is sourced | | "cannot open spreadsheet ... check the id" | The deploying account can't access that sheet | Share the sheet with the account the script runs as | | "cannot open document ... check the id" while `ping` works | The account can't edit that Doc, or the Documents permission was never authorized (DocumentApp was added to the code after the original authorization) | Share the Doc with the account the script runs as; if that isn't it, run any function that uses DocumentApp once from the editor's Run button and accept the prompt | | A call returns a non-JSON Google page although the write went through | Intermittent Apps Script redirect behaviour | Re-read the Doc or range before retrying; never blindly retry a mutation | | `doc_get_text` with `with_style` shows an empty text style for a paragraph that looks formatted | `with_style` reports only attributes set explicitly on the element's first character | Open the Doc in the browser when appearance matters; use `doc_set_text_style` with `copy_style_from` to set attributes explicitly | | "Google hasn't verified this app" | Expected for any self-authored script | Advanced → Go to gsheets-api (unsafe) → Allow | | "Anyone" access unavailable, or deploy blocked | Workspace admin policy | Ask the admin, or deploy from a personal account and share the sheets to it | | `Calendar is not defined` | The Calendar advanced service wasn't added | Sidebar → Services → + → Google Calendar API (identifier `Calendar`), then redeploy a new version | | Pasted code is truncated or garbled | Clipboard was overwritten between copy and paste | Re-copy immediately before pasting, then read the editor back to verify | --- ## Appendix A — `Code.gs` Write this to `~/.claude/skills/gsheets-api/apps-script/Code.gs`, replacing `YOUR_EMAIL_HERE` with the user's Google account address. ```javascript /** * gsheets-api — standalone Google Apps Script that gives Claude Code a narrow, * token-gated path to: * 1. Read and edit ANY Google Sheet your Google account can edit * (standalone + "Execute as: Me" means SpreadsheetApp.openById() reaches * everything — no per-sheet setup). * 2. Attach Drive files to Google Calendar events (no connector can do this). * 3. Read and edit ANY Google Doc your Google account can edit (doc_* ops): * the Drive connector can read Docs but cannot edit them. * * Security model: * - Every request must carry the SECRET_TOKEN stored in Script Properties * (never hardcoded here). Requests without it are rejected. * - Ops are a fixed whitelist; there is no arbitrary-code or eval path. * - String values starting with "=" are rejected unless allow_formulas:true, * so a write can never silently plant a formula. * - Reads/writes are size-capped (MAX_CELLS_*) to prevent runaway calls. * - Calendar attachment only works on calendars in ALLOWED_CALENDARS. * - Every mutating call (sheet or doc) is appended to a dedicated * "Claude Sheets Audit" spreadsheet, auto-created in My Drive on first mutation. * * Callers: see the gsheets-api skill (~/.claude/skills/gsheets-api/SKILL.md). */ const ALLOWED_CALENDARS = [ 'YOUR_EMAIL_HERE', // <-- replace with your own Google account address ]; const MAX_CELLS_READ = 20000; const MAX_CELLS_WRITE = 5000; const AUDIT_SS_NAME = 'Claude Sheets Audit'; const MAX_DOC_CHARS_READ = 200000; // --------------------------------------------------------------------------- // Entry points // --------------------------------------------------------------------------- function doGet() { // Unauthenticated liveness check only — reveals nothing and changes nothing. return ContentService.createTextOutput('gsheets-api alive'); } function doPost(e) { let req; try { req = JSON.parse(e.postData.contents); } catch (err) { return json_({ ok: false, error: 'invalid JSON body' }); } const token = PropertiesService.getScriptProperties().getProperty('SECRET_TOKEN'); if (!token || req.token !== token) { return json_({ ok: false, error: 'unauthorized' }); } try { let result; switch (req.op) { case 'ping': result = { pong: true, time: new Date().toISOString() }; break; case 'list_tabs': result = listTabs_(req); break; case 'get_headers': result = getHeaders_(req); break; case 'read_range': result = readRange_(req); break; case 'write_range': result = writeRange_(req); break; case 'append_rows': result = appendRows_(req); break; case 'add_row': result = addRow_(req); break; case 'set_cell': result = setCell_(req); break; case 'clear_range': result = clearRange_(req); break; case 'read_links': result = readLinks_(req); break; case 'set_link': result = setLink_(req); break; case 'read_notes': result = readNotes_(req); break; case 'set_note': result = setNote_(req); break; case 'attach_to_event': result = attachToEvent_(req); break; case 'strip_meet': result = stripMeet_(req); break; case 'doc_get_text': result = docGetText_(req); break; case 'doc_replace_text': result = docReplaceText_(req); break; case 'doc_replace_paragraph': result = docReplaceParagraph_(req); break; case 'doc_insert_paragraph_after': result = docInsertParagraphAfter_(req); break; case 'doc_delete_paragraph': result = docDeleteParagraph_(req); break; case 'doc_set_text_style': result = docSetTextStyle_(req); break; case 'doc_set_link': result = docSetLink_(req); break; default: return json_({ ok: false, error: 'unknown op: ' + req.op }); } return json_({ ok: true, result: result }); } catch (err) { return json_({ ok: false, error: String(err && err.message ? err.message : err) }); } } // --------------------------------------------------------------------------- // Read ops // --------------------------------------------------------------------------- /** * list_tabs — { spreadsheet_id } * Returns every tab's name, dimensions, and hidden flag. */ function listTabs_(req) { const ss = getSs_(req); return { spreadsheet: ss.getName(), tabs: ss.getSheets().map(function (s) { return { name: s.getName(), rows: s.getLastRow(), cols: s.getLastColumn(), hidden: s.isSheetHidden() }; }), }; } /** * get_headers — { spreadsheet_id, sheet } * Returns row 1 as trimmed header texts plus the last data row, so callers can * validate column names before writing. */ function getHeaders_(req) { const sheet = getSheet_(req); const lastCol = Math.max(sheet.getLastColumn(), 1); const headers = sheet.getRange(1, 1, 1, lastCol).getDisplayValues()[0].map(function (h) { return String(h).trim(); }); return { spreadsheet: sheet.getParent().getName(), sheet: sheet.getName(), headers: headers, last_row: sheet.getLastRow() }; } /** * read_range — { spreadsheet_id, sheet, range?, render? } * range: A1 notation (e.g. "A1:E20"). Omit to read the whole data region. * render: "display" (default, what a user sees), "raw" (underlying values), * or "formula" (formulas where present, else values). */ function readRange_(req) { const sheet = getSheet_(req); const range = req.range ? sheet.getRange(String(req.range)) : sheet.getDataRange(); if (range.getNumRows() * range.getNumColumns() > MAX_CELLS_READ) { throw new Error('range exceeds ' + MAX_CELLS_READ + ' cells — read a narrower range'); } let values; if (req.render === 'raw') values = range.getValues(); else if (req.render === 'formula') { values = range.getValues(); const formulas = range.getFormulas(); formulas.forEach(function (row, r) { row.forEach(function (f, c) { if (f) values[r][c] = f; }); }); } else values = range.getDisplayValues(); return { sheet: sheet.getName(), range: range.getA1Notation(), values: values }; } // --------------------------------------------------------------------------- // Write ops // --------------------------------------------------------------------------- /** * write_range — { spreadsheet_id, sheet, range, values, allow_formulas? } * values: 2-D array whose dimensions must exactly match the A1 range. * Overwrites in place; old display values go to the audit log. */ function writeRange_(req) { const sheet = getSheet_(req); if (!req.range) throw new Error('range (A1 notation) is required'); const values = req.values; if (!Array.isArray(values) || !values.length || !Array.isArray(values[0])) { throw new Error('values must be a 2-D array, e.g. [["a","b"],["c","d"]]'); } const range = sheet.getRange(String(req.range)); if (range.getNumRows() !== values.length || range.getNumColumns() !== values[0].length) { throw new Error('values are ' + values.length + 'x' + values[0].length + ' but range ' + range.getA1Notation() + ' is ' + range.getNumRows() + 'x' + range.getNumColumns()); } capCells_(values, MAX_CELLS_WRITE); guardFormulas_(values, req.allow_formulas); const oldValues = range.getDisplayValues(); range.setValues(values); audit_('write_range', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), range: range.getA1Notation(), old_values: truncate_(oldValues), new_values: truncate_(values), }); return { sheet: sheet.getName(), range: range.getA1Notation(), cells_written: values.length * values[0].length }; } /** * append_rows — { spreadsheet_id, sheet, values, allow_formulas? } * values: 2-D array of new rows (ragged rows are padded with ""). Appended * starting at the first row after the current last data row. */ function appendRows_(req) { const sheet = getSheet_(req); const values = req.values; if (!Array.isArray(values) || !values.length || !Array.isArray(values[0])) { throw new Error('values must be a 2-D array of rows'); } const width = Math.max.apply(null, values.map(function (r) { return r.length; })); const padded = values.map(function (r) { return r.concat(new Array(width - r.length).fill('')); }); capCells_(padded, MAX_CELLS_WRITE); guardFormulas_(padded, req.allow_formulas); const startRow = sheet.getLastRow() + 1; sheet.getRange(startRow, 1, padded.length, width).setValues(padded); audit_('append_rows', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), start_row: startRow, rows: padded.length, values: truncate_(padded), }); return { sheet: sheet.getName(), start_row: startRow, rows_appended: padded.length }; } /** * add_row — { * spreadsheet_id, sheet, * values: { "Header text": "cell value", ... }, // keys must match row-1 headers exactly (after trimming) * allow_duplicate?: false * } * Header-keyed append for checklist-style sheets: unknown headers are an error * (typos never land in the wrong column), and unless allow_duplicate is true it * refuses when column A (+ column B when supplied) already matches an existing row. */ function addRow_(req) { const sheet = getSheet_(req); const lastCol = sheet.getLastColumn(); const headers = sheet.getRange(1, 1, 1, lastCol).getDisplayValues()[0].map(function (h) { return String(h).trim(); }); const rowValues = new Array(lastCol).fill(''); Object.keys(req.values || {}).forEach(function (key) { const idx = headers.indexOf(String(key).trim()); if (idx === -1) throw new Error('unknown column header: "' + key + '"'); rowValues[idx] = req.values[key]; }); guardFormulas_([rowValues], req.allow_formulas); const first = String(rowValues[0] || '').trim(); if (!first) throw new Error('a value for the first column is required'); if (!req.allow_duplicate && sheet.getLastRow() > 1) { const existing = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getDisplayValues(); existing.forEach(function (r) { if (String(r[0]).trim().toLowerCase() === first.toLowerCase() && (!rowValues[1] || String(r[1]).trim() === String(rowValues[1]).trim())) { throw new Error('a matching row appears to exist already — pass allow_duplicate: true to force'); } }); } const newRow = sheet.getLastRow() + 1; sheet.getRange(newRow, 1, 1, lastCol).setValues([rowValues]); audit_('add_row', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), row: newRow, values: req.values, }); return { row: newRow }; } /** * set_cell — { * spreadsheet_id, sheet, * row_match: { col_a_contains: "Row label", col_b_equals: "9/25/2026" }, // col_b_equals optional * column_header: "Status", * value: "Yes" * } * Locates exactly ONE data row by case-insensitive substring match on column A * (and, when given, exact display match on column B), then writes one cell in * the column whose row-1 header matches column_header exactly. Refuses on zero * or multiple matches. */ function setCell_(req) { const sheet = getSheet_(req); const col = findColumn_(sheet, req.column_header); const row = findRow_(sheet, req.row_match); guardFormulas_([[req.value]], req.allow_formulas); const cell = sheet.getRange(row, col); const oldValue = cell.getDisplayValue(); cell.setValue(req.value); audit_('set_cell', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), row: row, column_header: req.column_header, old_value: oldValue, new_value: req.value, row_match: req.row_match, }); return { row: row, column_header: req.column_header, old_value: oldValue, new_value: req.value }; } /** * clear_range — { spreadsheet_id, sheet, range } * Clears values (not formatting). Old display values go to the audit log. */ function clearRange_(req) { const sheet = getSheet_(req); if (!req.range) throw new Error('range (A1 notation) is required'); const range = sheet.getRange(String(req.range)); if (range.getNumRows() * range.getNumColumns() > MAX_CELLS_WRITE) { throw new Error('range exceeds ' + MAX_CELLS_WRITE + ' cells — clear a narrower range'); } const oldValues = range.getDisplayValues(); range.clearContent(); audit_('clear_range', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), range: range.getA1Notation(), old_values: truncate_(oldValues), }); return { sheet: sheet.getName(), range: range.getA1Notation(), cells_cleared: range.getNumRows() * range.getNumColumns() }; } /** * read_links — { spreadsheet_id, sheet, range } * Returns each cell's text plus any rich-text hyperlink (the kind added via * the Sheets UI "Insert link", which plain value reads can't see). `link` is * the whole-cell link or null; `runs` appears only when a cell has multiple * differently-linked text runs. */ function readLinks_(req) { const sheet = getSheet_(req); if (!req.range) throw new Error('range (A1 notation) is required'); const range = sheet.getRange(String(req.range)); if (range.getNumRows() * range.getNumColumns() > MAX_CELLS_READ) { throw new Error('range exceeds ' + MAX_CELLS_READ + ' cells — read a narrower range'); } const cells = range.getRichTextValues().map(function (row) { return row.map(function (rtv) { const cell = { text: rtv.getText(), link: rtv.getLinkUrl() }; if (!cell.link) { const runs = rtv.getRuns() .map(function (r) { return { text: r.getText(), link: r.getLinkUrl() }; }) .filter(function (r) { return r.link; }); if (runs.length) cell.runs = runs; } return cell; }); }); return { sheet: sheet.getName(), range: range.getA1Notation(), cells: cells }; } /** * set_link — { spreadsheet_id, sheet, cell, text, url } * Writes one cell as rich text whose full text is hyperlinked to url * (equivalent to typing the text and using Insert link in the UI). */ function setLink_(req) { const sheet = getSheet_(req); if (!req.cell) throw new Error('cell (A1 notation) is required'); if (!req.text || !req.url) throw new Error('text and url are required'); if (!/^https?:\/\//.test(String(req.url))) throw new Error('url must start with http:// or https://'); const range = sheet.getRange(String(req.cell)); if (range.getNumRows() !== 1 || range.getNumColumns() !== 1) { throw new Error('cell must be a single cell, e.g. "F305"'); } const oldValue = range.getDisplayValue(); range.setRichTextValue( SpreadsheetApp.newRichTextValue().setText(String(req.text)).setLinkUrl(String(req.url)).build() ); audit_('set_link', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), cell: range.getA1Notation(), old_value: oldValue, text: req.text, url: req.url, }); return { cell: range.getA1Notation(), old_value: oldValue, text: req.text, url: req.url }; } /** * read_notes — { spreadsheet_id, sheet, range } * Returns each cell's display text plus its NOTE (the yellow-corner annotation * from "Insert note" — a different feature from threaded comments, and invisible * to plain value reads). `note` is the note text, or null when the cell has none. * Threaded comments are NOT readable here; nothing in the Sheets service exposes them. */ function readNotes_(req) { const sheet = getSheet_(req); if (!req.range) throw new Error('range (A1 notation) is required'); const range = sheet.getRange(String(req.range)); if (range.getNumRows() * range.getNumColumns() > MAX_CELLS_READ) { throw new Error('range exceeds ' + MAX_CELLS_READ + ' cells — read a narrower range'); } const texts = range.getDisplayValues(); const notes = range.getNotes(); const cells = notes.map(function (row, r) { return row.map(function (n, c) { return { text: texts[r][c], note: n ? n : null }; }); }); return { sheet: sheet.getName(), range: range.getA1Notation(), cells: cells }; } /** * set_note — { spreadsheet_id, sheet, cell, note } * Sets one cell's NOTE (the "Insert note" annotation), leaving the cell's value * and formatting untouched. Pass an empty string or null to clear an existing note. * This does NOT create a threaded comment — those need the Drive API and are not * available through this script. */ function setNote_(req) { const sheet = getSheet_(req); if (!req.cell) throw new Error('cell (A1 notation) is required'); const range = sheet.getRange(String(req.cell)); if (range.getNumRows() !== 1 || range.getNumColumns() !== 1) { throw new Error('cell must be a single cell, e.g. "M313"'); } const note = (req.note === null || req.note === undefined) ? '' : String(req.note); if (note.length > 5000) throw new Error('note exceeds 5000 characters'); const oldNote = range.getNote(); range.setNote(note); audit_('set_note', { spreadsheet: sheet.getParent().getName(), spreadsheet_id: req.spreadsheet_id, sheet: sheet.getName(), cell: range.getA1Notation(), old_note: String(oldNote).slice(0, 500), new_note: note.slice(0, 500), }); return { cell: range.getA1Notation(), old_note: oldNote ? oldNote : null, new_note: note ? note : null, cleared: !note, }; } // --------------------------------------------------------------------------- // Calendar op // --------------------------------------------------------------------------- /** * attach_to_event — { * calendar_id: "you@example.com", * event_id: "abc123...", // plain event id (no @google.com suffix needed) * file_id: "1AbC...", // Drive file id * title: "optional display title", // defaults to the Drive file's name * share_with: ["someone@example.com"] // optional viewers * } * Appends a Drive-file attachment to an existing Calendar event, preserving any * attachments already on it. Requires the Calendar advanced service (SETUP.md). * Files attached via API are NOT auto-shared, so pass share_with for anyone who * must open them. */ function attachToEvent_(req) { if (ALLOWED_CALENDARS.indexOf(req.calendar_id) === -1) { throw new Error('calendar not in allowlist: ' + req.calendar_id); } const file = DriveApp.getFileById(req.file_id); (req.share_with || []).forEach(function (email) { file.addViewer(email); }); const event = Calendar.Events.get(req.calendar_id, req.event_id); const attachments = event.attachments || []; const fileUrl = 'https://drive.google.com/open?id=' + req.file_id; if (attachments.some(function (a) { return a.fileUrl === fileUrl; })) { return { event_id: req.event_id, already_attached: true, attachment_count: attachments.length }; } attachments.push({ fileUrl: fileUrl, title: req.title || file.getName(), mimeType: file.getMimeType(), }); Calendar.Events.patch({ attachments: attachments }, req.calendar_id, req.event_id, { supportsAttachments: true }); audit_('attach_to_event', { calendar_id: req.calendar_id, event_id: req.event_id, file: file.getName(), file_id: req.file_id, shared_with: req.share_with || [], }); return { event_id: req.event_id, attached: file.getName(), attachment_count: attachments.length }; } /** * strip_meet — { calendar_id, event_id } * Removes the Google Meet conference from an existing event (the Calendar * connector auto-adds Meet links on creation and cannot remove them). Uses a * full Events.update with conferenceData omitted + conferenceDataVersion 1, * which is the reliable removal path. Allowlisted calendars only. */ function stripMeet_(req) { if (ALLOWED_CALENDARS.indexOf(req.calendar_id) === -1) { throw new Error('calendar not in allowlist: ' + req.calendar_id); } const event = Calendar.Events.get(req.calendar_id, req.event_id); if (!event.conferenceData) { return { event_id: req.event_id, had_meet: false }; } delete event.conferenceData; Calendar.Events.update(event, req.calendar_id, req.event_id, { conferenceDataVersion: 1 }); audit_('strip_meet', { calendar_id: req.calendar_id, event_id: req.event_id, summary: event.summary || '' }); return { event_id: req.event_id, had_meet: true, removed: true }; } // --------------------------------------------------------------------------- // Google Docs ops (DocumentApp). Elements are addressed by their index among // the document body's direct children (see doc_get_text). Every mutating op // requires a `contains` guard: the target element's current text must contain // it, so a stale index can never edit the wrong paragraph. // --------------------------------------------------------------------------- /** * doc_get_text — { doc_id, contains?, with_links?, with_style? } * Returns the body's direct children as { index, type, text }. type is the * element type (PARAGRAPH, LIST_ITEM, TABLE, ...); text is the element's text, * truncated to 3000 chars per element. Pass `contains` (case-sensitive * substring) to return only matching elements. with_links: true adds a `links` * array per element: { text, url } for every hyperlinked run. with_style: true * adds a `style` object: the element's paragraph attributes plus the text * attributes of its first character (font, size, colour, bold...). Total output * capped at MAX_DOC_CHARS_READ. */ function docGetText_(req) { const doc = getDoc_(req); const body = doc.getBody(); const n = body.getNumChildren(); const needle = req.contains ? String(req.contains) : null; const out = []; let total = 0; for (let i = 0; i < n; i++) { const child = body.getChild(i); const text = elementText_(child); if (needle && text.indexOf(needle) === -1) continue; const t = text.slice(0, 3000); total += t.length; if (total > MAX_DOC_CHARS_READ) throw new Error('document text exceeds ' + MAX_DOC_CHARS_READ + ' chars — use `contains` to narrow'); const item = { index: i, type: String(child.getType()), text: t }; if (req.with_links) item.links = elementLinks_(child); if (req.with_style) item.style = elementStyle_(child); out.push(item); } return { title: doc.getName(), doc_id: doc.getId(), num_children: n, elements: out }; } /** * doc_replace_text — { doc_id, find, replace, expect_count? } * Literal (not regex) find-and-replace across the whole body. Formatting of * the matched run is preserved. Counts occurrences first; if expect_count is * given and differs, refuses without changing anything. Returns the count. */ function docReplaceText_(req) { const doc = getDoc_(req); if (!req.find) throw new Error('find is required'); if (typeof req.replace !== 'string') throw new Error('replace (string) is required'); const body = doc.getBody(); const pattern = escapeRegex_(String(req.find)); let count = 0; let r = body.findText(pattern); while (r) { count++; r = body.findText(pattern, r); } if (count === 0) throw new Error('find text not present in document'); if (req.expect_count !== undefined && req.expect_count !== null && Number(req.expect_count) !== count) { throw new Error('found ' + count + ' occurrence(s) but expect_count is ' + req.expect_count + ' — nothing changed'); } body.replaceText(pattern, String(req.replace)); audit_('doc_replace_text', { doc: doc.getName(), doc_id: doc.getId(), find: String(req.find).slice(0, 500), replace: String(req.replace).slice(0, 500), count: count }); return { doc: doc.getName(), replaced: count }; } /** * doc_replace_paragraph — { doc_id, index, contains, text } * Replaces the full text of the paragraph/list item at body-child `index`, * keeping its paragraph attributes (heading, alignment, spacing). Refuses * unless the element's current text contains `contains`. */ function docReplaceParagraph_(req) { const doc = getDoc_(req); const el = getGuardedChild_(doc, req); if (typeof req.text !== 'string') throw new Error('text (string) is required'); const oldText = elementText_(el); el.asText().setText(req.text); audit_('doc_replace_paragraph', { doc: doc.getName(), doc_id: doc.getId(), index: Number(req.index), old_text: oldText.slice(0, 2000), new_text: req.text.slice(0, 2000) }); return { doc: doc.getName(), index: Number(req.index), old_text: oldText, new_text: req.text }; } /** * doc_insert_paragraph_after — { doc_id, index, contains, text } * Inserts a new paragraph immediately after the body child at `index` * (guarded by `contains`), copying that element's attributes so the new * paragraph matches its neighbour's formatting. Reference must be a PARAGRAPH. */ function docInsertParagraphAfter_(req) { const doc = getDoc_(req); const ref = getGuardedChild_(doc, req); if (String(ref.getType()) !== 'PARAGRAPH') throw new Error('reference element is ' + ref.getType() + ', not PARAGRAPH — pick a plain paragraph to insert after'); if (typeof req.text !== 'string' || !req.text) throw new Error('text (non-empty string) is required'); const body = doc.getBody(); const at = Number(req.index) + 1; const p = body.insertParagraph(at, req.text); try { p.setAttributes(ref.getAttributes()); } catch (err) { /* keep default formatting if copy fails */ } audit_('doc_insert_paragraph_after', { doc: doc.getName(), doc_id: doc.getId(), after_index: Number(req.index), after_text: elementText_(ref).slice(0, 300), new_index: at, text: req.text.slice(0, 2000) }); return { doc: doc.getName(), new_index: at, text: req.text }; } /** * doc_delete_paragraph — { doc_id, index, contains } * Removes the body child at `index` (guarded by `contains`). Old text goes to * the audit log. Refuses to remove the body's only child. */ function docDeleteParagraph_(req) { const doc = getDoc_(req); const el = getGuardedChild_(doc, req); if (doc.getBody().getNumChildren() < 2) throw new Error('refusing to remove the only element in the body'); const oldText = elementText_(el); el.removeFromParent(); audit_('doc_delete_paragraph', { doc: doc.getName(), doc_id: doc.getId(), index: Number(req.index), old_text: oldText.slice(0, 2000) }); return { doc: doc.getName(), removed_index: Number(req.index), old_text: oldText }; } /** * doc_set_text_style — { doc_id, index, contains, copy_style_from?, bold?, italic?, underline?, foreground_color?, font_family?, font_size? } * copy_style_from: body-child index of a reference element whose paragraph * attributes and first-character text attributes are copied onto the target * (the reliable way to make an inserted paragraph match its neighbours). * The explicit flags are applied after the copy, across the element's whole * text. Omitted ones are untouched. Target is `index`, guarded by `contains`. */ function docSetTextStyle_(req) { const doc = getDoc_(req); const el = getGuardedChild_(doc, req); const t = el.asText(); const applied = {}; if (req.copy_style_from !== undefined && req.copy_style_from !== null) { const body = doc.getBody(); const ri = Number(req.copy_style_from); if (isNaN(ri) || ri < 0 || ri >= body.getNumChildren()) throw new Error('copy_style_from index out of range'); const ref = body.getChild(ri); const style = elementStyle_(ref); // Paragraph attributes go through explicit setters: setAttributes() silently // drops enum-valued keys (alignment, heading) and the numeric ones with them. // setHeading() also wipes inline text formatting, so it only runs when the // heading differs, and the text attributes are applied AFTER this block. try { const rp = ref.asParagraph ? ref.asParagraph() : ref; const tp = el.asParagraph ? el.asParagraph() : el; if (rp.getHeading && tp.setHeading && rp.getHeading() && String(rp.getHeading()) !== String(tp.getHeading())) tp.setHeading(rp.getHeading()); if (rp.getAlignment && tp.setAlignment && rp.getAlignment()) tp.setAlignment(rp.getAlignment()); if (rp.getLineSpacing && tp.setLineSpacing && rp.getLineSpacing() !== null) tp.setLineSpacing(rp.getLineSpacing()); if (rp.getSpacingBefore && tp.setSpacingBefore && rp.getSpacingBefore() !== null) tp.setSpacingBefore(rp.getSpacingBefore()); if (rp.getSpacingAfter && tp.setSpacingAfter && rp.getSpacingAfter() !== null) tp.setSpacingAfter(rp.getSpacingAfter()); if (rp.getIndentStart && tp.setIndentStart && rp.getIndentStart() !== null) tp.setIndentStart(rp.getIndentStart()); if (rp.getIndentEnd && tp.setIndentEnd && rp.getIndentEnd() !== null) tp.setIndentEnd(rp.getIndentEnd()); if (rp.getIndentFirstLine && tp.setIndentFirstLine && rp.getIndentFirstLine() !== null) tp.setIndentFirstLine(rp.getIndentFirstLine()); } catch (err) { applied.paragraph_copy_error = String(err && err.message ? err.message : err); } if (Object.keys(style.text).length) t.setAttributes(style.text); applied.copied_from = ri; } if (typeof req.bold === 'boolean') { t.setBold(req.bold); applied.bold = req.bold; } if (typeof req.italic === 'boolean') { t.setItalic(req.italic); applied.italic = req.italic; } if (typeof req.underline === 'boolean') { t.setUnderline(req.underline); applied.underline = req.underline; } if (typeof req.foreground_color === 'string') { t.setForegroundColor(req.foreground_color); applied.foreground_color = req.foreground_color; } if (typeof req.font_family === 'string') { t.setFontFamily(req.font_family); applied.font_family = req.font_family; } if (typeof req.font_size === 'number') { t.setFontSize(req.font_size); applied.font_size = req.font_size; } if (!Object.keys(applied).length) throw new Error('pass copy_style_from or at least one of bold, italic, underline, foreground_color, font_family, font_size'); audit_('doc_set_text_style', { doc: doc.getName(), doc_id: doc.getId(), index: Number(req.index), text: elementText_(el).slice(0, 300), applied: applied }); return { doc: doc.getName(), index: Number(req.index), applied: applied }; } /** * doc_set_link — { doc_id, find, url, expect_count? } * Sets (or, with url: null, removes) the hyperlink on every literal occurrence * of `find` in the body. expect_count refuses if the occurrence count differs. */ function docSetLink_(req) { const doc = getDoc_(req); if (!req.find) throw new Error('find is required'); if (req.url !== null && typeof req.url !== 'string') throw new Error('url (string, or null to remove the link) is required'); const body = doc.getBody(); const pattern = escapeRegex_(String(req.find)); const hits = []; let r = body.findText(pattern); while (r) { hits.push(r); r = body.findText(pattern, r); } if (!hits.length) throw new Error('find text not present in document'); if (req.expect_count !== undefined && req.expect_count !== null && Number(req.expect_count) !== hits.length) { throw new Error('found ' + hits.length + ' occurrence(s) but expect_count is ' + req.expect_count + ' — nothing changed'); } const before = []; hits.forEach(function (h) { const t = h.getElement().asText(); const a = h.getStartOffset(), b = h.getEndOffsetInclusive(); before.push(t.getLinkUrl(a)); t.setLinkUrl(a, b, req.url); }); audit_('doc_set_link', { doc: doc.getName(), doc_id: doc.getId(), find: String(req.find).slice(0, 300), old_urls: before, new_url: req.url, count: hits.length }); return { doc: doc.getName(), updated: hits.length, old_urls: before, new_url: req.url }; } const PARA_ATTRS = ['HEADING', 'HORIZONTAL_ALIGNMENT', 'LINE_SPACING', 'SPACING_BEFORE', 'SPACING_AFTER', 'INDENT_START', 'INDENT_END', 'INDENT_FIRST_LINE']; const TEXT_ATTRS = ['FONT_FAMILY', 'FONT_SIZE', 'FOREGROUND_COLOR', 'BACKGROUND_COLOR', 'BOLD', 'ITALIC', 'UNDERLINE', 'STRIKETHROUGH']; function elementStyle_(el) { const out = { paragraph: {}, text: {} }; let pa = {}; try { pa = el.getAttributes() || {}; } catch (err) { pa = {}; } PARA_ATTRS.forEach(function (k) { if (pa[k] !== null && pa[k] !== undefined) out.paragraph[k] = pa[k]; }); try { const t = el.asText(); if (t.getText()) { const ta = t.getAttributes(0) || {}; TEXT_ATTRS.forEach(function (k) { if (ta[k] !== null && ta[k] !== undefined) out.text[k] = ta[k]; }); } } catch (err) { /* not a text element */ } return out; } function elementLinks_(el) { const links = []; let t; try { t = el.asText(); } catch (err) { return links; } const text = t.getText(); if (!text) return links; const idx = t.getTextAttributeIndices(); for (let k = 0; k < idx.length; k++) { const start = idx[k]; const end = (k + 1 < idx.length ? idx[k + 1] : text.length) - 1; const url = t.getLinkUrl(start); if (url) links.push({ text: text.slice(start, end + 1), url: url }); } return links; } function getDoc_(req) { if (!req.doc_id) throw new Error('doc_id is required'); try { return DocumentApp.openById(String(req.doc_id)); } catch (err) { throw new Error('cannot open document ' + req.doc_id + ' — check the id and that this account can edit it'); } } function getGuardedChild_(doc, req) { if (req.index === undefined || req.index === null || isNaN(Number(req.index))) throw new Error('index (body child index from doc_get_text) is required'); if (!req.contains) throw new Error('contains (substring of the target element\'s current text) is required as a safety guard'); const body = doc.getBody(); const i = Number(req.index); if (i < 0 || i >= body.getNumChildren()) throw new Error('index ' + i + ' out of range (body has ' + body.getNumChildren() + ' children)'); const el = body.getChild(i); const text = elementText_(el); if (text.indexOf(String(req.contains)) === -1) { throw new Error('element ' + i + ' does not contain "' + req.contains + '" — re-run doc_get_text, indices may have shifted. Current text starts: "' + text.slice(0, 120) + '"'); } return el; } function elementText_(el) { try { return el.asText().getText(); } catch (err) { /* not a text-bearing element */ } try { return el.getText(); } catch (err) { return ''; } } function escapeRegex_(s) { return s.replace(/[.*+?^${}()|[\]\\\/-]/g, '\\$&'); } // --------------------------------------------------------------------------- // Helpers // --------------------------------------------------------------------------- function getSs_(req) { if (!req.spreadsheet_id) throw new Error('spreadsheet_id is required'); try { return SpreadsheetApp.openById(String(req.spreadsheet_id)); } catch (err) { throw new Error('cannot open spreadsheet ' + req.spreadsheet_id + ' — check the id and that this account can access it'); } } function getSheet_(req) { const ss = getSs_(req); if (!req.sheet) throw new Error('sheet (tab name) is required, e.g. "2026"'); const sheet = ss.getSheetByName(String(req.sheet)); if (!sheet) { throw new Error('no tab named "' + req.sheet + '" in "' + ss.getName() + '" — tabs: ' + ss.getSheets().map(function (s) { return s.getName(); }).join(', ')); } return sheet; } function findColumn_(sheet, header) { if (!header) throw new Error('column_header is required'); const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getDisplayValues()[0]; const wanted = String(header).trim(); const matches = []; headers.forEach(function (h, i) { if (String(h).trim() === wanted) matches.push(i + 1); }); if (matches.length === 0) throw new Error('no column with header "' + wanted + '"'); if (matches.length > 1) throw new Error('multiple columns share header "' + wanted + '"'); return matches[0]; } function findRow_(sheet, rowMatch) { if (!rowMatch || !rowMatch.col_a_contains) { throw new Error('row_match.col_a_contains is required'); } const needle = String(rowMatch.col_a_contains).trim().toLowerCase(); const wantB = rowMatch.col_b_equals ? String(rowMatch.col_b_equals).trim() : null; const numRows = Math.max(sheet.getLastRow() - 1, 1); const data = sheet.getRange(2, 1, numRows, 2).getDisplayValues(); const matches = []; data.forEach(function (r, i) { const aOk = String(r[0]).toLowerCase().indexOf(needle) !== -1; const bOk = !wantB || String(r[1]).trim() === wantB; if (aOk && bOk) matches.push(i + 2); }); if (matches.length === 0) throw new Error('no row matches row_match ' + JSON.stringify(rowMatch)); if (matches.length > 1) throw new Error('row_match is ambiguous (rows ' + matches.join(', ') + ') — add col_b_equals or a longer substring'); return matches[0]; } function guardFormulas_(values, allowFormulas) { if (allowFormulas) return; values.forEach(function (row) { row.forEach(function (v) { if (typeof v === 'string' && v.charAt(0) === '=') { throw new Error('a value starts with "=" and would become a formula — pass allow_formulas: true if intentional'); } }); }); } function capCells_(values, max) { const cells = values.length * values[0].length; if (cells > max) throw new Error('write of ' + cells + ' cells exceeds the ' + max + '-cell cap — split into smaller calls'); } function truncate_(values) { // Keep audit entries bounded: at most 20 rows x 10 cols, 200 chars per cell. return values.slice(0, 20).map(function (row) { return row.slice(0, 10).map(function (v) { return String(v).slice(0, 200); }); }); } function audit_(op, details) { const props = PropertiesService.getScriptProperties(); let ss = null; const id = props.getProperty('AUDIT_SPREADSHEET_ID'); if (id) { try { ss = SpreadsheetApp.openById(id); } catch (err) { ss = null; } } if (!ss) { ss = SpreadsheetApp.create(AUDIT_SS_NAME); props.setProperty('AUDIT_SPREADSHEET_ID', ss.getId()); ss.getSheets()[0].appendRow(['Timestamp', 'Op', 'Details']); } ss.getSheets()[0].appendRow([new Date().toISOString(), op, JSON.stringify(details).slice(0, 45000)]); } function json_(obj) { return ContentService.createTextOutput(JSON.stringify(obj)) .setMimeType(ContentService.MimeType.JSON); } ``` --- ## Appendix B — `SKILL.md` Write this verbatim to `~/.claude/skills/gsheets-api/SKILL.md`. ````markdown --- name: gsheets-api description: Load context for editing Google Sheets AND Google Docs via your token-gated Apps Script web app (any spreadsheet or document your Google account can edit), plus attaching Drive files to Calendar events. Use whenever a task needs to WRITE to a Google Sheet (add/update/clear cells or rows) or to EDIT the body of a Google Doc (find-and-replace, replace/insert/delete a paragraph), or when tempted to edit a sheet or doc via Claude in Chrome (this path replaces that). Also for attaching files to calendar events. Not needed for read-only access (the Google Drive connector reads sheets and docs fine, including comments). --- # gsheets-api: Google Sheets and Google Docs write path A standalone Apps Script web app deployed from the user's Google account ("Execute as: Me") exposes a fixed op whitelist over any spreadsheet or Google Doc that account can edit. This is the sanctioned way to edit Google Sheets and Google Docs; do NOT use Claude in Chrome for sheet or doc edits. The Drive connector can read a Doc but cannot edit its body. ## Credentials - Env vars `GSHEETS_API_URL` and `GSHEETS_API_TOKEN` are set in the user's shell profile and available in every Claude Code shell. - **Never print, echo, log, or store the token's value anywhere** (chat, files, command output). Reference it only via the env var. Never ask the user to paste it. - If the env vars are empty, the script isn't deployed yet. Point the user at the setup handoff document instead of improvising another write path. ## Call pattern POST JSON; always use `-sL`, and NEVER pass `-X POST`. Apps Script answers with a 302 redirect, and `-X POST` forces POST onto the redirect, which returns Google's "Page Not Found" HTML instead of your result (`-d` alone already makes the first request a POST): ```bash curl -sL -H 'Content-Type: application/json' \ -d "{\"token\":\"$GSHEETS_API_TOKEN\",\"op\":\"ping\"}" "$GSHEETS_API_URL" ``` For payloads with real content, build the JSON in Python (proper escaping of emoji/quotes) rather than hand-rolling shell strings. Responses are `{"ok":true,"result":{...}}` or `{"ok":false,"error":"..."}`; always check `ok`. Latency is ~1–3s per call. ## Ops Every sheet op takes `spreadsheet_id` (the long id from the sheet's URL) and, except `list_tabs`, a `sheet` tab name. Exact payloads are documented in `apps-script/Code.gs`. | op | what it does | |---|---| | `ping` | liveness + auth check | | `list_tabs` | tab names, dimensions, hidden flags | | `get_headers` | row-1 headers (trimmed) + last data row | | `read_range` | values for an A1 range (or whole data region); `render`: `display` (default) / `raw` / `formula` | | `write_range` | overwrite an exact A1 range with a matching 2-D array | | `append_rows` | append raw rows after the last data row | | `add_row` | header-keyed append: `values` maps header text → cell; unknown headers error; duplicate guard on cols A/B (`allow_duplicate: true` to force) | | `set_cell` | one cell, located by `row_match` (`col_a_contains` + optional `col_b_equals`) × `column_header`; refuses ambiguous matches | | `clear_range` | clear values in an A1 range (formatting untouched) | | `read_links` | cell text + rich-text hyperlink URLs for an A1 range (UI "Insert link" links, invisible to plain reads) | | `set_link` | write one cell as text hyperlinked to a URL (rich text, like Insert link in the UI); plain writes to a linked cell strip the link | | `read_notes` | cell text + each cell's NOTE for an A1 range (the "Insert note" annotation, invisible to plain reads) | | `set_note` | set one cell's NOTE, leaving its value and formatting alone; empty/null note clears it. NOT threaded comments, which need the Drive API and aren't available here | | `attach_to_event` | attach a Drive file to a Calendar event (allowlisted calendars only); pass `share_with`, since API attachments are not auto-shared | | `strip_meet` | remove the Google Meet conference from a Calendar event (allowlisted calendars only); the Calendar connector auto-adds Meet links on creation and can't remove them | ### Google Docs ops Every doc op takes `doc_id` (the long id from the Doc's URL). Elements are addressed by their index among the body's direct children, as returned by `doc_get_text`. Every mutating op except `doc_replace_text` and `doc_set_link` requires a `contains` guard: the target element's current text must contain it, or the op refuses. Re-run `doc_get_text` after any insert or delete, because indices shift. | op | what it does | |---|---| | `doc_get_text` | body children as `{index, type, text}` (text capped at 3000 chars each); optional `contains` filter (case-sensitive) returns only matching elements | | `doc_replace_text` | literal find-and-replace across the body; keeps the matched run's formatting; `expect_count` refuses the whole op if the occurrence count differs. Best tool for most edits. | | `doc_replace_paragraph` | replace the full text of the element at `index` (guarded by `contains`); paragraph attributes kept, inline formatting reset | | `doc_insert_paragraph_after` | insert a new paragraph after the PARAGRAPH at `index` (guarded by `contains`), copying its attributes | | `doc_delete_paragraph` | remove the element at `index` (guarded by `contains`); old text goes to the audit log | | `doc_set_text_style` | `copy_style_from: ` copies a reference element's text attributes (font, size, colour, bold...) and paragraph attributes (spacing, indents, alignment) onto the element at `index` (guarded by `contains`); explicit `bold`, `italic`, `underline`, `foreground_color`, `font_family`, `font_size` apply afterwards. Always run this after `doc_insert_paragraph_after`, pointing at a plain body paragraph. | | `doc_set_link` | set the hyperlink (`url`, or `null` to remove) on every literal occurrence of `find`; `expect_count` refuses on a count mismatch | `doc_get_text` also accepts `with_links: true` (adds a `links` array, `{text, url}`, per element: the only way to see where a URL's text actually points) and `with_style: true` (adds `style: {paragraph, text}` per element, for comparing an edited paragraph against its neighbours). Docs caveats: `doc_replace_text` keeps the hyperlink attribute of the matched run, so replacing a URL's text does not change where it links (check with `with_links`, fix with `doc_set_link`). `doc_insert_paragraph_after` copies attributes imperfectly (a new paragraph can come out bold, black, and with default spacing next to styled neighbours); always follow it with `doc_set_text_style` using `copy_style_from` a plain body paragraph, then confirm with `with_style` that the two elements' `style` objects are identical. `copy_style_from` can only copy attributes the reference paragraph has set explicitly, and `with_style` reports only explicit attributes on the element's first character, so an empty `text` style means "no explicit attributes", not "default appearance"; open the Doc in the browser when appearance matters. Paragraphs that look separate in the Drive connector's rendering can be one element with line breaks, so read `doc_get_text` before choosing an insert point. A call occasionally returns a non-JSON Google redirect page even though the write succeeded: re-read before retrying, never blindly retry a mutation. ## Rules 1. **Read before you write.** `get_headers` (or `read_range`) first for sheets, `doc_get_text` first for docs; never write to a range or element you haven't looked at this session. 2. **Show the user the proposed write and get their OK** before `write_range`, `clear_range`, or anything that overwrites non-empty cells. `set_cell`/`add_row`/`append_rows` on cells that are empty or expected checklist updates can proceed when the task already authorizes them. 3. **Verify after writing**: re-read the range (or check the op's returned `old_value`/`new_value`) and confirm to the user what changed. 4. Prefer `set_cell`/`add_row` for header-keyed checklist sheets (they validate headers and refuse ambiguity); use `write_range`/`append_rows` for everything else. 5. Formula guard: values starting with `=` are rejected unless you pass `allow_formulas: true`. Only pass it when a formula is genuinely intended (e.g. `HYPERLINK`). 6. Dates: write them as display strings in the sheet's existing format (e.g. `1/25/2027`); Sheets parses them into real dates. 7. Every mutation (sheet or doc) is auto-logged (with old values) to the "Claude Sheets Audit" spreadsheet in the user's My Drive; mention this if they worry about an overwrite. 8. Caps: reads ≤ 20,000 cells, writes ≤ 5,000 cells per call; `doc_get_text` output ≤ 200,000 chars (use `contains` to narrow). Split larger jobs. 9. This path can't do everything (no tab creation, formatting, comments, filters for sheets; no tables, images, or partial-run formatting for docs). If a task needs an op that doesn't exist, tell the user and propose adding it to `Code.gs` (they redeploy a new version; the URL stays the same). Don't fall back to Claude in Chrome without asking. ## Reading (no write needed) Use the Google Drive connector (`read_file_content`, which can include comments) for read-only work on sheets and docs. `read_range` is still handy when you need exact cell coordinates, formulas, or a fresh read immediately after a write; `doc_get_text` when you need element indices or a fresh read after a doc write. ## Redeploying After editing `Code.gs`: put the new code into the Apps Script editor (Monaco `setValue` through the Claude in Chrome `javascript_tool`, verified by checksum, or paste), save, then **Deploy → Manage deployments → pencil icon → Version: New version → Deploy**. The URL stays the same. Creating a new deployment instead produces a different URL and breaks the stored `GSHEETS_API_URL`. If the edit makes the script use a Google service it did not use before, redeploying does NOT prompt for the new permission: run any function that uses that service once from the editor's Run button and accept the authorization prompt, or every call into it fails while `ping` still works. ````