Introduction
# 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
```bash # Read values from a spreadsheet (note: range is URL-encoded) python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://gateway.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://gateway.maton.ai/google-sheets/{native-api-path} ```
Replace `{native-api-path}` with the actual Google Sheets API endpoint path. The gateway proxies requests to `sheets.googleapis.com` and automatically injects your OAuth token.
## Authentication
All requests require the Maton API key in the Authorization header:
``` Authorization: Bearer $MATON_API_KEY ```
**Environment Variable:** Set your API key as `MATON_API_KEY`:
```bash export MATON_API_KEY="YOUR_API_KEY" ```
### Getting Your API Key
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
## Connection Management
Manage your Google OAuth connections at `https://ctrl.maton.ai`.
### List Connections
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.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
```bash python <<'EOF' import urllib.request, os, json data = json.dumps({'app': 'google-sheets'}).encode() req = urllib.request.Request('https://ctrl.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
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.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": "21fd90f9-5935-43cd-b6c8-bde9d915ca80", "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
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://ctrl.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 with the `Maton-Connection` header:
```bash python <<'EOF' import urllib.request, os, json req = urllib.request.Request('https://gateway.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID') req.add_header('Authorization', f'Bearer {os.environ["MATON_API_KEY"]}') req.add_header('Maton-Connection', '21fd90f9-5935-43cd-b6c8-bde9d915ca80') print(json.dumps(json.load(urllib.request.urlopen(req)), indent=2)) EOF ```
If omitted, the gateway uses the default (oldest) active connection.
## API Reference
### Get Spreadsheet Metadata
```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId} ```
### Get Values
```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range} ```
Example:
```bash GET /google-sheets/v4/spreadsheets/SHEET_ID/values/Sheet1%21A1%3AD10 ```
### Get Multiple Ranges
```bash GET /google-sheets/v4/spreadsheets/{spreadsheetId}/values:batchGet?ranges=Sheet1%21A1%3AB10&ranges=Sheet2%21A1%3AC5 ```
### 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"] ] } ```
### 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"] ] } ```
### 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"]]} ] } ```
### Clear Values
```bash POST /google-sheets/v4/spreadsheets/{spreadsheetId}/values/{range}:clear ```
### Create Spreadsheet
```bash POST /google-sheets/v4/spreadsheets Content-Type: application/json
{ "properties": {"title": "New Spreadsheet"}, "sheets": [{"properties": {"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"}}} ] } ```
## 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
### JavaScript
```javascript // Read values const response = await fetch( 'https://gateway.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://gateway.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://gateway.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://gateway.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
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://ctrl.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://gateway.maton.ai/google-sheets/v4/spreadsheets/SPREADSHEET_ID` - Incorrect: `https://gateway.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 Community](https://discord.com/invite/dBfFAcefs2) - [Maton Support](mailto:[email protected])