Spreadsheet Governance & Collaborative Training
Audit Academy verifiedLessons vs MCP Learning-Progress Reads
Separate server-verified Academy lesson completion (verifiedLessons) from saved drafts, locked Pro lesson metadata, and read-only MCP training checks in Arte Sheets.
Distinguish conversational study from server-verified completion in Arte Sheets
When a team tracks spreadsheet training across a 6-lesson module, governance depends on separating three distinct states: (1) lesson content accessibility (free lessons open to any signed-in learner vs paid lessons requiring an active Pro plan, while locked lessons expose catalog metadata only), (2) conversational practice or saved worksheet drafts, and (3) official completion recorded in verifiedLessons by running the interactive exercise check inside Arte Sheets. In the Arte Sheets ChatGPT plugin 0.1.0 (currently In review / Not published), the scoped OAuth academy:read tools (list_lessons, get_lesson, get_learning_progress, managed under Profile -> Connected apps) are strictly read-only: get_learning_progress reports saved progress and verifiedLessons, but the connector cannot submit exercises, mark lessons complete, or issue certificates — and reading a lesson never grants permission to edit a workbook!
Build a 6-lesson cohort audit table (A2:E7) for a learner with Active_Pro = 1 (H2): Lessons 1–2 are Free and server-verified (E2:E3 = 1); Lesson 3 (Free) and Lesson 4 (Pro) were practiced in chat with saved drafts (D4:D5 = 1) but not yet checked in Arte Sheets (E4:E5 = 0); Lesson 5 (Pro) is server-verified (E6 = 1); and Lesson 6 (Pro) is unstarted. Notice how a naive COUNTIF(D2:D7, 1) overstates completion at 5 / 6, whereas auditing E2:E7 (verifiedLessons) accurately reports 3 / 6 verified, flags 2 unverified chat-practice gaps requiring the in-app exercise check, and shows what happens to accessible lessons when Active_Pro = 0 (dropping from 6 to 3 Free lessons while locked Pro metadata never unlocks paid lesson bodies). Also remember the credit boundary: ordinary MCP read calls do not consume Arte AI credits, whereas sending a hint prompt in the in-app Pro Assistant uses metered monthly Arte AI credits.
| Audit Metric (6-Lesson Track) | Formula Logic | Verified Output (Pro = 1) |
|---|---|---|
| Accessible Lesson Content Count | Free lessons + Pro lessons if Active_Pro = 1 | 6 (drops to 3 when Pro = 0) |
| Official verifiedLessons Count | COUNTIF(E2:E7, 1) (Server-Verified Only) | 3 of 6 |
| Unverified Practice Gap Count | COUNTIFS(D2:D7, 1, E2:E7, 0) | 2 (Run Check in Arte Sheets) |
| Track Completion Readiness | Requires all 6 in verifiedLessons | Incomplete: 3 Unverified |
Accessible Lesson Count (Honoring Free vs Active-Pro Entitlement) Formula
Count accessible full lesson bodies without treating locked catalog metadata as unlocked content when Active_Pro = 0.
=IF(AND(H2 <> 0, H2 <> 1), "Invalid Pro Flag", COUNTIF(B2:B7, "Free") + IF(H2 = 1, COUNTIF(B2:B7, "Pro"), 0))Server-Verified Completion Gate and Unverified Practice Gap Formula
Require every lesson row to carry Server_Verified = 1 (verifiedLessons) — AND catch any corrupted state where Server_Verified = 1 without Saved_Progress = 1!
=LET(bad_r, COUNTIFS(D2:D7, 0, E2:E7, 1), ver_n, COUNTIF(E2:E7, 1), tot_n, ROWS(E2:E7), IF(bad_r > 0, "Audit Error: Verified Without Saved Check", IF(ver_n = tot_n, "Track Complete (All Server-Verified in Arte Sheets)", "Incomplete: " & (tot_n - ver_n) & " Unverified")))Try the example
Use the original values or change them to test your understanding.