Donne120
Claude Opus 5
Add a Sheet publisher script that works for any university
13d7de0
|
Raw History Blame Contribute Delete
3.13 kB

Knowledge sheet publishers

Two scripts live here. They do the same job β€” publish a Google Sheet as JSON for the backend to ingest β€” but they are not interchangeable.

UniversityTemplate.gs β€” use this for every new university

Paste it in, deploy, done. No editing. It finds the header row (even under a title banner), matches columns by meaning rather than exact spelling, reads every tab, and skips ones that look internal.

The sheet needs a header row naming its columns. Only two matter:

Column Also accepted as
Question Title, Topic, Subject, Name, Resource, Heading
Answer Content, Details, Description, Body, Text, Info, Response

Optional: Link (URL, Source), Category (Type, Section), Updated (Last Updated, Reviewed). Extra columns are ignored.

Tab names become the department used for routing and citations, so name them after the team that owns the content β€” "Admissions", "Library", "Fees".

Setup

  1. In the sheet: Extensions β†’ Apps Script. Delete what's there, paste this file.
  2. Deploy β†’ New deployment β†’ Web app. Execute as Me, access Anyone with the link.
  3. Copy the /exec URL.
  4. In the admin dashboard: Organizations β†’ that university β†’ Sheet β†’ paste β†’ Save.

The sheet itself stays private. Only the rows this script reads are exposed, and only to whoever holds the long random URL.

Checking it worked β€” in the Apps Script editor run previewPayload and read the log. It prints how many entries were found, broken down by tab. Zero usually means the columns aren't named recognisably, or the header sits more than ten rows down.

Code.gs β€” ALU only, do not reuse

ALU's sheet predates the template and has an unusual shape: headers in row 2 on one tab and row 7 on another, a prose-only Overview tab, and a misspelled discription column. Code.gs hardcodes all of that in TAB_CONFIG.

Pasted into any other university's sheet it finds none of those tab names and publishes zero entries, silently β€” no error, just an empty knowledge base. Use UniversityTemplate.gs instead.

It stays as-is because it works and ALU's sheet is already wired to it. If ALU's sheet is ever restructured to match the template's conventions, this file can go.

How the two differ

Code.gs (ALU) UniversityTemplate.gs
Per-university editing Required None
Tab discovery Named explicitly in TAB_CONFIG Automatic
Header row Declared per tab Found automatically
Column names Exact match Matched by meaning
Unknown tabs Ignored Read if they have usable headers

What the backend does with it

sheet_sync.py fetches the URL, compares the version hash to what it saw last, and skips everything if unchanged β€” so polling an untouched sheet costs one small request and no re-embedding. When it has changed, rows are embedded into that university's own collection, and rows deleted from the sheet are removed from the index.

Sync runs about every 15 minutes, or immediately via Refresh now in the dashboard.