Spaces:
Sleeping
Sleeping
File size: 10,281 Bytes
eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 392a0c4 eaa56f0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 | /**
* 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);
}
|