Api/Models/Index

Models
======

The models represent common spreadsheet entities: :class:a spreadsheet <gspread.spreadsheet.Spreadsheet>,
:class:a worksheet <gspread.worksheet.Worksheet> and :class:a cell <gspread.cell.Cell>.

.. note::

The classes described below should not be instantiated by the end-user. Their
instances result from calling other objects' methods.

.. toctree::
:maxdepth: 2

spreadsheet
worksheet
cell

---

Api/Models/Worksheet

Worksheet
=========

ValueRange
----------

.. autoclass:: gspread.worksheet.ValueRange
:members:

Worksheet
---------

.. autoclass:: gspread.worksheet.Worksheet
:members:

---

Api/Exceptions

Exceptions
==========

.. autoexception:: gspread.exceptions.APIError
.. autoexception:: gspread.exceptions.GSpreadException
.. autoexception:: gspread.exceptions.IncorrectCellLabel
.. autoexception:: gspread.exceptions.InvalidInputValue
.. autoexception:: gspread.exceptions.NoValidUrlKeyFound
.. autoexception:: gspread.exceptions.SpreadsheetNotFound
.. autoexception:: gspread.exceptions.UnSupportedExportFormat
.. autoexception:: gspread.exceptions.WorksheetNotFound

---

Api/Http Client

HTTP Client
===========

.. note::

This class is not intended to be used directly.
It is used by all gspread models to interact with the Google API

.. autoclass:: gspread.HTTPClient
:members:

.. autoclass:: gspread.BackOffHTTPClient
:members:

---

Api/Index

API Reference
=============

.. toctree::
:maxdepth: 2

top-level
auth
client
http_client
models/index
utils
exceptions

---

Api/Top Level

Top level
=========

.. module:: gspread

.. autofunction:: oauth
.. autofunction:: service_account
.. autofunction:: authorize

---

Advanced

Advanced Usage
==============

Custom Authentication
---------------------

Google Colaboratory
~~~~~~~~~~~~~~~~~~~

If you familiar with the Jupyter Notebook, Google Colaboratory <https://colab.research.google.com/>_ is probably the easiest way to get started using gspread::

from google.colab import auth
auth.authenticate_user()

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

gc = gspread.authorize(creds)

See the full example in the External data: Local Files, Drive, Sheets, and Cloud Storage <https://colab.research.google.com/notebooks/io.ipynb#scrollTo=sOm9PFrT8mGG>_ notebook.

Using Authlib
~~~~~~~~~~~~~

Using `Authlib instead of google-auth. Similar to google.auth.transport.requests.AuthorizedSession <https://google-auth.readthedocs.io/en/latest/reference/google.auth.transport.requests.html#google.auth.transport.requests.AuthorizedSession>_ Authlib's AssertionSession can automatically refresh tokens.::

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)

wks = gc.open("Where is the money Lebowski?").sheet1

wks.update_acell('B2', "it's down there somewhere, let me take another look.")

# Fetch a cell range cell_list = wks.range('A1:B7')

---

`Community`

Community Extensions ====================

.. _gspread-formating-label:

gspread-formating ~~~~~~~~~~~~~~~~~

gspread-formatting <https://github.com/robin900/gspread-formatting>_ offers extensive functionality to help you when you go beyond basic format provided bygspread.

.. _gspread-pandas-label:

Using gspread with pandas ~~~~~~~~~~~~~~~~~~~~~~~~~

You can find the below libraries to use gspread with pandas:

* gspread-pandas <https://github.com/aiguofer/gspread-pandas>_ *gspread-dataframe <https://github.com/robin900/gspread-dataframe>_

.. _gspread-orm-label:

Object Relational Mappers (ORMs) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The gspread-models <https://github.com/s2t2/gspread-models-py>_ package provides a straightforward and intuitive model-based query interface, making it easy to interact with Google Sheets as if it were more like a database.

---

`Index`

gspread =======

gspread_ is a Python API for Google Sheets.

Features:

- Google Sheets API v4. - Open a spreadsheet by title, key or url. - Read, write, and format cell ranges. - Sharing and access control. - Batching updates.

Installation ------------

.. code:: sh

pip install gspread

Requirements: Python 3+.

Quick Example -------------

.. code:: python

import gspread

gc = gspread.service_account()

# Open a sheet from a spreadsheet in one go wks = gc.open("Where is the money Lebowski?").sheet1

# Update a range of cells using the top left corner address wks.update([[1, 2], [3, 4]], 'A1')

# Or update a single cell wks.update_acell('B42', "it's down there somewhere, let me take another look.")

# Format the header wks.format('A1:B1', {'textFormat': {'bold': True}})

Getting Started ---------------

.. toctree:: :maxdepth: 2

oauth2

Usage -----

.. toctree:: :maxdepth: 2

user-guide

Advanced --------

.. toctree:: :maxdepth: 2

advanced

Community extensions --------------------

.. toctree:: :maxdepth: 2

community

API Documentation ---------------------------

.. toctree:: :maxdepth: 2

api/index

How to Contribute -----------------

Please make sure to take a moment and read the Code of Conduct_.

Ask Questions ~~~~~~~~~~~~~

The best way to get an answer to a question is to ask on Stack Overflow
with a gspread tag_.

Report Issues ~~~~~~~~~~~~~

Please report bugs and suggest features via the GitHub Issues_.

Before opening an issue, search the tracker for possible duplicates. If you find a duplicate, please add a comment saying that you encountered the problem as well.

Contribute code ~~~~~~~~~~~~~~~

Please make sure to read the Contributing Guide_ before making a pull request.

Indices and tables ==================

* :ref:genindex* :ref:modindex* :ref:search

.. _gspread: https://github.com/burnash/gspread .. _Obtain OAuth2 credentials from Google Developers Console: oauth2.html .. _Code of Conduct: https://github.com/burnash/gspread/blob/master/.github/CODE_OF_CONDUCT.md .. _Stack Overflow with a gspread tag: http://stackoverflow.com/questions/tagged/gspread?sort=votes&pageSize=50 .. _GitHub Issues: https://github.com/burnash/gspread/issues .. _Contributing Guide: https://github.com/burnash/gspread/blob/master/.github/CONTRIBUTING.md

---

`Oauth2`

Authentication ==============

To access spreadsheets your application needs to authenticate itself with the Google Sheets API. Choose from the following options.

#. Create an :ref:API key <api-key>if you’d like to only open public spreadsheets. #. (or) Create a :ref:OAuth Client ID <oauth-client-id> if you’d like to access spreadsheets on behalf of end users (including yourself).

- When your application runs, it will prompt the user to authorize it.

#. (or) Create a :ref:Service Account <service-account> to access spreadsheets as a standalone bot.

- Service accounts get their own email address, so can be authorized by sharing the Sheet with the account in the same way it is shared with a person. - Service accounts don't need any explicit permissions to access Sheets that are shared to "anyone with the URL".

An API Key is the easiest option, but to access private Sheets you need authorization. To provide that interactively, use an OAuth Client ID. To pre-configure authorization, use a Service Account.

.. _enable-api-access:

Enable API Access for a Project -------------------------------

1. Head to Google Developers Console <https://console.developers.google.com/>_ and create a new project (or select the one you already have).

2. In the box labeled "Search for APIs and Services", search for "Google Drive API" and enable it.

3. In the box labeled "Search for APIs and Services", search for "Google Sheets API" and enable it.

.. _service-account:

For Bots: Using Service Account -------------------------------

A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs [sic].

Since it's a separate account, by default it does not have access to any spreadsheet until you share it with this account. Just like any other Google account.

Here's how to get one:

1. :ref:enable-api-access if you haven't done it yet.

2. Go to "APIs & Services > Credentials" and choose "Create credentials > Service account key".

3. Fill out the form

4. Click "Create" and "Done".

5. Press "Manage service accounts" above Service Accounts.

6. Press on ⋮ near recently created service account and select "Manage keys" and then click on "ADD KEY > Create new key".

7. Select JSON key type and press "Create".

You will automatically download a JSON file with credentials. It may look like this:

::

{ "type": "service_account", "project_id": "api-project-XXX", "private_key_id": "2cd … ba4", "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n", "client_email": "[email protected]", "client_id": "473 … hd.apps.googleusercontent.com", ... }

Remember the path to the downloaded credentials file. Also, in the next step you'll need the value of client_email from this file.

6. Very important! Go to your spreadsheet and share it with a client_email from the step above. Just like you do with any other Google account. If you don't do this, you'll get a gspread.exceptions.SpreadsheetNotFound exception when trying to access this spreadsheet from your application or a script.

7. Move the downloaded file to ~/.config/gspread/service_account.json. Windows users should put this file to %APPDATA%\gspread\service_account.json.

8. Create a new Python file with this code:

::

import gspread

gc = gspread.service_account()

sh = gc.open("Example spreadsheet")

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

Ta-da!

.. NOTE:: If you want to store the credentials file somewhere else, specify the path toservice_account.json in :meth:~gspread.service_account: ::

gc = gspread.service_account(filename='path/to/the/downloaded/file.json')

Make sure you store the credentials file in a safe place.

For the curious, under the hood :meth:~gspread.service_accountloads your credentials and authorizes gspread. Similarly to the code that has been used for authentication prior to the gspread version 3.6:

::

from google.oauth2.service_account import Credentials

scopes = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive' ]

credentials = Credentials.from_service_account_file( 'path/to/the/downloaded/file.json', scopes=scopes )

gc = gspread.authorize(credentials)

There is also the option to pass credentials as a dictionary:

::

import gspread credentials = { "type": "service_account", "project_id": "api-project-XXX", "private_key_id": "2cd … ba4", "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n", "client_email": "[email protected]", "client_id": "473 … hd.apps.googleusercontent.com", ... }

gc = gspread.service_account_from_dict(credentials)

sh = gc.open("Example spreadsheet")

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

.. NOTE:: Older versions of gspread have usedoauth2client <https://github.com/google/oauth2client>_. Google hasdeprecated <https://google-auth.readthedocs.io/en/latest/oauth2client-deprecation.html>_ it in favor ofgoogle-auth. If you're still using oauth2clientcredentials, the library will convert these togoogle-authfor you, but you can change your code to use the new credentials to make sure nothing breaks in the future.

.. _oauth-client-id:

For Bots Running inside GCP (Cloud Run, Cloud Functions, Cloud Build): Using Application-Default Credentials ------------------------------------------------------------------------------------------------------------

When your code runs inside Google Cloud, every container, function, or build already has an identity—the service account the runtime is configured to use. Google injects a short-lived OAuth 2.0 access token for that service account, so you don’t need to ship or mount a JSON key file. All you have to do is:

1. :ref:enable-api-access if you haven’t done it yet (Sheets API and Drive API).

2. Attach a service account to the Cloud Run service / Cloud Function / Cloud Build step. Share the target spreadsheet with the service account’s email address (e.g.[email protected]) just as you would with a colleague.

3. Use google.auth.default() to pick up the in-runtime credentials and hand them to gspread:

::

import google.auth import gspread

SCOPES = [ "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive", ]

creds, _ = google.auth.default(scopes=SCOPES) gc = gspread.authorize(creds)

sh = gc.open_by_key("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms") print(sh.sheet1.get("A1"))

* No GOOGLE_APPLICATION_CREDENTIALSenvironment variable. * No JSON key copied into the container. * Tokens are rotated automatically by the platform.

4. Local testing: run

::

gcloud auth application-default login \ --scopes=https://www.googleapis.com/auth/drive,\ https://www.googleapis.com/auth/spreadsheets

to emulate the same Application-Default Credentials flow on your laptop.

.. note:: If you forget to pass the Sheets and Drive scopes when callinggoogle.auth.default(scopes=...)you will get a *403: insufficient permissions* error even though the code is running on GCP. Always include both scopes.

.. warning:: ADC proves who your code is, but Sheets access is still controlled by the spreadsheet’s share list. Make sure the service account is listed there, otherwise you’ll seegspread.exceptions.SpreadsheetNotFound.

For End Users: Using OAuth Client ID ------------------------------------

This is the case where your application or a script is accessing spreadsheets on behalf of an end user. When you use this scenario, your application or a script will ask the end user (or yourself if you're running it) to grant access to the user's data.

1. :ref:enable-api-accessif you haven't done it yet. #. Configure the consent screen. Go to "APIs & Services > OAuth consent screen" (this opens the "Google Auth Platform"). If the project doesn't have a consent screen yet, click "Get started".

a. Under "App Information", give your app a name and select a "User support email". Click "Next". #. Under "Audience", select "External". Click "Next". #. Under "Contact Information", enter your email address. Click "Next". #. Agree to the user data policy and click "Create".

3. Add yourself as a test user. Go to "Audience", and under "Test users" click "Add users". Add the Google account you will authorize with, typically your own. #. Create the OAuth client. Go to "Clients" and click "Create client". #. Select "Desktop app" as the "Application type", name the client and click "Create". #. Download the JSON file for the client you just created (the download icon in the "OAuth 2.0 Client IDs" list). #. Move the downloaded file to~/.config/gspread/credentials.json. Windows users should put this file to %APPDATA%\gspread\credentials.json.

.. NOTE:: Only accounts listed under "Test users" can authorize the app while its publishing status is "Testing". Signing in with any other account fails withError 403: access_denied. If you are signed in to several Google accounts, make sure the browser uses the one you added above.

Create a new Python file with this code:

::

import gspread

gc = gspread.oauth()

sh = gc.open("Example spreadsheet")

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

When you run this code, it launches a browser asking you for authentication. Follow the instruction on the web page. Once finished, gspread stores the authorized credentials in authorized_user.json, in the config directory next to credentials.json — i.e. ~/.config/gspread/authorized_user.json, or %APPDATA%\gspread\authorized_user.jsonon Windows. This file contains a refresh token, so following runs reuse it: you only need to do authorization in the browser once.

.. NOTE:: Google shows a "Google hasn't verified this app" warning during authorization. This is expected: the app requests sensitive scopes and has not gone through Google's verification process, which is only required to distribute it to other people. To continue, click "Advanced", then "Go to <your app name> (unsafe)".

.. WARNING:: While the publishing status of your app is "Testing", Google expires the refresh token after 7 days. Once it expires, gspread fails withgoogle.auth.exceptions.RefreshError: invalid_grant.

To authorize again, delete authorized_user.json and re-run your code. gspread reuses an existing authorized_user.jsonwithout checking whether it is still valid, so the browser flow does not start again while the stale file is in place.

To stop the token from expiring, publish the app: go to "APIs & Services → OAuth consent screen → Audience" and click "Publish app". Read morehere <https://support.google.com/cloud/answer/15549945>_ about publishing an app.

.. NOTE:: If you want to store the credentials file somewhere else, specify the path tocredentials.json and authorized_user.json in :meth:~gspread.oauth: ::

gc = gspread.oauth( credentials_filename='path/to/the/credentials.json', authorized_user_filename='path/to/the/authorized_user.json' )

Make sure you store the credentials file in a safe place.

There is also the option to pass your credentials directly as a python dict. This way you don't have to store them as files or you can store them in your favorite password manager.

::

import gspread

credentials = { "installed": { "client_id": "12345678901234567890abcdefghijklmn.apps.googleusercontent.com", "project_id": "my-project1234", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", ... } } gc, authorized_user = gspread.oauth_from_dict(credentials)

sh = gc.open("Example spreadsheet")

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

Once authenticated you must store the returned json string containing your authenticated user information. Provide that details as a python dict as second argument in your nextoauth request to be directly authenticated and skip the flow.

.. NOTE:: The second time if your authorized user has not expired, you can omit the credentials. Be aware, if the authorized user has expired your credentials are required to authenticate again.

::

import gspread

credentials = { "installed": { "client_id": "12345678901234567890abcdefghijklmn.apps.googleusercontent.com", "project_id": "my-project1234", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", ... } } authorized_user = { "refresh_token": "8//ThisALONGTOkEn....", "token_uri": "https://oauth2.googleapis.com/token", "client_id": "12345678901234567890abcdefghijklmn.apps.googleusercontent.com", "client_secret": "MySecRet....", "scopes": [ "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive" ], "expiry": "1070-01-01T00:00:00.000001Z" } gc, authorized_user = gspread.oauth_from_dict(credentials, authorized_user)

sh = gc.open("Example spreadsheet")

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

.. warning:: Security credentials file and authorized credentials contain sensitive data. Do not share these files with others and treat them like private keys.

If you are concerned about giving the application access to your spreadsheets and Drive, use Service Accounts.

.. NOTE:: The user interface of Google Developers Console may be different when you're reading this. If you find that this document is out of sync with the actual UI, please update it. Improvements to the documentation are always welcome. Click Edit on GitHub in the top right corner of the page, make it better and submit a PR.

.. _api-key:

For public spreadsheets only ----------------------------

An API key is a token that allows an application to open public spreadsheet files.

Here's how to get one:

1. :ref:enable-api-access if you haven't done it yet.

2. Go to "APIs & Services > Credentials" and choose "Create credentials > API key"

3. A pop-up should display your newly created key.

4. Copy the key.

5. That's it your key is created.

.. note::

You can access your key any time later, come back to the "APIs & Services > Credentials" page, you'll be able to see your key again.

6. Create a new Python file with this code:

::

import gspread

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

sh = gc.open_by_key("1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms")

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

Ta-da !

.. note::

You can only open public keys, this means you can only open spreadsheet files using the methods:gc.open_by_key and gc.open_by_url.

The method gc.open()searches your private files to find the one with a matching name so it will never work.

---

`User Guide`

Examples of gspread Usage =========================

If you haven't yet authorized your app, read :doc:oauth2 first.

Opening a Spreadsheet ~~~~~~~~~~~~~~~~~~~~~

You can open a spreadsheet by its title as it appears in Google Docs:

.. code:: python

sh = gc.open('My poor gym results') .. NOTE:: If you have multiple Google Sheets with the same title, only the latest sheet will be opened by this method without throwing an error. It's recommended to open the sheet using its unique ID instead (see below)

If you want to be specific, use a key (which can be extracted from the spreadsheet's url):

.. code:: python

sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

Or, if you feel really lazy to extract that key, paste the entire spreadsheet's url

.. code:: python

sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

Creating a Spreadsheet ~~~~~~~~~~~~~~~~~~~~~~

Use :meth:~gspread.Client.create to create a new blank spreadsheet:

.. code:: python

sh = gc.create('A new spreadsheet')

.. NOTE:: If you're using a :ref:service account <service-account>, this new spreadsheet will be visible only to this account. To be able to access newly created spreadsheet from Google Sheets with your own Google account you must share it with your email. See how to share a spreadsheet in the section below.

Sharing a Spreadsheet ~~~~~~~~~~~~~~~~~~~~~

If your email is [email protected] you can share the newly created spreadsheet with yourself:

.. code:: python

sh.share('[email protected]', perm_type='user', role='writer')

See :meth:~gspread.models.Spreadsheet.share documentation for a full list of accepted parameters.

Selecting a Worksheet ~~~~~~~~~~~~~~~~~~~~~

Select worksheet by index. Worksheet indexes start from zero:

.. code:: python

worksheet = sh.get_worksheet(0)

Or by title:

.. code:: python

worksheet = sh.worksheet("January")

Or the most common case: Sheet1:

.. code:: python

worksheet = sh.sheet1

To get a list of all worksheets:

.. code:: python

worksheet_list = sh.worksheets()

Creating a Worksheet ~~~~~~~~~~~~~~~~~~~~

.. code:: python

worksheet = sh.add_worksheet(title="A worksheet", rows=100, cols=20)

Deleting a Worksheet ~~~~~~~~~~~~~~~~~~~~

.. code:: python

sh.del_worksheet(worksheet)

Updating a Worksheet's name and color ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: python

worksheet.update_title("December Transactions") worksheet.update_tab_color("#FF0000")

Getting a Cell Value ~~~~~~~~~~~~~~~~~~~~

Using A1 notation <https://developers.google.com/sheets/api/guides/concepts#a1_notation>_:

.. code:: python

val = worksheet.acell('B1').value

Or row and column coordinates:

.. code:: python

val = worksheet.cell(1, 2).value

If you want to get a cell formula:

.. code:: python

cell = worksheet.acell('B1', value_render_option='FORMULA').value

# or

cell = worksheet.cell(1, 2, value_render_option='FORMULA').value

Getting Unformatted Cell Value ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Get the Unformatted value from a cell. Example: cells formatted as currency will display with the selected currency but they actual value is regular number.

Get the formatted (as displayed) value:

.. code:: python

worksheet.get("A1:B2")

Results in: [['$12.00']]

Get the unformatted value:

.. code:: python

from gspread.utils import ValueRenderOption worksheet.get("A1:B2", value_render_option=ValueRenderOption.unformatted)

Results in: [[12]]

Getting Cell formula ~~~~~~~~~~~~~~~~~~~~

Get the formula from a cell instead of the resulting value:

.. code:: python

from gspread.utils import ValueRenderOption worksheet.get("G6", value_render_option=ValueRenderOption.formula)

Resulsts in: [['=1/1024']]

Getting All Values From a Row or a Column ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Get all values from the first row:

.. code:: python

values_list = worksheet.row_values(1)

Get all values from the first column:

.. code:: python

values_list = worksheet.col_values(1)

.. NOTE:: So far we've been fetching a limited amount of data from a sheet. This works great until you need to get values from hundreds of cells or iterating over many rows or columns.

Under the hood, gspread uses Google Sheets API v4 <https://developers.google.com/sheets/api>_. Most of the time when you call a gspread method to fetch or update a sheet gspread produces one HTTP API call.

HTTP calls have performance costs. So if you find your app fetching values one by one in a loop or iterating over rows or columns you can improve the performance of the app by fetching data in one go.

What's more, Sheets API v4 introduced Usage Limits <https://developers.google.com/sheets/api/limits>_ (as of this writing, 300 requests per 60 seconds per project, and 60 requests per 60 seconds per user). When your application hits that limit, you get an :exc:~gspread.exceptions.APIError 429 RESOURCE_EXHAUSTED.

Here are the methods that may help you to reduce API calls:

* :meth:~gspread.models.Worksheet.get_all_valuesfetches values from all of the cells of the sheet. * :meth:~gspread.models.Worksheet.getfetches all values from a range of cells. * :meth:~gspread.models.Worksheet.batch_getcan fetch values from multiple ranges of cells with one API call. * :meth:~gspread.models.Worksheet.updatelets you update a range of cells with a list of lists. * :meth:~gspread.models.Worksheet.batch_update lets you update multiple ranges of cells with one API call.

Getting All Values From a Worksheet as a List of Lists ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

.. code:: python

list_of_lists = worksheet.get_all_values()

Getting All Values From a Worksheet as a List of Dictionaries ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using get_all_records() (assumes first row contains headers):

.. code:: python

list_of_dicts = worksheet.get_all_records()

Alternatively, use gspread.utils.to_records() for more control over headers:

.. code:: python

# Define custom headers headers = ["fruit", "alternate name", "tastiness"] values = worksheet.get() records = gspread.utils.to_records(headers, values)

for record in records: print(record) # {'fruit': 'apple', 'alternate name': 'red circle', 'tastiness': 'very'} # {'fruit': 'banana', 'alternate name': 'yellow stick', 'tastiness': 'quite'}

Getting All Values From Every Worksheet at Once ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using get_all_worksheet_values() (fetches every worksheet in a single API call):

.. code:: python

all_values = spreadsheet.get_all_worksheet_values() # {'Sheet1': [['a', 'b'], ['c', 'd']], 'Sheet 2': [['1', '2']]}

Optionally, pass skip_worksheet_titles to leave out worksheets you don't need:

.. code:: python

all_values = spreadsheet.get_all_worksheet_values(skip_worksheet_titles=["Sheet1"])

Loading Worksheet Data into a DataFrame ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using pandas (or polars):

.. code:: python

records = worksheet.get_all_records() df = pd.DataFrame(records)

Finding a Cell ~~~~~~~~~~~~~~

Find a cell matching a string:

.. code:: python

cell = worksheet.find("Dough")

print("Found something at R%sC%s" % (cell.row, cell.col))

Find a cell matching a regular expression

.. code:: python

amount_re = re.compile(r'(Big|Enormous) dough') cell = worksheet.find(amount_re)

find returns None if value is not Found

Finding All Matched Cells ~~~~~~~~~~~~~~~~~~~~~~~~~

Find all cells matching a string:

.. code:: python

cell_list = worksheet.findall("Rug store")

Find all cells matching a regexp:

.. code:: python

criteria_re = re.compile(r'(Small|Room-tiering) rug') cell_list = worksheet.findall(criteria_re)

Clear A Worksheet ~~~~~~~~~~~~~~~~~

Clear one or multiple cells ranges at once:

.. code:: python

worksheet.batch_clear(["A1:B1", "C2:E2", "my_named_range"])

Clear the entire worksheet:

.. code:: python

worksheet.clear()

Cell Object ~~~~~~~~~~~

Each cell has a value and coordinates properties:

.. code:: python

value = cell.value row_number = cell.row column_number = cell.col

Updating Cells ~~~~~~~~~~~~~~

Using A1 notation <https://developers.google.com/sheets/api/guides/concepts#a1_notation>_:

.. code:: python

worksheet.update_acell('B1', 'Bingo!')

Or row and column coordinates:

.. code:: python

worksheet.update_cell(1, 2, 'Bingo!')

Update a range

.. code:: python

worksheet.update([[1, 2], [3, 4]], 'A1:B2')

Serializing Values the StandardjsonModule Cannot Encode ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

gspread encodes request bodies with the standard library jsonmodule, which cannot encode some types such asdatetime or Decimal. The data-writing methods (update, batch_update, append_rowandappend_rows) accept a default_serializerargument for these cases. It works exactly like thedefault argument of :func:json.dumps: it receives a value the encoder cannot handle and returns a JSON-serializable substitute.

.. code:: python

import datetime

worksheet.update( [[datetime.date(2026, 6, 23)]], "A1", default_serializer=str, )

str covers common types like datetime, date, DecimalandUUID. For finer control, pass your own callable:

.. code:: python

def encode(value): if isinstance(value, datetime.datetime): return value.isoformat() raise TypeError(f"cannot serialize {type(value)}")

worksheet.update([[some_value]], "A1", default_serializer=encode)

.. note::

The substitute you return determines how the value lands in the cell. For exampledefault_serializer=str sends a Decimalas the JSON string"19.99" (stored as text unless value_input_optionparses it), whereas returningfloat(value)sends a number. Choose the form that matches how you want the cell interpreted.

Adding Data Validation ~~~~~~~~~~~~~~~~~~~~~~

You can add a strict validation to a cell.

.. code:: python

ws.add_validation( 'A1', ValidationConditionType.number_greater, [10], strict=True, inputMessage='Value must be greater than 10', )

Or add validation with a drop down.

.. code:: python worksheet.add_validation( 'C2:C7', ValidationConditionType.one_of_list, ['Yes', 'No'], showCustomUi=True )

Check out the api docs forDataValidationRule_ and CondtionType_ for more details.

.. _CondtionType: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionType

.. _DataValidationRule: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#DataValidationRule

Extract table ~~~~~~~~~~~~~

Gspread provides a function to extract a data table. A data table is defined as a rectangular table that stops either on the first empty cell or the enge of the sheet.

You can extract table from any address by providing the top left corner of the desired table.

Gspread provides 3 directions for searching the end of the table:

* :attr:~gspread.utils.TableDirection.right: extract a single row searching on the right of the starting cell * :attr:~gspread.utils.TableDirection.down: extract a single column searching on the bottom of the starting cell * :attr:~gspread.utils.TableDirection.table: extract a rectangular table by first searching right from starting cell, then searching down from starting cell.

.. note::

Gspread will not look for empty cell inside the table. it only look at the top row and first column.

Example extracting a table from the below sample sheet:

.. list-table:: Find table :header-rows: 1

* - ID - Name - Universe - Super power * - 1 - Batman - DC - Very rich * - 2 - DeadPool - Marvel - self healing * - 3 - Superman - DC - super human * - - \- - \- - \- * - 5 - Lavigne958 - - maintains Gspread * - 6 - Alifee - - maintains Gspread

Using the below code will result in rows 2 to 4:

.. code:: python

worksheet.expand("A2")

[ ["Batman", "DC", "Very rich"], ["DeadPool", "Marvel", "self healing"], ["Superman", "DC", "super human"], ]

Formatting ~~~~~~~~~~

Here's an example of basic formatting.

Set A1:B1 text format to bold:

.. code:: python

worksheet.format('A1:B1', {'textFormat': {'bold': True}})

Color the background of A2:B2 cell range in black, change horizontal alignment, text color and font size:

.. code:: python

worksheet.format("A2:B2", { "backgroundColor": { "red": 0.0, "green": 0.0, "blue": 0.0 }, "horizontalAlignment": "CENTER", "textFormat": { "foregroundColor": { "red": 1.0, "green": 1.0, "blue": 1.0 }, "fontSize": 12, "bold": True } })

The second argument to :meth:~gspread.models.Worksheet.format is a dictionary containing the fields to update. A full specification of format options is available at CellFormat <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#cellformat>_ in Sheet API Reference.

.. Tip:: for more complex formatting see :ref:gspread-formating-label.

Named Ranges ~~~~~~~~~~~~

List all named ranges in a spreadsheet:

.. code:: python

named_ranges = spreadsheet.list_named_ranges()

Update an existing named range (rename it, change its range, or both):

.. code:: python

worksheet.update_named_range("named_range_id", new_name="new_name") worksheet.update_named_range("named_range_id", new_range="A1:B10") worksheet.update_named_range("named_range_id", new_name="new_name", new_range="A1:B10")

Delete a named range:

.. code:: python

worksheet.delete_named_range("named_range_id")

.. Note:: Thenamed_range_id can be obtained from :meth:~gspread.Spreadsheet.list_named_ranges.

Using gspread with pandas ~~~~~~~~~~~~~~~~~~~~~~~~~

pandas <https://pandas.pydata.org/>_ is a popular library for data analysis. The simplest way to get data from a sheet to a pandas DataFrame is with :meth:~gspread.models.Worksheet.get_all_records:

.. code:: python

import pandas as pd

dataframe = pd.DataFrame(worksheet.get_all_records())

Here's a basic example for writing a dataframe to a sheet. With :meth:~gspread.models.Worksheet.update we put the header of a dataframe into the first row of a sheet followed by the values of a dataframe:

.. code:: python

import pandas as pd

worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())

For advanced pandas use cases check out community section :ref:gspread-pandas-label

Using gspread with NumPy ~~~~~~~~~~~~~~~~~~~~~~~~

NumPy <https://numpy.org/>_ is a library for scientific computing in Python. It provides tools for working with high performance multi-dimensional arrays.

Read contents of a sheet into a NumPy array:

.. code:: python

import numpy as np array = np.array(worksheet.get_all_values())

The code above assumes that your data starts from the first row of the sheet. If you have a header row in the first row, you need replace worksheet.get_all_values() with worksheet.get_all_values()[1:].

Write a NumPy array to a sheet:

.. code:: python

import numpy as np

array = np.array([[1, 2, 3], [4, 5, 6]])

# Write the array to worksheet starting from the A2 cell worksheet.update(array.tolist(), 'A2')

---

`README`

`Google Spreadsheets Python API v4`

`Maintainer needed`

We are sorry to announce that we are currently unable to maintain Gspread.

We are looking for new maintainers to keep up the good work. Feel free to reach out to us using this issue #1570

`Overview`

Simple interface for working with Google Sheets.

Features:

- Open a spreadsheet by title, key or URL. - Read, write, and format cell ranges. - Sharing and access control. - Batching updates.

`Installation`

sh

pip install gspread

Requirements: Python 3.8+.

`Basic Usage`

1. Create credentials in Google API Console

2. Start using gspread

python

import gspread
First you need access to the Google API. Based on the route you

chose in Step 1, call either service_account(), oauth() or api_key().

gc = gspread.service_account()
Open a sheet from a spreadsheet in one go

wks = gc.open("Where is the money Lebowski?").sheet1
Update a range of cells using the top left corner address

wks.update([[1, 2], [3, 4]], "A1")
Or update a single cell

wks.update_acell("B42", "it's down there somewhere, let me take another look.")
Format the header

wks.format('A1:B1', {'textFormat': {'bold': True}})

`v5.12 to v6.0 Migration Guide`

`Upgrade from Python 3.7`

Python 3.7 is end-of-life. gspread v6 requires a minimum of Python 3.8.

`Change` Worksheet.update `arguments`

The first two arguments (values & range_name) have swapped (to range_name & values). Either swap them (works in v6 only), or use named arguments (works in v5 & v6).

As well, values can no longer be a list, and must be a 2D array.

diff

- file.sheet1.update([["new", "values"]])
+ file.sheet1.update([["new", "values"]]) # unchanged
- file.sheet1.update("B2:C2", [["54", "55"]])
+ file.sheet1.update([["54", "55"]], "B2:C2")
or

+ file.sheet1.update(range_name="B2:C2", values=[["54", "55"]])

`More`

<details><summary>See More Migration Guide</summary>

`Change colors from dictionary to text`

v6 uses hexadecimal color representation. Change all colors to hex. You can use the compatibility function gspread.utils.convert_colors_to_hex_value() to convert a dictionary to a hex string.

diff

- tab_color = {"red": 1, "green": 0.5, "blue": 1}
+ tab_color = "#FF7FFF"
file.sheet1.update_tab_color(tab_color)

`Switch lastUpdateTime from property to method`

diff

- age = spreadsheet.lastUpdateTime
+ age = spreadsheet.get_lastUpdateTime()

`Replace method` Worksheet.get_records

In v6 you can now only get all sheet records, using Worksheet.get_all_records(). The method Worksheet.get_records() has been removed. You can get some records using your own fetches and combine them with gspread.utils.to_records().

diff

+ from gspread import utils
  all_records = spreadsheet.get_all_records(head=1)
- some_records = spreadsheet.get_all_records(head=1, first_index=6, last_index=9)
- some_records = spreadsheet.get_records(head=1, first_index=6, last_index=9)
+ header = spreadsheet.get("1:1")[0]
+ cells = spreadsheet.get("6:9")
+ some_records = utils.to_records(header, cells)

`Silence warnings`

In version 5 there are many warnings to mark deprecated feature/functions/methods. They can be silenced by setting theGSPREAD_SILENCE_WARNINGS environment variable to 1

`Add more data to` gspread.Worksheet.init

diff

gc = gspread.service_account(filename="google_credentials.json")
  spreadsheet = gc.open_by_key("{{key}}")
  properties = spreadsheet.fetch_sheet_metadata()["sheets"][0]["properties"]
- worksheet = gspread.Worksheet(spreadsheet, properties)
+ worksheet = gspread.Worksheet(spreadsheet, properties, spreadsheet.id, gc.http_client)

</details>

`More Examples`

`Opening a Spreadsheet`

python

You can open a spreadsheet by its title as it appears in Google Docs

sh = gc.open('My poor gym results') # <-- Look ma, no keys!
If you want to be specific, use a key (which can be extracted from

the spreadsheet's url)

sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
Or, if you feel really lazy to extract that key, paste the entire url

sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

`Creating a Spreadsheet`

python

sh = gc.create('A new spreadsheet')
But that new spreadsheet will be visible only to your script's account.

To be able to access newly created spreadsheet you must share it

with your email. Which brings us to…

`Sharing a Spreadsheet`

python

sh.share('[email protected]', perm_type='user', role='writer')

`Selecting a Worksheet`

python

Select worksheet by index. Worksheet indexes start from zero

worksheet = sh.get_worksheet(0)
By title

worksheet = sh.worksheet("January")
Most common case: Sheet1

worksheet = sh.sheet1
Get a list of all worksheets

worksheet_list = sh.worksheets()

`Creating a Worksheet`

python

worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")

`Deleting a Worksheet`

python

sh.del_worksheet(worksheet)

`Getting a Cell Value`

python

With label

val = worksheet.get('B1').first()
With coords

val = worksheet.cell(1, 2).value

`Getting All Values From a Row or a Column`

python

Get all values from the first row

values_list = worksheet.row_values(1)
Get all values from the first column

values_list = worksheet.col_values(1)

`Getting All Values From a Worksheet as a List of Lists`

python

from gspread.utils import GridRangeType
list_of_lists = worksheet.get(return_type=GridRangeType.ListOfLists)

`Getting a range of values`

Receive only the cells with a value in them.

python

>>> worksheet.get("A1:B4")
[['A1', 'B1'], ['A2']]

Receive a rectangular array around the cells with values in them.

python

>>> worksheet.get("A1:B4", pad_values=True)
[['A1', 'B1'], ['A2', '']]

Receive an array matching the request size regardless of if values are empty or not.

python

>>> worksheet.get("A1:B4", maintain_size=True)
[['A1', 'B1'], ['A2', ''], ['', ''], ['', '']]

`Finding a Cell`

python

Find a cell with exact string value

cell = worksheet.find("Dough")
print("Found something at R%sC%s" % (cell.row, cell.col))
Find a cell matching a regular expression

amount_re = re.compile(r'(Big|Enormous) dough')
cell = worksheet.find(amount_re)

`Finding All Matched Cells`

python

Find all cells with string value

cell_list = worksheet.findall("Rug store")
Find all cells with regexp

criteria_re = re.compile(r'(Small|Room-tiering) rug')
cell_list = worksheet.findall(criteria_re)

`Updating Cells`

python

Update a single cell

worksheet.update_acell('B1', 'Bingo!')
Update a range

worksheet.update([[1, 2], [3, 4]], 'A1:B2')
Update multiple ranges at once

worksheet.batch_update([{
    'range': 'A1:B2',
    'values': [['A1', 'B1'], ['A2', 'B2']],
}, {
    'range': 'J42:K43',
    'values': [[1, 2], [3, 4]],
}])

`Get unformatted cell value or formula`

python

from gspread.utils import ValueRenderOption
Get formatted cell value as displayed in the UI

>>> worksheet.get("A1:B2")
[['$12.00']]
Get unformatted value from the same cell range

>>> worksheet.get("A1:B2", value_render_option=ValueRenderOption.unformatted)
[[12]]
Get formula from a cell

>>> worksheet.get("C2:D2", value_render_option=ValueRenderOption.formula)
[['=1/1024']]

`Add data validation to a range`

python

import gspread
from gspread.utils import ValidationConditionType
Restrict the input to greater than 10 in a single cell

worksheet.add_validation(
  'A1',
  ValidationConditionType.number_greater,
  [10],
  strict=True,
  inputMessage='Value must be greater than 10',
)
Restrict the input to Yes/No for a specific range with dropdown

worksheet.add_validation(
  'C2:C7',
   ValidationConditionType.one_of_list,
   ['Yes',
   'No',]
   showCustomUi=True
)

`Documentation`

[Documentation]\: [https://gspread.readthedocs.io/][Documentation]

[Documentation]: https://gspread.readthedocs.io/en/latest/

`Ask Questions`

The best way to get an answer to a question is to ask on Stack Overflow with a gspread tag.

`Contributors`

List of contributors

`How to Contribute`

Please make sure to take a moment and read the Code of Conduct.

`Report Issues`

Please report bugs and suggest features via the GitHub Issues.

Before opening an issue, search the tracker for possible duplicates. If you find a duplicate, please add a comment saying that you encountered the problem as well.

`Improve Documentation`

Documentation is as important as code. If you know how to make it more consistent, readable and clear, please submit a pull request. The documentation files are in docs` folder, use reStructuredText markup and rendered by Sphinx.

Contribute code

Please make sure to read the Contributing Guide before making a pull request.

---

Api/Models/Index Copy Section

Api/Models/Worksheet Copy Section

Api/Exceptions Copy Section

Api/Http Client Copy Section

Api/Index Copy Section

Api/Top Level Copy Section

Advanced Copy Section

Community Copy Section

Index Copy Section

Oauth2 Copy Section

User Guide Copy Section

README Copy Section

Google Spreadsheets Python API v4

Maintainer needed Copy Chapter

Overview Copy Chapter

Installation Copy Chapter

Basic Usage Copy Chapter

First you need access to the Google API. Based on the route you

chose in Step 1, call either service_account(), oauth() or api_key().

Open a sheet from a spreadsheet in one go

Update a range of cells using the top left corner address

Or update a single cell

Format the header

v5.12 to v6.0 Migration Guide Copy Chapter

Upgrade from Python 3.7 Copy Section

Change Worksheet.update arguments arguments')" title="Copy section prompt for LLMs"> Copy Section

or

More Copy Section

Change colors from dictionary to text Copy Section

Switch lastUpdateTime from property to method Copy Section

Replace method Worksheet.get_records ')" title="Copy section prompt for LLMs"> Copy Section

Silence warnings Copy Section

Add more data to gspread.Worksheet.__init__ ')" title="Copy section prompt for LLMs"> Copy Section

More Examples Copy Chapter

Opening a Spreadsheet Copy Section

You can open a spreadsheet by its title as it appears in Google Docs

If you want to be specific, use a key (which can be extracted from

the spreadsheet's url)

Or, if you feel really lazy to extract that key, paste the entire url

Creating a Spreadsheet Copy Section

But that new spreadsheet will be visible only to your script's account.

To be able to access newly created spreadsheet you must share it

with your email. Which brings us to…

Sharing a Spreadsheet Copy Section

Selecting a Worksheet Copy Section

Select worksheet by index. Worksheet indexes start from zero

By title

Most common case: Sheet1

Get a list of all worksheets

Creating a Worksheet Copy Section

Deleting a Worksheet Copy Section

Getting a Cell Value Copy Section

With label

With coords

Getting All Values From a Row or a Column Copy Section

Get all values from the first row

Get all values from the first column

Getting All Values From a Worksheet as a List of Lists Copy Section

Getting a range of values Copy Section

Finding a Cell Copy Section

Find a cell with exact string value

Find a cell matching a regular expression

Finding All Matched Cells Copy Section

Find all cells with string value

Find all cells with regexp

Updating Cells Copy Section

Update a single cell

Update a range

Update multiple ranges at once

Get unformatted cell value or formula Copy Section

Get formatted cell value as displayed in the UI

Get unformatted value from the same cell range

Get formula from a cell

Add data validation to a range Copy Section

Restrict the input to greater than 10 in a single cell

Restrict the input to Yes/No for a specific range with dropdown

Documentation Copy Chapter

Ask Questions Copy Section

Contributors Copy Chapter

How to Contribute Copy Chapter

Api/Models/Index

Api/Models/Worksheet

Api/Exceptions

Api/Http Client

Api/Index

Api/Top Level

Advanced

`Community`

`Index`

`Oauth2`

`User Guide`

`README`

`Google Spreadsheets Python API v4`

`Maintainer needed`

`Overview`

`Installation`

`Basic Usage`

`v5.12 to v6.0 Migration Guide`

`Upgrade from Python 3.7`

`Change` Worksheet.update `arguments`

`More`

`Change colors from dictionary to text`

`Switch lastUpdateTime from property to method`

`Replace method` Worksheet.get_records

`Silence warnings`

`Add more data to` gspread.Worksheet.init

`More Examples`

`Opening a Spreadsheet`

`Creating a Spreadsheet`

`Sharing a Spreadsheet`

`Selecting a Worksheet`

`Creating a Worksheet`

`Deleting a Worksheet`

`Getting a Cell Value`

`Getting All Values From a Row or a Column`

`Getting All Values From a Worksheet as a List of Lists`

`Getting a range of values`

`Finding a Cell`

`Finding All Matched Cells`

`Updating Cells`

`Get unformatted cell value or formula`

`Add data validation to a range`

`Documentation`

`Ask Questions`

`Contributors`

`How to Contribute`

`Report Issues`

`Improve Documentation`

Contribute code