# Google Sheets API Cheat Sheet
## Overview
The Google Sheets API lets you read, write, and format Google Sheets data. It's RESTful, supports OAuth 2.0 authentication, and provides both REST and Google Apps Script interfaces.
## Quick Links
- [API Reference](https://developers.google.com/sheets/api/reference/rest)
- [Developer Guide](https://developers.google.com/sheets/api/guides/concepts)
- [Google Cloud Console](https://console.cloud.google.com/)
- [API Explorer](https://developers.google.com/apis-explorer/#p/sheets/v4/)
## Authentication
1. Create a Google Cloud Project
2. Enable Google Sheets API
3. Create credentials:
- OAuth 2.0 for user data
- Service Account for server-to-server
- API Key for public data
### OAuth 2.0 Scopes
- `…/auth/spreadsheets.readonly` - Read only
- `…/auth/spreadsheets` - Read/write
- `…/auth/drive.file` - Access to files created by the app
- `…/auth/drive` - Full Drive access
## Key Concepts
1. **Spreadsheet ID**: From URL
```
https://docs.google.com/spreadsheets/d/{spreadsheetId}/edit#gid={sheetId}
```
2. **A1 Notation**:
- Single cell: `A1`
- Range: `A1:B2`
- Column: `A:A`
- Row: `1:1`
- Named range: `=MyRange`
3. **R1C1 Notation**:
- R[0]C[0] - Current cell
- R[1]C[0] - One row down
- R[-1]C[0] - One row up
## Common Operations
### Reading Data
```javascript
// Get values from range
GET /v4/spreadsheets/{spreadsheetId}/values/{range}
// Get multiple ranges
GET /v4/spreadsheets/{spreadsheetId}/values:batchGet
{
"ranges": ["A1:B2", "C3:D4"]
}
// Get formatted values
GET /v4/spreadsheets/{spreadsheetId}/values/{range}?valueRenderOption=FORMATTED_VALUE
```
### Writing Data
```javascript
// Update values
PUT /v4/spreadsheets/{spreadsheetId}/values/{range}
{
"values": [
["Cell A1", "Cell B1"],
["Cell A2", "Cell B2"]
]
}
// Append values
POST /v4/spreadsheets/{spreadsheetId}/values/{range}:append
{
"values": [["New Row"]]
}
// Batch update
POST /v4/spreadsheets/{spreadsheetId}/values:batchUpdate
{
"data": [
{
"range": "Sheet1!A1:B2",
"values": [["Update 1"], ["Update 2"]]
}
],
"valueInputOption": "USER_ENTERED"
}
```
### Formatting
```javascript
POST /v4/spreadsheets/{spreadsheetId}:batchUpdate
{
"requests": [
{
"repeatCell": {
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1
},
"cell": {
"userEnteredFormat": {
"backgroundColor": {
"red": 1.0,
"green": 1.0,
"blue": 0.0
}
}
},
"fields": "userEnteredFormat.backgroundColor"
}
}
]
}
```
## Node.js Example
```javascript
const { google } = require('googleapis');
const sheets = google.sheets('v4');
async function getValues(spreadsheetId, range) {
const auth = new google.auth.GoogleAuth({
keyFile: 'path/to/credentials.json',
scopes: ['https://www.googleapis.com/auth/spreadsheets.readonly'],
});
const client = await auth.getClient();
const response = await sheets.spreadsheets.values.get({
auth: client,
spreadsheetId,
range,
});
return response.data.values;
}
```
## Python Example
```python
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
def get_values(spreadsheet_id, range_name):
creds = Credentials.from_authorized_user_file(
'token.json',
['https://www.googleapis.com/auth/spreadsheets.readonly']
)
service = build('sheets', 'v4', credentials=creds)
result = service.spreadsheets().values().get(
spreadsheetId=spreadsheet_id,
range=range_name
).execute()
return result.get('values', [])
```
## Best Practices
1. **Batch Operations**
- Use batch requests for multiple operations
- Limit to 10MB per request
- Maximum 100 ranges per request
2. **Rate Limits**
- 300 requests per minute per project
- 60 requests per minute per user per project
- Use exponential backoff for retries
3. **Performance**
- Minimize API calls by batching
- Cache frequently accessed data
- Use appropriate value render options
4. **Security**
- Use minimal scopes
- Rotate credentials regularly
- Validate user input
- Store credentials securely
## Common Issues & Solutions
1. **Quota Exceeded**
- Implement exponential backoff
- Request quota increase
- Optimize request frequency
2. **Authentication Errors**
- Check credential expiration
- Verify OAuth scopes
- Confirm API enablement
3. **Permission Denied**
- Check file sharing settings
- Verify OAuth scopes
- Confirm service account permissions
## Webhook Integration
```javascript
// Set up push notifications
POST /v4/spreadsheets/{spreadsheetId}/watchs
{
"address": "https://your-domain.com/webhook",
"expiration": "timestamp",
"payload": "custom-payload"
}
```
## Advanced Features
### Macros & Automation
```javascript
// Create a macro using Apps Script
function myMacro() {
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange('A1:D4').setBackground('yellow');
sheet.getRange('A1:D1').setFontWeight('bold');
}
// Set up a trigger
ScriptApp.newTrigger('myMacro')
.timeBased()
.everyHours(1)
.create();
```
### Pivot Tables
```javascript
{
"requests": [{
"updateCells": {
"rows": {
"values": [{
"pivotTable": {
"source": {
"sheetId": sourceSheetId,
"startRowIndex": 0,
"startColumnIndex": 0,
"endRowIndex": 20,
"endColumnIndex": 7
},
"rows": [{
"sourceColumnOffset": 0,
"showTotals": true,
"sortOrder": "ASCENDING"
}],
"columns": [{
"sourceColumnOffset": 1,
"showTotals": true,
"sortOrder": "ASCENDING"
}],
"values": [{
"summarizeFunction": "SUM",
"sourceColumnOffset": 2
}]
}
}]
},
"start": {
"sheetId": targetSheetId,
"rowIndex": 0,
"columnIndex": 0
},
"fields": "pivotTable"
}
}]
}
```
### Charts
```javascript
{
"requests": [{
"addChart": {
"chart": {
"spec": {
"title": "Sales Data",
"basicChart": {
"chartType": "LINE",
"legendPosition": "BOTTOM_LEGEND",
"domains": [{
"domain": {
"sourceRange": {
"sources": [{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 10,
"startColumnIndex": 0,
"endColumnIndex": 1
}]
}
}
}],
"series": [{
"series": {
"sourceRange": {
"sources": [{
"sheetId": sourceSheetId,
"startRowIndex": 0,
"endRowIndex": 10,
"startColumnIndex": 1,
"endColumnIndex": 2
}]
}
}
}]
}
},
"position": {
"overlayPosition": {
"anchorCell": {
"sheetId": targetSheetId,
"rowIndex": 0,
"columnIndex": 3
}
}
}
}
}
}]
}
```
## Apple Shortcuts Integration
### Via IFTTT
1. Enable Maker Webhooks and Google Sheets services on IFTTT
2. Create an applet:
```javascript
// Webhook URL format
https://maker.ifttt.com/trigger/{event}/with/key/{your_key}
// POST request body
{
"value1": "Column1Data",
"value2": "Column2Data",
"value3": "Column3Data"
}
```
### Via Custom API Endpoint
1. Create a simple server (e.g., on Glitch):
```javascript
const express = require('express');
const { google } = require('googleapis');
app.post('/append', async (req, res) => {
const { spreadsheetId, values } = req.body;
const auth = new google.auth.GoogleAuth({
keyFile: 'credentials.json',
scopes: ['https://www.googleapis.com/auth/spreadsheets']
});
const sheets = google.sheets({ version: 'v4', auth });
await sheets.spreadsheets.values.append({
spreadsheetId,
range: 'Sheet1!A1',
valueInputOption: 'USER_ENTERED',
resource: { values: [values] }
});
res.json({ success: true });
});
```
2. In Apple Shortcuts:
```
Action: Get Contents of URL
Method: POST
Headers:
Content-Type: application/json
Request Body:
{
"spreadsheetId": "your_sheet_id",
"values": ["value1", "value2", "value3"]
}
```
## Latest Features
### Linked Charts
```javascript
// Get chart info
GET /v4/spreadsheets/{spreadsheetId}/charts/{chartId}
// Update linked chart
POST /v4/spreadsheets/{spreadsheetId}:batchUpdate
{
"requests": [{
"updateEmbeddedObjectPosition": {
"objectId": chartId,
"newPosition": {
"overlayPosition": {
"anchorCell": {
"sheetId": targetSheetId,
"rowIndex": 0,
"columnIndex": 0
}
}
}
}
}]
}
```
### Partner Integrations
- Anaplan: Enterprise planning and analysis
- Asana: Project management
- Salesforce: CRM integration
- SAP Anywhere: Business management
- Sage: Accounting and finance
### Performance Optimizations
1. **Caching**:
```javascript
const cache = new Map();
async function getCachedValues(spreadsheetId, range) {
const cacheKey = `${spreadsheetId}:${range}`;
if (cache.has(cacheKey)) {
return cache.get(cacheKey);
}
const values = await getValues(spreadsheetId, range);
cache.set(cacheKey, values);
return values;
}
```
2. **Batch Processing**:
```javascript
const batchRequests = values.map(value => ({
range: `Sheet1!A${value.row}`,
values: [[value.data]]
}));
await sheets.spreadsheets.values.batchUpdate({
spreadsheetId,
resource: {
data: batchRequests,
valueInputOption: 'USER_ENTERED'
}
});
```
## Related Resources
```dataview
LIST
FROM #api/google OR #tool/google-sheets
WHERE file.name != this.file.name
SORT file.name ASC
```