Spreadsheet Governance & Collaborative AI Workflows
Academy MCP Lesson Gate, Locale & Progress Audit Formula
Audit academy:read lesson access across free vs Pro-gated catalog metadata, transparent locale fallback without fabricated official translations, and read-only server-verified progress boundaries.
Separate catalog metadata, Pro lesson entitlement, and verified in-app exercises cleanly!
In Arte Sheets, the ChatGPT connector release 0.1.0 is currently In review / Not published (never promise public installation, store approval, revenue share, or ChatGPT credit transfer). Where authorized via scoped OAuth (Profile -> Connected apps), its read-only academy:read tools—list_lessons, get_lesson, and get_learning_progress—consume 0 Arte AI credits (subject to the shared 120 MCP tool calls/min per-account limiter, distinct from the metered in-app Pro Assistant’s 2,000 monthly credits) and enforce three strict pedagogical guardrails: (1) Free lessons (Paid_Flag = 0) open via get_lesson without Pro, whereas paid lessons (Paid_Flag = 1) require an active Pro subscription (or active paid period)—seeing locked title/summary metadata in list_lessons NEVER authorizes reading or reconstructing locked lesson content; (2) when Official_Locale_Available = 0 for a user’s requested language, offer the available English source lesson transparently rather than fabricating an "official" localized lesson; and (3) reading a lesson never authorizes mutating a workbook (sheets:write) or sharing (sheets:share), nor can chat walkthroughs submit exercises, mark completion, or issue Academy certificates!
Build a 6-parameter governance audit sheet—Lesson_Paid_Flag = 0 (free lesson, B2), Account_Active_Pro = 0 (free account, B3), Official_Locale_Available = 1 (localized lesson exists, B4), Server_Verified_Exercises = 12 (completed inside the Arte Sheets app validator, B5), Chat_Guided_Walkthroughs = 5 (B6), and Course_Required_Exercises = 15 (B7): Lesson Access Gate returns "Allow get_lesson: Free Tier" (E2); Locale Policy returns "Serve Official Localized Lesson" (E3); Counted Verified Progress equals 12 (chat walkthroughs add 0 to verifiedLessons!, E4); and Remaining In-App Exercises equals 15 - 12 = 3 (E5)!
| Academy MCP Governance Metric (Free Lesson=0, Free Acct=0, Locale=1, 12/15 Verified, 5 Chat) | Formula | Verified Output |
|---|---|---|
| get_lesson Entitlement Gate | Free Allowed; Paid Requires Active Pro | Allow get_lesson: Free Tier |
| Localization Delivery Rule | Official Locale vs Disclosed EN Source | Serve Official Localized Lesson |
| Counted verifiedLessons | Server_Verified_Exercises Only | 12 (5 chat runs add 0 verified) |
| Remaining In-App Exercises | MAX(0, Required - Server_Verified) | 3 in-app validator runs needed |
Academy Lesson Entitlement & Locked-Metadata Guard Formula
Flip Lesson_Paid_Flag = 1 on a free account (B2 = 1, B3 = 0): E2 switches to "Locked Metadata Only: Active Pro Required" and E3 blocks unentitled content ("Catalog Metadata Only (Do Not Fabricate Lesson)")!
=LET(pd_l, B2, pr_a, B3, lc_a, B4, vr_n, B5, ch_n, B6, rq_n, B7, IF(OR(AND(pd_l <> 0, pd_l <> 1), AND(pr_a <> 0, pr_a <> 1), AND(lc_a <> 0, lc_a <> 1), vr_n < 0, ch_n < 0, rq_n <= 0, vr_n > rq_n), "Invalid Academy Audit Input", IF(pd_l = 0, "Allow get_lesson: Free Tier", IF(pr_a = 1, "Allow get_lesson: Active Pro", "Locked Metadata Only: Active Pro Required"))))Transparent Locale Fallback & Verified-Exercise Boundary Formula
Test Paid_Flag = 1, Active_Pro = 1, Official_Locale_Available = 0: E2 permits get_lesson under Active Pro while E3 returns "Offer EN Source (Disclose Unlocalized)"!
=IF(E2 = "Invalid Academy Audit Input", E2, IF(LEFT(E2, 6) = "Locked", "Catalog Metadata Only (Do Not Fabricate Lesson)", IF(B4 = 1, "Serve Official Localized Lesson", "Offer EN Source (Disclose Unlocalized)")))Try the example
Use the original values or change them to test your understanding.