Skip to content

Plugin

botcity.plugins.googlesheets.plugin.BotGoogleSheetsPlugin

__init__(self, client_secret_path, spreadsheet_id, active_sheet=None) special

This class gives you easy access to Google Sheets API's functionalities. This plugin works with one spreadsheet at a time, so if you want to access multiple files, simply create multiple objects of this class. However, if you need to work with different sheets within the same file, you can do so by supplying the sheet's name to this class's methods, or change the default sheet with set_active_sheet().

Parameters:

Name Type Description Default
client_secret_path str

The path to your client_secret file. Get it from your Google Cloud Console!

 required
spreadsheet_id str

The ID of a Google Spreadsheet file. You can get it from the file's URL.

 required
active_sheet str, Optional

The sheet this plugin will access by default. If None, the first sheet is used. Defaults to None.

 None

add_column(self, column, sheet=None)

Adds a new column to the right end of the sheet.

Parameters:

Name Type Description Default
column List[object]

A list with the cell values.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

add_columns(self, columns, sheet=None)

Adds new columns to the right end of the sheet.

Parameters:

Name Type Description Default
columns List[List[object]]

A list of rows. Each row is a list with cell values.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

add_row(self, row, sheet=None)

Adds a new row to the bottom of the sheet.

Parameters:

Name Type Description Default
row List[object]

A list with the cell values.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

add_rows(self, rows, sheet=None)

Adds new rows to the bottom of the sheet.

Parameters:

Name Type Description Default
rows List[List[object]]

A list of rows. Each row is a list with cell values.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

as_list(self, sheet=None)

Returns the contents of an entire sheet in a list of lists format.

This is equivalent to get_range("", sheet).

Parameters:

Name Type Description Default
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
List[List[object]]

A list of rows. Each row is a list of cell values.

clear(self, sheet=None)

Clears an entire sheet. Only the cells' content is removed, while their formatting remains.

This method is equivalent to clear_range("").

Parameters:

Name Type Description Default
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

clear_range(self, range_, sheet=None)

Clears the provided area of the sheet. Only the cells' content is removed, while the formatting remains.

Parameters:

Name Type Description Default
range_ str

The range to be cleared, in A1 format. Example: 'A1:B2', 'B', '3', 'A1'.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

create_sheet(self, sheet)

Creates a new sheet within the spreadsheet.

Parameters:

Name Type Description Default
sheet str

The new sheet's name.

 required

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

get_cell(self, column, row, sheet=None)

Returns the value of a single cell.

Parameters:

Name Type Description Default
column str

The letter-indexed column name ('a', 'A', 'AA').

 required
row int

The cell's 1-indexed row number.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
object

The cell's value.

get_column(self, column, sheet=None)

Returns the contents of an entire column in a list format.

Please note that altering the values in this list will not alter the values in the original sheet.

Parameters:

Name Type Description Default
column str

The letter-indexed column name ('a', 'A', 'AA').

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
List[object]

The values of all cells within the column.

get_range(self, range_, sheet=None)

Returns the values of all cells within an area of the sheet in a list of list format.

Parameters:

Name Type Description Default
range_ str

The range (minus the sheet) to be retrieved, in A1 format. Example: 'A1:B2', 'B', '3', 'A1'.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
List[List[object]]

A list with the recovered rows. Each row is a list of objects.

get_row(self, row, sheet=None)

Returns the contents of an entire row in a list format.

Please note that altering the values in this list will not alter the values in the original sheet.

Parameters:

Name Type Description Default
row int

The 1-indexed row number.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
List[object]

The values of all cells within the row.

get_spreadsheet_id(self)

Returns the ID of the spreadsheet this plugin is currently linked to.

Specially useful if created a new sheet using the new_spreadsheet class method, since you wouldn't know it's ID.

Returns:

Type Description
str

The ID of a Google Spreadsheet file.

Returns the URL Link to the spreadsheet this plugin is currently linked to.

Returns:

Type Description
str

The URL to a Google Spreadsheet file.

list_sheets(self)

Returns a list with the name of all the sheets in this spreadsheet.

Returns:

Type Description
List[str]

A list of sheet names.

new_spreadsheet(client_secret_path, name) classmethod

An alternative way to initialize the plugin. This factory method will create a new spreadsheet, and return an initialized object of the plugin that refers to it.

Parameters:

Name Type Description Default
client_secret_path str

The path to your client_secret file. Get it from your Google Cloud Console!

 required
name str

The name of the new spreadsheet.

 required

Returns:

Type Description
BotGoogleSheetsPlugin

An initialized object of the plugin points to the newly created sheet.

remove_column(self, column, sheet=None)

Removes a single column from the sheet.

Keep in mind that the columns to its right will be moved to the left.

Parameters:

Name Type Description Default
column str

The letter-indexed name ('a', 'A', 'AA') of the column to be removed.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

remove_columns(self, columns, sheet=None)

Removes columns from the sheet.

Keep in mind that each column removed will cause the columns to its right to be moved left. For this reason, this method will sort the indexes of the columns you provide, and remove then in descending order.

Parameters:

Name Type Description Default
columns List[str]

A list of the letter-indexed names of the columns to be removed.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

remove_row(self, row, sheet=None)

Removes a single row from the sheet.

Keep in mind that the rows below will be moved up.

Parameters:

Name Type Description Default
row int

The 1-indexed number of the row to be removed.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

remove_rows(self, rows, sheet=None)

Removes rows from the sheet.

Keep in mind that each row removed will cause the rows below it to be moved up. For this reason, this method will sort the indexes of the rows you provide, and remove then in descending order.

Parameters:

Name Type Description Default
rows List[int]

A list of the 1-indexed numbers of the rows to be removed.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

remove_sheet(self, sheet=None)

Removes a sheet from the spreadsheet.

Keep in mind that if you remove the active_sheet, you must set another sheet as active before using trying to modify it!

Parameters:

Name Type Description Default
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

set_cell(self, column, row, value, sheet=None)

Replaces the value of a single cell.

Parameters:

Name Type Description Default
column str

The cell's letter-indexed column name.

 required
row int

The cell's 1-indexed row number.

 required
value object

The new value of the cell.

 required
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

set_range(self, values, range_=None, sheet=None)

Replace the values within an area of the sheet by the values supplied.

Parameters:

Name Type Description Default
values List[List[object]]

A list of rows. Each row is a list of cell values.

 required
range_ str, Optional

The range (minus the sheet) to have its values replaced, in A1 format. Ex: 'A1:B2', 'B', '3', 'A1'. If None, the entire sheet will be used as range. Defaults to None.

 None
sheet str

(str, Optional): If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)

sort(self, by_columns, ascending=True, start_row=2, end_row=None, sheet=None)

Sorts the sheet's rows according to the columns provided.

Unless the start and end point are provided, all rows minus the first one will be sorted!

Parameters:

Name Type Description Default
by_columns Union[str, List[str]]

Either a letter-indexed column name to sort the rows by, or a list of them. In case of a tie, the second column is used, and so on.

 required
ascending bool, Optional

Set to False to sort by descending order. Defaults to True.

 True
start_row str, Optional

The 1-indexed row number where the sort will start from. Defaults to 2.

 2
end_row str, Optional

The 1-indexed row number where the sort will end at (inclusive). Defaults to None.

 None
sheet str, Optional

If a sheet is provided, it'll be used by this method instead of the Active Sheet. Defaults to None.

 None

Returns:

Type Description
BotGoogleSheetsPlugin

self (allows Method Chaining)
Back to top