Sheets

albert.resources.sheets

CellColor

Bases: str, Enum

The allowed colors for a cell

Attributes:

Name	Type	Description
WHITE
RED
GREEN
BLUE
YELLOW
ORANGE
PURPLE

WHITE

WHITE = 'RGB(255, 255, 255)'

RED

RED = 'RGB(255, 161, 161)'

GREEN

GREEN = 'RGB(130, 222, 198)'

BLUE

BLUE = 'RGB(214, 233, 255)'

YELLOW

YELLOW = 'RGB(254, 240, 159)'

ORANGE

ORANGE = 'RGB(255, 227, 210)'

PURPLE

PURPLE = 'RGB(238, 215, 255)'

CellType

Bases: str, Enum

The type of information in the Cell

Attributes:

Name	Type	Description
INVENTORY
APP
BLANK
FORMULA
TAG
PRICE
PDC
BAT
TOTAL
TAS
DEF
LKP
FOR
EXTINV
BTI

INVENTORY

INVENTORY = 'INV'

APP

APP = 'APP'

BLANK

BLANK = 'BLK'

FORMULA

FORMULA = 'Formula'

TAG

TAG = 'TAG'

PRICE

PRICE = 'PRC'

PDC

PDC = 'PDC'

BAT

BAT = 'BAT'

TOTAL

TOTAL = 'TOT'

TAS

TAS = 'TAS'

DEF

DEF = 'DEF'

LKP

LKP = 'LKP'

FOR

FOR = 'FOR'

EXTINV

EXTINV = 'EXTINV'

BTI

BTI = 'BTI'

DesignType

Bases: str, Enum

The type of Design

Attributes:

Name	Type	Description
APPS
PRODUCTS
RESULTS
PROCESS

APPS

APPS = 'apps'

PRODUCTS

PRODUCTS = 'products'

RESULTS

RESULTS = 'results'

PROCESS

PROCESS = 'process'

Cell

Bases: BaseResource

A Cell in a Sheet

Attributes:

Name	Type	Description
column_id	str	The column ID of the cell.
row_id	str	The row ID of the cell.
value	str | dict	The value of the cell. If the cell is an inventory item, this will be a dict.
min_value	str | None	The minimum allowed value for inventory cells. Optional.
max_value	str | None	The maximum allowed value for inventory cells. Optional.
row_label_name	(str, optional)	The display name of the row.
type	CellType	The type of the cell. Allowed values are INV, APP, BLK, Formula, TAG, PRC, PDC, BAT, TOT, TAS, DEF, LKP, FOR, and EXTINV.
row_type	(CellType, optional)	The type of the row containing this cell. Usually one of INV (inventory row), TOT (total row), TAS (task row), TAG, PRC, PDC, BAT or BLK.
name	str | None	The name of the cell. Optional. Default is None.
calculation	str	The calculation of the cell. Optional. Default is "".
design_id	str	The design ID of the design this cell is in.
format	dict	The format of the cell. Optional. Default is {}. The format is a dict with the keys bgColor and fontColor. The values are strings in the format RGB(255, 255, 255).
raw_value	str	The raw value of the cell. If the cell is an inventory item, this will be the value of the inventory item. Read-only.
color	str | None	The color of the cell. Read only.

column_id

column_id: str = Field(alias='colId')

row_id

row_id: str = Field(alias='rowId')

row_label_name

row_label_name: str | None = Field(
    default=None, alias="lableName"
)

value

value: str | dict = ''

min_value

min_value: str | None = Field(
    default=None, alias="minValue"
)

max_value

max_value: str | None = Field(
    default=None, alias="maxValue"
)

type

type: CellType

row_type

row_type: CellType | None = Field(default=None)

name

name: str | None = Field(default=None)

calculation

calculation: str = ''

design_id

design_id: str

format

format: dict = Field(
    default_factory=dict, alias="cellFormat"
)

inventory_id

inventory_id: str | None = Field(default=None)

raw_value

raw_value

color

color

Component

Bases: BaseResource

Represents an amount of an inventory item in a formulation

Attributes:

Name	Type	Description
inventory_item	InventoryItem	The inventory item in the component
amount	float	The amount of the inventory item in the component
cell	Cell	The cell that the component is in. Read-only.

inventory_item

inventory_item: InventoryItem

amount

amount: float

min_value

min_value: float | None = Field(default=None)

max_value

max_value: float | None = Field(default=None)

cell

cell

DesignState

Bases: BaseResource

The state of a Design

Attributes:

Name	Type	Description
collapsed	bool | None

collapsed

collapsed: bool | None = False

Design

Bases: BaseSessionResource

A Design in a Sheet. Designs are sheet subsections that are largly abstracted away from the user.

Attributes:

Name	Type	Description
id	str	The Albert ID of the design.
design_type	DesignType	The type of the design. Allowed values are apps, products, and results.
state	DesignState | None	The state of the design. Optional. Default is None.
grid	DataFrame | None	The grid of the design. Optional. Default is None. Read-only.
rows	list[Row] | None	The rows of the design. Optional. Default is None. Read-only.
columns	list[Column] | None	The columns of the design. Optional. Default is None. Read-only.

state

state: DesignState | None = Field({})

id

id: str = Field(alias='albertId')

design_type

design_type: DesignType = Field(alias='designType')

sheet

sheet

grid

grid

columns

columns: list[Column]

rows

rows: list[Row]

SheetFormulationRef

Bases: BaseAlbertModel

A reference to a formulation in a sheet

Show JSON schema:

{
  "description": "A reference to a formulation in a sheet",
  "properties": {
    "id": {
      "description": "The Albert ID of the inventory item that is the formulation",
      "title": "Id",
      "type": "string"
    },
    "name": {
      "description": "The name of the formulation",
      "title": "Name",
      "type": "string"
    },
    "hidden": {
      "description": "Whether the formulation is hidden",
      "title": "Hidden",
      "type": "boolean"
    }
  },
  "required": [
    "id",
    "name",
    "hidden"
  ],
  "title": "SheetFormulationRef",
  "type": "object"
}

Fields:

• id (str)
• name (str)
• hidden (bool)

id

id: str

The Albert ID of the inventory item that is the formulation

name

name: str

The name of the formulation

hidden

hidden: bool

Whether the formulation is hidden

Sheet

Bases: BaseSessionResource

A Sheet in Albert

Attributes:

Name	Type	Description
id	str	The Albert ID of the sheet.
name	str	The name of the sheet.
hidden	bool	Whether the sheet is hidden.
designs	list[Design]	The designs of the sheet.
project_id	str	The Albert ID of the project the sheet is in.
grid	DataFrame | None	The grid of the sheet. Optional. Default is None. Read-only.
columns	list[Column]	The columns of the sheet. Read-only.
rows	list[Row]	The rows of the sheet. Read-only.

Methods:

Name	Description
set_session
set_sheet_fields
rename
add_formulation
add_formulation_columns
add_blank_row
add_inventory_row
update_cells
add_blank_column
delete_column
delete_row
get_column	Retrieve a Column by its colId, underlying inventory ID, or display header name.
lock_column	Lock or unlock a column in the sheet.

id

id: str = Field(alias='albertId')

name

name: str

formulations

formulations: list[SheetFormulationRef] = Field(
    default_factory=list, alias="Formulas"
)

hidden

hidden: bool

designs

designs: list[Design] = Field(alias='Designs')

project_id

project_id: str = Field(alias='projectId')

app_design

app_design

product_design

product_design

result_design

result_design

grid

grid

leftmost_pinned_column

leftmost_pinned_column

The leftmost pinned column in the sheet

columns

columns: list[Column]

The columns of a given sheet

rows

rows: list[Row]

The rows of a given sheet

set_session

set_session()

Source code in src/albert/resources/sheets.py

@model_validator(mode="after")
def set_session(self):
    if self.session is not None:
        for d in self.designs:
            d._session = self.session
    return self

set_sheet_fields

set_sheet_fields() -> Sheet

Source code in src/albert/resources/sheets.py

@model_validator(mode="after")
def set_sheet_fields(self: "Sheet") -> "Sheet":
    for _idx, d in enumerate(self.designs):  # Instead of creating a new list
        d._sheet = self  # Set the reference to the sheet
        if d.design_type == DesignType.APPS:
            self._app_design = d
        elif d.design_type == DesignType.PRODUCTS:
            self._product_design = d
        elif d.design_type == DesignType.RESULTS:
            self._result_design = d
    return self

rename

rename(*, new_name: str)

Source code in src/albert/resources/sheets.py

def rename(self, *, new_name: str):
    endpoint = f"/api/v3/worksheet/sheet/{self.id}"

    payload = [{"attribute": "name", "operation": "update", "newValue": new_name}]

    self.session.patch(endpoint, json=payload)

    self.name = new_name
    return self

add_formulation

add_formulation(
    *,
    formulation_name: str,
    components: list[Component],
    inventory_id: InventoryId | None = None,
    enforce_order: bool = False,
    clear: bool = True,
) -> Column

Source code in src/albert/resources/sheets.py

def add_formulation(
    self,
    *,
    formulation_name: str,
    components: list[Component],
    inventory_id: InventoryId | None = None,
    enforce_order: bool = False,
    clear: bool = True,
) -> Column:
    existing_formulation_names = [x.name for x in self.columns]
    if clear and formulation_name in existing_formulation_names:
        # get the existing column and clear it out to put the new formulation in
        col = self.get_column(column_name=formulation_name, inventory_id=inventory_id)
        self._clear_formulation_from_column(column=col)
    else:
        col = self.add_formulation_columns(formulation_names=[formulation_name])[0]
    col_id = col.column_id

    all_cells = []
    self.grid = None  # reset the grid for saftey

    for component in components:
        row_id = self._get_row_id_for_component(
            inventory_item=component.inventory_item,
            existing_cells=all_cells,
            enforce_order=enforce_order,
        )
        if row_id is None:
            raise AlbertException(f"no component with id {component.inventory_item.id}")

        value = str(component.amount)
        min_value = str(component.min_value) if component.min_value is not None else None
        max_value = str(component.max_value) if component.max_value is not None else None
        this_cell = Cell(
            column_id=col_id,
            row_id=row_id,
            value=value,
            calculation="",
            type=CellType.INVENTORY,
            design_id=self.product_design.id,
            name=formulation_name,
            inventory_id=col.inventory_id,
            min_value=min_value,
            max_value=max_value,
        )
        all_cells.append(this_cell)

    self.update_cells(cells=all_cells)
    return self.get_column(column_id=col_id)

add_formulation_columns

add_formulation_columns(
    *,
    formulation_names: list[str],
    starting_position: dict | None = None,
) -> list[Column]

Source code in src/albert/resources/sheets.py

def add_formulation_columns(
    self,
    *,
    formulation_names: list[str],
    starting_position: dict | None = None,
) -> list["Column"]:
    if starting_position is None:
        starting_position = {
            "reference_id": self.leftmost_pinned_column,
            "position": "rightOf",
        }
    sheet_id = self.id

    endpoint = f"/api/v3/worksheet/sheet/{sheet_id}/columns"

    # In case a user supplied a single formulation name instead of a list
    formulation_names = (
        formulation_names if isinstance(formulation_names, list) else [formulation_names]
    )

    payload = []
    for formulation_name in (
        formulation_names
    ):  # IS there a limit to the number I can add at once? Need to check this.
        # define payload for this item
        payload.append(
            {
                "type": "INV",
                "name": formulation_name,
                "referenceId": starting_position["reference_id"],  # initially defined column
                "position": starting_position["position"],
            }
        )
    response = self.session.post(endpoint, json=payload)

    self.grid = None
    new_dicts = self._reformat_formulation_addition_payload(response_json=response.json())
    return [Column(**x) for x in new_dicts]

add_blank_row

add_blank_row(
    *,
    row_name: str,
    design: DesignType | str | None = PRODUCTS,
    position: dict | None = None,
)

Source code in src/albert/resources/sheets.py

def add_blank_row(
    self,
    *,
    row_name: str,
    design: DesignType | str | None = DesignType.PRODUCTS,
    position: dict | None = None,
):
    if design == DesignType.RESULTS:
        raise AlbertException("You cannot add rows to the results design")
    if position is None:
        position = {"reference_id": "ROW1", "position": "above"}
    endpoint = f"/api/v3/worksheet/design/{self._get_design_id(design=design)}/rows"

    payload = [
        {
            "type": "BLK",
            "name": row_name,
            "referenceId": position["reference_id"],
            "position": position["position"],
        }
    ]

    response = self.session.post(endpoint, json=payload)

    self.grid = None
    row_dict = response.json()[0]
    return Row(
        rowId=row_dict["rowId"],
        type=row_dict["type"],
        session=self.session,
        design=self._get_design(design=design),
        name=row_dict["name"],
        sheet=self,
    )

add_inventory_row

add_inventory_row(
    *, inventory_id: str, position: dict | None = None
)

Source code in src/albert/resources/sheets.py

def add_inventory_row(
    self,
    *,
    inventory_id: str,
    position: dict | None = None,
):
    if position is None:
        position = {"reference_id": "ROW1", "position": "above"}
    design_id = self.product_design.id
    endpoint = f"/api/v3/worksheet/design/{design_id}/rows"

    payload = {
        "type": "INV",
        "id": ("INV" + inventory_id if not inventory_id.startswith("INV") else inventory_id),
        "referenceId": position["reference_id"],
        "position": position["position"],
    }

    response = self.session.post(endpoint, json=payload)

    self.grid = None
    row_dict = response.json()
    return Row(
        rowId=row_dict["rowId"],
        inventory_id=inventory_id,
        type=row_dict["type"],
        session=self.session,
        design=self.product_design,
        sheet=self,
        name=row_dict["name"],
        id=row_dict["id"],
        manufacturer=row_dict["manufacturer"],
    )

update_cells

update_cells(*, cells: list[Cell])

Source code in src/albert/resources/sheets.py

def update_cells(self, *, cells: list[Cell]):
    request_path_dict = {}
    updated = []
    failed = []
    # sort by design ID
    for c in cells:
        if c.design_id not in request_path_dict:
            request_path_dict[c.design_id] = [c]
        else:
            request_path_dict[c.design_id].append(c)

    for design_id, cell_list in request_path_dict.items():
        payloads = []
        for cell in cell_list:
            change_dict = self._get_cell_changes(cell=cell)
            if change_dict is not None:
                # For non-calculation cells, only one change is allowed at a time.
                is_calculation_cell = cell.calculation is not None and cell.calculation != ""
                max_items = 2 if is_calculation_cell else 1

                if len(change_dict["data"]) > max_items:
                    for item in change_dict["data"]:
                        payloads.append(
                            {
                                "Id": change_dict["Id"],
                                "data": [item],
                            }
                        )
                else:
                    payloads.append(change_dict)

        if not payloads:
            continue

        this_url = f"/api/v3/worksheet/{design_id}/values"
        for payload in payloads:
            response = self.session.patch(
                this_url,
                json=[payload],  # The API expects a list of changes
            )

            original_cell = next(
                (
                    c
                    for c in cell_list
                    if c.row_id == payload["Id"]["rowId"]
                    and c.column_id == payload["Id"]["colId"]
                ),
                None,
            )

            if response.status_code == 204:
                if original_cell and original_cell not in updated:
                    updated.append(original_cell)
            elif response.status_code == 206:
                cell_results = self._filter_cells(
                    cells=[original_cell], response_dict=response.json()
                )
                updated.extend(cell_results[0])
                failed.extend(cell_results[1])
            else:
                if original_cell and original_cell not in failed:
                    failed.append(original_cell)

    # reset the in-memory grid after updates
    self.grid = None
    return (updated, failed)

add_blank_column

add_blank_column(*, name: str, position: dict = None)

Source code in src/albert/resources/sheets.py

def add_blank_column(self, *, name: str, position: dict = None):
    if position is None:
        position = {"reference_id": self.leftmost_pinned_column, "position": "rightOf"}
    endpoint = f"/api/v3/worksheet/sheet/{self.id}/columns"
    payload = [
        {
            "type": "BLK",
            "name": name,
            "referenceId": position["reference_id"],
            "position": position["position"],
        }
    ]

    response = self.session.post(endpoint, json=payload)

    data = response.json()
    data[0]["sheet"] = self
    data[0]["session"] = self.session
    self.grid = None  # reset the known grid. We could probably make this nicer later.
    return Column(**data[0])

delete_column

delete_column(*, column_id: str) -> None

Source code in src/albert/resources/sheets.py

def delete_column(self, *, column_id: str) -> None:
    endpoint = f"/api/v3/worksheet/sheet/{self.id}/columns"
    payload = [{"colId": column_id}]
    self.session.delete(endpoint, json=payload)

    if self._grid is not None:  # if I have a grid loaded into memory, adjust it.
        self.grid = None

delete_row

delete_row(*, row_id: str, design_id: str) -> None

Source code in src/albert/resources/sheets.py

def delete_row(self, *, row_id: str, design_id: str) -> None:
    endpoint = f"/api/v3/worksheet/design/{design_id}/rows"
    payload = [{"rowId": row_id}]
    self.session.delete(endpoint, json=payload)

    if self._grid is not None:  # if I have a grid loaded into memory, adjust it.
        self.grid = None

get_column

get_column(
    *,
    column_id: str | None = None,
    inventory_id: InventoryId | None = None,
    column_name: str | None = None,
) -> Column

Retrieve a Column by its colId, underlying inventory ID, or display header name.

Parameters:

Name	Type	Description	Default
column_id	str | None	The sheet column ID to match (e.g. "COL5").	None
inventory_id	str | None	The internal inventory identifier to match (e.g. "INVP015-001").	None
column_name	str | None	The human-readable header name of the column (e.g. "p1").	None

Returns:

Type	Description
Column	The matching Column object.

Raises:

Type	Description
AlbertException	If no matching column is found or if multiple matches exist.

Source code in src/albert/resources/sheets.py

@validate_call
def get_column(
    self,
    *,
    column_id: str | None = None,
    inventory_id: InventoryId | None = None,
    column_name: str | None = None,
) -> Column:
    """
    Retrieve a Column by its colId, underlying inventory ID, or display header name.

    Parameters
    ----------
    column_id : str | None
        The sheet column ID to match (e.g. "COL5").
    inventory_id : str | None
        The internal inventory identifier to match (e.g. "INVP015-001").
    column_name : str | None
        The human-readable header name of the column (e.g. "p1").

    Returns
    -------
    Column
        The matching Column object.

    Raises
    ------
    AlbertException
        If no matching column is found or if multiple matches exist.
    """

    if not (column_id or inventory_id or column_name):
        raise AlbertException(
            "Must provide at least one of column_id, inventory_id or column_name"
        )
    # Gather candidates matching your filters
    candidates: list[Column] = []
    for col in self.columns:
        if column_id and col.column_id != column_id:
            continue
        if inventory_id and col.inventory_id != inventory_id:
            continue
        if column_name and col.name != column_name:
            continue
        candidates.append(col)

    if not candidates:
        raise AlbertException(
            f"No column found matching id={column_id}, "
            f"inventory_id={inventory_id}, column_name={column_name}"
        )
    if len(candidates) > 1:
        raise AlbertException("Ambiguous column match; please be more specific.")

    return candidates[0]

lock_column

lock_column(
    *,
    column_id: str | None = None,
    inventory_id: InventoryId | None = None,
    column_name: str | None = None,
    locked: bool = True,
) -> Column

Lock or unlock a column in the sheet.

The column can be specified by its sheet column ID (e.g. "COL5"), by the underlying inventory identifier of a formulation/product, or by the displayed header name. By default the column will be locked; pass locked=False to unlock it.

Parameters:

Name	Type	Description	Default
column_id	str | None	The sheet column ID to match.	None
inventory_id	str | None	The inventory identifier of the formulation or product to match.	None
column_name	str | None	The displayed header name of the column.	None
locked	bool	Whether to lock (True) or unlock (False) the column. Defaults to True.	True

Returns:

Type	Description
Column	The column that was updated.

Source code in src/albert/resources/sheets.py

def lock_column(
    self,
    *,
    column_id: str | None = None,
    inventory_id: InventoryId | None = None,
    column_name: str | None = None,
    locked: bool = True,
) -> Column:
    """Lock or unlock a column in the sheet.

    The column can be specified by its sheet column ID (e.g. ``"COL5"``),
    by the underlying inventory identifier of a formulation/product, or by
    the displayed header name. By default the column will be locked; pass
    ``locked=False`` to unlock it.

    Parameters
    ----------
    column_id : str | None
        The sheet column ID to match.
    inventory_id : str | None
        The inventory identifier of the formulation or product to match.
    column_name : str | None
        The displayed header name of the column.
    locked : bool
        Whether to lock (``True``) or unlock (``False``) the column. Defaults to
        ``True``.

    Returns
    -------
    Column
        The column that was updated.
    """

    column = self.get_column(
        column_id=column_id, inventory_id=inventory_id, column_name=column_name
    )

    payload = {
        "data": [
            {
                "operation": "update",
                "attribute": "locked",
                "colIds": [column.column_id],
                "newValue": locked,
            }
        ]
    }

    self.session.patch(
        url=f"/api/v3/worksheet/sheet/{self.id}/columns",
        json=payload,
    )

    self.grid = None

    return self.get_column(column_id=column.column_id)

Column

Bases: BaseSessionResource

A column in a Sheet

Attributes:

Name	Type	Description
column_id	str	The column ID of the column.
name	str | None	The name of the column. Optional. Default is None.
type	CellType	The type of the column. Allowed values are INV, APP, BLK, Formula, TAG, PRC, PDC, BAT, TOT, TAS, DEF, LKP, FOR, and EXTINV.
sheet	Sheet	The sheet the column is in.
cells	list[Cell]	The cells in the column. Read-only.
df_name	str	The name of the column in the DataFrame. Read-only

Methods:

Name	Description
rename
recolor_cells

column_id

column_id: str = Field(alias='colId')

name

name: str | None = Field(default=None)

type

type: CellType

sheet

sheet: Sheet

inventory_id

inventory_id: str | None = Field(default=None, exclude=True)

locked

locked: bool = Field(default=False)

hidden

hidden: bool | None = Field(default=None)

pinned

pinned: str | None = Field(default=None)

column_width

column_width: str | None = Field(default=None)

df_name

df_name: str

cells

cells: list[Cell]

rename

rename(new_name)

Source code in src/albert/resources/sheets.py

def rename(self, new_name):
    payload = {
        "data": [
            {
                "operation": "update",
                "attribute": "name",
                "colId": self.column_id,
                "oldValue": self.name,
                "newValue": new_name,
            }
        ]
    }

    self.session.patch(
        url=f"/api/v3/worksheet/sheet/{self.sheet.id}/columns",
        json=payload,
    )

    if self.sheet._grid is not None:  # if I have a grid loaded into memory, adjust it.
        self.sheet.grid = None
        # self.sheet._grid.rename(axis=1, mapper={self.name:new_name})
    self.name = new_name
    return self

recolor_cells

recolor_cells(color: CellColor)

Source code in src/albert/resources/sheets.py

def recolor_cells(self, color: CellColor):
    new_cells = []
    for c in self.cells:
        cell_copy = c.model_copy(update={"format": {"bgColor": color.value}})
        new_cells.append(cell_copy)
    return self.sheet.update_cells(cells=new_cells)

Row

Bases: BaseSessionResource

A row in a Sheet

Attributes:

Name	Type	Description
row_id	str	The row ID of the row.
type	CellType	The type of the row. Allowed values are INV, APP, BLK, Formula, TAG, PRC, PDC, BAT, TOT, TAS, DEF, LKP, FOR, and EXTINV.
design	Design	The design the row is in.
sheet	Sheet	The sheet the row is in.
name	str | None	The name of the row. Optional. Default is None.
inventory_id	str | None	The inventory ID of the row. Optional. Default is None.
manufacturer	str | None	The manufacturer of the row. Optional. Default is None.
row_unique_id	str	The unique ID of the row. Read-only.
cells	list[Cell]	The cells in the row. Read-only.

Methods:

Name	Description
recolor_cells

row_id

row_id: str = Field(alias='rowId')

type

type: CellType

design

design: Design

sheet

sheet: Sheet

name

name: str | None = Field(default=None)

inventory_id

inventory_id: str | None = Field(default=None, alias='id')

manufacturer

manufacturer: str | None = Field(default=None)

row_unique_id

row_unique_id

cells

cells: list[Cell]

recolor_cells

recolor_cells(color: CellColor)

Source code in src/albert/resources/sheets.py

def recolor_cells(self, color: CellColor):
    new_cells = []
    for c in self.cells:
        cell_copy = c.model_copy(update={"format": {"bgColor": color.value}})
        cell_copy.format = {"bgColor": color.value}
        new_cells.append(cell_copy)
    return self.sheet.update_cells(cells=new_cells)