Spaces:
Sleeping
Sleeping
Download sheet_integration/apps_script/Code.gs from StudentCompanion/alu-chatbot: direct link, hf CLI and curl.
- Browser
- Download file 10.3 kB
-
https://huggingface.co/spaces/StudentCompanion/alu-chatbot/resolve/main/sheet_integration/apps_script/Code.gs
- Command line
-
hf download hf://spaces/StudentCompanion/alu-chatbot/sheet_integration/apps_script/Code.gs
-
curl -L -o Code.gs https://huggingface.co/spaces/StudentCompanion/alu-chatbot/resolve/main/sheet_integration/apps_script/Code.gs
10.3 kB
| /** | |
| * 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://<your-space>.hf.space/api/admin/refresh-sheet | |
| * BACKEND_REFRESH_TOKEN = <same value as SHEET_REFRESH_TOKEN on the Space> | |
| * 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); | |
| } | |