Plan: Cotton PO Rate Population — App Script Fix

Objective

Rewrite the broken updateBaseRates() function in Rates.gs so it correctly reads the vendor/mill from the Collections tab, parses each SKU's fabric type, looks up the correct rate from the Simple Pricing tab, and writes the Base Rate + D/R Rate back to each collection tab.

Context

Google Sheet: https://docs.google.com/spreadsheets/d/16Gx-N9HK35MSLZfz5DAGJ6FhHN6u2VLdruwmiXIL4lE/

Key sheet tabs:

• Collections — master list: col C = collection name, col D = PO#, col E = Vendor (full mill name)
• Simple Pricing — rate lookup: col A = Vendor, col B = Class, col C = Fabric Type, col F = SKU Prefix, col G = SKU Suffix, col H = Rate, col I = Surcharge
• [Collection tabs] — individual tabs named like FOXGLOVE-030926, WHISPINES-030926, etc. Row 4 col B = PO#, rows 12+ = SKU data in col A

Existing Apps Script project: RB Cotton PO File to rewrite: Rates.gs — function updateBaseRates() Menu trigger: PO Tools → "Populate All Rates (Base & D/R)"

Mills in Simple Pricing:

• Y&K Together Co. Ltd → rates: 1.80/yd base, 2.1 panel, 1.8 panel-PD, 2.5 flannel, 1.75 basic
• TaiHan Textile Co., Ltd. → rates: 1.95/yd base, 1.95 panel, 1.95 panel-PD, 2.49 flannel, 1.8 basic
• Daechun Company Ltd. → rates: 1.90/yd base, 2.1 panel, 1.9 panel-PD, 2.5 flannel, 1.83 basic

SKU type detection logic (3 checks):

1. Does SKU suffix contain -PANEL? → Panel rate (SKU Prefix = P)
2. Does SKU contain PD as prefix? → Digital Panel rate (SKU Prefix = PD)
3. Does the collection tab's fabric type indicate Flannel? → Flannel rate
4. Else → base Cotton Designer rate

Important: Mission Control writes the .gs code as files. Cole or Sabrina pastes/deploys into the Apps Script editor. Do NOT attempt browser automation to deploy.

Phase 1: Proof of Concept — Vendor Lookup

DISPATCH tasks:

1. Write a standalone test function testVendorLookup() in a new file TestRates.gs (Cole pastes this in to test): ```javascript // Test: given a PO# (e.g. "FOXGLOVE-030926"), can we find the vendor? function testVendorLookup() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const collectionsSheet = ss.getSheetByName('Collections'); const poNumber = 'FOXGLOVE-030926'; // test case

   // Find PO# in column D, return vendor from column E const data = collectionsSheet.getDataRange().getValues(); for (let i = 1; i < data.length; i++) { if (String(data[i][3]).trim() === poNumber) { Logger.log('Found vendor: ' + data[i][4]); return data[i][4]; } } Logger.log('PO# not found: ' + poNumber); } ```

2. Write a second test function testSimplePricingLookup() that: - Reads the full Simple Pricing tab into memory - Given vendor = "Daechun Company Ltd." + fabric type = "Cotton Designer" → returns rate 1.9 - Given vendor = "Y&K Together Co. Ltd" + fabric type = "Cotton Designer Panel" + prefix "P" → returns rate 2.1 + surcharge 0.2

3. Write both functions to a local file: ~/ai-projects/Shiplap/shopify-theme/po-scripts/TestRates.gs
4. Write clear paste-and-test instructions for Cole: ~/ai-projects/Shiplap/shopify-theme/po-scripts/PHASE1-TEST-INSTRUCTIONS.md

Acceptance Criteria:

• Both test functions written and syntactically correct (verify with logic review)
• Instructions clear enough that Cole can paste + run in <5 minutes
• Expected log output documented so Cole knows if it passed or failed

[STOP — Ready for UAT: Phase 1] Cole/Sabrina: paste TestRates.gs into Apps Script editor, run both test functions, check Execution log. Did testVendorLookup() return "Daechun Company Ltd." for FOXGLOVE? Did testSimplePricingLookup() return 1.9?

Phase 2: SKU Type Detection Logic

DISPATCH tasks:

1. Write classifySku(sku, vendor) helper function that returns one of: - 'panel' — SKU contains -PANEL or prefix matches P in Simple Pricing - 'panel_digital' — SKU prefix matches PD in Simple Pricing - 'flannel' — SKU contains FLNL, FLN, or FLANNEL (case insensitive), OR bolt length = 15YD for this vendor - 'basic' — SKU class = Cotton Basic (check Simple Pricing col B) - 'designer' — default Cotton Designer
2. Write getRateForSku(vendor, skuType) that returns { baseRate, surcharge, drRate }: - Reads Simple Pricing tab once (cache in memory for the run) - Matches on Vendor + skuType - D/R rate = base rate × 2.5 (standard D&R markup — confirm this multiplier with Cole in UAT)
3. Write test function testSkuClassification() with these test cases: - C18080-BLUE (Daechun) → designer, rate 1.90, D/R ~4.75 - C18087-PANEL (Y&K) → panel, rate 2.10 + 0.20 surcharge - PD-C18082-BLUE (TaiHan) → panel_digital, rate 1.95 + 0.17 - Any SKU with FLANNEL (Y&K) → flannel, rate 2.50 + 0.15
4. Write all to ~/ai-projects/Shiplap/shopify-theme/po-scripts/TestSkuClassification.gs
5. Write test instructions: PHASE2-TEST-INSTRUCTIONS.md

Acceptance Criteria:

• All 4 test cases classified correctly
• getRateForSku returns correct rate + surcharge for each type × vendor combination
• D/R multiplier documented (needs Cole to confirm the correct value)

[STOP — Ready for UAT: Phase 2] Cole/Sabrina: run testSkuClassification(). Did all 4 cases return correct types and rates? Also confirm: what is the correct D/R rate multiplier (is it 2.5x base, or a different formula)?

Phase 3: Full updateBaseRates() Rewrite

DISPATCH tasks:

1. Rewrite Rates.gs completely with the new logic: ``` updateBaseRates():

   • Get all collection tab names (filter: tabs with date suffix, exclude: Collections, Simple Pricing, Data, Template, Dropship, Import, etc.)

   • For each collection tab: a. Read PO# from cell B4 b. Look up vendor from Collections tab col E (match on col D) c. If vendor not found: skip tab, log warning d. For each data row (row 12 to last non-empty row):

     • Read SKU from col A
     • Classify SKU type using classifySku()
     • Get rate using getRateForSku()
     • Write Base Rate to col [TBD — confirm which column]
     • Write D/R Rate to col [TBD — confirm which column] e. Log: "FOXGLOVE-030926: 24 SKUs updated, vendor Daechun" ```

2. Important: Identify the correct output columns for Base Rate and D/R Rate — read the existing Data tab and check if there are existing columns for these rates on the collection tabs. If unclear, write a note in the code and flag for Cole in UAT instructions.

3. Add error handling: unknown vendor → skip + log, no PO# → skip + log, Simple Pricing row not found → use 0 and flag
4. Write the complete Rates.gs to: ~/ai-projects/Shiplap/shopify-theme/po-scripts/Rates.gs
5. Write deployment instructions: PHASE3-DEPLOY-INSTRUCTIONS.md - Copy content of new Rates.gs - Open Apps Script editor → Rates.gs → select all → paste - Save (Ctrl+S) - Run "Populate All Rates" from PO Tools menu

Acceptance Criteria:

• Complete, syntactically valid Rates.gs written
• Handles all 3 mills + all SKU types
• Error handling prevents silent failures
• Deployment instructions are copy-paste simple

[STOP — Ready for UAT: Phase 3] Cole/Sabrina: deploy new Rates.gs following PHASE3-DEPLOY-INSTRUCTIONS.md. Run "Populate All Rates" from PO Tools menu. Check Execution log for errors. Then open FOXGLOVE tab — are the rates populated? Do they match Simple Pricing (Daechun, 1.90/yd)?

Phase 4: Multi-Collection Test + Edge Case Fixes

DISPATCH tasks:

1. Based on Phase 3 UAT feedback: fix any reported issues
2. Write a validation function validateRates() that: - Runs after updateBaseRates() - Checks for: any rows with $0 rate (unexpected), any skipped tabs, any vendor mismatches - Prints a clean summary: "12 tabs updated, 0 errors, 0 warnings"
3. Add validation as automatic step at end of updateBaseRates()
4. Test against all collection tabs visible in the spreadsheet
5. Update ~/ai-projects/Shiplap/shopify-theme/po-scripts/Rates.gs with final version

Acceptance Criteria:

• All collection tabs with a known vendor get rates populated
• Validation summary shows 0 errors
• Tabs with unknown vendor (e.g. Greentex) are skipped gracefully with a log message

[STOP — Ready for UAT: Phase 4] Cole/Sabrina: run "Populate All Rates" on the full sheet. Check validation summary in Execution log. Spot-check 3 different collection tabs (different mills). Are rates correct? Confirm with Simple Pricing manually on 2-3 SKUs.

Phase 5: Final Polish + Handoff to Sabrina

DISPATCH tasks:

1. Clean up TestRates.gs and TestSkuClassification.gs — either delete or move to a "Dev Tools" section clearly marked as non-production
2. Write ~/ai-projects/Shiplap/shopify-theme/po-scripts/RATES-HOW-IT-WORKS.md: - Plain English explanation of rate logic - How to add a new mill to Simple Pricing - How to handle a new SKU type - Troubleshooting guide
3. Final version of all script files saved locally in po-scripts/

Acceptance Criteria:

• No test functions in production code
• Documentation written in plain English (Sabrina-readable)
• Final scripts saved locally

[STOP — Ready for UAT: Phase 5 — Final] Cole: share with Sabrina. Does she understand how to use it and maintain it? Any final tweaks before calling this done?

Execution Log

2026-04-12 — Phase 1 DISPATCH complete. Wrote TestRates.gs + PHASE1-TEST-INSTRUCTIONS.md to ~/ai-projects/Shiplap/shopify-theme/po-scripts/. Started with Phase 1 (not full rewrite) because output columns and D/R multiplier still need UAT confirmation before Phase 3 can be written correctly. Next: Cole pastes TestRates.gs, runs both test functions, reports back results + column/multiplier answers.