# Vendor Equipment Consolidation > Builds the Consolidated Vendor Equipment table — one row per equipment Tag ID — from the weekly LA LNG ISBL planning schedule's vendor detail tabs, following Quiana Cipriani's documented consolidation process. Use when the user asks to "build the vendor equipment table", "rebuild Quiana's table", "run the consolidation prompt", "consol vendor equip table", "line-item equipment table", or wants tags with weights and ETAs listed individually. Do NOT use for shipment-level arrival analysis, MOF/port timelines, or "the consolidated view" (use cargo-arrival-schedule-analysis), nor for unrelated spreadsheets (use the xlsx skill). - Skill: `dgusoff/vendor-equipment-consolidation` (Agent Skill, multi-file: 5 files) - Install (CLI): `npx skillmds@latest add dgusoff/vendor-equipment-consolidation` - Raw SKILL.md: https://api.skillmd.com/api/skills/dgusoff/vendor-equipment-consolidation/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - Author: dgusoff (https://skillmd.com/u/dgusoff) - Updated: 2026-09-22 - Page: https://skillmd.com/skills/dgusoff/vendor-equipment-consolidation --- # Consolidated Vendor Equipment Table Rebuilds the vendor-equipment line-item table from the newest weekly LA LNG planning schedule. One row per Tag ID, drawn only from the vendor detail tabs. **Original process author: Quiana Cipriani.** The column set, filters, sort order and highlighting below are hers and should not be changed without her agreement. What this skill adds is defence against source drift, plus the Run Notes sheet. ## When NOT to Use - Shipment-level arrivals, MOF/port timelines, week-over-week diffs → `cargo-arrival-schedule-analysis` - General spreadsheet work → `xlsx` - Any other Teams channel or document library ## Relationship to `cargo-arrival-schedule-analysis` Same source workbook, opposite half of it. Keep both; they answer different questions. | | This skill | cargo-arrival-schedule-analysis | |---|---|---| | Grain | one row per **Tag ID** (~845) | one row per **shipment** (~78) | | Reads | the 13 **vendor detail** tabs | the **movement** tabs (Port/MOF/Road/CONSOLIDATED) | | Date shown | `Updated ETA Jobsite` | Port / MOF / Road arrival milestones | | Answers | what equipment, how heavy, when due | what lands where, when, and what conflicts | ## Source - Team **LALNG ISBL Critical Cargo Arrival Schedule** → channel **Logistics Barge Schedule** - Use the **highest `SF##` sequence number**, e.g. `SF29 31 Aug Planning Schedule.xlsx`. When the newest-by-timestamp disagrees with the highest SF number, prefer the SF number and say which you used. ## Workflow 1. **Locate.** `m365_teams-ListTeams` → `m365_teams-ListChannels` → `graph-QueryGraph` on `/teams/{teamId}/channels/{channelId}/filesFolder` for the drive and folder id, then `sharepoint_onedrive-GetDriveChildren` (`top` ≥ 50). 2. **Download** with `sharepoint_onedrive-ReadFileContent`. 3. **Build the data.** Never hand-read cells: ```bash python scripts/build_vendor_table.py "" working/vendor_table.csv ``` It prints a JSON run report — **read it before building the workbook.** It carries `tabs` (every tab and how it was classified), `tag_coercion_risk.cells` (the cells you must repair and verify), `no_eta_rows` / `no_weight_rows` as pre-collapsed contiguous ranges, and `last_data_row`. It **refuses** a workbook with no vendor tabs rather than emitting an empty table. 4. **Create** on the live surface: `CreateArtifact(surface="live", path=" Consol. Vendor Equip Table.xlsx")` — the date is today's, matching the process convention. 5. **Build the `Consolidated` sheet** (see below). 6. **Add the `Run Notes` sheet** (see below). 7. **Verify**, then report the row count, the repairs, and the source gaps. ## The `Consolidated` sheet Columns, in this exact order — this is the client's spec: `Vendor | Tag ID | Material Description | Total Weight (kg) | Total Weight (lbs) | Updated ETA Jobsite | HH/HL` Title in row 1, header row 2, data from row 3. - `insert_tabular_data` with `file=` at `A2`. The CSV leaves **`Total Weight (lbs)` empty** — it is filled by formula in the next step. - **`add_table` over exactly `A2:G`**, `styleName: "TableStyleLight9"`, `showRowStripes: true`. Range the table to the *real last data row*; a range extending past the data is rejected and takes the whole batch with it. Banding comes from the table style — **never emit per-row fills for striping** (845 rows of that is ~78 KB of patches for something the style gives free). - **The lbs column is ONE patch.** With the table in place, set the first data cell only: `set_cells row 3, column 5, {"type":"formula","formula":"=IF(D3=\"\",\"\",D3*2.20462262)"}` The table propagates it down the whole column as a calculated column — verified filling all 845 rows. Do NOT write per-cell formulas (828 patches, ~71 KB) and do NOT substitute computed values; the process specifies a live formula. - Number formats: `#,##0` on `D:E`, `dd mmm yy` on `F`. Freeze at `A3`. Column widths sized to content. - **Highlighting** (hers): amber fill `FFF4CE` / ink `8A6100` across the whole row where the ETA is blank; light-red fill `FFD9D4` / ink `A32B1C` on the `D` cell where the weight is blank. Use the **collapsed ranges** from the run report — in SF29 that is 1 amber range and 13 red ranges, ~14 patches, not 39 individual rows. - Sort is applied by the script: ETA ascending, undated rows last. ## The `Run Notes` sheet Second sheet. Live `COUNTA`/`COUNTBLANK` formulas over the Consolidated sheet — never hardcode a count. Carries: source file and location; a warning if the in-sheet header date disagrees with the filename; every tab with its classification and row count; the repairs applied; the source gaps; and the method paragraph. *This sheet is an addition to the original process.* It exists because every failure this skill defends against was silent. Mention it when handing the file over. ## Hazard 1 — the WRITE path corrupts identifier tags **This is not a source defect and not a defect in the original process.** Tags like `1E-1210` are stored as **text** in the planning schedule and are read correctly. The corruption is introduced when the table is written: - `insert_tabular_data` infers cell types. `1E-1210` matches scientific notation, so it is stored as 1×10⁻¹²¹⁰ and displays as **`0`**. - It is **silent** — no error, and the row keeps its correct vendor, description and weight, so it still looks plausible. 14 cells in SF29. - **Pre-formatting the column as text does NOT prevent it** — the op infers regardless of cell format. Tested directly; do not spend another probe on it. - Quiana Cipriani's own 7-18-26 table has **no** such corruption — all 12 distinct at-risk tags are intact strings there. Do not report her output as defective on this basis. **Defence:** take `tag_coercion_risk.cells` from the run report, rewrite those cells as typed text after the insert, then **read exactly those cells back**. This is mandatory, not optional — a silent corruption that produces plausible data cannot be caught by eye. **This hazard applies to any skill writing identifier columns through `insert_tabular_data`, not just this one.** Multi-token cells (`1E-5411A-F / 5412A-F`) survive by accident because the whole cell is not numeric-looking; a single-tag cell does not. ## Hazard 2 — source drift (three occurrences to date) | Hazard | What happens | Defence | |---|---|---| | **Movement tab renamed** | `ROAD 06 APR` → `via ROAD` escaped a name-based ignore list and got scraped as vendor data. | Tabs are classified by **shape**, never by name. | | **New tab appears** | `INDUSTEEL` arrived in SF26 in neither the scope nor the ignore list, and is empty — no header row to find. | Shape classification; empty tabs are reported, not skipped silently. | | **Header text drift** | Domestic Barging headers carry embedded newlines and varying case (`Total Weight \n(kg)`, `UPDATED ETA JOBSITE`). | Header keys are whitespace-collapsed and casefolded before matching. | These three are genuine changes in the source workbook and are the ones worth raising with whoever maintains it. ## When it goes wrong If the source workbook cannot be opened, say so plainly and ask for it to be re-shared — never fabricate, and never substitute sample, representative or placeholder rows for data you could not read. If a column is missing from a vendor tab, the script leaves those values blank and names the tab in `issues`; report that gap rather than filling it. If the script returns no rows, the layout has changed — do not report an empty table as an empty schedule. | Symptom | Cause | Do this | |---|---|---| | Script prints `"refused": true` | No vendor line-item tabs — almost certainly a legacy layout predating the current format (files before ~Feb 2026) | Report the refusal and list the tabs it found. Do **not** force it; an empty table read as an empty schedule is the worst outcome here. | | `add_table` fails with `InvalidArgument` | The range runs past the last data row, or overlaps an existing table | Re-range to exactly `A2:G` from the run report. If a table already exists on the sheet, reuse it rather than adding a second. | | A batch fails partway | The live surface does **not** roll back — earlier patches in that batch may have applied | `GetArtifactModel` on a narrow range first, see what actually landed, then resend only what did not. Never blind-retry an `add_sheet`. | | lbs column stays empty below row 3 | The table was not created before the formula was set, so there is no calculated column to propagate | Create the table first, then set `E3`. Re-check `E4` and the last data row. | | A tag reads `0` or a tiny number | The coercion repair did not take, or new scientific-notation tags appeared | Re-run the script, take the fresh `tag_coercion_risk.cells`, repair as typed text, read those exact cells back. | | Row count differs sharply from last week | Either the source genuinely changed, or a tab was reclassified | Compare the `tabs` block against the previous run's Run Notes sheet before assuming a data change. | | Workbook will not open at all | Corrupt or unsupported file | Say so plainly and ask for it to be re-shared. Never substitute sample, representative or illustrative rows. | If the run report and the finished sheet disagree on any count, trust neither and re-read the sheet — a mismatch means a patch silently failed. ## Guardrails - **Deliver to the invoking user's own OneDrive only.** Never write to another person's OneDrive and never post to a Teams channel from this skill — build the file and tell the user that any further distribution (saving elsewhere, sharing a link) is their own manual step to take. - Reproduce values exactly as the source states them. A blank ETA, weight or HH/HL stays blank — never inferred, interpolated or estimated. ~2/3 of rows have no HH/HL; that is a source gap to report, not a hole to fill. - Never present a rebuilt table as verified until the coercion-risk cells have been read back. That failure mode produces plausible wrong data, not an error. - Do not change the column set, sort order or highlight rules — they are the client's spec. Propose changes to the user; do not apply them unilaterally. - Never modify the source planning schedule — this skill only reads it. - Always let the user review the finished workbook before it is shared; never auto-send, auto-post or auto-share the output, and always confirm with the user before writing anywhere outside their own space. - Never overwrite a previous week's table. Each build is dated; if today's filename already exists, ask the user whether to replace it or version it. - Always state the row count, the repairs applied and the source gaps when handing the file over — do not let a clean-looking sheet imply a clean source. - Never report a count from memory; read it from the run report or a live formula. - Report shipment and equipment status only; never evaluate any individual's performance. ## Efficiency notes The costly parts of this build are all avoidable: - Row data never enters the context — the script writes a CSV and `insert_tabular_data` reads it from disk with `file=`. - Banding from the table style, exceptions only for highlights: **~14 formatting patches instead of 845**. - Calculated column for lbs: **1 patch instead of 828**. - **Verify with windowed reads**, never a whole-sheet `GetArtifactModel` — on this sheet that returns 90-170 KB per call. Read the repaired tag rows plus the first and last data row; the script already knows what it wrote.