gspread_asyncio¶

An asyncio wrapper for burnash’s excellent Google Spreadsheet API library. gspread_asyncio isn’t just a plain asyncio wrapper around the gspread API, it implements several useful and helpful features on top of those APIs. It’s useful for long-running processes and one-off scripts.

Requires Python >= 3.8.

Features¶

Complete async wrapping of the gspread API. All gspread API calls are run off the main thread in a threadpool executor.
Internal caching and reuse of gspread Client/Spreadsheet/Worksheet objects.
Automatic renewal of expired credentials.
Automatic retries of spurious failures from Google’s servers (HTTP 5xx).
Automatic rate limiting with defaults set to Google’s default API limits.
Many methods that don’t need to return a value can optionally return an already-scheduled Future (the nowait kwarg). You can ignore that future, allowing forward progress on your calling coroutine while the asyncio event loop schedules and runs the Google Spreadsheet API call at a later time for you.

Example usage¶

import asyncio

import gspread_asyncio

# from google-auth package
from google.oauth2.service_account import Credentials

# First, set up a callback function that fetches our credentials off the disk.
# gspread_asyncio needs this to re-authenticate when credentials expire.

def get_creds():
    # To obtain a service account JSON file, follow these steps:
    # https://gspread.readthedocs.io/en/latest/oauth2.html#for-bots-using-service-account
    creds = Credentials.from_service_account_file("serviceacct_spreadsheet.json")
    scoped = creds.with_scopes([
        "https://spreadsheets.google.com/feeds",
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive",
    ])
    return scoped

# Create an AsyncioGspreadClientManager object which
# will give us access to the Spreadsheet API.

agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)

# Here's an example of how you use the API:

async def example(agcm):
    # Always authorize first.
    # If you have a long-running program call authorize() repeatedly.
    agc = await agcm.authorize()

    ss = await agc.create("Test Spreadsheet")
    print("Spreadsheet URL: https://docs.google.com/spreadsheets/d/{0}".format(ss.id))
    print("Open the URL in your browser to see gspread_asyncio in action!")

    # Allow anyone with the URL to write to this spreadsheet.
    await agc.insert_permission(ss.id, None, perm_type="anyone", role="writer")

    # Create a new spreadsheet but also grab a reference to the default one.
    ws = await ss.add_worksheet("My Test Worksheet", 10, 5)
    zero_ws = await ss.get_worksheet(0)

    # Write some stuff to both spreadsheets.
    for row in range(1, 11):
        for col in range(1, 6):
            val = "{0}/{1}".format(row, col)
            await ws.update_cell(row, col, val + " ws")
            await zero_ws.update_cell(row, col, val + " zero ws")
    print("All done!")

# Turn on debugging if you're new to asyncio!
asyncio.run(example(agcm), debug=True)

Observational notes and gotchas¶

This module does not define its own exceptions, it propagates instances of gspread.exceptions.GSpreadException.
Always call AsyncioGspreadClientManager.authorize(), AsyncioGspreadClient.open_*() and AsyncioGspreadSpreadsheet.get_worksheet() before doing any work on a spreadsheet. These methods keep an internal cache so it is painless to call them many times, even inside of a loop. This makes sure you always have a valid set of authentication credentials from Google.
The only object you should store in your application is the AsyncioGspreadClientManager (agcm).
Right now the gspread library does not support bulk appends of rows or bulk changes of cells. When this is done gspread_asyncio will support batching of these Google API calls without any changes to the Python gspread_asyncio API.
I came up with the default 1.1 second delay between API calls (the gspread_delay kwarg) after extensive experimentation. The official API rate limit is one call every second but however Google measures these things introduces a tiny bit of jitter that will get you rate blocked if you ride that limit exactly.
Google’s service reliability on these endpoints is surprisingly bad. There are frequent HTTP 500s and the retry logic will save your butt in long-running scripts or short, one-shot, one-off ones.
Experimentation also found that Google’s credentials expire after an hour and the default reauth_interval of 45 minutes takes care of that just fine.

License¶

MIT

Sponsorship¶

Development of gspread_asyncio is sponsored by Pro Football History.com, your source for NFL coaching biographies.

API reference¶

Exceptions¶

The gspread_asyncio module does not have any exceptions of its own, it instead propagates exceptions thrown from within gspread calls. These are all derived from a base class gspread.exceptions.GSpreadException and it is recommended that you catch and handle these errors.

Socket, network and rate limiting exceptions are handled by gspread_asyncio internally. The defaults are sensible but you can subclass several methods of AsyncioGspreadClientManager if you need to customize that behavior.

AsyncioGspreadClientManager¶

class gspread_asyncio.AsyncioGspreadClientManager(credentials_fn, gspread_delay=1.1, reauth_interval=45, loop=None, cell_flush_delay=5.0)[source]¶

Users of gspread_asyncio should instantiate this class and store it for the duration of their program.

Parameters:

credentials_fn (Callable) – A (non-async) function that takes no arguments and returns an instance of google.auth.credentials.Credentials (preferred), oauth2client.service_account.ServiceAccountCredentials, oauth2client.client.OAuth2Credentials, oauth2client.client.AccessTokenCredentials, or oauth2client.client.GoogleCredentials. This function will be called as needed to obtain new credential objects to replace expired ones. It is called from a thread in the default asyncio ThreadPoolExecutor so your function must be threadsafe.
gspread_delay (float) – (optional) The default delay (in seconds) between calls to Google Spreadsheet’s APIs. The default rate limit is 1 call per second, the default rate limit here of 1.1 seconds is a good choice as it allows for some variance that creeps into Google’s rate limit calculations.
reauth_interval (int) – (optional) The default delay (in minutes) before the AsyncioGspreadClientManager requests new credentials.
loop (AbstractEventLoop) – (optional) The asyncio event loop to use.
cell_flush_delay (float) – (optional) Currently unused

await authorize()[source]¶

(Re)-authenticates an AsyncioGspreadClientManager. You must call this method first to log in to the Google Spreadsheets API.

Feel free to call this method often, even in a loop, as it caches Google’s credentials and only re-authenticates when the credentials are nearing expiration.

Returns:: a ready-to-use AsyncioGspreadClient

await before_gspread_call(method, args, kwargs)[source]¶

Called before invoking a gspread method. Optionally subclass this to implement custom logging, tracing, or modification of the method arguments.

The default implementation logs the method name, args and kwargs.

Parameters:

method – gspread class method to be invoked
args – positional arguments for the gspread class method
kwargs – keyword arguments for the gspread class method

await delay()[source]¶

Called before invoking a gspread class method. Optionally subclass this to implement custom rate limiting.

The default implementation figures out the delta between the last Google API call and now and sleeps for the delta if it is less than gspread_delay.

await handle_gspread_error(e, method, args, kwargs)[source]¶

Called in the exception handler for a gspread.exceptions.APIError. Optionally subclass this to implement custom error handling, error logging, rate limiting, backoff, or jitter.

The default implementation logs the error and sleeps for gspread_delay seconds. It does not throw an exception of its own so it keeps retrying failed requests forever.

gspread throws an APIError when an error is returned from the Google API. Google has some documentation on their HTTP status codes. gspread makes a requests.Response object accessible at e.response.

Note that the internal _call() method which invokes this method will not do so for any HTTP 400 statuses. These are errors that arise from mistaken usage of the Google API and are fatal. The exception is status code 429, the rate limiting status, to let this code handle client-side rate limiting.

Parameters:

e (APIError) – Exception object thrown by gspread
method – gspread class method called
args – positional arguments for the gspread class method
kwargs – keyword arguments for the gspread class method

await handle_requests_error(e, method, args, kwargs)[source]¶

Called in the exception handler for a requests.RequestException. Optionally subclass to implement custom error handling, error logging, rate limiting, backoff, or jitter.

The default implementation logs the error and sleeps for gspread_delay seconds. It does not throw an exception of its own so it keeps retrying failed requests forever.

gspread throws a RequestException when a socket layer error occurs.

Parameters:

e (RequestException) – Exception object thrown by gspread
method – gspread class method called
args – positional arguments for the gspread class method
kwargs – keyword arguments for the gspread class method

AsyncioGspreadClient¶

class gspread_asyncio.AsyncioGspreadClient(agcm, gc)[source]¶

An asyncio wrapper for gspread.Client. You must obtain instances of this class from gspread_asyncio.AsyncioGspreadClientManager.authorize().

await copy(file_id, title=None, copy_permissions=False, folder_id=None, copy_comments=True)[source]¶

Copies a spreadsheet.

Parameters:

file_id (str) – A key of a spreadsheet to copy.
title (str) – (optional) A title for the new spreadsheet.
copy_permissions (bool) – (optional) If True, copy permissions from the original spreadsheet to the new spreadsheet.
folder_id (str) – Id of the folder where we want to save the spreadsheet.
copy_comments (bool) – (optional) If True, copy the comments from the original spreadsheet to the new spreadsheet.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

Returns:

a AsyncioGspreadSpreadsheet instance.

New in version 1.6.

Note

If you’re using custom credentials without the Drive scope, you need to add https://www.googleapis.com/auth/drive to your OAuth scope in order to use this method. Example:

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

Otherwise, you will get an Insufficient Permission error when you try to copy a spreadsheet.

await create(title, folder_id=None)[source]¶

Create a new Google Spreadsheet. Wraps gspread.Client.create().

Parameters:

title (str) – Human-readable name of the new spreadsheet.
folder_id (str) – Id of the folder where we want to save the spreadsheet.

Return type:

gspread_asyncio.AsyncioGspreadSpreadsheet

await del_spreadsheet(file_id)[source]¶

Delete a Google Spreadsheet. Wraps gspread.Client.del_spreadsheet().

Parameters:

file_id (str) – Google’s spreadsheet id
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await export(file_id, format=ExportFormat.PDF)[source]¶

Export the spreadsheet in the format. Wraps gspread.Client.export().

Parameters:

file_id (str) – A key of a spreadsheet to export
format (gspread.utils.ExportFormat) –
The format of the resulting file. Possible values are:
- gspread.utils.ExportFormat.PDF
- gspread.utils.ExportFormat.EXCEL
- gspread.utils.ExportFormat.CSV
- gspread.utils.ExportFormat.OPEN_OFFICE_SHEET
- gspread.utils.ExportFormat.TSV
- gspread.utils.ExportFormat.ZIPPED_HTML
See ExportFormat in the Drive API.

Returns:

The content of the exported file.

Return type:

bytes

New in version 1.6.

await import_csv(file_id, data)[source]¶

Upload a csv file and save its data into the first page of the Google Spreadsheet. Wraps gspread.Client.import_csv().

Parameters:

file_id (str) – Google’s spreadsheet id
data (str) – The CSV file
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await insert_permission(file_id, value, perm_type, role, notify=True, email_message=None, with_link=False)[source]¶

Add new permission to a Google Spreadsheet. Wraps gspread.Client.insert_permission().

Parameters:

file_id (str) – Google’s spreadsheet id
value (str, None) – user or group e-mail address, domain name or None for ‘default’ type.
perm_type (str) – Allowed values are: user, group, domain, anyone.
role (str) – the primary role for this user. Allowed values are: owner, writer, reader.
notify (bool) – (optional) Whether to send an email to the target user/domain.
email_message (str) – (optional) The email to be sent if notify=True
with_link (bool) – (optional) Whether the link is required for this permission to be active.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await list_permissions(file_id)[source]¶

List the permissions of a Google Spreadsheet. Wraps gspread.Client.list_permissions().

Parameters:: file_id (str) – Google’s spreadsheet id
Returns:: Some kind of object with permissions in it. I don’t know, the author of gspread forgot to document it.

await open(title)[source]¶

Opens a Google Spreadsheet by title. Wraps gspread.Client.open().

Feel free to call this method often, even in a loop, as it caches the underlying spreadsheet object.

Parameters:: title (str) – The title of the spreadsheet
Return type:: AsyncioGspreadSpreadsheet

await open_by_key(key)[source]¶

Opens a Google Spreadsheet by spreasheet id. Wraps gspread.Client.open_by_key().

Feel free to call this method often, even in a loop, as it caches the underlying spreadsheet object.

Parameters:: key (str) – Google’s spreadsheet id
Return type:: AsyncioGspreadSpreadsheet

await open_by_url(url)[source]¶

Opens a Google Spreadsheet from a URL. Wraps gspread.Client.open_by_url().

Feel free to call this method often, even in a loop, as it caches the underlying spreadsheet object.

Parameters:: url (str) – URL to a Google Spreadsheet
Return type:: AsyncioGspreadSpreadsheet

await openall(title=None)[source]¶

Open all available spreadsheets. Wraps gspread.Client.openall().

Feel free to call this method often, even in a loop, as it caches the underlying spreadsheet objects.

Parameters:: title (str) – (optional) If specified can be used to filter spreadsheets by title.
Return type:: List[AsyncioGspreadSpreadsheet]

await remove_permission(file_id, permission_id)[source]¶

Delete permissions from a Google Spreadsheet. Wraps gspread.Client.remove_permission().

Parameters:

file_id (str) – Google’s spreadsheet id
permission_id (str) – The permission’s id
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

AsyncioGspreadSpreadsheet¶

class gspread_asyncio.AsyncioGspreadSpreadsheet(agcm, ss)[source]¶

An asyncio wrapper for gspread.Spreadsheet. You must obtain instances of this class from AsyncioGspreadClient.open(), AsyncioGspreadClient.open_by_key(), AsyncioGspreadClient.open_by_url(), or AsyncioGspreadClient.openall().

await accept_ownership(permission_id)[source]¶

Accept the pending ownership request on that file.

It is necessary to edit the permission with the pending ownership.

Note

You can only accept ownership transfer for the user currently being used.

New in version 1.7.

Parameters:

permission_id (str) – New permission ID
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await add_worksheet(title, rows, cols, index=None)[source]¶

Add new worksheet (tab) to a spreadsheet. Wraps gspread.Spreadsheet.add_worksheet().

Parameters:

title (str) – Human-readable title for the new worksheet
rows (int) – Number of rows for the new worksheet
cols (int) – Number of columns for the new worksheet
index (int) – (optional) Position of the sheet

Return type:

AsyncioGspreadWorksheet

await batch_update(body)[source]¶

Lower-level method that directly calls spreadsheets/<ID>:batchUpdate.

Parameters:

body (dict) – Request body.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

Returns:

Response body.

Return type:

dict

New in version 1.6.

await del_worksheet(worksheet)[source]¶

Delete a worksheet (tab) from a spreadsheet. Wraps gspread.Spreadsheet.del_worksheet().

Parameters:

worksheet (gspread.Worksheet) – Worksheet to delete
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await duplicate_sheet(source_sheet_id, insert_sheet_index=None, new_sheet_id=None, new_sheet_name=None)[source]¶

Duplicates the contents of a sheet. Wraps gspread.Worksheet.duplicate_sheet().

Parameters:

source_sheet_id (int) – The sheet ID to duplicate.
insert_sheet_index (int) – (optional) The zero-based index where the new sheet should be inserted. The index of all sheets after this are incremented.
new_sheet_id (int) – (optional) The ID of the new sheet. If not set, an ID is chosen. If set, the ID must not conflict with any existing sheet ID. If set, it must be non-negative.
new_sheet_name (str) – (optional) The name of the new sheet. If empty, a new name is chosen for you.

Returns:

a newly created AsyncioGspreadWorksheet

New in version 1.6.

await export(format=ExportFormat.PDF)[source]¶

Export the spreadsheet in the format. Wraps gspread.Spreadsheet.export().

Parameters:

format (gspread.utils.ExportFormat) –

The format of the resulting file. Possible values are:

gspread.utils.ExportFormat.PDF

gspread.utils.ExportFormat.EXCEL

gspread.utils.ExportFormat.CSV

gspread.utils.ExportFormat.OPEN_OFFICE_SHEET

gspread.utils.ExportFormat.TSV

gspread.utils.ExportFormat.ZIPPED_HTML

See ExportFormat in the Drive API.

Returns:

The content of the exported file.

Return type:

bytes

New in version 1.6.

await fetch_sheet_metadata(params=None)[source]¶

Retrieve spreadsheet metadata.

Parameters:: params (dict) – (optional) Query parameters.
Returns:: Response body.
Return type:: dict

New in version 1.8.1.

await get_sheet1()[source]¶

Returns:: Shortcut for getting the first worksheet.
Return type:: AsyncioGspreadWorksheet

await get_worksheet(index)[source]¶

Retrieves a worksheet (tab) from a spreadsheet by index number. Indexes start from zero. Wraps gspread.Spreadsheet.get_worksheet().

Feel free to call this method often, even in a loop, as it caches the underlying worksheet object.

Parameters:: index (int) – Index of worksheet
Return type:: AsyncioGspreadWorksheet

await get_worksheet_by_id(id)[source]¶

Returns a worksheet with specified worksheet id.

Parameters:: id (int) – The id of a worksheet. it can be seen in the url as the value of the parameter ‘gid’.
Return type:: an instance of AsyncioGspreadWorksheet.
Raises:: gspread.exceptions.WorksheetNotFound: if can’t find the worksheet

New in version 1.5.

property id¶

Returns:: Google’s spreadsheet id.
Return type:: str

await list_named_ranges()[source]¶

Lists the spreadsheet’s named ranges. Wraps gspread.Spreadsheet.list_named_ranges().

Returns:: The gspread author forgot to document this

New in version 1.6.

await list_permissions()[source]¶

List the permissions of a Google Spreadsheet. Wraps gspread.Spreadsheet.list_permissions().

Returns:: The gspread author forgot to document this

await list_protected_ranges()[source]¶

Lists the spreadsheet’s protected named ranges. Wraps gspread.Spreadsheet.list_protected_ranges().

Returns:: The gspread author forgot to document this

New in version 1.6.

property locale¶

Spreadsheet locale. Wraps gspread.Spreadsheet.locale().

Return type:: str

New in version 1.6.

await named_range(named_range)[source]¶

return a list of gspread.cell.Cell objects from the specified named range.

Parameters:: named_range (str) – A string with a named range value to fetch.
Return type:: List[gspread.Cell]

New in version 1.6.

await remove_permissions(value, role='any')[source]¶

Remove permissions from a user or domain. Wraps gspread.Spreadsheet.remove_permissions().

Parameters:

value (str) – User or domain to remove permissions from
role (str) – (optional) Permission to remove. Defaults to all permissions.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await reorder_worksheets(worksheets_in_desired_order)[source]¶

Updates the index property of each Worksheet to reflect its index in the provided sequence of Worksheets. Wraps gspread.worksheet.Worksheet.reorder_worksheet().

Parameters:

worksheets_in_desired_order (Iterable[AsyncioGspreadSpreadsheet]) – Iterable of Worksheet objects in desired order.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

Note: If you omit some of the Spreadsheet’s existing Worksheet objects from the provided sequence, those Worksheets will be appended to the end of the sequence in the order that they appear in the list returned by gspread.spreadsheet.Spreadsheet.worksheets().

New in version 1.6.

property timezone¶

Returns:: Title of the spreadsheet.
Return type:: str

New in version 1.6.

property title¶

Returns:: Title of the spreadsheet.
Return type:: str

await transfer_ownership(permission_id)[source]¶

Transfer the ownership of this file to a new user.

It is necessary to first create the permission with the new owner’s email address, get the permission ID then use this method to transfer the ownership.

Note

You can list all permission using gspread.spreadsheet.Spreadsheet.list_permissions()

Warning

You can only transfer ownership to a new user, you cannot transfer ownership to a group or a domain email address.

Parameters:

permission_id (str) – New permission ID
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.7.

await update_locale(locale)[source]¶

Update the locale of the spreadsheet.

Can be any of the ISO 639-1 language codes, such as: de, fr, en, … Or an ISO 639-2 if no ISO 639-1 exists. Or a combination of the ISO language code and country code, such as en_US, de_CH, fr_FR, …

Parameters:

locale (str) – New locale
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

Note

Note: when updating this field, not all locales/languages are supported.

New in version 1.6.

await update_timezone(timezone)[source]¶

Updates the current spreadsheet timezone. Can be any timezone in CLDR format such as “America/New_York” or a custom time zone such as GMT-07:00.

Parameters:

timezone (str) – New timezone
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await update_title(title)[source]¶

Renames the spreadsheet.

Parameters:

title (str) – A new title.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await values_append(range, params, body)[source]¶

Lower-level method that directly calls spreadsheets/<ID>/values:append.

Parameters:

range (str) – The A1 notation of a range to search for a logical table of data. Values will be appended after the last row of the table.
params (dict) – Query parameters.
body (dict) – Request body.

Returns:

Response body.

Return type:

dict

New in version 1.6.

await values_batch_get(ranges, params=None)[source]¶

Lower-level method that directly calls spreadsheets/<ID>/values:batchGet.

Parameters:

ranges –
List of ranges in the A1 notation of the values to retrieve.
params (dict) – (optional) Query parameters.

Returns:

Response body.

Return type:

dict

New in version 1.6.

await values_clear(range)[source]¶

Lower-level method that directly calls spreadsheets/<ID>/values:clear.

Parameters:

range (str) –

The A1 notation of the values to clear.

Returns:

Response body.

Return type:

dict

New in version 1.6.

await values_get(range, params=None)[source]¶

Lower-level method that directly calls spreadsheets/<ID>/values/<range>.

Parameters:

range (str) –
The A1 notation of the values to retrieve.
params (dict) – (optional) Query parameters.

Returns:

Response body.

Return type:

dict

New in version 1.6.

await values_update(range, params=None, body=None)[source]¶

Lower-level method that directly calls spreadsheets/<ID>/values/<range>.

Parameters:

range (str) –
The A1 notation of the values to update.
params (dict) – (optional) Query parameters.
body (dict) – (optional) Request body.

Returns:

Response body.

Return type:

dict

Example:

sh.values_update(
    'Sheet1!A2',
    params={
        'valueInputOption': 'USER_ENTERED'
    },
    body={
        'values': [[1, 2, 3]]
    }
)

New in version 1.6.

await worksheet(title)[source]¶

Gets a worksheet (tab) by title. Wraps gspread.Spreadsheet.worksheet().

Feel free to call this method often, even in a loop, as it caches the underlying worksheet object.

Parameters:: title (str) – Human-readable title of the worksheet.
Return type:: AsyncioGspreadWorksheet

await worksheets()[source]¶

Gets all worksheets (tabs) in a spreadsheet. Wraps gspread.Spreadsheet.worksheets().

Feel free to call this method often, even in a loop, as it caches the underlying worksheet objects.

Return type:: List[AsyncioGspreadWorksheet]

AsyncioGspreadWorksheet¶

class gspread_asyncio.AsyncioGspreadWorksheet(agcm, ws)[source]¶

An asyncio wrapper for gspread.Worksheet. You must obtain instances of this class from AsyncioGspreadSpreadsheet.add_worksheet(), AsyncioGspreadSpreadsheet.get_worksheet(), AsyncioGspreadSpreadsheet.worksheet(), or AsyncioGspreadSpreadsheet.worksheets().

await acell(label, value_render_option=ValueRenderOption.formatted)[source]¶

Get cell by label (A1 notation). Wraps gspread.Worksheet.acell().

Parameters:

label (str) – Cell label in A1 notation Letter case is ignored.
value_render_option (gspread.utils.ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

Return type:

gspread.Cell

await add_cols(cols)[source]¶

Adds columns to worksheet. Wraps gspread.Worksheet.add_cols().

Parameters:

cols (int) – Number of new columns to add.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await add_dimension_group_columns(start, end)[source]¶

Group columns in order to hide them in the UI.

Note

API behavior with nested groups and non-matching [start:end) range can be found here: Add Dimension Group Request

Parameters:

start (int) – The start (inclusive) of the group
end (int) – The end (exclusive) of the group

New in version 1.6.

await add_dimension_group_rows(start, end)[source]¶

Group rows in order to hide them in the UI.

Note

API behavior with nested groups and non-matching [start:end) range can be found here: Add Dimension Group Request

Parameters:

start (int) – The start (inclusive) of the group
end (int) – The end (exclusive) of the group

New in version 1.6.

await add_protected_range(name, editor_users_emails=None, editor_groups_emails=None, description=None, warning_only=False, requesting_user_can_edit=False)[source]¶

Add protected range to the sheet. Only the editors can edit the protected range.

Parameters:

name (str) – A string with range value in A1 notation, e.g. ‘A1:A5’.
editor_users_emails (list) – (optional) The email addresses of users with edit access to the protected range.
editor_groups_emails (list) – (optional) The email addresses of groups with edit access to the protected range.
description (str) – (optional) Description for the protected ranges.
warning_only (bool) – (optional) When true this protected range will show a warning when editing. Defaults to False.
requesting_user_can_edit (bool) – (optional) True if the user who requested this protected range can edit the protected cells. Defaults to False.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.1.

await add_rows(rows)[source]¶

Adds rows to worksheet. Wraps gspread.worksheet.Worksheet.add_rows().

Parameters:

rows (int) – Number of new rows to add.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await append_row(values, value_input_option=ValueInputOption.raw, insert_data_option=None, table_range=None)[source]¶

Adds a row to the worksheet and populates it with values. Widens the worksheet if there are more values than columns. Wraps gspread.Worksheet.append_row().

Parameters:

values (List[str]) – List of values for the new row.
value_input_option (gspread.utils.ValueInputOption) – (optional) Determines how values should be rendered in the output. See ValueInputOption in the Sheets API.
insert_data_option (str) – (optional) Determines how the input data should be inserted. See InsertDataOption in the Sheets API reference.
table_range (str) – (optional) The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. Examples: A1 or B2:D4
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await append_rows(values, value_input_option=ValueInputOption.raw, insert_data_option=None, table_range=None)[source]¶

Adds multiple rows to the worksheet and populates them with values.

Widens the worksheet if there are more values than columns.

NOTE: it doesn’t extend the filtered range.

Wraps gspread.Worksheet.append_rows().

Parameters:

values (list) – List of rows each row is List of values for the new row.
value_input_option (gspread.utils.ValueInputOption) – (optional) Determines how input data should be interpreted. See ValueInputOption in the Sheets API.
insert_data_option (str) – (optional) Determines how the input data should be inserted. See InsertDataOption in the Sheets API reference.
table_range (str) – (optional) The A1 notation of a range to search for a logical table of data. Values are appended after the last row of the table. Examples: A1 or B2:D4
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.1.

await batch_clear(ranges)[source]¶

Clears multiple ranges of cells with 1 API call.

Wraps gspread.Worksheet.batch_clear().

Parameters:

ranges (List[str]) – List of ‘A1:B1’ or named ranges to clear.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.5.

await batch_format(formats)[source]¶

Formats cells in batch.

Parameters:

formats (list) –
List of ranges to format and the new format to apply to each range.

The list is composed of dict objects with the following keys/values:
- range : A1 range notation
- format : a valid dict object with the format to apply for that range see CellFormat in the Sheets API for available fields.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

Examples:

# Format the range ``A1:C1`` with bold text
# and format the range ``A2:C2`` a font size of 16
formats = [
    {
        "range": "A1:C1",
        "format": {
            "textFormat": {
                "bold": True,
            },
        },
    },
    {
        "range": "A2:C2",
        "format": {
            "textFormat": {
                "fontSize": 16,
            },
        },
    },
]
worksheet.batch_update(formats)

New in version 1.6.

await batch_get(ranges, major_dimension=None, value_render_option=None, date_time_render_option=None)[source]¶

Returns one or more ranges of values from the sheet.

Parameters:

ranges (list) – List of cell ranges in the A1 notation or named ranges.
major_dimension (str) – (optional) The major dimension that results should use.
value_render_option (gspread.utils.ValueRenderOption) – (optional) How values should be represented in the output. The default render option is FORMATTED_VALUE.
date_time_render_option (gspread.utils.DateTimeOption) – (optional) How dates, times, and durations should be represented in the output. This is ignored if value_render_option is FORMATTED_VALUE. The default dateTime render option is SERIAL_NUMBER.

Examples:

# Read values from 'A1:B2' range and 'F12' cell
await worksheet.batch_get(['A1:B2', 'F12'])

New in version 1.1.

await batch_update(data, raw=True, value_input_option=None, include_values_in_response=None, response_value_render_option=None, response_date_time_render_option=None)[source]¶

Sets values in one or more cell ranges of the sheet at once. Wraps gspread.Worksheet.batch_update().

Parameters:

data (list) –
List of dictionaries in the form of {‘range’: ‘…’, ‘values’: [[.., ..], …]} where range is a target range to update in A1 notation or a named range, and values is a list of lists containing new values.

For input, supported value types are: bool, string, and double. Null values will be skipped. To set a cell to an empty value, set the string value to an empty string.
raw (bool) – (optional) Force value_input_option=”RAW”
value_input_option (gspread.utils.ValueInputOption) –
(optional) How the input data should be interpreted. Possible values are:

RAW
The values the user has entered will not be parsed and will be stored as-is.

USER_ENTERED
The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.
include_values_in_response (bool) – (optional) Determines if the update response should include the values of the cells that were updated. By default, responses do not include the updated values.
response_value_render_option (gspread.utils.ValueRenderOption) – (optional) Determines how values in the response should be rendered. See ValueRenderOption in the Sheets API.
response_date_time_render_option (gspread.utils.DateTimeOption) – (optional) Determines how dates, times, and durations in the response should be rendered. See DateTimeRenderOption in the Sheets API.

Examples:

await worksheet.batch_update([{
    'range': 'A1:B1',
    'values': [['42', '43']],
}, {
    'range': 'my_range',
    'values': [['44', '45']],
}, {
    'range': 'A2:B2',
    'values': [['42', None]],
}])

# Note: named ranges are defined in the scope of
# a spreadsheet, so even if `my_range` does not belong to
# this sheet it is still updated

New in version 1.1.

await cell(row, col, value_render_option=ValueRenderOption.formatted)[source]¶

Returns an instance of a gspread.Cell located at row and col column. Wraps gspread.Worksheet.cell().

Parameters:

row (int) – Row number.
col (int) – Column number.
value_render_option (gspread.utils.ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

Return type:

gspread.Cell

await clear()[source]¶

Clears all cells in the worksheet. Wraps gspread.Worksheet.clear().

Parameters:: nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await clear_basic_filter()[source]¶

Remove the basic filter from a worksheet.

Parameters:: nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await clear_note(cell)[source]¶

Clear a note. The note is attached to a certain cell.

Parameters:

cell (str) – A string with a cell coordinates in A1 notation, e.g. ‘D7’.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.4.

await clear_notes(ranges)[source]¶

Clear all notes located at the cells in ranges.

Parameters:: ranges (List[str]) – List of A1 coordinates to clear notes

New in version 1.9.

property col_count¶

Returns:: Number of columns in the worksheet.
Return type:: int

await col_values(col, value_render_option=ValueRenderOption.formatted)[source]¶

Returns a list of all values in column col. Wraps gspread.Worksheet.col_values().

Empty cells in this list will be rendered as None.

Parameters:

col (int) – Column number.
value_render_option (gspread.utils.ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

Return type:

List[Optional[str]]

await columns_auto_resize(start_column_index, end_column_index)[source]¶

Updates the size of rows or columns in the worksheet.

Index start from 0.

Parameters:

start_column_index (int) – The index (inclusive) to begin resizing
end_column_index (int) – The index (exclusive) to finish resizing
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await copy_range(source, dest, paste_type=PasteType.normal, paste_orientation=PasteOrientation.normal)[source]¶

Copies a range of data from source to dest

Note

paste_type values are explained here: Paste Types

Parameters:

source (str) – The A1 notation of the source range to copy
dest (str) – The A1 notation of the destination where to paste the data Can be the A1 notation of the top left corner where the range must be paste ex: G16, or a complete range notation ex: G16:I20. The dimensions of the destination range is not checked and has no effect, if the destination range does not match the source range dimension, the entire source range is copies anyway.
paste_type (gspread.utils.PasteType) – the paste type to apply. Many paste type are available from the Sheet API, see above note for detailed values for all values and their effects. Defaults to PasteType.normal
paste_orientation (gspread.utils.PasteOrientation) – The paste orient to apply. Possible values are: normal to keep the same orientation, transpose where all rows become columns and vice versa.

New in version 1.8.

await copy_to(spreadsheet_id)[source]¶

Copies this sheet to another spreadsheet.

Parameters:: spreadsheet_id (str) – The ID of the spreadsheet to copy the sheet to.
Returns:: a dict with the response containing information about the newly created sheet.
Return type:: dict

New in version 1.6.

await cut_range(source, dest, paste_type=PasteType.normal)[source]¶

Moves a range of data form source to dest

Note

paste_type values are explained here: Paste Types

Parameters:

source (str) – The A1 notation of the source range to move
dest (str) – The A1 notation of the destination where to paste the data it must be a single cell in the A1 notation. ex: G16
paste_type (gspread.utils.PasteType) – the paste type to apply. Many paste type are available from the Sheet API, see above note for detailed values for all values and their effects. Defaults to PasteType.normal

New in version 1.8.

await define_named_range(name, range_name)[source]¶

Parameters:

name (str) – A string with range value in A1 notation, e.g. ‘A1:A5’.
range_name (str) – The name to assign to the range of cells

Returns:

the response body from the request

Return type:

dict

New in version 1.6.

await delete_columns(start_index, end_index=None)[source]¶

Deletes multiple columns from the worksheet at the specified index.

Parameters:

start_index (int) – Index of a first column for deletion.
end_index (int) – Index of a last column for deletion. When end_index is not specified this method only deletes a single column at start_index.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await delete_dimension(dimension, start_index, end_index=None)[source]¶

Deletes multi rows from the worksheet at the specified index.

Parameters:

dimension (str) – A dimension to delete. Dimension.rows or Dimension.cols.
start_index (int) – Index of a first row for deletion.
end_index (int) – Index of a last row for deletion. When end_index is not specified this method only deletes a single row at start_index.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await delete_named_range(named_range_id)[source]¶

Parameters:

named_range_id (str) – The ID of the named range to delete. Can be obtained with AsyncioGspreadSpreadsheet.list_named_ranges().
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.6.

await delete_protected_range(id)[source]¶

Delete protected range identified by the ID id.

Parameters:: id (str) – The ID of the protected range to delete. Can be obtained with AsyncioGspreadSpreadsheet.list_protected_ranges().

New in version 1.6.

await delete_rows(index, end_index=None)[source]¶

Deletes multiple rows from the worksheet starting at the specified index. Wraps gspread.Worksheet.delete_rows().

Parameters:

index (int) – Index of a row for deletion.
end_index (int) – Index of a last row for deletion. When end_index is not specified this method only deletes a single row at start_index.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.2.

await find(query, in_row=None, in_column=None, case_sensitive=True)[source]¶

Finds the first cell matching the query. Wraps gspread.Worksheet.find().

Parameters:

query (str, re.Pattern) – A literal string to match or compiled regular expression.
in_row (int) – (optional) One-based row number to scope the search.
in_column (int) – (optional) One-based column number to scope the search.
case_sensitive (bool) – (optional) case sensitive string search. Default is True, does not apply to regular expressions.

Return type:

gspread.Cell

await findall(query, in_row=None, in_column=None)[source]¶

Finds all cells matching the query. Wraps gspread.Worksheet.find().

Parameters:

query (str, re.Pattern) – A literal string to match or compiled regular expression.
in_row (int) – (optional) One-based row number to scope the search.
in_column (int) – (optional) One-based column number to scope

Return type:

List[gspread.Cell]

await format(ranges, format)[source]¶

Format a list of ranges with the given format.

Parameters:

ranges (str|list) – Target ranges in the A1 notation.
format (dict) – Dictionary containing the fields to update. See CellFormat in the Sheets API for available fields.

Examples:

# Set 'A4' cell's text format to bold
worksheet.format("A4", {"textFormat": {"bold": True}})

# Set 'A1:D4' and 'A10:D10' cells's text format to bold
worksheet.format(["A1:D4", "A10:D10"], {"textFormat": {"bold": True}})

# Color the background of 'A2:B2' cell range in black,
# change horizontal alignment, text color and font size
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
    }
})

New in version 1.6.

await freeze(rows=None, cols=None)[source]¶

Freeze rows and/or columns on the worksheet.

Parameters:

rows – Number of rows to freeze.
cols – Number of columns to freeze.

New in version 1.6.

property frozen_col_count¶: Number of frozen columns.

New in version 1.6.

property frozen_row_count¶: Number of frozen rows.

New in version 1.6.

await get(range_name=None, major_dimension=None, value_render_option=None, date_time_render_option=None, combine_merged_cells=False)[source]¶

Reads values of a single range or a cell of a sheet.

Parameters:

range_name (str) – (optional) Cell range in the A1 notation or a named range.
major_dimension (str) – (optional) The major dimension that results should use. Either ROWS or COLUMNS.
value_render_option (gspread.utils.ValueRenderOption) – (optional) How values should be represented in the output. The default render option is ValueRenderOption.formatted.
date_time_render_option (gspread.utils.DateTimeOption) – (optional) How dates, times, and durations should be represented in the output. This is ignored if value_render_option is ValueRenderOption.formatted. The default date_time_render_option is SERIAL_NUMBER.
combine_merged_cells (bool) –
(optional) If True, then all cells that are part of a merged cell will have the same value as the top-left cell of the merged cell. Defaults to False.

Warning

Setting this to True will cause an additional API request to be made to retrieve the values of all merged cells.

Return type:

gspread.worksheet.ValueRange

Examples:

# Return all values from the sheet
worksheet.get()

# Return value of 'A1' cell
worksheet.get('A1')

# Return values of 'A1:B2' range
worksheet.get('A1:B2')

# Return values of 'my_range' named range
worksheet.get('my_range')

New in version 1.6.

await get_all_records(empty2zero=False, head=1, default_blank='', allow_underscores_in_numeric_literals=False, numericise_ignore=[], value_render_option=None)[source]¶

Returns a list of dictionaries, all of them having the contents of the spreadsheet with the head row as keys and each of these dictionaries holding the contents of subsequent rows of cells as values. Wraps gspread.Worksheet.get_all_records().

Cell values are numericised (strings that can be read as ints or floats are converted).

Parameters:

empty2zero (bool) – (optional) Determines whether empty cells are converted to zeros.
head (int) – (optional) Determines which row to use as keys, starting from 1 following the numeration of the spreadsheet.
default_blank (str) – (optional) Determines whether empty cells are converted to something else except empty string or zero.
allow_underscores_in_numeric_literals (bool) – (optional) Allow underscores in numeric literals, as introduced in PEP 515
numericise_ignore (list) – (optional) List of ints of indices of the column (starting at 1) to ignore numericising, special use of [‘all’] to ignore numericising on all columns.
value_render_option (gspread.utils.ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.

Return type:

List[dict]

await get_all_values()[source]¶

Returns a list of lists containing all cells’ values as strings. Wraps gspread.Worksheet.get_all_values().

Return type:: List[List[str]]

await get_note(cell)[source]¶

Get the content of the note located at cell, or the empty string if the cell does not have a note.

Parameters:: cell (str) – A string with cell coordinates in A1 notation, e.g. ‘D7’.
Return type:: str

New in version 1.5.

await get_values(range_name=None, major_dimension=None, value_render_option=None, date_time_render_option=None, combine_merged_cells=False)[source]¶

Returns a list of lists containing all values from specified range. By default values are returned as strings. See value_render_option to change the default format.

Parameters:

range_name (str) – (optional) Cell range in the A1 notation or a named range. If not specified the method returns values from all non empty cells.
major_dimension (str) – (optional) The major dimension of the values. Either ROWS or COLUMNS. Defaults to ROWS
value_render_option (gspread.utils.ValueRenderOption) –
(optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API. Possible values are:

FORMATTED_VALUE
(default) Values will be calculated and formatted according to the cell’s formatting. Formatting is based on the spreadsheet’s locale, not the requesting user’s locale.

UNFORMATTED_VALUE
Values will be calculated, but not formatted in the reply. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return the number 1.23.

FORMULA
Values will not be calculated. The reply will include the formulas. For example, if A1 is 1.23 and A2 is =A1 and formatted as currency, then A2 would return “=A1”.
date_time_render_option (gspread.utils.ValueRenderOption) – (optional) How dates, times, and durations should be represented in the output. This is ignored if value_render_option is FORMATTED_VALUE. The default date_time_render_option is SERIAL_NUMBER.

Note

Empty trailing rows and columns will not be included.

Parameters:

combine_merged_cells (bool) –

(optional) If True, then all cells that are part of a merged cell will have the same value as the top-left cell of the merged cell. Defaults to False.

Warning

Setting this to True will cause an additional API request to be made to retrieve the values of all merged cells.

Return type:

List[List]

New in version 1.5.

await hide()[source]¶: Hides the current worksheet from the UI.

New in version 1.6.

await hide_columns(start, end)[source]¶

Explicitly hide the given column index range.

Index start from 0.

Parameters:

start (int) – The (inclusive) starting column to hide
end (int) – The (exclusive) end column to hide

New in version 1.6.

await hide_gridlines()[source]¶: Hide gridlines on the current worksheet

New in version 1.9.

await hide_rows(start, end)[source]¶

Explicitly hide the given row index range.

Index start from 0.

Parameters:

start (int) – The (inclusive) starting column to hide
end (int) – The (exclusive) end column to hide

New in version 1.6.

property id¶

Returns:: Worksheet ID.
Return type:: int

New in version 1.6.

property index¶

Returns:: Worksheet index.
Return type:: int

New in version 1.6.

await insert_cols(values, col=1, value_input_option=ValueInputOption.raw, inherit_from_before=False)[source]¶

Adds multiple new cols to the worksheet at specified index and populates them with values. Wraps gspread.Worksheet.insert_cols().

Parameters:

values (List[List]) – List of values for the new columns.
col (int) – (optional) Offset for the newly inserted columns.
value_input_option (gspread.utils.ValueInputOption) – (optional) Determines how values should be rendered in the output. Possible values are RAW or USER_ENTERED. See ValueInputOption in the Sheets API.
inherit_from_before (bool) –
(optional) If True, new columns will inherit their properties from the previous column. Defaults to False, meaning that new columns acquire the properties of the column immediately after them.

Warning

inherit_from_before must be False if adding at the left edge of a spreadsheet (col=1), and must be True if adding at the right edge of the spreadsheet.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.4.

await insert_note(cell, content)[source]¶

Insert a note. The note is attached to a certain cell.

Parameters:

cell (str) – A string with a cell coordinates in A1 notation, e.g. ‘D7’.
content (str) – The text note to insert.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.4.

await insert_notes(notes)[source]¶

Insert multiple notes.

Parameters:

notes (dict) –

A dict of notes with their cells coordinates and respective content

dict format is:

key: the cell coordinates as A1 range format
value: the string content of the cell

Example:

{
    "D7": "Please read my notes",
    "GH42": "this one is too far",
}

New in version 1.9.

await insert_row(values, index=1, value_input_option=ValueInputOption.raw, inherit_from_before=False)[source]¶

Adds a row to the worksheet at the specified index and populates it with values. Wraps gspread.Worksheet.insert_row().

Widens the worksheet if there are more values than columns.

Parameters:

values (List) – List of values for the new row.
index (int) – (optional) Offset for the newly inserted row.
value_input_option (gspread.utils.ValueInputOption) – (optional) Determines how values should be rendered in the output. See ValueInputOption in the Sheets API.
inherit_from_before (bool) –
(optional) If True, the new row will inherit its properties from the previous row. Defaults to False, meaning that the new row acquires the properties of the row immediately after it.

Warning

inherit_from_before must be False when adding a row to the top of a spreadsheet (index=1), and must be True when adding to the bottom of the spreadsheet.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await insert_rows(values, row=1, value_input_option=ValueInputOption.raw, inherit_from_before=False)[source]¶

Adds multiple rows to the worksheet at the specified index and populates them with values.

Parameters:

values (List[List]) – List of row lists. a list of lists, with the lists each containing one row’s values. Widens the worksheet if there are more values than columns.
row (int) – Start row to update (one-based). Defaults to 1 (one).
value_input_option (gspread.utils.ValueInputOption) – (optional) Determines how input data should be interpreted. Possible values are RAW or USER_ENTERED. See ValueInputOption in the Sheets API.
inherit_from_before (bool) –
(optional) If True, the new row will inherit its properties from the previous row. Defaults to False, meaning that the new row acquires the properties of the row immediately after it.

Warning

inherit_from_before must be False when adding a row to the top of a spreadsheet (index=1), and must be True when adding to the bottom of the spreadsheet.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.1.

await list_dimension_group_columns()[source]¶

List all the grouped columns in this worksheet

Returns:: list of the grouped columns
Return type:: list

New in version 1.6.

await list_dimension_group_rows()[source]¶

List all the grouped rows in this worksheet

Returns:: list of the grouped rows
Return type:: list

New in version 1.6.

await merge_cells(name, merge_type='MERGE_ALL')[source]¶

Merge cells. There are 3 merge types: MERGE_ALL, MERGE_COLUMNS, and MERGE_ROWS.

Parameters:

name (str) – Range name in A1 notation, e.g. ‘A1:A5’.
merge_type (str) – (optional) one of MERGE_ALL, MERGE_COLUMNS, or MERGE_ROWS. Defaults to MERGE_ROWS. See MergeType in the Sheets API reference.

Returns:

the response body from the request

Return type:

dict

New in version 1.6.

await range(*args, **kwargs)[source]¶

Returns a list of Cell objects from a specified range. Wraps gspread.Worksheet.range().

Parameters:: name (str) – A string with range value in A1 notation, e.g. ‘A1:A5’ or the named range to fetch.

Alternatively, you may specify numeric boundaries. All values index from 1 (one):

Parameters:

first_row (int) – Row number
first_col (int) – Row number
last_row (int) – Row number
last_col (int) – Row number

Return type:

List[gspread.Cell]

await resize(rows=None, cols=None)[source]¶

Resizes the worksheet. Specify one of rows or cols. Wraps gspread.Worksheet.resize().

Parameters:

rows (int) – (optional) New number of rows.
cols (int) – (optional) New number columns.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

property row_count¶

Returns:: Number of rows in the worksheet.
Return type:: int

await row_values(row, major_dimension=None, value_render_option=None, date_time_render_option=None)[source]¶

Returns a list of all values in a row. Wraps gspread.Worksheet.row_values().

Empty cells in this list will be rendered as None.

Parameters:

row (int) – Row number.
value_render_option (gspread.utils.ValueRenderOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.
date_time_render_option (gspread.utils.DateTimeOption) – (optional) How dates, times, and durations should be represented in the output.

await rows_auto_resize(start_row_index, end_row_index)[source]¶

Updates the size of rows or columns in the worksheet.

Index start from 0

Parameters:

start_row_index – The index (inclusive) to begin resizing
end_row_index – The index (exclusive) to finish resizing

New in version 1.6.

await set_basic_filter(name)[source]¶

Add a basic filter to the worksheet. If a range or boundaries are passed, the filter will be limited to the given range.

Parameters:: name (str) – A string with range value in A1 notation, e.g. A1:A5.

New in version 1.6.

await show()[source]¶: Show the current worksheet in the UI.

New in version 1.6.

await show_gridlines()[source]¶: Show gridlines on the current worksheet

New in version 1.9.

await sort(specs, range=None)[source]¶

Sorts worksheet using given sort orders.

Parameters:

specs (list) – The sort order per column. Each sort order represented by a tuple where the first element is a column index and the second element is the order itself: ‘asc’ or ‘des’.
range (str) – The range to sort in A1 notation. By default sorts the whole sheet excluding frozen rows.

Example:

# Sort sheet A -> Z by column 'B'
wks.sort((2, 'asc'))

# Sort range A2:G8 basing on column 'G' A -> Z
# and column 'B' Z -> A
wks.sort((7, 'asc'), (2, 'des'), range='A2:G8')

New in version 1.6.

property title¶

Returns:: Human-readable worksheet title.
Return type:: str

await unhide_columns(start, end)[source]¶

Explicitly unhide the given column index range.

Index start from 0.

Parameters:

start (int) – The (inclusive) starting column to hide
end (int) – The (exclusive) end column to hide

New in version 1.6.

await unhide_rows(start, end)[source]¶

Explicitly unhide the given row index range.

Index start from 0.

Parameters:

start (int) – The (inclusive) starting row to hide
end (int) – The (exclusive) end row to hide

New in version 1.6.

await unmerge_cells(name)[source]¶

Unmerge cells.

Unmerge previously merged cells.

Parameters:: name (str) – Range name in A1 notation, e.g. ‘A1:A5’.
Returns:: the response body from the request
Return type:: dict

New in version 1.6.

await update(values, range_name=None, raw=True, major_dimension=None, value_input_option=None, include_values_in_response=None, response_value_render_option=None, response_date_time_render_option=None)[source]¶

Sets values in a cell range of the sheet. Wraps gspread.Worksheet.update().

Parameters:

values (list) – The data to be written.
range_name (str) – The A1 notation of the values to update.
raw (bool) – The values will not be parsed by Sheets API and will be stored as-is. For example, formulas will be rendered as plain strings. Defaults to True. This is a shortcut for the value_input_option parameter.
major_dimension (str) – (optional) The major dimension of the values. Either ROWS or COLUMNS.
value_input_option (gspread.utils.ValueInputOption) –
(optional) How the input data should be interpreted. Possible values are:

RAW
The values the user has entered will not be parsed and will be stored as-is.

USER_ENTERED
The values will be parsed as if the user typed them into the UI. Numbers will stay as numbers, but strings may be converted to numbers, dates, etc. following the same rules that are applied when entering text into a cell via the Google Sheets UI.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.5.

await update_acell(label, value)[source]¶

Updates the value of a cell. Wraps gspread.Worksheet.row_values().

Parameters:

label (str) – Cell label in A1 notation. Letter case is ignored.
value – New value.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await update_cell(row, col, value)[source]¶

Updates the value of a cell.

Parameters:

row (int) – Row number.
col (int) – Column number.
value – New value.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await update_cells(cell_list, value_input_option=ValueInputOption.raw)[source]¶

Updates many cells at once. Wraps gspread.Worksheet.update_cells().

Parameters:

cell_list – List of Cell objects to update.
value_input_option (gspread.utils.ValueInputOption) – (optional) Determines how values should be rendered in the output. See ValueRenderOption in the Sheets API.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

await update_index(index)[source]¶

Updates the index property for the worksheet.

See the Sheets API documentation for information on how updating the index property affects the order of worksheets in a spreadsheet.

To reorder all worksheets in a spreadsheet, see AsyncioGspreadSpreadsheet.reorder_worksheets.

New in version 1.6.

await update_note(cell, content)[source]¶

Update the content of the note located at cell.

Parameters:

cell (str) – A string with cell coordinates in A1 notation, e.g. ‘D7’.
content (str) – The text note to insert.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.4.

await update_notes(notes)[source]¶

Update multiple notes.

Parameters:

notes (dict) –

A dict of notes with their cell coordinates and respective content

dict format is:

key: the cell coordinates as A1 range format
value: the string content of the cell

Example:

{
    "D7": "Please read my notes",
    "GH42": "this one is too far",
}

await update_tab_color(color)[source]¶

Changes the worksheet’s tab color.

Parameters:

color (dict) – The red, green and blue values of the color, between 0 and 1.
nowait (bool) – (optional) If true, return a scheduled future instead of waiting for the API call to complete.

New in version 1.7.0.

property url¶

Returns:: Worksheet URL.
Return type:: str

New in version 1.6.

Cell¶

See gspread.cell.Cell.

gspread_asyncio

Navigation

Related Topics

gspread_asyncio¶

Features¶

Example usage¶

Observational notes and gotchas¶

License¶

Sponsorship¶

API reference¶

Exceptions¶

AsyncioGspreadClientManager¶

AsyncioGspreadClient¶

AsyncioGspreadSpreadsheet¶

AsyncioGspreadWorksheet¶

Cell¶