Sheets¶

implement api to access google sheet

class pysuite.sheets.Sheets(service: googleapiclient.discovery.Resource, max_retry: int = 0, sleep: int = 5)[source]¶

Bases: object

provide api to operate google spreadsheet. An authenticated google api client is needed.

Parameters

service – an authorized Google Spreadsheet service client.
max_retry – max number of retry on quota exceeded error. if 0 or less, no retry will be attempted.
sleep – base number of seconds between retries. the sleep time is exponentially increased after each retry.

batch_update(id: str, body: dict)[source]¶

low level api used to submit a json body to make changes to the specified spreadsheet.

Parameters

id – id of the target spreadsheet
body – request json

Returns

response from batch upate

clear(id: str, sheet_range: str)[source]¶

remove content in the target sheet range.

Parameters

id – id of the target spreadsheet
sheet_range – range in the target spreadsheet. for example, ‘sheet!A1:D’. this means selecting from tab “sheet” and download column A to D and rows from 1 to the last row with non-empty values.

Returns

None

create_sheet(id: str, title: str)[source]¶

create a new sheet with given name in the specified spreadsheet.

Parameters

id – id of the spreadsheet
title – title of the new sheet

Returns

a dictionary containing information about created sheet, such as sheet id, title, index.

create_spreadsheet(name: str) → str[source]¶

create a spreadsheet with requested name.

Parameters: name – name of the created sheet.
Returns: id of the spreadsheet

delete_sheet(id: str, sheet_id: int)[source]¶

delete the specified sheet in the target spreadsheet. You can find sheet_id from URL when you select the sheet in the spreadsheet after “gid=”

Parameters

id – id of spreadsheet.
sheet_id – id of sheet

Returns

None

download(id: str, sheet_range: str, dimension: str = 'ROWS', fill_row: bool = False) → list[source]¶

download target sheet range by specified dimension. All entries will be considered as strings.

Parameters

id – id of the target spreadsheet.
sheet_range – range in the target spreadsheet. for example, ‘sheet!A1:D’. this means selecting from tab “sheet” and download column A to D and rows from 1 to the last row with non-empty values.
dimension – “ROW” or “COLUMNS”. If “ROWS”, each entry in the output list would be one row in the spreadsheet. If “COLUMNS”, each entry in the output list would be one column in the spreadsheet.
fill_row – Whether force to return rows with desired number of columns. Google Sheet API ignores trailing empty cells by default. By setting this to True, empty strings will be filled in those ignored cells. This parameter only works when dimension is “ROWS”.

Returns

content of target sheet range in a list of lists.

read_sheet(id: str, sheet_range: str, header=True, dtypes: Optional[dict] = None, columns: Optional[list] = None, fill_row: bool = True)[source]¶

download the target sheet range into a pandas dataframe. this method will fail if pandas cannot be imported.

Parameters

id – id of the target spreadsheet
sheet_range – range in the target spreadsheet. for example, ‘sheet!A1:D’. this means selecting from tab “sheet” and download column A to D and rows from 1 to the last row with non-empty values.
header – whether first row is used as column names in the output dataframe.
dtypes – a mapping from column name to the type. if not None, type conversions will be applied to columns requested in the dictionary.
columns – a list of column names. If not None and header is False, this will be used as columns of the output dataframe
fill_row – Whether attempt to fill the trailing empty cell with empty strings. This prevents errors when the trailing cells in some rows are empty in the sheet. When header is True, this will attempt to fill the missing header with _col{i}, where i is the index of the column (starting from 1).

Returns

a pandas dataframe containing target spreadsheet values.

rename_sheet(id: str, sheet_id: int, title: str)[source]¶

rename a sheet in target spreadsheet to the new title.

Parameters

id – id of the target spreadsheet
sheet_id – id of the sheet
title – new title of the sheet

Returns

None

to_sheet(df, id: str, sheet_range: str)[source]¶

Upload pandas dataframe to target sheet range. The number of columns must fit the range. More columns or fewer columns will both raise exception. The data in the provided dataframe must be serializable.

Parameters

df (pandas.DataFrame) – pandas dataframe to be uploaded
id – id of the target spreadsheet
sheet_range – range in the target spreadsheet. for example, ‘sheet!A1:D’. this means selecting from tab “sheet” and download column A to D and rows from 1 to the last row with non-empty values.

Returns

None

upload(values: list, id: str, range: str) → None[source]¶

Upload a list of lists to target sheet range. All entries in the provided list must be serializable.

Parameters

values – a list of lists of objects that can be converted to str.
id – id of the target spreadsheet
range – range in the target spreadsheet. for example, ‘sheet!A1:D’. this means selecting from tab “sheet” and download column A to D and rows from 1 to the last row with non-empty values.

Returns

None

pysuite.sheets.get_col_counts_from_range(sheet_range: str) → int[source]¶

Calculate the number of columns in the given range.

Example

>>> get_col_counts_from_range("test!A1:A")  # 1
>>> get_col_counts_from_range("test!A1:D")  # 4
>>> get_col_counts_from_range("test!AA2:AZ")  # 26

Parameters: sheet_range – a string representation of sheet range. For example, “test_sheet!A1:D”
Returns: the number of columns contained in the range.

pysuite.sheets.get_column_number(col: str) → int[source]¶

Convert spreadsheet column numbers to integer.

Example

>>> get_column_number('A')  # 1
>>> get_column_number('AA') # 27
>>> get_column_number('ZY') # 701

Parameters: col – upper case spreadsheet column
Returns: index of the column starting from 1.