Sheets

albert.resources.sheets

AlbertException

AlbertException(message: str)

Bases: Exception

Source code in src/albert/exceptions.py

def __init__(self, message: str):
    super().__init__(message)
    self.message = message

message

message = message

BaseResource

Bases: BaseAlbertModel

The base resource for all Albert resources.

Attributes:

Name	Type	Description
status	Status | None	The status of the resource, optional.
created	AuditFields | None	Audit fields for the creation of the resource, optional.
updated	AuditFields | None	Audit fields for the update of the resource, optional.

status

status: Status | None = Field(default=None)

created

created: AuditFields | None = Field(
    default=None, alias="Created", exclude=True, frozen=True
)

updated

updated: AuditFields | None = Field(
    default=None, alias="Updated", exclude=True, frozen=True
)

to_entity_link

to_entity_link() -> EntityLink

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

def to_entity_link(self) -> EntityLink:
    if id := getattr(self, "id", None):
        return EntityLink(id=id)
    raise AlbertException(
        "A non-null 'id' is required to create an entity link. "
        "Ensure the linked object is registered and has a valid 'id'."
    )

BaseSessionResource

BaseSessionResource(**data)

Bases: BaseResource

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

def __init__(self, **data):
    super().__init__(**data)
    self._session = data.get("session")

session

session: AlbertSession | None

InventoryItem

Bases: BaseTaggedResource

An InventoryItem is a Pydantic model representing an item in the inventory. Can be a raw material, consumable, equipment, or formula. Note: Formulas should be registered via the Worksheet collection / Sheet resource.

Returns:

Type	Description
InventoryItem	An InventoryItem that can be used to represent an item in the inventory. Can be a raw material, consumable, equipment, or formula.

Attributes:

Name	Type	Description
name	str	The name of the InventoryItem.
id	str | None	The Albert ID of the InventoryItem. Set when the InventoryItem is retrieved from Albert.
description	str | None	The description of the InventoryItem.
category	InventoryCategory	The category of the InventoryItem. Allowed values are RawMaterials, Consumables, Equipment, and Formulas.
unit_category	InventoryUnitCategory	The unit category of the InventoryItem. Can be mass, volume, length, pressure, or units. By default, mass is used for RawMaterials and Formulas, and units is used for Equipment and Consumables.
security_class	SecurityClass | None	The security class of the InventoryItem. Optional. Can be confidential, shared, or restricted.
company	Company | str | None	The company associated with the InventoryItem. Can be a Company object or a string. If a String is provided, a Company object with the name of the provided string will be first-or-created.
minimum	list[InventoryMinimum] | None	The minimum amount of the InventoryItem that must be kept in stock at a given Location. Optional.
alias	str | None	An alias for the InventoryItem. Optional.
cas	list[CasAmount] | None	The CAS numbers associated with the InventoryItem. This is how a compositional breakdown can be provided. Optional.
metadata	dict[str, str | list[EntityLink] | EntityLink] | None	Metadata associated with the InventoryItem. Optional. Allowed metadata fields can be found in the CustomFields documentation.
project_id	str | None	The project ID associated with the InventoryItem. Read Only. Required for Formulas.
formula_id	str | None	The formula ID associated with the InventoryItem. Read Only.
tags	list[str | Tag] | None	The tags associated with the InventoryItem. Optional. If a string is provided, a Tag object with the name of the provided string will be first-or-created.

name

name: str | None = None

id

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

description

description: str | None = None

category

category: InventoryCategory

unit_category

unit_category: InventoryUnitCategory | None = Field(
    default=None, alias="unitCategory"
)

security_class

security_class: SecurityClass | None = Field(
    default=None, alias="class"
)

company

company: SerializeAsEntityLink[Company] | None = Field(
    default=None, alias="Company"
)

minimum

minimum: list[InventoryMinimum] | None = Field(default=None)

alias

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

cas

cas: list[CasAmount] | None = Field(
    default=None, alias="Cas"
)

metadata

metadata: dict[str, MetadataItem] | None = Field(
    alias="Metadata", default=None
)

project_id

project_id: str | None = Field(
    default=None, alias="parentId"
)

acls

acls: list[ACL] = Field(default_factory=list, alias='ACL')

task_config

task_config: list[dict] | None = Field(
    default=None,
    alias="TaskConfig",
    exclude=True,
    frozen=True,
)

formula_id

formula_id: str | None = Field(
    default=None,
    alias="formulaId",
    exclude=True,
    frozen=True,
)

symbols

symbols: list[dict] | None = Field(
    default=None, alias="Symbols", exclude=True, frozen=True
)

un_number

un_number: str | None = Field(
    default=None,
    alias="unNumber",
    exclude=True,
    frozen=True,
)

recent_atachment_id

recent_atachment_id: str | None = Field(
    default=None,
    alias="recentAttachmentId",
    exclude=True,
    frozen=True,
)

validate_company_string

validate_company_string(value: Any) -> Any

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

@field_validator("company", mode="before")
@classmethod
def validate_company_string(cls, value: Any) -> Any:
    if isinstance(value, str):
        value = Company(name=value)
    return value

validate_un_number

validate_un_number(value: Any) -> Any

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

@field_validator("un_number", mode="before")
@classmethod
def validate_un_number(cls, value: Any) -> Any:
    if value == "N/A":
        value = None
    return value

set_unit_category

set_unit_category() -> InventoryItem

Set unit category from category if not defined.

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

@model_validator(mode="after")
def set_unit_category(self) -> "InventoryItem":
    """Set unit category from category if not defined."""
    if self.unit_category is None:
        if self.category in [InventoryCategory.RAW_MATERIALS, InventoryCategory.FORMULAS]:
            object.__setattr__(self, "unit_category", InventoryUnitCategory.MASS)
        elif self.category in [InventoryCategory.EQUIPMENT, InventoryCategory.CONSUMABLES]:
            object.__setattr__(self, "unit_category", InventoryUnitCategory.UNITS)
    return self

validate_formula_fields

validate_formula_fields() -> InventoryItem

Ensure required fields are present for formulas.

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

@model_validator(mode="after")
def validate_formula_fields(self) -> "InventoryItem":
    """Ensure required fields are present for formulas."""
    if self.category == InventoryCategory.FORMULAS and not self.project_id and not self.id:
        # Some legacy on platform formulas don't have a project_id so check if its already on platform
        raise ValueError("A project_id must be supplied for all formulas.")
    return self

CellColor

Bases: str, Enum

The allowed colors for a cell

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

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

APPS

APPS = 'apps'

PRODUCTS

PRODUCTS = 'products'

RESULTS

RESULTS = 'results'

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.
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.
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')

value

value: str | dict = ''

type

type: CellType

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

cell

cell

DesignState

Bases: BaseResource

The state of a Design

collapsed

collapsed: bool | None = False

Design

Design(**data)

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.

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

def __init__(self, **data):
    super().__init__(**data)
    self._session = data.get("session")

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

id

id: str = Field(
    description="The Albert ID of the inventory item that is the formulation"
)

name

name: str = Field(description="The name of the formulation")

hidden

hidden: bool = Field(
    description="Whether the formulation is hidden"
)

Sheet

Sheet(**data)

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.

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

def __init__(self, **data):
    super().__init__(**data)
    self._session = data.get("session")

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

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],
    enforce_order: bool = False,
) -> Column

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

def add_formulation(
    self, *, formulation_name: str, components: list[Component], enforce_order: bool = False
) -> "Column":
    existing_formulation_names = [x.name for x in self.columns]
    if formulation_name not in existing_formulation_names:
        col = self.add_formulation_columns(formulation_names=[formulation_name])[0]
    else:
        # get the existing column and clear it out to put the new formulation in
        col = self.get_column(column_name=formulation_name)
        self._clear_formulation_from_column(column=col)
    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}")
        this_cell = Cell(
            column_id=col_id,
            row_id=row_id,
            value=str(component.amount),
            calculation="",
            type=CellType.INVENTORY,
            design_id=self.product_design.id,
            name=formulation_name,
            inventory_id=col.inventory_id,
        )
        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():
        payload = []
        for cell in cell_list:
            change_dict = self._get_cell_changes(cell=cell)
            if change_dict is not None:
                payload.append(change_dict)

        if payload == []:
            continue

        this_url = f"/api/v3/worksheet/{design_id}/values"
        response = self.session.patch(
            this_url,
            json=payload,
        )

        if response.status_code == 204:
            # They all updated
            updated.extend(cell_list)
        elif response.status_code == 206:
            # Some updated and some did not.
            cell_results = self._filter_cells(cells=cell_list, response_dict=response.json())
            updated.extend(cell_results[0])
            failed.extend(cell_results[1])
    # 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: None | str = None,
    column_name: str | None = None,
)

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

def get_column(self, *, column_id: None | str = None, column_name: str | None = None):
    if column_id is None and column_name is None:
        raise AlbertException("Either a column name or id must be provided")
    else:
        matching_series = self._find_column(column_id=column_id, column_name=column_name)
        first_item = matching_series.iloc[0]
        inv_id = first_item.inventory_id
        if inv_id is not None and not inv_id.startswith("INV"):
            inv_id = "INV" + inv_id
        return Column(
            name=first_item.name,
            colId=first_item.column_id,
            type=first_item.type,
            sheet=self,
            session=self.session,
            inventory_id=first_item.inventory_id,
        )

Column

Column(**data)

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

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

def __init__(self, **data):
    super().__init__(**data)
    self._session = data.get("session")

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)

df_name

df_name

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

Row(**data)

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.

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

def __init__(self, **data):
    super().__init__(**data)
    self._session = data.get("session")

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)