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

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

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

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)

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

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)