/** * ALU Student Companion — Knowledge Base Publisher * ------------------------------------------------ * Reads every configured tab in this spreadsheet, normalises rows into a * single JSON document, and either: * (a) serves it at the web-app URL when the backend polls `doGet`, or * (b) pushes a refresh ping to the backend on every edit (`onEdit`). * * Setup: * 1. Extensions → Apps Script. Paste this file as `Code.gs`. * 2. Project Settings → Script Properties, add: * BACKEND_REFRESH_URL = https://.hf.space/api/admin/refresh-sheet * BACKEND_REFRESH_TOKEN = * 3. Deploy → New deployment → type "Web app", execute as "Me", * access "Anyone with the link". Copy the /exec URL — that becomes * KNOWLEDGE_JSON_URL on the backend. * 4. Triggers (clock icon): add an installable trigger * onEdit → From spreadsheet → On edit * (Apps Script's simple onEdit can't call UrlFetchApp; we need an * installable trigger for that.) * * Adding a new tab? Add one row to TAB_CONFIG. No code change. */ // ---------------------------------------------------------------------------- // Tab configuration // ---------------------------------------------------------------------------- // Each entry describes how to read one tab. Two reader modes: // // mode: "rows" (default) // Reads the tab as a table. Set `headerRow` to the 1-indexed row that // contains the column labels (most sheets put them in row 1, but some // have title banners or merged cells above the headers). // `columns` maps the header text (case-insensitive) to a semantic field // the backend understands. // // mode: "wholeSheet" // Treats the entire tab as one prose blob. Useful for tabs like // "Overview" that are a single narrative paragraph rather than a table. // // Common fields on every config: // department — institutional owner (used for routing + citations) // category — default category when the row doesn't have one // skip — set true to exclude the tab from publishing // // Semantic fields the backend understands (right-hand side of `columns`): // title — short label (becomes the citation title) // content — the body the chatbot reads // source_url — the canonical ALU link to cite // category — sub-grouping inside the department // last_updated — ISO date if the sheet tracks it const TAB_CONFIG = { // Single prose paragraph in cell A1 — slurp it as one entry. "Overview": { mode: "wholeSheet", department: "ALU", category: "About", title: "About the Student Companion Chatbot", source_url: "https://alustudentcompanion.vercel.app", }, // Clean table with headers in ROW 2. "LIBRARY": { mode: "rows", headerRow: 2, department: "Library", category: "Resources", columns: { "category": "category", "resource / page title": "title", "description": "content", "url": "source_url", }, }, // Project-management tabs are image-heavy and don't have clean header rows // in the first few rows. Leave them disabled until they're restructured — // flip `skip` to false and add a `headerRow` + `columns` mapping when ready. "Development Workplan 2024/2025": { skip: true, department: "ALU", category: "Internal" }, "Data Provision Workplan 2024/2025": { skip: true, department: "ALU", category: "Internal" }, // Data Center: catalogue of student-facing resources (support pages, handbooks, // staff/faculty info, calendars, policies). Headers live in row 7 under a // project-banner block. The `Category` column doubles as the department // routing hint — but we keep a single department label so all rows share // a consistent owner. Rich department-level routing can come later by // adding a column-to-department mapping in the backend if needed. "Data Center": { mode: "rows", headerRow: 7, department: "ALU Resources", category: "Data Center", columns: { "category": "category", // "Registration", "Support", "Handbook", ... "discription": "title", // misspelled header in the sheet — holds the resource name "url": "source_url", // the actual URL students click "responsible": "last_updated", // best-effort: owning team (reused field) }, }, // Add new tabs here, e.g.: // "Wellness": { mode: "rows", headerRow: 1, department: "Wellness Center", // category: "Support", // columns: { "topic": "title", "details": "content", "link": "source_url" } }, }; const FALLBACK_CONFIG = { department: "ALU", category: "General", columns: {} }; // ---------------------------------------------------------------------------- // Public entrypoints // ---------------------------------------------------------------------------- /** Backend polls this. Returns the full knowledge JSON. */ function doGet(_e) { const payload = buildPayload_(); return ContentService .createTextOutput(JSON.stringify(payload)) .setMimeType(ContentService.MimeType.JSON); } /** Installable onEdit trigger. Pings the backend so it refreshes within seconds. */ function onEditTrigger(_e) { const props = PropertiesService.getScriptProperties(); const url = props.getProperty("BACKEND_REFRESH_URL"); const token = props.getProperty("BACKEND_REFRESH_TOKEN"); if (!url || !token) return; try { UrlFetchApp.fetch(url, { method: "post", contentType: "application/json", headers: { "X-Refresh-Token": token }, payload: JSON.stringify({ reason: "sheet-edited" }), muteHttpExceptions: true, }); } catch (err) { console.error("Failed to ping backend: " + err); } } /** Manual run — useful for testing in the Apps Script editor. */ function previewPayload() { const payload = buildPayload_(); console.log(JSON.stringify(payload, null, 2).slice(0, 4000)); console.log("entries: " + payload.entries.length + " | version: " + payload.version); } // ---------------------------------------------------------------------------- // Internals // ---------------------------------------------------------------------------- function buildPayload_() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const sheets = ss.getSheets(); const entries = []; sheets.forEach(function (sheet) { const tabName = sheet.getName(); const cfg = TAB_CONFIG[tabName]; if (!cfg) return; // ignore unconfigured tabs if (cfg.skip) return; const mode = cfg.mode || "rows"; const rows = mode === "wholeSheet" ? readWholeSheet_(sheet, cfg) : readTab_(sheet, cfg); rows.forEach(function (e) { entries.push(e); }); }); // Stable id-based sort so the content hash is deterministic. entries.sort(function (a, b) { return a.id < b.id ? -1 : a.id > b.id ? 1 : 0; }); const version = hashEntries_(entries); return { version: version, generated_at: new Date().toISOString(), spreadsheet_id: ss.getId(), entry_count: entries.length, entries: entries, }; } function readTab_(sheet, cfg) { const range = sheet.getDataRange(); const values = range.getValues(); const headerRowIdx = Math.max(0, (cfg.headerRow || 1) - 1); if (values.length <= headerRowIdx + 1) return []; const headers = (values[headerRowIdx] || []).map(function (h) { return String(h || "").trim().toLowerCase(); }); const columnMap = {}; Object.keys(cfg.columns || {}).forEach(function (header) { const idx = headers.indexOf(header.toLowerCase()); if (idx >= 0) columnMap[cfg.columns[header]] = idx; }); const tabName = sheet.getName(); const out = []; for (let r = headerRowIdx + 1; r < values.length; r++) { const row = values[r]; const get = function (field) { const idx = columnMap[field]; if (idx === undefined) return ""; const v = row[idx]; return v === null || v === undefined ? "" : String(v).trim(); }; const title = get("title"); const content = get("content"); // Need at least one of title/content to be meaningful. if (!title && !content) continue; const id = slug_(tabName) + "-" + (slug_(title) || ("row-" + r)); out.push({ id: id, department: cfg.department, tab: tabName, category: get("category") || cfg.category, title: title || cfg.department + " entry", content: content || title, source_url: get("source_url"), last_updated: get("last_updated"), row: r + 1, // human-readable for staff debugging }); } return out; } /** * Treat the entire tab as a single prose entry. Concatenates every non-empty * cell into one content block — useful for narrative tabs like Overview * where the meaningful text lives in a single merged cell or a handful of * free-form paragraphs. */ function readWholeSheet_(sheet, cfg) { const values = sheet.getDataRange().getValues(); const parts = []; values.forEach(function (row) { row.forEach(function (cell) { const s = String(cell == null ? "" : cell).trim(); if (s) parts.push(s); }); }); const content = parts.join("\n\n").trim(); if (!content) return []; const tabName = sheet.getName(); return [{ id: slug_(tabName) + "-whole", department: cfg.department || "ALU", tab: tabName, category: cfg.category || "About", title: cfg.title || tabName, content: content, source_url: cfg.source_url || "", last_updated: "", row: 1, }]; } function slug_(s) { return String(s || "") .toLowerCase() .replace(/[^a-z0-9]+/g, "-") .replace(/^-+|-+$/g, "") .slice(0, 60); } /** Stable content hash so the backend can skip re-ingest when nothing changed. */ function hashEntries_(entries) { const raw = entries.map(function (e) { return [e.id, e.title, e.content, e.source_url, e.category, e.department].join("␟"); }).join("␞"); const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, raw); return digest.map(function (b) { const v = (b < 0 ? b + 256 : b).toString(16); return v.length === 1 ? "0" + v : v; }).join("").slice(0, 16); }