Google Sheets API
Create spreadsheets, read and write cells with A1 ranges, append rows, format, sort and tag data with the Sheets API v4.
Productivity & workspaceGuided#spreadsheets#google-sheets#oauth2#google-workspace#google
Fork copies the collection into your workspace, without the publisher's secrets, so you can add your own key and send requests. Star saves it to your starred APIs (Account › Marketplace) and helps others find it.
Overview
The Google Sheets API (v4) reads and writes Google Sheets spreadsheets: cell values, formulas, formatting, tabs, sorting, conditional formats, charts and metadata. Typical uses are exporting reports into a sheet, appending form submissions or logs, using a sheet as a lightweight database or config store, and building dashboards from sheet data.
Two families of calls
spreadsheets.values: plain cell values addressed in A1 notation (Orders!A1:D10). Read, write, append, clear, and batch versions of each.spreadsheets.batchUpdate: a list of structural and formatting requests (addSheet,repeatCell,updateSheetProperties,sortRange,addConditionalFormatRule,createDeveloperMetadataand dozens more). Grid ranges there use the numericsheetIdand zero-based indexes.
Set up credentials
- In the Google Cloud console create or pick a project and enable the Google Sheets API.
- Configure the OAuth consent screen (Google Auth Platform): External, publishing status Testing, add your Google account as a test user, add the scopes you need.
- Create an OAuth client (Web application) with a redirect URI and copy the client ID and secret into this collection.
- Run 1. Get an access token, or get a token quickly from the OAuth 2.0 Playground.
- Optional: create an API key for reading public sheets (folder 6).
Scopes
| Scope | Access | Classification |
|---|---|---|
drive.file | sheets your app created or the user opened with it | non-sensitive, recommended |
spreadsheets.readonly | read all the user's sheets | sensitive |
spreadsheets | read and write all the user's sheets | sensitive |
drive, drive.readonly | all Drive files | restricted |
All scopes start with https://www.googleapis.com/auth/. Sensitive scopes need Google's app verification before you leave Testing; restricted ones have stricter extra requirements. See Choose Sheets API scopes.
Base URL and auth
https://sheets.googleapis.com/v4, with Authorization: Bearer <token> (or ?key= for public, read-only access). The spreadsheet ID is the long string in the sheet URL: docs.google.com/spreadsheets/d/<spreadsheetId>/edit#gid=<sheetId>.
How to use this collection
Fill in clientId and clientSecret, run folder 1, then Create a spreadsheet (saves spreadsheetId and sheetId). Run folder 3 in order (write, read, append...), then the formatting requests in folder 4 and the metadata requests in folder 5. To work on an existing sheet, paste its ID into spreadsheetId and its tab's gid into sheetId, and rename Orders in the ranges to your tab's name.
Limits and pricing
Quotas are per minute, per project and per user, separately for reads and writes, with no daily cap; a request runs at most 180 seconds and payloads of about 2 MB or less are recommended. Exceeding a quota returns 429: retry with exponential backoff. Batch calls count as one request, so batch whenever you can. Normal use is free; check the current numbers and any billing changes on Usage limits.
Common errors
| Status | Meaning | Fix |
|---|---|---|
| 400 INVALID_ARGUMENT | bad A1 range, missing valueInputOption, wrong fields mask, unknown sheetId | read error.message |
| 401 UNAUTHENTICATED | token expired | refresh the token |
| 403 PERMISSION_DENIED | user has no access to the file, scope missing, or API not enabled | share the file, add the scope, enable the API |
| 404 NOT_FOUND | wrong spreadsheet ID | copy the ID from the URL |
| 429 RESOURCE_EXHAUSTED | per-minute quota hit | back off and retry |
Links
Endpoints 24
1. Get an access token
- GETBuild the sign-in URL (open in a browser)
https://accounts.google.com/o/oauth2/v2/authNot an API call: this request only builds Google's consent screen URL so you can copy it. Open it in a browser (sending it from here just returns the HTML… - POSTExchange the code for tokens
https://oauth2.googleapis.com/tokenTrades the one-time code from the redirect for an access token and a refresh token. When: once, right after the browser sign-in. A code works once and expires… - POSTRefresh the access token
https://oauth2.googleapis.com/tokenGets a new access token from the saved refresh token, without asking the user again. When: whenever a request returns 401 (access tokens last about an hour).…
2. Spreadsheets
- POSTCreate a spreadsheet
/spreadsheetsCreates a new spreadsheet in the user's Drive with one tab called Orders. Scope: spreadsheets or drive.file. Body: a Spreadsheet object. Only properties.title… - GETGet spreadsheet structure (field mask)
/spreadsheets/{{spreadsheetId}}Reads the spreadsheet's metadata: title, locale, and each tab's ID, title and grid size. Without fields and with no grid data the response has no cell data,… - GETGet cells with formatting (includeGridData)
/spreadsheets/{{spreadsheetId}}Reads cell data with formats, notes and formulas for one range, which the values endpoints do not return. Query: ranges=Orders!A1:D3 limits the result to that…
3. Values (read and write cells)
- PUTWrite a header and rows (update)
/spreadsheets/{{spreadsheetId}}/values/Orders!A1:D4Writes values into a fixed range, overwriting whatever is there. Scope: spreadsheets (or drive.file for your app's own sheets). Path: values/Orders!A1:D4.… - GETRead a range
/spreadsheets/{{spreadsheetId}}/values/Orders!A1:D10Reads the values of one range. A1 notation: Orders!A1:D5 is a block, Orders!A:A a whole column, Orders!2:2 a whole row, Orders!A2:D from row 2 to the end, and… - GETRead several ranges (batchGet)
/spreadsheets/{{spreadsheetId}}/values:batchGetReads several ranges in one call, which counts as a single read request against your quota. Query: repeat ranges once per range. The other options are the… - POSTAppend rows after the last row (append)
/spreadsheets/{{spreadsheetId}}/values/Orders!A:D:appendAdds rows below the existing data in a table, without you working out the next free row. Ideal for logs and form submissions. How the target is found: the API… - POSTWrite several ranges (values batchUpdate)
/spreadsheets/{{spreadsheetId}}/values:batchUpdateWrites several ranges in one request (one write against your quota): here a status column header and two cells. Body: valueInputOption (required, applies to… - POSTClear a range
/spreadsheets/{{spreadsheetId}}/values/Orders!E1:E10:clearClears the values in a range. Formatting, validation and notes stay. To remove rows themselves use a deleteDimension request in batchUpdate. Body: an empty…
4. Format and structure (batchUpdate)
- POSTAdd a sheet (tab)
/spreadsheets/{{spreadsheetId}}:batchUpdateAdds a new tab named Customers, with a tab colour and a frozen first row. Request kind: addSheet with properties (title must be unique; leave out sheetId and… - POSTRename a sheet
/spreadsheets/{{spreadsheetId}}:batchUpdateRenames the tab added by Add a sheet (newSheetId). Request kind: updateSheetProperties with properties (the sheetId and the new values) and fields, a field… - POSTMake the header row bold with a background
/spreadsheets/{{spreadsheetId}}:batchUpdateFormats row 1 (A1:E1) of Orders: bold text and a light grey background. Request kind: repeatCell applies one cell to every cell of range. fields lists exactly… - POSTFreeze the header row
/spreadsheets/{{spreadsheetId}}:batchUpdateFreezes row 1 so it stays visible while scrolling. Request kind: updateSheetProperties with gridProperties.frozenRowCount: 1 and fields:… - POSTAuto-resize columns
/spreadsheets/{{spreadsheetId}}:batchUpdateResizes columns A-E of Orders to fit their contents, like double-clicking the column border in the UI. Request kind: autoResizeDimensions with dimensions:… - POSTHighlight large orders (conditional formatting)
/spreadsheets/{{spreadsheetId}}:batchUpdateAdds a rule that colours the Total cell green when it is greater than 100. Request kind: addConditionalFormatRule with rule.ranges (grid ranges),… - POSTSort rows by total
/spreadsheets/{{spreadsheetId}}:batchUpdateSorts the two order rows written by the first values request (rows 2-3, columns A-E) by Total, largest first, leaving the header where it is. Widen…
5. Developer metadata
- POSTTag a column with developer metadata
/spreadsheets/{{spreadsheetId}}:batchUpdateTags column D (Total) of Orders with column=total. Request kind: createDeveloperMetadata with metadataKey, metadataValue, visibility (DOCUMENT or PROJECT) and… - POSTSearch developer metadata
/spreadsheets/{{spreadsheetId}}/developerMetadata:searchFinds metadata by key (or value, location or ID) and tells you where it currently is, so you can locate the tagged column even after users moved it. Body:… - GETGet developer metadata by ID
/spreadsheets/{{spreadsheetId}}/developerMetadata/{{metadataId}}Reads one metadata entry by its numeric ID (metadataId, saved by Tag a column with developer metadata). What comes back: the DeveloperMetadata with key,… - POSTDelete a sheet (tab)
/spreadsheets/{{spreadsheetId}}:batchUpdateDeletes the tab created by Add a sheet (newSheetId), with all its data. Cleans up after the walkthrough. Request kind: deleteSheet with sheetId. What comes…
6. Public sheets with an API key
- GETRead a public sheet with an API key
/spreadsheets/{{publicSpreadsheetId}}/values/A1:D20Reads values from a link-shared spreadsheet with only an API key: handy for public price lists, schedules or open data. Setup: set publicSpreadsheetId to the…
API details
- Base URL
https://sheets.googleapis.com/v4- Authentication
- A bearer token in the Authorization header
OAuth 2.0 (authorization code flow) with 5 scopes
An API key in the key query parameter - Operations
- 24 operations on 24 pathsPOST 16GET 7PUT 1
- Main resources
- 1. Get an access token 32. Spreadsheets 33. Values (read and write cells) 64. Format and structure (batchUpdate) 75. Developer metadata 46. Public sheets with an API key 1
- Description format
- Swastik Codes guided collection, API version
checked against the docs on 2026-11-17 - Links
- Documentation
How to test the Google Sheets API in Swastik Codes
- Open the APISelect “Fork & run in Swastik Codes” on this page. Sign in or create a free account if asked.
- Fork it into your workspaceChoose a workspace. You get your own copy of the Google Sheets API collection, without the publisher's secrets.
- Add your credentialsThis API uses A bearer token in the Authorization header. Add yours in the collection's variables or an environment; the requests already reference it.
- Send a requestPick an endpoint, for example “Build the sign-in URL (open in a browser)” (GET https://accounts.google.com/o/oauth2/v2/auth), and press Send. Check the response, add tests and run the whole collection with the runner.
Frequently asked questions
What is the Google Sheets API?
Create spreadsheets, read and write cells with A1 ranges, append rows, format, sort and tag data with the Sheets API v4. It is listed under Productivity & workspace and has 24 endpoints.
Who publishes the Google Sheets API?
It is listed from Google's public API description (an OpenAPI document), not published by Google on Swastik Codes. Anyone who controls googleapis.com can claim the profile by adding a DNS TXT record.
What is the base URL of the Google Sheets API?
Requests go to https://sheets.googleapis.com/v4. Endpoint paths are added to this base URL.
How do I authenticate with the Google Sheets API?
It accepts 3 methods: A bearer token in the Authorization header; OAuth 2.0 (authorization code flow) with 5 scopes; an API key in the key query parameter.
How many endpoints does the Google Sheets API have?
24 operations on 24 paths (16 POST, 7 GET, 1 PUT). The largest groups are 1. Get an access token, 2. Spreadsheets, 3. Values (read and write cells), 4. Format and structure (batchUpdate).
Is forking the Google Sheets API free?
Yes. Forking copies the collection into your workspace for free. It has been starred 0 times and forked 0 times.