\r \r

Google Sheets Skill\r

\r Fetches data from Google Sheets via the Google Sheets API v4, caches it on disk, and answers user questions about the data.\r \r ---\r \r

Session Memory\r

\r Maintain the following context throughout the conversation. Update it as new information is learned:\r \r

SHEET_CONTEXT = {\r
  spreadsheetId: null,   // Active Sheet ID\r
  activeTab: null,       // Current tab being worked on\r
  tabs: [],              // Cached list of tab names\r
  headers: {},           // Cached headers per tab: { tabName: [...] }\r
  rawData: {},           // Cached rows per tab: { tabName: [[...]] }\r
  cacheFile: null,       // Path to on-disk cache file\r
}\r
```\r
\r
**Rules:**\r
- Once `spreadsheetId` is known → use it for all subsequent turns, never ask again\r
- Once a cache file exists and is within TTL → skip the API call\r
- Always check session context before asking the user for anything\r
\r
---\r
\r
## Step 1 — Get the Sheet ID\r
\r
Check in this order:\r
1. `SHEET_CONTEXT.spreadsheetId` already set → use it directly\r
2. URL in the message → extract the ID between `/d/` and `/edit`:\r
   `https://docs.google.com/spreadsheets/d/**SHEET_ID**/edit`\r
3. User provides the ID directly → save and use it\r
4. Not found anywhere → ask **exactly once**:\r
\r
> "Could you share the Google Sheet link or Sheet ID? I'll remember it for the rest of our conversation 😊"\r
\r
Once received → save to `SHEET_CONTEXT.spreadsheetId` immediately and proceed.\r
\r
---\r
\r
## Step 2 — Fetch Data from Google Sheets API\r
\r
Use **Google Sheets API v4**. Choose the auth method based on the sheet type:\r
\r
### Option A: Public sheet (Anyone with the link)\r
\r
```bash\r
# List tabs\r
curl -s "https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}?key={GOOGLE_API_KEY}&fields=sheets.properties" \\r
  | python3 -c "import sys,json; d=json.load(sys.stdin); [print(s['properties']['title']) for s in d['sheets']]"\r
\r
# Fetch tab data\r
curl -s "https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values/{TAB_NAME}!A1:Z1000?key={GOOGLE_API_KEY}" \\r
  | python3 -c "import sys,json; d=json.load(sys.stdin); print(json.dumps(d.get('values',[])))"\r
```\r
\r
### Option B: Private sheet (Service Account)\r
\r
```python\r
import os, json\r
from google.oauth2 import service_account\r
from googleapiclient.discovery import build\r
\r
creds = service_account.Credentials.from_service_account_file(\r
    os.environ["GOOGLE_SERVICE_ACCOUNT_JSON"],\r
    scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"]\r
)\r
service = build("sheets", "v4", credentials=creds)\r
\r
# List tabs\r
meta = service.spreadsheets().get(spreadsheetId=SHEET_ID).execute()\r
tabs = [s["properties"]["title"] for s in meta["sheets"]]\r
\r
# Fetch tab data\r
result = service.spreadsheets().values().get(\r
    spreadsheetId=SHEET_ID,\r
    range=f"{TAB_NAME}!A1:Z1000"\r
).execute()\r
rows = result.get("values", [])\r
```\r
\r
Save results to `SHEET_CONTEXT.headers[tabName]` and `SHEET_CONTEXT.rawData[tabName]`.\r
\r
---\r
\r
## Step 3 — Disk Cache\r
\r
Cache fetched data to avoid redundant API calls across turns.\r
\r
### Cache path\r
```\r
~/.openclaw/workspace/.cache/sheets/{spreadsheetId}/{tabName}.json\r
```\r
\r
### Cache file structure\r
```json\r
{\r
  "spreadsheetId": "abc123",\r
  "tabName": "Sheet1",\r
  "fetchedAt": 1710000000,\r
  "ttl": 300,\r
  "headers": ["Name", "Status", "Date"],\r
  "rows": [\r
    ["Task A", "Done", "2024-01-01"],\r
    ["Task B", "Pending", "2024-01-02"]\r
  ]\r
}\r
```\r
\r
### Cache script (run via exec tool)\r
\r
```python\r
import os, json, time, shutil\r
\r
CACHE_DIR = os.path.expanduser("~/.openclaw/workspace/.cache/sheets")\r
TTL = 300  # 5 minutes — increase to 3600 for rarely-changing data\r
\r
def cache_path(sheet_id, tab):\r
    d = os.path.join(CACHE_DIR, sheet_id)\r
    os.makedirs(d, exist_ok=True)  # auto-creates on first use\r
    return os.path.join(d, f"{tab.replace('/', '_')}.json")\r
\r
def load_cache(sheet_id, tab):\r
    path = cache_path(sheet_id, tab)\r
    if not os.path.exists(path):\r
        return None\r
    with open(path) as f:\r
        c = json.load(f)\r
    if time.time() - c.get("fetchedAt", 0) > c.get("ttl", TTL):\r
        return None  # expired — will re-fetch\r
    return c\r
\r
def save_cache(sheet_id, tab, headers, rows):\r
    path = cache_path(sheet_id, tab)\r
    with open(path, "w") as f:\r
        json.dump({\r
            "spreadsheetId": sheet_id,\r
            "tabName": tab,\r
            "fetchedAt": int(time.time()),\r
            "ttl": TTL,\r
            "headers": headers,\r
            "rows": rows\r
        }, f, ensure_ascii=False)\r
\r
def clear_cache(sheet_id=None):\r
    target = os.path.join(CACHE_DIR, sheet_id) if sheet_id else CACHE_DIR\r
    if os.path.exists(target):\r
        shutil.rmtree(target)\r
```\r
\r
### Cache flow\r
\r
```python\r
cached = load_cache(SHEET_ID, TAB_NAME)\r
if cached:\r
    headers, rows = cached["headers"], cached["rows"]\r
else:\r
    # fetch from API...\r
    headers, rows = fetched_rows[0], fetched_rows[1:]\r
    save_cache(SHEET_ID, TAB_NAME, headers, rows)\r
```\r
\r
### When to clear cache\r
\r
| User says | Action |\r
|---|---|\r
| "refresh", "reload", "get latest data" | `clear_cache(SHEET_ID)` then re-fetch |\r
| "clear cache" | `clear_cache()` — wipes everything |\r
| TTL expired | Automatically re-fetches on next request |\r
| User switches to a new sheet | Keep old cache, create new cache for the new sheet |\r
\r
---\r
\r
## Step 4 — Answer the User\r
\r
- Always state which **sheet/tab** the data is from\r
- Use **markdown tables** when displaying multiple rows\r
- Reply in the **same language as the user**\r
- If data exceeds 500 rows, analyze the first 200 and ask if the user wants to narrow the range\r
\r
---\r
\r
## Configuration in openclaw.json\r
\r
Add under `skills.entries` (top-level, not inside `agents`):\r
\r
### Public sheet\r
```json\r
{\r
  "skills": {\r
    "entries": {\r
      "google-sheets-soha": {\r
        "enabled": true,\r
        "env": {\r
          "GOOGLE_API_KEY": "AIza..."\r
        }\r
      }\r
    }\r
  }\r
}\r
```\r
\r
### Private sheet\r
```json\r
{\r
  "skills": {\r
    "entries": {\r
      "google-sheets-soha": {\r
        "enabled": true,\r
        "env": {\r
          "GOOGLE_SERVICE_ACCOUNT_JSON": "/home/node/.openclaw/google-sa.json"\r
        }\r
      }\r
    }\r
  }\r
}\r
```\r
\r
---\r
\r
## Error Handling\r
\r
| Situation | Action |\r
|---|---|\r
| Sheet ID not provided | Ask once, save when received |\r
| API returns 403 | Sheet is private → guide user to share with service account email |\r
| API returns 404 | Wrong Sheet ID → ask again |\r
| `GOOGLE_API_KEY` not set | Guide user to add it in `openclaw.json` |\r
| Tab not found | List `SHEET_CONTEXT.tabs` and ask user to pick |\r
| Data too large | Analyze first 200 rows, notify user |\r
| `python3` not found | Run: `apt-get install -y python3` inside container |