google-sheets-soha（Google sheets soha 搜索）

Google Sheets Skill

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

Session Memory

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

SHEET_CONTEXT = {
  spreadsheetId: null,   // Active Sheet ID
  activeTab: null,       // Current tab being worked on
  tabs: [],              // Cached list of tab names
  headers: {},           // Cached headers per tab: { tabName: [...] }
  rawData: {},           // Cached rows per tab: { tabName: [[...]] }
  cacheFile: null,       // Path to on-disk cache file
}

Rules:

• Once spreadsheetId is known → use it for all subsequent turns, never ask again
• Once a cache file exists and is within TTL → skip the API call
• Always check session context before asking the user for anything

Step 1 — Get the Sheet ID

Check in this order:

1. SHEET_CONTEXT.spreadsheetId already set → use it directly
2. URL in the message → extract the ID between /d/ and /edit:

https://docs.google.com/spreadsheets/d/**SHEET_ID**/edit

1. User provides the ID directly → save and use it
2. Not found anywhere → ask exactly once:

"Could you share the Google Sheet link or Sheet ID? I'll remember it for the rest of our conversation 😊"

Once received → save to SHEET_CONTEXT.spreadsheetId immediately and proceed.

Step 2 — Fetch Data from Google Sheets API

Use Google Sheets API v4. Choose the auth method based on the sheet type:

Option A: Public sheet (Anyone with the link)

# List tabs
curl -s "https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}?key={GOOGLE_API_KEY}&fields=sheets.properties" \
  | python3 -c "import sys,json; d=json.load(sys.stdin); [print(s['properties']['title']) for s in d['sheets']]"

# Fetch tab data
curl -s "https://sheets.googleapis.com/v4/spreadsheets/{SHEET_ID}/values/{TAB_NAME}!A1:Z1000?key={GOOGLE_API_KEY}" \
  | python3 -c "import sys,json; d=json.load(sys.stdin); print(json.dumps(d.get('values',[])))"

Option B: Private sheet (Service Account)

import os, json
from google.oauth2 import service_account
from googleapiclient.discovery import build

creds = service_account.Credentials.from_service_account_file(
    os.environ["GOOGLE_SERVICE_ACCOUNT_JSON"],
    scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"]
)
service = build("sheets", "v4", credentials=creds)

# List tabs
meta = service.spreadsheets().get(spreadsheetId=SHEET_ID).execute()
tabs = [s["properties"]["title"] for s in meta["sheets"]]

# Fetch tab data
result = service.spreadsheets().values().get(
    spreadsheetId=SHEET_ID,
    range=f"{TAB_NAME}!A1:Z1000"
).execute()
rows = result.get("values", [])

Save results to SHEET_CONTEXT.headers[tabName] and SHEET_CONTEXT.rawData[tabName].

Step 3 — Disk Cache

Cache fetched data to avoid redundant API calls across turns.

Cache path

~/.openclaw/workspace/.cache/sheets/{spreadsheetId}/{tabName}.json

Cache file structure

{
  "spreadsheetId": "abc123",
  "tabName": "Sheet1",
  "fetchedAt": 1710000000,
  "ttl": 300,
  "headers": ["Name", "Status", "Date"],
  "rows": [
    ["Task A", "Done", "2024-01-01"],
    ["Task B", "Pending", "2024-01-02"]
  ]
}

Cache script (run via exec tool)

import os, json, time, shutil

CACHE_DIR = os.path.expanduser("~/.openclaw/workspace/.cache/sheets")
TTL = 300  # 5 minutes — increase to 3600 for rarely-changing data

def cache_path(sheet_id, tab):
    d = os.path.join(CACHE_DIR, sheet_id)
    os.makedirs(d, exist_ok=True)  # auto-creates on first use
    return os.path.join(d, f"{tab.replace('/', '_')}.json")

def load_cache(sheet_id, tab):
    path = cache_path(sheet_id, tab)
    if not os.path.exists(path):
        return None
    with open(path) as f:
        c = json.load(f)
    if time.time() - c.get("fetchedAt", 0) > c.get("ttl", TTL):
        return None  # expired — will re-fetch
    return c

def save_cache(sheet_id, tab, headers, rows):
    path = cache_path(sheet_id, tab)
    with open(path, "w") as f:
        json.dump({
            "spreadsheetId": sheet_id,
            "tabName": tab,
            "fetchedAt": int(time.time()),
            "ttl": TTL,
            "headers": headers,
            "rows": rows
        }, f, ensure_ascii=False)

def clear_cache(sheet_id=None):
    target = os.path.join(CACHE_DIR, sheet_id) if sheet_id else CACHE_DIR
    if os.path.exists(target):
        shutil.rmtree(target)

Cache flow

cached = load_cache(SHEET_ID, TAB_NAME)
if cached:
    headers, rows = cached["headers"], cached["rows"]
else:
    # fetch from API...
    headers, rows = fetched_rows[0], fetched_rows[1:]
    save_cache(SHEET_ID, TAB_NAME, headers, rows)

When to clear cache

Step 4 — Answer the User

• Always state which sheet/tab the data is from
• Use markdown tables when displaying multiple rows
• Reply in the same language as the user
• If data exceeds 500 rows, analyze the first 200 and ask if the user wants to narrow the range

Configuration in openclaw.json

Add under skills.entries (top-level, not inside agents):

Public sheet

{
  "skills": {
    "entries": {
      "google-sheets-soha": {
        "enabled": true,
        "env": {
          "GOOGLE_API_KEY": "AIza..."
        }
      }
    }
  }
}

Private sheet

{
  "skills": {
    "entries": {
      "google-sheets-soha": {
        "enabled": true,
        "env": {
          "GOOGLE_SERVICE_ACCOUNT_JSON": "/home/node/.openclaw/google-sa.json"
        }
      }
    }
  }
}

Error Handling