Sheets API Helpers

Contains helpers for communicating with the Google Sheets API.

class codepost_powertools.utils.sheets_api.Dimension

Indicates which dimension an operation should apply to.

https://developers.google.com/sheets/api/reference/rest/v4/Dimension

ROWS = 'ROWS': Operates on the rows of a sheet.

COLUMNS = 'COLUMNS': Operates on the columns of a sheet.

class codepost_powertools.utils.sheets_api.DimensionRange

A range along a single dimension on a sheet.

All indexes are zero-based. Indexes are half open: the start index is inclusive and the end index is exclusive. Missing indexes indicate the range is unbounded on that side.

https://developers.google.com/sheets/api/reference/rest/v4/DimensionRange

__init__(*, sheet_id, dimension, start_index=None, end_index=None)

Parameters:

sheet_id (int) –
dimension (Dimension) –
start_index (int | None) –
end_index (int | None) –

classmethod from_range(*, sheet_id, dimension, range_a1)

Parameters:

sheet_id (int) –
dimension (Dimension) –
range_a1 (str) –

classmethod rows(*, sheet_id, range_a1)

Parameters:

sheet_id (int) –
range_a1 (str) –

classmethod cols(*, sheet_id, range_a1)

Parameters:

sheet_id (int) –
range_a1 (str) –

class codepost_powertools.utils.sheets_api.GridProperties

Properties of a grid.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#GridProperties

__init__(*, row_count=None, col_count=None, frozen_row_count=None, frozen_col_count=None, hide_gridlines=None, row_group_control_after=None, col_group_control_after=None)

Parameters:

row_count (int | None) –
col_count (int | None) –
frozen_row_count (int | None) –
frozen_col_count (int | None) –
hide_gridlines (bool | None) –
row_group_control_after (bool | None) –
col_group_control_after (bool | None) –

class codepost_powertools.utils.sheets_api.SheetProperties

Properties of a sheet.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#sheetproperties

__init__(*, sheet_id, title=None, index=None, grid_properties=None, hidden=None, tab_color_style=None, right_to_left=None)

Parameters:

sheet_id (int) –
title (str | None) –
index (int | None) –
grid_properties (GridProperties | None) –
hidden (bool | None) –
tab_color_style (ColorStyle | None) –
right_to_left (bool | None) –

updateRequest()

Returns the UpdateSheetPropertiesRequest dict with these properties.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatesheetpropertiesrequest

Return type:: Dict

class codepost_powertools.utils.sheets_api.DimensionProperties

Properties about a dimension.

Note

Excludes the field "developerMetadata".

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#DimensionProperties

__init__(*, hidden_by_user=None, pixel_size=None, developer_metadata=None)

Parameters:

hidden_by_user (bool | None) –
pixel_size (int | None) –

updateRequest(dim_range)

Returns the UpdateDimensionPropertiesRequest dict with these properties and the given dimension range.

Note

Excludes the union field "dataSourceSheetRange".

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatedimensionpropertiesrequest

Parameters:: dim_range (DimensionRange) –
Return type:: Dict

class codepost_powertools.utils.sheets_api.ConditionalFormatRule

A rule describing a conditional format.

Warning

This class is not implemented at all.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/sheets#conditionalformatrule

class codepost_powertools.utils.sheets_api.CellData

Data about a specific cell.

Note

Excludes the fields "textFormatRuns[]", "pivotTable", "dataSourceTable", and "dataSourceFormula".

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData

__init__(*, user_entered_value=None, user_entered_format=None, hyperlink=None, note=None, text_format_runs=None, data_validation=None, pivot_table=None, data_source_table=None, data_source_formula=None)

Parameters:

user_entered_value (ExtendedValue | None) –
user_entered_format (CellFormat | None) –
hyperlink (str | None) –
note (str | None) –
data_validation (DataValidationRule | None) –

updateRequest(grid_range)

Returns the RepeatCellRequest dict with these properties and the given grid range.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#repeatcellrequest

Parameters:: grid_range (GridRange) –
Return type:: Dict

class codepost_powertools.utils.sheets_api.CellFormat

The format of a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellFormat

__init__(*, number_format=None, background_color_style=None, borders=None, padding=None, horizontal_alignment=None, vertical_alignment=None, wrap_strategy=None, text_direction=None, text_format=None, hyperlink_display_type=None, text_rotation=None)

Parameters:

number_format (NumberFormat | None) –
background_color_style (ColorStyle | None) –
borders (Borders | None) –
padding (Padding | None) –
horizontal_alignment (HorizontalAlign | None) –
vertical_alignment (VerticalAlign | None) –
wrap_strategy (WrapStrategy | None) –
text_direction (TextDirection | None) –
text_format (TextFormat | None) –
hyperlink_display_type (HyperlinkDisplayType | None) –
text_rotation (TextRotation | None) –

class codepost_powertools.utils.sheets_api.NumberFormat

The number format of a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#NumberFormat

__init__(*, format_type, pattern=None)

Parameters:

format_type (NumberFormatType) –
pattern (str | None) –

class codepost_powertools.utils.sheets_api.NumberFormatType

The number format of the cell.

The examples are in the en_US locale, but the actual format depends on the locale of the spreadsheet.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#numberformattype

TEXT = 'TEXT': Text formatting, e.g. 1000.12

NUMBER = 'NUMBER': Number formatting, e.g. 1,000.12

PERCENT = 'PERCENT': Percent formatting, e.g. 10.12%

CURRENCY = 'CURRENCY': Currency formatting, e.g. $1,000.12

DATE = 'DATE': Date formatting, e.g. 9/26/2008

TIME = 'TIME': Time formatting, e.g. 3:59:00 PM

DATE_TIME = 'DATE_TIME': Date+Time formatting, e.g. 9/26/08 15:59:00

SCIENTIFIC = 'SCIENTIFIC': Scientific number formatting, e.g. 1.01E+03

class codepost_powertools.utils.sheets_api.Borders

The borders of the cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#Borders

__init__(*, top=None, bottom=None, left=None, right=None)

Parameters:

top (Border | None) –
bottom (Border | None) –
left (Border | None) –
right (Border | None) –

class codepost_powertools.utils.sheets_api.Border

A border along a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#border

__init__(*, style=None, color_style=None)

Parameters:

style (Style | None) –
color_style (ColorStyle | None) –

class codepost_powertools.utils.sheets_api.Style

The style of a border.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#style

DOTTED = 'DOTTED': The border is dotted.

DASHED = 'DASHED': The border is dashed.

SOLID = 'SOLID': The border is a thin solid line.

SOLID_MEDIUM = 'SOLID_MEDIUM': The border is a medium solid line.

SOLID_THICK = 'SOLID_THICK': The border is a thick solid line.

NONE = 'NONE': No border. Used only when updating a border in order to erase it.

DOUBLE = 'DOUBLE': The border is two solid lines.

class codepost_powertools.utils.sheets_api.Padding

The amount of padding around the cell, in pixels.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#Padding

__init__(*, top, right, bottom, left)

Parameters:

top (int) –
right (int) –
bottom (int) –
left (int) –

class codepost_powertools.utils.sheets_api.VerticalAlign

The vertical alignment of text in a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#VerticalAlign

TOP = 'TOP': The text is explicitly aligned to the top of the cell.

MIDDLE = 'MIDDLE': The text is explicitly aligned to the middle of the cell.

BOTTOM = 'BOTTOM': The text is explicitly aligned to the bottom of the cell.

class codepost_powertools.utils.sheets_api.WrapStrategy

How to wrap text in a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#WrapStrategy

OVERFLOW_CELL = 'OVERFLOW_CELL'

Lines that are longer than the cell width will be written in the next cell over, so long as that cell is empty. If the next cell over is non-empty, this behaves the same as CLIP. The text will never wrap to the next line unless the user manually inserts a new line.

Example:

| First sentence. |
| Manual newline that is very long. <- Text continues into next cell
| Next newline.   |

CLIP = 'CLIP'

Lines that are longer than the cell width will be clipped. The text will never wrap to the next line unless the user manually inserts a new line.

Example:

| First sentence. |
| Manual newline t| <- Text is clipped
| Next newline.   |

WRAP = 'WRAP'

Words that are longer than a line are wrapped at the character level rather than clipped.

Example:

| Cell has a |
| loooooooooo| <- Word is broken.
| ong word.  |

class codepost_powertools.utils.sheets_api.TextDirection

The direction of text in a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#TextDirection

LEFT_TO_RIGHT = 'LEFT_TO_RIGHT': Left to right.

RIGHT_TO_LEFT = 'RIGHT_TO_LEFT': Right to left.

class codepost_powertools.utils.sheets_api.HyperlinkDisplayType

Whether to explicitly render a hyperlink.

If not specified, the hyperlink is linked.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#HyperlinkDisplayType

LINKED = 'LINKED': A hyperlink should be explicitly rendered.

PLAIN_TEXT = 'PLAIN_TEXT': A hyperlink should not be rendered.

class codepost_powertools.utils.sheets_api.TextRotation

The rotation applied to text in a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#textrotation

classmethod angle(angle)

The angle between the standard orientation and the desired orientation, measured in degrees.

Valid values are between -90 and 90. Positive angles are angled upwards, negative are angled downwards.

Note

For LTR text direction positive angles are in the counterclockwise direction, whereas for RTL they are in the clockwise direction.

Parameters:: angle (int) –
Return type:: TextRotation

classmethod vertical(vertical)

If true, text reads top to bottom, but the orientation of individual characters is unchanged.

Parameters:: vertical (bool) –
Return type:: TextRotation

class codepost_powertools.utils.sheets_api.DataValidationRule

A data validation rule.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#DataValidationRule

__init__(*, condition, input_message=None, strict=None, show_custom_ui=None)

Parameters:

condition (BooleanCondition) –
input_message (str | None) –
strict (bool | None) –
show_custom_ui (bool | None) –

class codepost_powertools.utils.sheets_api.Color

A color in the RGB color space.

Note

Alpha values are not supported.

MAX_RGB = 255

__init__(red, green, blue)

Creates a color from 3 floats between [0, 1].

Parameters:

red (float) –
green (float) –
blue (float) –

classmethod ints(red, green, blue)

Creates a color from 3 integers between [0, 255].

Parameters:

red (int) –
green (int) –
blue (int) –

Return type:

Color

classmethod auto(*args)

Automatically constructs a color based on the given arg(s).

Return type:: Color

class codepost_powertools.utils.sheets_api.ColorStyle

A color value.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ColorStyle

classmethod from_color(color)

Parameters:: color (Color) –
Return type:: ColorStyle

classmethod rgb(red, green, blue)

Creates a color style from 3 floats between [0, 1].

Parameters:

red (float) –
green (float) –
blue (float) –

Return type:

ColorStyle

classmethod rgb_ints(red, green, blue)

Creates a color style from 3 integers between [0, 255].

Parameters:

red (int) –
green (int) –
blue (int) –

Return type:

ColorStyle

classmethod theme(theme_color_type)

Parameters:: theme_color_type (ThemeColorType) –
Return type:: ColorStyle

classmethod auto(*args)

Automatically constructs a color style based on the given arg(s).

Return type:: ColorStyle

class codepost_powertools.utils.sheets_api.ThemeColorType

Theme color types.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ThemeColorType

TEXT = 'TEXT': Represents the primary text color

BACKGROUND = 'BACKGROUND': Represents the primary background color

ACCENT1 = 'ACCENT1': Represents the first accent color

ACCENT2 = 'ACCENT2': Represents the second accent color

ACCENT3 = 'ACCENT3': Represents the third accent color

ACCENT4 = 'ACCENT4': Represents the fourth accent color

ACCENT5 = 'ACCENT5': Represents the fifth accent color

ACCENT6 = 'ACCENT6': Represents the sixth accent color

LINK = 'LINK': Represents the color to use for hyperlinks

class codepost_powertools.utils.sheets_api.HorizontalAlign

The horizontal alignment of text in a cell.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#HorizontalAlign

LEFT = 'LEFT': The text is explicitly aligned to the left of the cell.

CENTER = 'CENTER': The text is explicitly aligned to the center of the cell.

RIGHT = 'RIGHT': The text is explicitly aligned to the right of the cell.

class codepost_powertools.utils.sheets_api.TextFormat

The format of a run of text in a cell.

Note

Since the Link type is simply {"uri": string}, this class accepts the uri itself.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#TextFormat

__init__(*, foreground_color_style=None, font_family=None, font_size=None, bold=None, italic=None, strikethrough=None, underline=None, link=None)

Parameters:

foreground_color_style (ColorStyle | None) –
font_family (str | None) –
font_size (int | None) –
bold (bool | None) –
italic (bool | None) –
strikethrough (bool | None) –
underline (bool | None) –
link (str | None) –

class codepost_powertools.utils.sheets_api.ExtendedValue

The kinds of value that a cell in a spreadsheet can have.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ExtendedValue

classmethod number(number)

Parameters:: number (int | float) –
Return type:: ExtendedValue

classmethod string(string)

Parameters:: string (str) –
Return type:: ExtendedValue

classmethod boolean(boolean)

Parameters:: boolean (bool) –
Return type:: ExtendedValue

classmethod formula(formula)

Parameters:: formula (str) –
Return type:: ExtendedValue

class codepost_powertools.utils.sheets_api.BooleanCondition

A condition that can evaluate to true or false.

This class is modified a bit from the official documentation, taking into consideration all the different condition types and value constraints.

Note

Excludes the condition types TEXT_NOT_EQ and DATE_NOT_EQ, which are only supported by filters on data source objects.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#BooleanCondition

supports(rule)

Checks if the given rule is supported by this BooleanCondition.

It is up to the rules to determine if they are allowed to use this condition.

Possible rules are:

ConditionalFormatRule
DataValidationRule
FilterCriteria

classmethod NUMBER_GREATER(number)

Parameters:: number (int | float) –
Return type:: BooleanCondition

classmethod NUMBER_GREATER_THAN_EQ(number)

Parameters:: number (int | float) –
Return type:: BooleanCondition

classmethod NUMBER_LESS(number)

Parameters:: number (int | float) –
Return type:: BooleanCondition

classmethod NUMBER_LESS_THAN_EQ(number)

Parameters:: number (int | float) –
Return type:: BooleanCondition

classmethod NUMBER_EQ(number)

Parameters:: number (int | float) –
Return type:: BooleanCondition

classmethod NUMBER_NOT_EQ(number)

Parameters:: number (int | float) –
Return type:: BooleanCondition

classmethod NUMBER_BETWEEN(low, high)

Parameters:

low (int | float) –
high (int | float) –

Return type:

BooleanCondition

classmethod NUMBER_NOT_BETWEEN(low, high)

Parameters:

low (int | float) –
high (int | float) –

Return type:

BooleanCondition

classmethod TEXT_CONTAINS(value)

Parameters:: value (str) –
Return type:: BooleanCondition

classmethod TEXT_NOT_CONTAINS(value)

Parameters:: value (str) –
Return type:: BooleanCondition

classmethod TEXT_STARTS_WITH(value)

Parameters:: value (str) –
Return type:: BooleanCondition

classmethod TEXT_ENDS_WITH(value)

Parameters:: value (str) –
Return type:: BooleanCondition

classmethod TEXT_EQ(value)

Parameters:: value (str) –
Return type:: BooleanCondition

classmethod TEXT_IS_EMAIL()

Return type:: BooleanCondition

classmethod TEXT_IS_URL()

Return type:: BooleanCondition

classmethod DATE_EQ(date)

Parameters:: date (str) –
Return type:: BooleanCondition

classmethod DATE_BEFORE(date)

Parameters:: date (str | RelativeDate) –
Return type:: BooleanCondition

classmethod DATE_AFTER(date)

Parameters:: date (str | RelativeDate) –
Return type:: BooleanCondition

classmethod DATE_ON_OR_BEFORE(date)

Parameters:: date (str | RelativeDate) –
Return type:: BooleanCondition

classmethod DATE_ON_OR_AFTER(date)

Parameters:: date (str | RelativeDate) –
Return type:: BooleanCondition

classmethod DATE_BETWEEN(date1, date2)

Parameters:

date1 (str) –
date2 (str) –

Return type:

BooleanCondition

classmethod DATE_NOT_BETWEEN(date1, date2)

Parameters:

date1 (str) –
date2 (str) –

Return type:

BooleanCondition

classmethod DATE_IS_VALID()

Return type:: BooleanCondition

classmethod ONE_OF_RANGE(range_a1)

Parameters:: range_a1 (str) –
Return type:: BooleanCondition

classmethod ONE_OF_LIST(values)

The cell’s value must be in the list of condition values.

Supports any number of condition values, one per item in the list. Formulas are not supported in the values.

Parameters:: values (Iterable[str]) –
Return type:: BooleanCondition

classmethod BLANK()

Return type:: BooleanCondition

classmethod NOT_BLANK()

Return type:: BooleanCondition

classmethod CUSTOM_FORMULA(formula)

Parameters:: formula (str) –
Return type:: BooleanCondition

classmethod BOOLEAN() → BooleanCondition
classmethod BOOLEAN(checked_value: Any) → BooleanCondition
classmethod BOOLEAN(checked_value: Any, unchecked_value: Any) → BooleanCondition: Renders a cell checkbox, with the given values rendering as checked and unchecked, respectively.

classmethod CHECKBOX(checked_value=None, unchecked_value=None): Renders a cell checkbox, with the given values rendering as checked and unchecked, respectively.

class codepost_powertools.utils.sheets_api.RelativeDate

Controls how a date condition is evaluated.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#RelativeDate

PAST_YEAR = 'PAST_YEAR': The value is one year before today.

PAST_MONTH = 'PAST_MONTH': The value is one month before today.

PAST_WEEK = 'PAST_WEEK': The value is one week before today.

YESTERDAY = 'YESTERDAY': The value is yesterday.

TODAY = 'TODAY': The value is today.

TOMORROW = 'TOMORROW': The value is tomorrow.

class codepost_powertools.utils.sheets_api.MergeType

The type of merge to create.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#mergetype

MERGE_ALL = 'MERGE_ALL': Create a single merge from the range

MERGE_COLUMNS = 'MERGE_COLUMNS': Create a merge for each column in the range

MERGE_ROWS = 'MERGE_ROWS': Create a merge for each row in the range

class codepost_powertools.utils.sheets_api.GridRange

A range on a sheet.

All indexes are zero-based. Indexes are half open, i.e. the start index is inclusive and the end index is exclusive. Missing indexes indicate the range is unbounded on that side.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#GridRange

__init__(*, sheet_id, start_row_index=None, end_row_index=None, start_col_index=None, end_col_index=None)

Parameters:

sheet_id (int) –
start_row_index (int | None) –
end_row_index (int | None) –
start_col_index (int | None) –
end_col_index (int | None) –

classmethod entire_sheet(*, sheet_id)

Parameters:: sheet_id (int) –

classmethod from_range(*, sheet_id, range_a1)

Parameters:

sheet_id (int) –
range_a1 (str) –

mergeRequest(*, merge_type=MergeType.MERGE_ALL)

Returns the MergeCellsRequest dict for this GridRange.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#MergeCellsRequest

Parameters:: merge_type (MergeType) –
Return type:: Dict

class codepost_powertools.utils.sheets_api.FilterCriteria

Criteria for showing/hiding rows in a filter or filter view.

Warning

This class is not implemented at all.

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#FilterCriteria