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.

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:

   python scripts/build_vendor_table.py "<schedule.xlsx>" 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="<M-D-YY> 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<last_data_row>, 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)

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.

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.