Donne120
sheet_sync: disambiguate duplicate entry ids within a batch
392a0c4
Raw History Blame Contribute Delete
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);
}