Exports to Log (completed statuses with MA and Doc) and Comments. At 6 PM daily, Apps Script auto-calculates averages across 8 sheets: Daily/Weekly/Monthly per Room and per POD, plus breakdowns by MA and by Doc.
installTrigger() once from the Apps Script editor to set up the 6 PM daily recalculation// ================================================================
// CLINIC ROOM STATUS BOARD - Google Apps Script (complete)
// ================================================================
// Sheets managed:
// Log - one row per room (upserted by room number in col C)
// Comments - one row per saved comment (append-only)
// Daily Room - today avg per room x status (rebuilt at 6 PM)
// Daily POD - today avg per POD x status
// Weekly Room - 7-day avg per room x status
// Weekly POD - 7-day avg per POD x status
// Monthly Room - 30-day avg per room x status
// Monthly POD - 30-day avg per POD x status
// MA Averages - weekly avg per MA name x status
// Doc Averages - weekly avg per Doc name x status
//
// Log columns: Date | Time | Room | POD | Status | Duration | MA | Doc
// Column C (Room) is unique — new data for a room overwrites its row.
//
// After pasting: run installTrigger() once to set up the 6 PM job.
// ================================================================
var STATUSES = ["MA w/pt","Waiting","MD w/pt","AVS","Lab","Imm","BP"];
// ---- Core helpers -----------------------------------------------
function getOrCreate(ss, name) {
var sh = ss.getSheetByName(name);
if (!sh) sh = ss.insertSheet(name);
return sh;
}
function mmss(sec) {
if (sec === null || sec === undefined) return "";
var s = Math.round(sec);
return ("0" + Math.floor(s / 60)).slice(-2) + ":" + ("0" + (s % 60)).slice(-2);
}
function avgArr(arr) {
if (!arr || !arr.length) return null;
return arr.reduce(function(a, b) { return a + b; }, 0) / arr.length;
}
function todayStr() {
return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
}
function daysAgoStr(n) {
var d = new Date();
d.setDate(d.getDate() - n);
return Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
// ---- Date/duration cell parsers ---------------------------------
// Google Sheets stores date and duration cells as Date objects,
// not strings. These two functions handle both cases correctly.
// Converts a duration cell to total seconds.
// Sheets stores mm:ss durations using a 1899/1900 epoch Date object.
function parseDuration(val) {
if (val === null || val === undefined || val === "") return null;
if (val instanceof Date) {
var secs = val.getHours() * 3600 + val.getMinutes() * 60 + val.getSeconds();
return secs;
}
var s = String(val).trim();
var parts = s.split(":");
if (parts.length === 2) {
var v = parseInt(parts[0], 10) * 60 + parseInt(parts[1], 10);
return isNaN(v) ? null : v;
}
if (parts.length === 3) {
var v = parseInt(parts[0], 10) * 3600 + parseInt(parts[1], 10) * 60 + parseInt(parts[2], 10);
return isNaN(v) ? null : v;
}
return null;
}
// Converts a date cell to "yyyy-MM-dd" string in the script timezone.
function formatDateCell(val, tz) {
if (!val && val !== 0) return "";
if (val instanceof Date) {
return Utilities.formatDate(val, tz, "yyyy-MM-dd");
}
return String(val).substring(0, 10);
}
// ---- Read Log sheet ---------------------------------------------
function readLog(ss) {
var sh = ss.getSheetByName("Log");
if (!sh || sh.getLastRow() < 2) return [];
var tz = Session.getScriptTimeZone();
var data = sh.getRange(2, 1, sh.getLastRow() - 1, 8).getValues();
return data.map(function(r) {
return {
date: formatDateCell(r[0], tz),
time: String(r[1]),
room: String(r[2]),
pod: String(r[3]),
status: String(r[4]),
durSec: parseDuration(r[5]),
ma: String(r[6] || "").trim(),
doc: String(r[7] || "").trim()
};
}).filter(function(r) {
return r.durSec !== null && r.durSec > 0 && r.room !== "" && r.room !== "TEST";
});
}
function filterByDate(rows, from, to) {
return rows.filter(function(r) { return r.date >= from && r.date <= to; });
}
// ---- Write an average table -------------------------------------
function writeAvgTable(sh, rows, groupFn, keyHeader) {
sh.clearContents();
var hdr = [keyHeader].concat(STATUSES);
sh.appendRow(hdr);
sh.getRange(1, 1, 1, hdr.length).setFontWeight("bold").setBackground("#eeeeee");
sh.setFrozenRows(1);
var groups = {};
rows.forEach(function(r) {
var k = groupFn(r);
if (!k) return;
if (!groups[k]) groups[k] = {};
if (!groups[k][r.status]) groups[k][r.status] = [];
groups[k][r.status].push(r.durSec);
});
var keys = Object.keys(groups).sort(function(a, b) {
var na = isNaN(a) ? a : Number(a);
var nb = isNaN(b) ? b : Number(b);
return na < nb ? -1 : na > nb ? 1 : 0;
});
if (keys.length === 0) {
sh.getRange(2, 1).setValue("(no data for this period)");
return;
}
var outRows = keys.map(function(k) {
var row = [k];
STATUSES.forEach(function(st) {
var vals = groups[k][st] || [];
row.push(vals.length ? mmss(avgArr(vals)) : "");
});
return row;
});
sh.getRange(2, 1, outRows.length, hdr.length).setValues(outRows);
}
// ---- Main recalculation (runs at 6 PM daily) -------------------
function calcAllAverages() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var rows = readLog(ss);
var today = todayStr();
var week = daysAgoStr(7);
var month = daysAgoStr(30);
var dayRows = filterByDate(rows, today, today);
var weekRows = filterByDate(rows, week, today);
var monthRows = filterByDate(rows, month, today);
writeAvgTable(getOrCreate(ss, "Daily Room"), dayRows, function(r) { return r.room; }, "Room");
writeAvgTable(getOrCreate(ss, "Daily POD"), dayRows, function(r) { return r.pod; }, "POD");
writeAvgTable(getOrCreate(ss, "Weekly Room"), weekRows, function(r) { return r.room; }, "Room");
writeAvgTable(getOrCreate(ss, "Weekly POD"), weekRows, function(r) { return r.pod; }, "POD");
writeAvgTable(getOrCreate(ss, "Monthly Room"), monthRows, function(r) { return r.room; }, "Room");
writeAvgTable(getOrCreate(ss, "Monthly POD"), monthRows, function(r) { return r.pod; }, "POD");
writeAvgTable(getOrCreate(ss, "MA Averages"), weekRows, function(r) { return r.ma || "(none)"; }, "MA");
writeAvgTable(getOrCreate(ss, "Doc Averages"), weekRows, function(r) { return r.doc || "(none)"; }, "Doc");
Logger.log("Averages recalculated: " + today + " | Log rows used: " + rows.length);
}
// ---- Upsert helper: find row in Log by room number (col C) -----
// Returns the 1-based row index if found, -1 if not.
function findRoomRow(sh, roomName) {
var lastRow = sh.getLastRow();
if (lastRow < 2) return -1;
var col = sh.getRange(2, 3, lastRow - 1, 1).getValues();
for (var i = 0; i < col.length; i++) {
if (String(col[i][0]) === String(roomName)) return i + 2;
}
return -1;
}
// ---- doPost: receives data from the board ----------------------
// Log sheet: column C (Room) is unique. Each incoming entry either
// overwrites the existing row for that room, or appends a new one.
function doPost(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var data = JSON.parse(e.postData.contents);
var tz = Session.getScriptTimeZone();
// Log sheet
var logSh = getOrCreate(ss, "Log");
if (logSh.getLastRow() === 0) {
logSh.appendRow(["Date","Time","Room","POD","Status","Duration","MA","Doc"]);
logSh.getRange(1, 1, 1, 8).setFontWeight("bold").setBackground("#eeeeee");
logSh.setFrozenRows(1);
}
if (data.entries && data.entries.length > 0) {
data.entries.forEach(function(entry) {
var dt = new Date(entry.timestamp);
var date = Utilities.formatDate(dt, tz, "yyyy-MM-dd");
var time = Utilities.formatDate(dt, tz, "HH:mm:ss");
var mins = Math.floor(entry.durationSec / 60);
var secs = entry.durationSec % 60;
var dur = ("0" + mins).slice(-2) + ":" + ("0" + secs).slice(-2);
var rowData = [date, time, entry.room, entry.pod, entry.status, dur, entry.ma || "", entry.doc || ""];
// Upsert: overwrite existing row for this room, or append if new
var existingRow = findRoomRow(logSh, entry.room);
if (existingRow > 0) {
logSh.getRange(existingRow, 1, 1, 8).setValues([rowData]);
} else {
logSh.appendRow(rowData);
}
});
}
// Comments sheet (append-only — one row per comment)
var comSh = getOrCreate(ss, "Comments");
if (comSh.getLastRow() === 0) {
comSh.appendRow(["Date","Time","Room","POD","Comment"]);
comSh.getRange(1, 1, 1, 5).setFontWeight("bold").setBackground("#e8f4fd");
comSh.setFrozenRows(1);
}
if (data.comments && data.comments.length > 0) {
data.comments.forEach(function(c) {
var dt = new Date(c.timestamp);
var date = Utilities.formatDate(dt, tz, "yyyy-MM-dd");
var time = Utilities.formatDate(dt, tz, "HH:mm:ss");
comSh.insertRowAfter(1);
comSh.getRange(2, 1, 1, 5).setValues([[date, time, c.room, c.pod, c.comment]]);
});
}
return ContentService.createTextOutput("OK");
}
// ---- doGet: Export Today button downloads Daily Room as CSV ----
function doGet(e) {
var action = e && e.parameter && e.parameter.action;
if (action === "exportDaily") {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Daily Room");
var csv = "";
if (!sh || sh.getLastRow() < 1) {
csv = "No data yet. Run calcAllAverages() first or wait for the 6 PM trigger.";
} else {
var data = sh.getDataRange().getValues();
csv = data.map(function(row) {
return row.map(function(cell) {
var s = String(cell);
if (s.indexOf(",") > -1 || s.indexOf('"') > -1) {
s = '"' + s.replace(/"/g, '""') + '"';
}
return s;
}).join(",");
}).join("\n");
}
var today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
return ContentService
.createTextOutput(csv)
.setMimeType(ContentService.MimeType.CSV)
.downloadAsFile("daily-averages-" + today + ".csv");
}
return ContentService.createTextOutput("Clinic Room Status Board API");
}
// ---- Trigger setup ---------------------------------------------
function installTrigger() {
ScriptApp.getProjectTriggers().forEach(function(t) {
if (t.getHandlerFunction() === "calcAllAverages") ScriptApp.deleteTrigger(t);
});
ScriptApp.newTrigger("calcAllAverages")
.timeBased().atHour(18).everyDays(1).create();
Logger.log("6 PM daily trigger installed.");
}
// ---- Debug helpers (safe to leave in) --------------------------
function debugLog2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("Log");
var tz = Session.getScriptTimeZone();
Logger.log("timezone: " + tz);
Logger.log("todayStr: " + todayStr());
if (!sh || sh.getLastRow() < 2) { Logger.log("Log empty"); return; }
var numRows = Math.min(sh.getLastRow() - 1, 3);
var raw = sh.getRange(2, 1, numRows, 8).getValues();
raw.forEach(function(r, i) {
var dur = parseDuration(r[5]);
var dt = formatDateCell(r[0], tz);
Logger.log("row "+(i+2)+": date=["+dt+"] match="+(dt===todayStr())+" dur="+dur+"s room=["+r[2]+"] status=["+r[4]+"]");
});
var rows = readLog(ss);
Logger.log("readLog: " + rows.length + " valid rows");
var dayRows = filterByDate(rows, todayStr(), todayStr());
Logger.log("today rows: " + dayRows.length);
}