Welcome to Google Sheets Library’s documentation!

Usage

Create your client credentials and place in your project root. Then create your Google Sheets client like:

from google_sheets_lib import GoogleSheets

client = GoogleSheets()

Alternatively, create a service account and place the JSON in your project root. Then create your Google Sheets client like:

from google_sheets_lib import GoogleSheets
from pathlib import Path

client = GoogleSheets(service_account_file=Path.cwd() / 'client_secret.json')

Change Log

0.3

• Added service account authentication

API

class google_sheets_lib.GoogleSheets(drive_folder_id=None, logging_level: str = 'INFO', service_account_file: str = None, credentials=None)

Bases: object

add_column(at_column: int = -1) → google_sheets_lib.GoogleSheets

Adds a columns to the active worksheet at position at_column

https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.insert_cols

Parameters:at_column (int, optional) – Adds column at specified position in the worksheet, -1 (default) specifies end of active columns Returns:A copy of the current object; this allows call chaining Raises:TypeError – If a worksheet has not been activated

add_data_to_ws_rows(worksheet: str, data: List[Dict], preserve_blanks: bool = False) → str

Add data to an existing or new worksheet by header value

Parameters:

• worksheet (str) – The name of the worksheet to add to or create
• data (List of dicts) – Adds values to specified worksheet
• preserve_blanks (bool, optional) – Whether or not to preserve empty strings (‘’) when exporting data

Returns:

<worksheet>!<starting range>:<ending range>

Return type:

str

add_row(at_row: int = -1) → google_sheets_lib.GoogleSheets

Adds a row to the active worksheet at position at_row

https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.insert_rows

Parameters:at_row (int, optional) – Adds row at specified position in the worksheet, -1 (default) specifies end of active rows Returns:A copy of the current object; this allows call chaining Raises:TypeError – If a worksheet has not been activated

create_sheet(title: str) → google_sheets_lib.GoogleSheets

Creates and activates a Google Spreadsheet within drive_folder_id

https://pygsheets.readthedocs.io/en/stable/reference.html#pygsheets.Client.create

Parameters:title (str) – Title of the spreadsheet to create Returns:A copy of the current object; this allows call chaining

create_ws(title: str) → google_sheets_lib.GoogleSheets

Creates and activates a worksheet within the active Google Spreadsheet,

https://pygsheets.readthedocs.io/en/stable/spreadsheet.html#pygsheets.Spreadsheet.add_worksheet

Parameters:title (str) – Title of the worksheet to create Returns:A copy of the current object; this allows call chaining Raises:TypeError – If a Spreadsheet has not been activated

delete_sheet(*, title: str = None, key: str = None, url: str = None, ignore_errors: bool = False) → google_sheets_lib.GoogleSheets

Deletes the specified Google Spreadsheet by ID only

Parameters:

• title (str, optional) – The title of the spreadsheet to delete
• key (str, optional) – The key ID of the spreadsheet to delete
• url (str, optional) – The URL of the spreadsheet to delete
• ignore_errors (bool, optional) – Can optionally ignore any errors like missing spreadsheets

Returns:

A copy of the current object; this allows call chaining

Raises:

KeyError – If the specified Spreadsheet does not exist

delete_ws(ws_id: str) → google_sheets_lib.GoogleSheets

Deletes the specified worksheet by ID only

Parameters:

ws_id (str) – The worksheet ID to delete

Returns:

A copy of the current object; this allows call chaining

Raises:

• TypeError – If a Spreadsheet has not been activated
• KeyError – If the specified worksheet does not exist

find_cells(value, match_case: bool = True, match_entire_cell: bool = True) → List[pygsheets.cell.Cell]

Finds all cells that contains value across all worksheets

https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.find

Parameters:

• value – Value to find in active worksheet (supports a compiled regular expression)
• match_case (bool, optional) – Whether or not match cells based on case. Default is case sensitive
• match_entire_cell (bool, optional) – Whether or not match the entire value of the cell. Default is full cell matching

Returns:

A list of all pygsheets Cell objects that contains value

https://pygsheets.readthedocs.io/en/stable/cell.html

static format_addr(addr)

get_column(index: int) → List

Get all values in column index from the active worksheet

https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.get_col

Parameters:index (int) – The integer index of the column to access Returns:List of values from column Raises:TypeError – If a worksheet has not been activated

get_data_from_ws_range(ws_range: Union[str, Pattern[~AnyStr]]) → List[Dict]

Recursively retrieves data from other ranges in other worksheets of the same Spreadsheet

Parameters:ws_range (str or Pattern) – A <worksheet>!<starting range>:<ending range> formatted range string or a re regex parsed object Returns:List of dicts with header values as the key

get_row(index: int) → List

Get all values in row index from the active worksheet

https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.get_row

Parameters:index (int) – The integer index of the row to access Returns:List of values from row Raises:TypeError – If a worksheet has not been activated

list_sheets() → List[str]

Lists the available Google Spreadsheets under self.folder

Returns:A list of Google Spreadsheet titles

list_ws() → List[pygsheets.worksheet.Worksheet]

Lists all worksheets in the active Google Spreadsheet

https://pygsheets.readthedocs.io/en/stable/spreadsheet.html#pygsheets.Spreadsheet.worksheets

Returns:List of all pygsheets Worksheet objects in active Spreadsheet Raises:TypeError – If a Spreadsheet has not been activated

replace_value(find_value: str, replacement: str) → google_sheets_lib.GoogleSheets

Replace the find_value with replacement across all worksheets. This performs only a replacement on

the specified find_value, leaving the rest of the cell contents intact. https://pygsheets.readthedocs.io/en/stable/worksheet.html#pygsheets.Worksheet.find

Parameters:

• find_value – The string or regex value to find within the worksheet
• replacement (str) – The string used to replace all found values

Returns:

A copy of the current object; this allows call chaining

set_or_create_sheet(title: str) → google_sheets_lib.GoogleSheets

Activates the Google Spreadsheet by title if it exists, otherwise create it

Parameters:title (str) – The title of the Google Spreadsheet to set or create Returns:A copy of the current object; this allows call chaining

set_or_create_ws(title: str) → google_sheets_lib.GoogleSheets

Activates the worksheet by title if it exists, otherwise create it

Parameters:title (str) – The title of the worksheet to set or create Returns:A copy of the current object; this allows call chaining Raises:TypeError – If a Spreadsheet has not been activated

set_sheet(*, title: str = None, key: str = None, url: str = None) → google_sheets_lib.GoogleSheets

Activates the current Google Spreadsheet that subsequent actions will be performed on

At least one parameter must be specified. If more that one is specified, then only one will be used https://pygsheets.readthedocs.io/en/stable/reference.html#pygsheets.Client.open Sets the worksheet to index: 0 by default

Parameters:

• title (str, optional) – The title of the spreadsheet to activate
• key (str, optional) – The key ID of the spreadsheet to activate
• url (str, optional) – The URL of the spreadsheet to activate

Returns:

A copy of the current object; this allows call chaining

Raises:

• TypeError – If no keyword arguments are specified
• KeyError – If the specified Spreadsheet does not exist

set_ws(*, title: str = None, index: int = None, ws_id: str = None) → google_sheets_lib.GoogleSheets

Activates the worksheet that subsequent actions will be performed on

At least one parameter must be specified. If more that one is specified, then only one will be used https://pygsheets.readthedocs.io/en/stable/spreadsheet.html#pygsheets.Spreadsheet.worksheet

Parameters:

• title (str, optional) – The title of the worksheet to activate
• index (int, optional) – The index of the worksheet to activate
• ws_id (str, optional) – The ID of the worksheet to activate

Returns:

A copy of the current object; this allows call chaining

Raises:

• ValueError – If a Spreadsheet has not been activated
• TypeError – If no keyword arguments are specified
• KeyError – If the specified worksheet does not exist

sheet = None

update_column_by_header(values: List[Dict], column_offset: int, header_column: int = 1, case_sensitive: bool = True) → bool

Update values in a column based on the specified header keys. Will add additional columns to fit provided data

See also

blabla

VersionAdded:

1.2

Parameters:

• values (List of dicts) – Adds values starting at the column_offset based on position of matching key in header_column. Additional dicts in list will update the next column(s)
• column_offset (int) – Adds column at specified position in the worksheet
• header_column (int, optional) – Specifies which column to use for matching headers, defaults to the first column
• case_sensitive (bool, optional) – Specifies whether the header-key matching is case sensitive, it is by default

Returns:

True on successful update; false otherwise

Return type:

bool

Raises:

TypeError – If a worksheet has not been activated

update_column_by_index(values: List[List], column_offset: int, row_offset: int = 1) → bool

Update values in a column based on the numerical index. (row_offset, column_offset) specifies the starting

point in the worksheet for where to start updating values. Will add additional columns to fit provided data

Parameters:

• values (List of lists) – Adds values in order at the specified column_offset Additional lists in list will update the next column(s)
• column_offset (int) – Adds column at specified position in the worksheet
• row_offset (int, optional) – Starts the column at specified row, defaults to first row (top)

Returns:

True on successful update; false otherwise

Raises:

TypeError – If a worksheet has not been activated

update_row_by_header(values: List[Dict], row_offset: int, header_row: int = 1, case_sensitive: bool = True) → bool

Update values in a row based on the specified header keys. Will add additional rows to fit provided data

Parameters:

• values (List of dicts) – Adds values starting at the row_offset based on position of matching key in header_row Additional dicts in list will update the next row(s)
• row_offset (int) – Adds row at specified position in the worksheet
• header_row (int, optional) – Specifies which row to use for matching headers, defaults to the first row
• case_sensitive (bool, optional) – Specifies whether the header-key matching is case sensitive, it is by default

Returns:

True on successful update; false otherwise

Raises:

TypeError – If a worksheet has not been activated

update_row_by_index(values: List[List], row_offset: int, column_offset: int = 1) → bool

Update values in a row based on the numerical index. (row_offset, column_offset) specifies the starting

point in the worksheet for where to start updating values. Will add additional rows to fit provided data

Parameters:

• values (List of lists) – Adds values in order at the specified row_offset Additional lists in list will update the next row(s)
• row_offset (int) – Adds row at specified position in the worksheet
• column_offset (int, optional) – Starts the row at specified column, defaults to first column (far left)

Returns:

True on successful update; false otherwise

Raises:

TypeError – If a worksheet has not been activated

ws = None

ws_range_format = re.compile('(?P<worksheet>[a-zA-Z0-9_]+)!(?P<start_range>[A-Z0-9]+):(?P<end_range>[A-Z0-9]+)')

Index

• Index