¶
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. |
get_spreadsheet_link(self)
¶
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) |