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
// ================================================================
//
// Sheets managed:
// Log - raw event log (one row per completed status)
// Comments - one row per saved comment
// 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 (8):
// Date | Time | Room | POD | Status | Duration | MA | Doc
//
// To install the 6 PM trigger:
// Open Apps Script editor, run installTrigger() once manually.
// ================================================================
var STATUSES = ["MA w/pt","Waiting","MD w/pt","AVS","Lab","Imm","BP"];
// ---- 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 parseMMSS(s) {
if (!s) return null;
var parts = String(s).split(":");
if (parts.length !== 2) return null;
var v = parseInt(parts[0],10)*60 + parseInt(parts[1],10);
return isNaN(v) ? null : v;
}
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");
}
// ---- Read entire Log sheet --------------------------------------
function readLog(ss) {
var sh = ss.getSheetByName("Log");
if (!sh || sh.getLastRow() < 2) return [];
var data = sh.getRange(2, 1, sh.getLastRow()-1, 8).getValues();
return data.map(function(r) {
return {
date: String(r[0]),
time: String(r[1]),
room: String(r[2]),
pod: String(r[3]),
status: String(r[4]),
durSec: parseMMSS(r[5]),
ma: String(r[6]||"").trim(),
doc: String(r[7]||"").trim()
};
}).filter(function(r){ return r.durSec !== null; });
}
function filterByDate(rows, from, to) {
return rows.filter(function(r){ return r.date >= from && r.date <= to; });
}
// ---- Write an average table ------------------------------------
// groupFn(row) returns the key (room name, POD, MA, or Doc)
function writeAvgTable(sh, rows, groupFn, keyHeader) {
sh.clearContents();
var hdr = [keyHeader].concat(STATUSES);
sh.appendRow(hdr);
var hdrRange = sh.getRange(1, 1, 1, hdr.length);
hdrRange.setFontWeight("bold").setBackground("#eeeeee");
sh.setFrozenRows(1);
// Group durations by key and status
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);
});
// Sort keys naturally (numeric where possible)
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");
// MA and Doc breakdowns use the full weekly window
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);
}
// ---- doPost: receives data from the board ----------------------
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);
logSh.insertRowAfter(1);
logSh.getRange(2,1,1,8).setValues([[
date, time,
entry.room, entry.pod,
entry.status, dur,
entry.ma || "",
entry.doc || ""
]]);
});
}
// -- Comments sheet --------------------------------------------
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");
}
// ---- Trigger setup ---------------------------------------------
function installTrigger() {
// Delete any existing calcAllAverages triggers first
ScriptApp.getProjectTriggers().forEach(function(t) {
if (t.getHandlerFunction() === "calcAllAverages") {
ScriptApp.deleteTrigger(t);
}
});
// Create a new 6 PM daily trigger
ScriptApp.newTrigger("calcAllAverages")
.timeBased()
.atHour(18)
.everyDays(1)
.create();
Logger.log("6 PM daily trigger installed for calcAllAverages.");
}
// ---- doGet: serves CSV export for the Export Today button ---------
// Called via GET ?action=exportDaily
// Returns the Daily Room sheet as a downloadable CSV file.
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);
// Quote cells that contain commas or quotes
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");
}
// Default GET response
return ContentService.createTextOutput("Clinic Room Status Board API");
}