aiospread API Reference¶
gspread is an asynchronous Python client library for the Google Sheets API based off of gspread by burnash.
Main Interface¶
-
class
aiospread.
Client
(auth, session=None)¶ An instance of this class communicates with Google API.
Parameters: - auth – An OAuth2 credential object. Credential objects are those created by the oauth2client library. https://github.com/google/oauth2client
- session – (optional) A session object capable of making HTTP requests while persisting some parameters across requests. Defaults to requests.Session.
>>> c = gspread.Client(auth=OAuthCredentialObject)
-
create
(title)¶ This function is a coroutine.
Creates a new spreadsheet.
Parameters: title – A title of a new spreadsheet. Returns: a Spreadsheet
instance.Note
In order to use this method, you need to add
https://www.googleapis.com/auth/drive
to your oAuth scope.Example:
scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ]
Otherwise you will get an
Insufficient Permission
error when you try to create a new spreadsheet.
-
del_spreadsheet
(file_id)¶ This function is a coroutine.
Deletes a spreadsheet.
Parameters: file_id – a spreadsheet ID (aka file ID.)
-
import_csv
(file_id, data)¶ This function is a coroutine.
Imports data into the first page of the spreadsheet.
Parameters: - file_id – The file ID of the sheet
- data – A CSV string of data.
-
insert_permission
(file_id, value, perm_type, role, notify=True, email_message=None)¶ This function is a coroutine.
Creates a new permission for a file.
Parameters: - file_id – a spreadsheet ID (aka file ID.)
- value – user or group e-mail address, domain name or None for ‘default’ type.
- perm_type – the account type.
Allowed values are:
user
,group
,domain
,anyone
- role – the primary role for this user.
Allowed values are:
owner
,writer
,reader
- notify – Whether to send an email to the target user/domain.
- email_message – an email message to be sent if notify=True.
Examples:
# Give write permissions to otto@example.com gc.insert_permission( '0BmgG6nO_6dprnRRUWl1UFE', 'otto@example.org', perm_type='user', role='writer' ) # Make the spreadsheet publicly readable gc.insert_permission( '0BmgG6nO_6dprnRRUWl1UFE', None, perm_type='anyone', role='reader' )
-
list_permissions
(file_id)¶ This function is a coroutine.
Retrieve a list of permissions for a file.
Parameters: file_id – a spreadsheet ID (aka file ID.)
-
open
(title)¶ This function is a coroutine.
Opens a spreadsheet.
Parameters: title – A title of a spreadsheet. Returns: a Spreadsheet
instance.If there’s more than one spreadsheet with same title the first one will be opened.
Raises: gspread.SpreadsheetNotFound – if no spreadsheet with specified title is found. >>> c = await gspread.authorize(credentials) >>> await c.open('My fancy spreadsheet')
-
open_by_key
(key)¶ This function is a coroutine.
Opens a spreadsheet specified by key.
Parameters: key – A key of a spreadsheet as it appears in a URL in a browser. Returns: a Spreadsheet
instance.>>> c = await gspread.authorize(credentials) >>> await c.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
-
open_by_url
(url)¶ This function is a coroutine.
Opens a spreadsheet specified by url.
Parameters: url – URL of a spreadsheet as it appears in a browser. Returns: a Spreadsheet
instance.Raises: gspread.SpreadsheetNotFound – if no spreadsheet with specified url is found. >>> c = await gspread.authorize(credentials) >>> await c.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
-
openall
(title=None)¶ This function is a coroutine.
Opens all available spreadsheets.
Parameters: title – (optional) If specified can be used to filter spreadsheets by title. Returns: a list of Spreadsheet
instances.
-
remove_permission
(file_id, permission_id)¶ Deletes a permission from a file.
Parameters: - file_id – a spreadsheet ID (aka file ID.)
- permission_id – an ID for the permission.
Models¶
The models represent common spreadsheet objects: a spreadsheet
,
a worksheet
and a cell
.
Note
The classes described below should not be instantiated by end-user. Their instances result from calling other objects’ methods.
-
class
aiospread.models.
Spreadsheet
(client, properties)¶ The class that represents a spreadsheet.
-
add_worksheet
(title, rows, cols)¶ This function is a coroutine.
Adds a new worksheet to a spreadsheet.
Parameters: - title – A title of a new worksheet.
- rows – Number of rows.
- cols – Number of columns.
Returns: a newly created
worksheets
.
-
del_worksheet
(worksheet)¶ This function is a coroutine.
Deletes a worksheet from a spreadsheet.
Parameters: worksheet – The worksheet to be deleted.
-
get_worksheet
(index)¶ This function is a coroutine.
Returns a worksheet with specified index.
Parameters: index – An index of a worksheet. Indexes start from zero. Returns: an instance of gsperad.models.Worksheet
or None if the worksheet is not found.Example. To get first worksheet of a spreadsheet:
>>> async def sheets(): ... sht = await client.open('My fancy spreadsheet') ... worksheet = await sht.get_worksheet('Cool worksheet!')
-
id
¶ Spreadsheet ID.
-
remove_permissions
(value, role='any')¶ This function is a coroutine.
Example:
# Remove Otto's write permission for this spreadsheet await sh.remove_permissions('otto@example.com', role='writer') # Remove all Otto's permissions for this spreadsheet await sh.remove_permissions('otto@example.com')
This function is a coroutine.
Share the spreadsheet with other accounts. :param value: user or group e-mail address, domain name
or None for ‘default’ type.Parameters: - perm_type – the account type.
Allowed values are:
user
,group
,domain
,anyone
. - role – the primary role for this user.
Allowed values are:
owner
,writer
,reader
. - notify – Whether to send an email to the target user/domain.
- email_message – The email to be sent if notify=True
Example:
# Give Otto a write permission on this spreadsheet await sh.share('otto@example.com', perm_type='user', role='writer') # Transfer ownership to Otto await sh.share('otto@example.com', perm_type='user', role='owner')
- perm_type – the account type.
Allowed values are:
-
sheet1
¶ Shortcut property for getting the first worksheet.
-
title
¶ Spreadsheet title.
-
worksheet
(title)¶ This function is a coroutine.
Returns a worksheet with specified title.
Parameters: title – A title of a worksheet. If there’re multiple worksheets with the same title, first one will be returned. Returns: an instance of gsperad.models.Worksheet
.Example. Getting worksheet named ‘Annual bonuses’
>>> async def sheet(): ... sht = await client.open('Sample one') ... worksheet = await sht.worksheet('Annual bonuses')
-
-
class
aiospread.models.
Worksheet
(spreadsheet, properties)¶ The class that represents a single sheet in a spreadsheet (aka “worksheet”).
-
acell
(label, value_render_option='FORMATTED_VALUE')¶ This function is a coroutine.
Returns an instance of a
gspread.models.Cell
.Parameters: - label – String with cell label in common format, e.g. ‘B1’. Letter case is ignored.
- value_render_option – Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.
Example:
>>> async def cell(): ... await worksheet.acell('A1') <Cell R1C1 "I'm cell A1">
-
add_cols
(cols)¶ This function is a coroutine.
Adds colums to worksheet.
Parameters: cols – Columns number to add.
-
add_rows
(rows)¶ This function is a coroutine.
Adds rows to worksheet.
Parameters: rows – Rows number to add.
-
append_row
(values, value_input_option='RAW')¶ This function is a coroutine.
Adds a row to the worksheet and populates it with values. Widens the worksheet if there are more values than columns.
Parameters: values – List of values for the new row.
-
cell
(row, col, value_render_option='FORMATTED_VALUE')¶ This function is a coroutine.
Returns an instance of a
gspread.models.Cell
positioned in row and col column.Parameters: - row – Integer row number.
- col – Integer column number.
- value_render_option – Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.
Example:
>>> async def cell(): ... await worksheet.cell(1, 1) <Cell R1C1 "I'm cell A1">
-
col_count
¶ Number of columns.
-
col_values
(col, value_render_option='FORMATTED_VALUE')¶ This function is a coroutine.
Returns a list of all values in column col.
Empty cells in this list will be rendered as
None
.Parameters: - col – Integer column number.
- value_render_option – Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.
-
delete_row
(index)¶ “This function is a coroutine.
Deletes a row from the worksheet at the specified index.
Parameters: index – Index of a row for deletion.
-
find
(query)¶ This function is a coroutine.
Finds first cell matching query.
Parameters: query – A text string or compiled regular expression.
-
findall
(query)¶ This function is a coroutine.
Finds all cells matching query.
Parameters: query – A text string or compiled regular expression.
-
get_all_records
(empty2zero=False, head=1, default_blank='')¶ This function is a coroutine.
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.
Cell values are numericised (strings that can be read as ints or floats are converted).
Parameters: - empty2zero – determines whether empty cells are converted to zeros.
- head – determines wich row to use as keys, starting from 1 following the numeration of the spreadsheet.
- default_blank – determines whether empty cells are converted to something else except empty string or zero.
-
get_all_values
()¶ This function is a coroutine.
Returns a list of lists containing all cells’ values as strings.
-
id
¶ Id of a worksheet.
-
insert_row
(values, index=1, value_input_option='RAW')¶ This function is a coroutine.
Adds a row to the worksheet at the specified index and populates it with values.
Widens the worksheet if there are more values than columns.
Parameters: - values – List of values for the new row.
- value_input_option – Determines how input data should be interpreted. See ValueInputOption in the Sheets API.
-
range
(name)¶ This function is a coroutine.
Returns a list of
Cell
objects from a specified range.Parameters: name – A string with range value in A1 notation, e.g. ‘A1:A5’. Alternatively, you may specify numeric boundaries. All values index from 1 (one):
Parameters: - first_row – Integer row number
- first_col – Integer row number
- last_row – Integer row number
- last_col – Integer row number
- Example::
>>> async def range(): ... # Using A1 notation ... worksheet.range('A1:B7') [<Cell R1C1 "42">, ...]
>>> async def range2(): ... # Same with numeric boundaries ... worksheet.range(1, 1, 7, 2) [<Cell R1C1 "42">, ...]
-
resize
(rows=None, cols=None)¶ This function is a coroutine.
Resizes the worksheet.
Parameters: - rows – New rows number.
- cols – New columns number.
-
row_count
¶ Number of rows.
-
row_values
(row, value_render_option='FORMATTED_VALUE')¶ This function is a coroutine.
Returns a list of all values in a row.
Empty cells in this list will be rendered as
None
.Parameters: - row – Integer row number.
- value_render_option – Determines how values should be rendered in the the output. See ValueRenderOption in the Sheets API.
-
title
¶ Title of a worksheet.
-
update_acell
(label, value)¶ This function is a coroutine.
Sets the new value to a cell.
Parameters: - label – String with cell label in common format, e.g. ‘B1’. Letter case is ignored.
- value – New value.
Example:
await worksheet.update_acell('A1', '42')
-
update_cell
(row, col, value)¶ This function is a coroutine.
Sets the new value to a cell.
Parameters: - row – Row number.
- col – Column number.
- value – New value.
Example:
await worksheet.update_cell(1, 1, '42')
-
update_cells
(cell_list, value_input_option='RAW')¶ This function is a coroutine.
Updates cells in batch.
Parameters: - cell_list – List of a
Cell
objects to update. - value_input_option – Determines how input data should be interpreted. See ValueInputOption in the Sheets API.
Example:
# Select a range cell_list = await worksheet.range('A1:C7') for cell in cell_list: cell.value = 'O_o' # Update in batch await worksheet.update_cells(cell_list)
- cell_list – List of a
-
update_title
(title)¶ This function is a coroutine.
Renames the worksheet.
Parameters: title – A new title.
-
updated
¶ Deprecated since version 2.0.
This feature is not supported in Sheets API v4.
-
Utils¶
gspread.utils¶
This module contains utility functions.
-
aiospread.utils.
rowcol_to_a1
(row, col)¶ Translates a row and column cell address to A1 notation.
Parameters: - row – The row of the cell to be converted. Rows start at index 1.
- col – The column of the cell to be converted. Columns start at index 1.
Returns: a string containing the cell’s coordinates in A1 notation.
Example:
>>> rowcol_to_a1(1, 1) A1
-
aiospread.utils.
a1_to_rowcol
(label)¶ Translates a cell’s address in A1 notation to a tuple of integers.
Parameters: label – String with cell label in A1 notation, e.g. ‘B1’. Letter case is ignored. Returns: a tuple containing row and column numbers. Both indexed from 1 (one). Example:
>>> a1_to_rowcol('A1') (1, 1)
Exceptions¶
-
exception
aiospread.exceptions.
GSpreadException
¶ A base class for gspread’s exceptions.
-
exception
aiospread.exceptions.
APIError
(response)¶