How to Read and Write Data with the Google Sheets API

The Google Sheets API is a RESTful interface that lets you read and modify a spreadsheet’s data. Engineers build integrations to read cell values, append rows, and run batch updates across workspaces. This Google Sheets API tutorial explains the core spreadsheets and values endpoints, reading and writing data ranges, and managing usage quotas.

Key Takeaways

• The API uses the base URL https://sheets.googleapis.com/v4/ and requires OAuth 2.0 authentication.
• Read operations do not use page tokens; developers must use specific ranges and batch methods.
• Google enforces limits of 300 read and 300 write requests per minute per project, and 60 of each per minute per user per project.

Setup and Authentication

API access requires an authorization token passed via HTTP headers. To authenticate, you must create one or more OAuth 2.0 Client IDs. According to Google’s own docs, a client ID is used to identify a single app to Google’s OAuth servers.

The service relies on the base URL https://sheets.googleapis.com/v4/. Authentication is managed by adding an Authorization header with a bearer token to each API request. For complete setup steps, read our guide on Google Sheets API authentication.

With Knit: Embed Knit’s auth component so your users can authorize integrations without leaving your product.

Reading Metadata and Handling Pagination

The API requires a different approach for reading spreadsheet properties versus cell data. You use the GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId} endpoint and pass a fields parameter to filter the returned metadata.

When handling large datasets, be aware that there are no page tokens on values.get. You cannot use standard cursor pagination. Instead, you use specific A1 ranges, spreadsheets.get fields masks, and batchGet to retrieve data efficiently without overloading the application.

Reading Data Ranges: values.get and batchGet

Retrieving cell data requires specifying the exact sheet and cells. The Google Sheets API reference defines the endpoint to get spreadsheet values using an HTTP request: GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}.

The path parameters are spreadsheetId, which is the string ID of the spreadsheet to retrieve data from, and range, which is the A1 notation or R1C1 notation of the range to retrieve values from. To fetch multiple distinct ranges simultaneously, use the GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet endpoint.

Here is a curl example for reading a range:

curl \
  'https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1!A1:D5' \
  --header 'Authorization: Bearer <access_token>' \
  --header 'Accept: application/json'

You can also write Python scripts using the requests library to fetch these exact URLs.

Writing Data: update, append, and batchUpdate

Modifying a spreadsheet involves targeting specific ranges or pushing bulk edits. When you write to Google Sheets API endpoints, modifying an existing range requires the PUT https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range} endpoint and a valueInputOption parameter to determine how input data is parsed. RAW stores values as-is, while USER_ENTERED parses them as if a user typed them into the UI. To add new data at the end of a sheet, a Google Sheets API append row operation requires the POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append endpoint. The API searches the input range for existing data to find a table and adds the new values after the table’s last row, starting from its first column. The insertDataOption parameter controls how the new data lands: OVERWRITE writes over existing data in the areas it is written, while INSERT_ROWS inserts rows for the new data.

Here is a curl example for appending a row:

curl -X POST \
  'https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/Sheet1!A1:D1:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS' \
  --header 'Authorization: Bearer <access_token>' \
  --header 'Content-Type: application/json' \
  --data '{"values": [["Jane Doe", "jane@example.com", "2026-11-03"]]}'

The request body is a ValueRange resource, and its values field is an array of arrays: each inner array is one row, and each item in it is one cell.

For bulk edits, the official reference states the values:batchUpdate endpoint is an HTTP request: POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchUpdate.

Google Sheets API Limits and Handling 429 Errors

Google enforces usage quotas on all API requests. These limits are specific to Google Sheets.

According to the usage limits page, read requests are limited to 300 per minute per project and 60 per minute per user per project. Write requests have the same limits: 300 per minute per project and 60 per minute per user per project. If your app sends 350 requests in one minute, the additional 50 requests exceed the quota and generate a 429: Too many requests HTTP status code response.

To handle a rate limit block, implement exponential backoff with jitter. For architecture patterns on managing these constraints, read our guide on API rate limiting best practices.

import time
import random
import requests

def get_sheet_with_backoff(url, headers):
    for n in range(5):
        response = requests.get(url, headers=headers)
        if response.status_code != 429:
            return response
        
        sleep_time = (2 ** n) + random.uniform(0, 1)
        time.sleep(sleep_time)
    return None

Frequently Asked Questions

How do I read and write data ranges using the Google Sheets API?

You read data by sending a GET request to the values.get endpoint with the spreadsheet ID and A1 notation range. You write data using POST or PUT requests to endpoints like values.update or values:batchUpdate.

How do I append a new row programmatically?

You send a POST request to the values.append endpoint with a valueInputOption and a request body whose values field holds the row. Use the insertDataOption parameter to choose between OVERWRITE and INSERT_ROWS.

What are the Google Sheets API read/write quotas?

Read and write requests are each limited to 300 per minute per project and 60 per minute per user per project.

How do I handle 429 Too Many Requests errors?

You handle a 429 HTTP status code by pausing your script. Implement an exponential backoff algorithm with jitter to wait before retrying the failed request.

How do I paginate through a large spreadsheet using the API?

The API does not offer page tokens for cell values. You handle large datasets by requesting specific A1 ranges, using the spreadsheets.get fields masks to check sheet boundaries, and making batchGet calls.

To start building, check the provider’s docs and set up your OAuth credentials.