--- name: google-sheets description: | Google Sheets API integration with managed OAuth. Read and write spreadsheet data, create sheets, apply formatting, and manage ranges. Use this skill when users want to read from or write to Google Sheets. For other third party apps, use the api-gateway skill (https://clawhub.ai/byungkyu/api-gateway). compatibility: Requires network access and valid Maton API key metadata: author: maton version: "1.0" clawdbot: emoji: 🧠 requires: env: - MATON_API_KEY --- # Google Sheets Access the Google Sheets API with managed OAuth authentication. Read and write spreadsheet values, create sheets, apply formatting, and perform batch operations. ## Quick Start **CLI:** ```bash maton google-sheets values get --range 'Sheet1!A1:D10' ``` ```bash maton api '/google-sheets/v4/spreadsheets/SPREADSHEET_ID/values/Sheet1%21A1%3AD10' ``` **Python:** ```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://api.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID/values/Sheet1%21A1%3AD10') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` ## Base URL ``` https://api.maton.ai/google-sheets/{native-api-path} ``` Maton proxies requests to `sheets.googleapis.com` and automatically injects your OAuth token. ## Installation **NPM:** ```bash npm install -g @maton-ai/cli ``` **Homebrew:** ```bash brew install maton-ai/cli/maton ``` ## Authentication **CLI:** ```bash maton login # Opens browser for API key maton login --interactive # Skip browser, paste API key directly maton whoami # Show current auth state ``` **Manual:** 1. Sign in or create an account at [maton.ai](https://maton.ai) 2. Go to [maton.ai/settings](https://maton.ai/settings) 3. Copy your API key 4. Set your API key as `MATON_API_KEY`: ```bash export MATON_API_KEY="YOUR_API_KEY" ``` ## Connection Management Manage your Google OAuth connections at `https://api.maton.ai`. ### List Connections **CLI:** ```bash maton connection list google-sheets --status ACTIVE ``` ```bash maton api -X GET /connections -f app=google-sheets -f status=ACTIVE ``` **Python:** ```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://api.maton.ai/connections?app=google-sheets&status=ACTIVE') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` ### Create Connection **CLI:** ```bash maton connection create google-sheets ``` ```bash maton api /connections -f app=google-sheets ``` **Python:** ```bash python <<'EOF' import urllib.request, os, json data = json.dumps({'app': 'google-sheets'}).encode() req = urllib.request.Request('https://api.maton.ai/connections', data=data, method='POST') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') req.add_header('Content-Type', 'application/json') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` ### Get Connection **CLI:** ```bash maton connection view {connection_id} ``` ```bash maton api /connections/{connection_id} ``` **Python:** ```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://api.maton.ai/connections/{connection_id}') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` **Response:** ```json { "connection": { "connection_id": "{connection_id}", "status": "ACTIVE", "creation_time": "2025-12-08T07:20:53.488460Z", "last_updated_time": "2026-01-31T20:03:32.593153Z", "url": "https://connect.maton.ai/?session_token=...", "app": "google-sheets", "metadata": {} } } ``` Open the returned `url` in a browser to complete OAuth authorization. ### Delete Connection **CLI:** ```bash maton connection delete {connection_id} ``` ```bash maton api -X DELETE /connections/{connection_id} ``` **Python:** ```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://api.maton.ai/connections/{connection_id}', method='DELETE') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` ### Specifying Connection If you have multiple Google accounts connected, specify which one to use: **CLI:** ```bash maton google-sheets spreadsheet view --connection {connection_id} ``` ```bash maton api /google-sheets/v4/spreadsheets/SPREADSHEET_ID --connection {connection_id} ``` **Python:** ```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://api.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') req.add_header('Maton-Connection', '{connection_id}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` If you have multiple connections, always specify the connection to ensure requests go to the intended account. ## Security & Permissions - Access is scoped to spreadsheets, sheets, cells, and data ranges within the connected Google Sheets account. - **All write operations require explicit user approval.** Before executing any create, update, or delete call, confirm the target resource and intended effect with the user. ## API Reference ### Get Spreadsheet Metadata ```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId} ``` Example: ```bash maton google-sheets spreadsheet view ``` ### Get Values ```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range} ``` Example: ```bash maton google-sheets values get --range 'Sheet1!A1:D10' ``` ### Get Multiple Ranges ```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId}/values:batchGet?ranges=Sheet1%21A1%3AB10&ranges=Sheet2%21A1%3AC5 ``` Example: ```bash maton google-sheets values batch-get --range 'Sheet1!A1:B10,Sheet2!A1:C5' ``` ### Update Values ```bash PUT /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}?valueInputOption=USER_ENTERED Content-Type: application/json { "values": [ ["A1", "B1", "C1"], ["A2", "B2", "C2"] ] } ``` Example: ```bash maton google-sheets values update --range 'Sheet1!A1:C2' --json-values '[["A1","B1","C1"],["A2","B2","C2"]]' ``` ### Append Values ```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED Content-Type: application/json { "values": [ ["New Row 1", "Data", "More Data"], ["New Row 2", "Data", "More Data"] ] } ``` Example: ```bash maton google-sheets values append --range 'Sheet1!A1' --json-values '[["New Row 1","Data","More Data"],["New Row 2","Data","More Data"]]' ``` ### Batch Update Values ```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values:batchUpdate Content-Type: application/json { "valueInputOption": "USER_ENTERED", "data": [ {"range": "Sheet1!A1:B2", "values": [["A1", "B1"], ["A2", "B2"]]}, {"range": "Sheet1!D1:E2", "values": [["D1", "E1"], ["D2", "E2"]]} ] } ``` Example: ```bash maton google-sheets values batch-update --data '[{"range":"Sheet1!A1:B2","values":[["A1","B1"],["A2","B2"]]},{"range":"Sheet1!D1:E2","values":[["D1","E1"],["D2","E2"]]}]' ``` ### Clear Values ```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}:clear ``` Example: ```bash maton google-sheets values clear --range 'Sheet1!A1:D10' ``` ### Create Spreadsheet ```bash POST /google-sheets/v4/spreadsheets Content-Type: application/json { "properties": {"title": "New Spreadsheet"}, "sheets": [{"properties": {"title": "Sheet1"}}] } ``` Example: ```bash maton google-sheets spreadsheet create --title 'New Spreadsheet' --sheet-title 'Sheet1' ``` ### Batch Update (formatting, add sheets, etc.) ```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}:batchUpdate Content-Type: application/json { "requests": [ {"addSheet": {"properties": {"title": "New Sheet"}}} ] } ``` Example: ```bash maton google-sheets spreadsheet batch-update --requests '[{"addSheet":{"properties":{"title":"New Sheet"}}}]' ``` ## Common batchUpdate Requests ### Update Cells with Formatting ```json { "updateCells": { "rows": [ {"values": [{"userEnteredValue": {"stringValue": "Name"}}, {"userEnteredValue": {"numberValue": 100}}]} ], "fields": "userEnteredValue", "start": {"sheetId": 0, "rowIndex": 0, "columnIndex": 0} } } ``` ### Format Header Row (Bold + Background Color) ```json { "repeatCell": { "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 3}, "cell": { "userEnteredFormat": { "backgroundColor": {"red": 0.2, "green": 0.6, "blue": 0.9}, "textFormat": {"bold": true} } }, "fields": "userEnteredFormat(backgroundColor,textFormat)" } } ``` ### Auto-Resize Columns ```json { "autoResizeDimensions": { "dimensions": {"sheetId": 0, "dimension": "COLUMNS", "startIndex": 0, "endIndex": 3} } } ``` ### Rename Sheet ```json { "updateSheetProperties": { "properties": {"sheetId": 0, "title": "NewName"}, "fields": "title" } } ``` ### Insert Rows/Columns ```json { "insertDimension": { "range": {"sheetId": 0, "dimension": "ROWS", "startIndex": 1, "endIndex": 3}, "inheritFromBefore": true } } ``` ### Sort Range ```json { "sortRange": { "range": {"sheetId": 0, "startRowIndex": 1, "endRowIndex": 10, "startColumnIndex": 0, "endColumnIndex": 3}, "sortSpecs": [{"dimensionIndex": 1, "sortOrder": "DESCENDING"}] } } ``` ### Add Filter ```json { "setBasicFilter": { "filter": { "range": {"sheetId": 0, "startRowIndex": 0, "endRowIndex": 100, "startColumnIndex": 0, "endColumnIndex": 5} } } } ``` ### Delete Sheet ```json { "deleteSheet": {"sheetId": 123456789} } ``` ## Value Input Options - `RAW` - Values are stored as-is - `USER_ENTERED` - Values are parsed as if typed into the UI (formulas executed, numbers parsed) ## Range Notation - `Sheet1!A1:D10` - Specific range - `Sheet1!A:D` - Entire columns A through D - `Sheet1!1:10` - Entire rows 1 through 10 - `Sheet1` - Entire sheet - `A1:D10` - Range in first sheet ## Code Examples ### CLI ```bash # Read values from a spreadsheet maton google-sheets values get --range 'Sheet1!A1:B10' # Filter with jq — e.g., extract just the values array maton google-sheets values get --range 'Sheet1!A1:B10' --json --jq '.values' # Append a row maton google-sheets values append --range A1 --values 'Alice,100,true' ``` ### JavaScript ```javascript // Read values const response = await fetch( 'https://api.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:D10', { headers: { 'Authorization': `Bearer ${process.env.MATON_API_KEY}` } } ); // Write values await fetch( 'https://api.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:B2?valueInputOption=USER_ENTERED', { method: 'PUT', headers: { 'Content-Type': 'application/json', 'Authorization': `Bearer ${process.env.MATON_API_KEY}` }, body: JSON.stringify({ values: [['A1', 'B1'], ['A2', 'B2']] }) } ); ``` ### Python ```python import os import requests # Read values response = requests.get( 'https://api.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:D10', headers={'Authorization': f'Bearer {os.environ["MATON_API_KEY"]}'} ) # Write values response = requests.put( 'https://api.maton.ai/google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1!A1:B2', headers={'Authorization': f'Bearer {os.environ["MATON_API_KEY"]}'}, params={'valueInputOption': 'USER_ENTERED'}, json={'values': [['A1', 'B1'], ['A2', 'B2']]} ) ``` ## Notes - When using curl, ranges in URL paths must be URL-encoded (! -> %21, : -> %3A). JavaScript fetch and Python requests handle encoding automatically. - Use `valueInputOption=USER_ENTERED` to parse formulas and numbers - Delete spreadsheets via the Google Drive API, not Sheets API - Sheet IDs are numeric and found in the spreadsheet metadata - IMPORTANT: When using curl commands, use `curl -g` when URLs contain brackets (`fields[]`, `sort[]`, `records[]`) to disable glob parsing - IMPORTANT: When piping curl output to `jq` or other commands, environment variables like `$MATON_API_KEY` may not expand correctly in some shell environments. You may get "Invalid API key" errors when piping. ## Error Handling | Status | Meaning | |--------|---------| | 400 | Missing Google Sheets connection | | 401 | Invalid or missing Maton API key | | 429 | Rate limited (10 req/sec per account) | | 4xx/5xx | Passthrough error from Google Sheets API | ### Troubleshooting: API Key Issues **CLI:** 1. Check your auth state: ```bash maton whoami ``` 2. Verify the API key is valid by listing connections: ```bash maton connection list ``` **Manual:** 1. Check that the `MATON_API_KEY` environment variable is set: ```bash echo $MATON_API_KEY ``` 2. Verify the API key is valid by listing connections: ```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://api.maton.ai/connections') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ``` ### Troubleshooting: Invalid App Name 1. Ensure your URL path starts with `google-sheets`. For example: - Correct: `https://api.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID` - Incorrect: `https://api.maton.ai/v4/spreadsheets/SPREADSHEET_ID` ## Resources - [Sheets API Overview](https://developers.google.com/workspace/sheets/api/reference/rest) - [Get Spreadsheet](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/get) - [Create Spreadsheet](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/create) - [Batch Update](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/batchUpdate) - [Batch Update Request Types](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets/request) - [Get Values](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/get) - [Update Values](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/update) - [Append Values](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/append) - [Batch Get Values](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchGet) - [Batch Update Values](https://developers.google.com/workspace/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate) - [Maton CLI Manual](https://cli.maton.ai/manual) - [Maton Community](https://discord.com/invite/dBfFAcefs2) - [Maton Support](mailto:support@maton.ai)