DOCSEARCHbyAlgolia
Submit Docs

gspread

repository·master·Indexed 27 days ago

https://github.com/burnash/gspread

A simple Python interface for interacting with Google Sheets via the Google Sheets API. It allows developers to read, write, format, and manage spreadsheets using model entities for Spreadsheets, Worksheets, and Cells. Supports multiple authentication methods including service accounts, OAuth2, API keys, and Application-Default Credentials (ADC). Requires Python 3.8+ for version 6.0.

Tokens
16.6K
Snippets
45
Records
142
Agent score
92%

What's inside gspread

  1. Understand gspread model entities

    master

    The gspread library uses three primary model entities to represent spreadsheet data:

    1. Spreadsheet: Represents a Google Spreadsheet file.
    2. Worksheet: Represents an individual sheet within a spreadsheet.
    3. Cell: Represents an individual cell within a worksheet.

    Important: Do not attempt to instantiate these classes directly. Instead, obtain instances of these models by calling methods on existing objects (e.g., opening a spreadsheet returns a Spreadsheet object, and accessing a sheet within that spreadsheet returns a Worksheet object).

  2. Migrate from v5.12 to v6.0

    master

    If upgrading from version 5.12, note the following breaking changes:

    • Python Version: Requires Python 3.8+.
    • Worksheet.update: Arguments are swapped. Use update(range_name, values) or use named arguments update(range_name='A1', values=[...]) for compatibility.
    • Worksheet.update values: values must now be a 2D array (list of lists).
    • Colors: Use hexadecimal strings (e.g., '#FF7FFF') instead of color dictionaries.
    • lastUpdateTime: Changed from a property to a method: get_lastUpdateTime().
    • Worksheet.get_records: This method has been removed. Use get_all_records() to get all sheet records, or combine get() with gspread.utils.to_records() for partial fetches.
    • Environment Variable: Use GSPREAD_SILENCE_WARNINGS=1 to silence deprecation warnings.

  4. Access public spreadsheets using an API Key

    master

    An API key allows your application to access public spreadsheet files only. This method does not support private files.

    Setup

    1. Enable API Access in the Google Cloud Console.
    2. Go to APIs & Services > Credentials.
    3. Select Create credentials > API key.
    4. Copy the generated key.

    Usage Constraints

    • You cannot use gc.open() with an API key, as that method searches for private files by name.
    • You must use gc.open_by_key() or gc.open_by_url() to access public spreadsheets.
    import gspread

gc = gspread.api_key("<your newly create key>")

# Use open_by_key or open_by_url for public sheets
sh = gc.open_by_key("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms")

print(sh.sheet1.get('A1'))

  6. Authenticate gspread in Google Colaboratory

    master

    To use gspread within a Google Colab notebook, use the google.colab.auth module to authenticate the user, then retrieve default credentials using google.auth.default() to authorize the gspread client.

    from google.colab import auth
auth.authenticate_user()

import gspread
from google.auth import default
creds, _ = default()

gc = gspread.authorize(creds)

  10. Use Authlib for custom authentication

    master

    You can use Authlib's AssertionSession instead of google-auth. This is useful if you want to leverage AssertionSession's automatic token refreshing. Pass the session to the gspread.Client constructor.

    import json
from gspread import Client
from authlib.integrations.requests_client import AssertionSession

def create_assertion_session(conf_file, scopes, subject=None):
    with open(conf_file, 'r') as f:
        conf = json.load(f)

    token_url = conf['token_uri']
    issuer = conf['client_email']
    key = conf['private_key']
    key_id = conf.get('private_key_id')

    header = {'alg': 'RS256'}
    if key_id:
        header['kid'] = key_id

    # Google puts scope in payload
    claims = {'scope': ' '.join(scopes)}
    return AssertionSession(
        grant_type=AssertionSession.JWT_BEARER_GRANT_TYPE,
        token_endpoint=token_url,
        issuer=issuer,
        audience=token_url,
        claims=claims,
        subject=subject,
        key=key,
        header=header,
    )

scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive',
]
session = create_assertion_session('your-google-conf.json', scopes)
gc = Client(None, session)

  11. Open a Spreadsheet

    master

    You can open an existing Google Spreadsheet using its title, its unique key (from the URL), or the full spreadsheet URL.

    Note: If multiple sheets have the same title, open() will return the latest one. It is recommended to use open_by_key() for precision.