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);
}