Create Google Apps Script Web App
In your Sheet: Extensions → Apps Script. Delete the default code and paste:
const SHEET_OUT = 'Outgoing';
const SHEET_IN = 'Incoming';
const SHEET_USERS = 'Users';
const SHEET_AUDIT = 'AuditLog';
const SS = SpreadsheetApp.getActiveSpreadsheet();
function sheetFor(kind){ return kind === 'incoming' ? SHEET_IN : SHEET_OUT; }
function doGet(e) {
const action = e.parameter.action;
const kind = e.parameter.kind || 'outgoing';
if (action === 'login') return loginUser(e.parameter.username, e.parameter.password);
if (action === 'getAll') return getAll(kind);
if (action === 'getBySection') return getBySection(e.parameter.section, kind);
if (action === 'getMine') return getMine(e.parameter.section, e.parameter.by, kind);
if (action === 'getStaff') return getStaff(e.parameter.section);
if (action === 'getAssignableUsers') return getAssignableUsers(e.parameter.section);
if (action === 'getInstructionTargets') return getInstructionTargets(e.parameter.section);
if (action === 'getAssigned') return getAssigned(e.parameter.assignedTo);
if (action === 'getAuditLog') return getAuditLog(e.parameter);
return _json({error:'Unknown action'});
}
function doPost(e) {
const data = JSON.parse(e.postData.contents);
const kind = data.kind || 'outgoing';
if (data.action === 'add') return addDoc(data, kind);
if (data.action === 'returnIncoming') return returnIncomingDoc(data);
if (data.action === 'assign') return assignDoc(data);
if (data.action === 'claim') return claimDoc(data);
if (data.action === 'complete') return completeDoc(data);
if (data.action === 'resubmit') return resubmitDoc(data);
if (data.action === 'resubmitIncoming') return resubmitIncomingDoc(data);
if (data.action === 'deleteIncoming') return deleteIncomingDoc(data);
if (data.action === 'sectionHeadReview') return sectionHeadReview(data);
if (data.action === 'chiefApprove') return chiefApprove(data);
if (data.action === 'release') return releaseDoc(data);
return _json({error:'Unknown action'});
}
function _json(obj){ return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON); }
// ── Header-name-based sheet helpers ─────────────────────────────────
// Every mutating function below looks up columns by header name (not by
// hardcoded index) so that adding new columns — in any order, at any
// position — never shifts an existing write out from under it.
function getHeaders(sheet){
return sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
}
// Sheets silently upgrades a plain "YYYY-MM-DD"/"HH:mm" string typed (or
// written by this script) into a cell into a real Date object. Left alone,
// that Date later gets JSON.stringify'd to a UTC ISO timestamp — which for
// any timezone ahead of UTC (e.g. Asia/Manila, UTC+8) shifts midnight back
// onto the PREVIOUS day (2026-07-29 local -> 2026-07-28T16:00:00.000Z). This
// formats the Date using the spreadsheet's own timezone before it ever
// reaches JSON, so the value matches what the sheet actually displays.
function _cellValue(v, tz){
if (!(v instanceof Date)) return v;
// A time-only cell (e.g. TimeReleased) is stored as a Date anchored to
// Sheets' epoch day (Dec 30, 1899) — format those as a time, not a date.
if (v.getFullYear() === 1899) return Utilities.formatDate(v, tz, 'HH:mm');
return Utilities.formatDate(v, tz, 'yyyy-MM-dd');
}
function rowToObj(headers, row){
const tz = Session.getScriptTimeZone() || 'GMT';
const obj = {};
headers.forEach((h,i) => obj[h] = _cellValue(row[i], tz));
return obj;
}
// Finds a data row by its ID column (col 1). Returns {rowIndex (1-based
// sheet row), headers, obj} or null.
function findRow(sheet, id){
const rows = sheet.getDataRange().getValues();
const headers = rows[0];
for (let i = 1; i < rows.length; i++) {
if (String(rows[i][0]) === String(id)) {
return { rowIndex: i + 1, headers, obj: rowToObj(headers, rows[i]) };
}
}
return null;
}
// Writes {HeaderName: value, ...} into the given sheet row, skipping any
// header name the sheet doesn't have (so this never throws on an
// older sheet that hasn't had every new column added yet).
function writeByHeader(sheet, rowIndex, headers, values){
Object.keys(values).forEach(name => {
const col = headers.indexOf(name);
if (col > -1) sheet.getRange(rowIndex, col + 1).setValue(values[name]);
});
}
// Fallbacks ONLY — Google's servers have no notion of the end user's actual
// timezone (Session.getScriptTimeZone() is just this Apps Script project's
// configured zone, unrelated to whoever is using the app), so every action
// below is written to prefer a machine-time value sent by the client
// (nowLocalISO()/todayISO()/nowTimeStr() in script.js) and only falls back
// to these if a request is ever missing that field.
function todayISO(){ return new Date().toISOString().split('T')[0]; }
function nowTimeStr(){
const d = new Date();
return Utilities.formatDate(d, Session.getScriptTimeZone() || 'GMT', 'HH:mm');
}
// ── Reads ────────────────────────────────────────────────────────────
function getAll(kind) {
const sheet = SS.getSheetByName(sheetFor(kind));
if (!sheet) return _json([]);
const rows = sheet.getDataRange().getValues();
const [headers, ...data] = rows;
const result = data.map(row => rowToObj(headers, row));
return _json(result);
}
function getMine(section, by, kind) {
const all = JSON.parse(getAll(kind).getContent());
return _json(all.filter(d => d.Section === section && d.SubmittedBy === by));
}
function getBySection(section, kind) {
const all = JSON.parse(getAll(kind).getContent());
if (!section || section === 'All') return _json(all);
return _json(all.filter(d => d.Section === section));
}
function getAssigned(assignedTo) {
const all = JSON.parse(getAll('incoming').getContent());
return _json(all.filter(d => d.AssignedTo === assignedTo));
}
function getStaff(section) {
const sheet = SS.getSheetByName(SHEET_USERS);
if (!sheet) return _json([]);
const rows = sheet.getDataRange().getValues();
const result = [];
for (let i = 1; i < rows.length; i++) {
const role = String(rows[i][3]).trim();
const sect = rows[i][4];
const active = rows[i][5];
const isActive = active === true || String(active).toUpperCase() === 'TRUE';
if (role.toLowerCase() === 'staff' && isActive && (!section || section === 'All' || sect === section)) {
result.push({ username: rows[i][0], fullName: rows[i][2], section: sect });
}
}
return _json(result);
}
// Any active user except an Encoder — used by the Log Incoming Document
// form's "Assign to Staff Member" dropdown, which despite its name can
// target Staff, Section Head, or Chief; only Encoders are excluded, since
// they're the ones doing the assigning, not receiving tasks.
function getAssignableUsers(section) {
const sheet = SS.getSheetByName(SHEET_USERS);
if (!sheet) return _json([]);
const rows = sheet.getDataRange().getValues();
const result = [];
for (let i = 1; i < rows.length; i++) {
const rawRole = String(rows[i][3]).trim();
const role = canonicalRole(rawRole);
const sect = rows[i][4];
const active = rows[i][5];
const isActive = active === true || String(active).toUpperCase() === 'TRUE';
if (role !== 'Encoder' && isActive && (!section || section === 'All' || sect === section)) {
result.push({ username: rows[i][0], fullName: rows[i][2], section: sect, role: role });
}
}
return _json(result);
}
// Like getStaff(), but also includes Section Heads — used only by the
// Instruction modal, since the Chief may instruct a Section Head directly
// (BRD 2.1: "Issue instructions to any personnel"), not just Staff.
function getInstructionTargets(section) {
const sheet = SS.getSheetByName(SHEET_USERS);
if (!sheet) return _json([]);
const rows = sheet.getDataRange().getValues();
const result = [];
for (let i = 1; i < rows.length; i++) {
const rawRole = String(rows[i][3]).trim();
const role = canonicalRole(rawRole);
const sect = rows[i][4];
const active = rows[i][5];
const isActive = active === true || String(active).toUpperCase() === 'TRUE';
if ((role === 'Staff' || role === 'SectionHead') && isActive && (!section || section === 'All' || sect === section)) {
result.push({ username: rows[i][0], fullName: rows[i][2], section: sect, role: role });
}
}
return _json(result);
}
// Canonicalizes legacy role values from older sheets (Admin,
// ProjectManager) to the current role vocabulary (Chief, SectionHead)
// so existing deployed Sheets never need a forced data migration.
function canonicalRole(raw){
const norm = String(raw || '').toLowerCase().replace(/[^a-z]/g, '');
if (norm === 'admin' || norm === 'chief' || norm === 'systemadministrator') return 'Chief';
if (norm === 'projectmanager' || norm === 'pm' || norm === 'sectionhead') return 'SectionHead';
if (norm === 'encoder') return 'Encoder';
return 'Staff';
}
function loginUser(username, password) {
const sheet = SS.getSheetByName(SHEET_USERS);
if (!sheet) return _json({success:false, error:'No "Users" sheet found. Create a tab named Users.'});
const rows = sheet.getDataRange().getValues();
for (let i = 1; i < rows.length; i++) {
const uname = String(rows[i][0]).trim();
const pw = String(rows[i][1]);
const name = rows[i][2];
const role = String(rows[i][3]).trim();
const sect = rows[i][4];
const active = rows[i][5];
const isActive = active === true || String(active).toUpperCase() === 'TRUE';
if (uname === String(username).trim() && pw === String(password)) {
if (!isActive) return _json({success:false, error:'This account is disabled. Contact your administrator.'});
return _json({success:true, fullName:name, role:canonicalRole(role), section:sect});
}
}
return _json({success:false, error:'Incorrect username or password.'});
}
// ── Sequential tracking / reference numbers ─────────────────────────
// PropertiesService + LockService give us an atomic per-kind-per-year
// counter without needing a database — safe even if two people submit
// at the exact same moment.
//
// The counter alone isn't quite enough, though: it can drift out of sync
// with what's actually in the sheet (a row added/edited by hand, a restored
// backup, properties cleared by an admin, etc.), which would let the counter
// re-mint a ReferenceNo/TrackingNo that already exists. So on every call we
// also read the column of already-issued numbers and skip past any
// collision — still inside the same lock, so two simultaneous submissions
// can never land on the same number.
function nextSequence(kind, year){
const props = PropertiesService.getScriptProperties();
const key = 'seq_' + kind + '_' + year;
const lock = LockService.getScriptLock();
lock.waitLock(30000);
try {
const sheet = SS.getSheetByName(sheetFor(kind));
const colName = kind === 'incoming' ? 'TrackingNo' : 'ReferenceNo';
const existing = _existingValueSet(sheet, colName);
const prefix = kind === 'incoming' ? 'IN' : 'OUT';
let n = parseInt(props.getProperty(key) || '0', 10);
let ref;
do {
n++;
ref = prefix + '-' + year + '-' + String(n).padStart(5, '0');
} while (existing.has(ref));
props.setProperty(key, String(n));
return ref;
} finally {
lock.releaseLock();
}
}
// All non-blank values currently in `colName`, for duplicate-checking.
function _existingValueSet(sheet, colName){
const set = new Set();
if (!sheet) return set;
const headers = getHeaders(sheet);
const col = headers.indexOf(colName);
const lastRow = sheet.getLastRow();
if (col === -1 || lastRow < 2) return set;
sheet.getRange(2, col + 1, lastRow - 1, 1).getValues().forEach(row => {
if (row[0]) set.add(String(row[0]));
});
return set;
}
// ── One-time cleanup: run manually from the Apps Script editor ─────────
// (select dedupeReferenceNumbers or dedupeTrackingNumbers in the function
// dropdown, then Run) if ReferenceNo/TrackingNo ever ended up duplicated —
// e.g. from the script-properties counter getting reset before
// nextSequence()'s collision-check above existed. Not reachable from the
// web app (no doGet/doPost route calls it), so it only ever runs when you
// deliberately trigger it.
//
// Scans the ENTIRE column (not just the last row — that's what caused the
// original bug) with a single read, keeps the FIRST row that has any given
// number as-is, and renumbers every later duplicate to the next number free
// across the whole sheet. Then resyncs the persistent counter per year to
// that true max, so nextSequence() continues from here instead of
// re-colliding. Logs every change it makes (row, old number -> new number)
// via View > Logs / View > Executions so you have a record of what moved.
function dedupeReferenceNumbers(){ return _dedupeSequenceColumn('outgoing'); }
function dedupeTrackingNumbers(){ return _dedupeSequenceColumn('incoming'); }
function _dedupeSequenceColumn(kind){
const sheet = SS.getSheetByName(sheetFor(kind));
if (!sheet) { Logger.log('No sheet found for kind: ' + kind); return []; }
const colName = kind === 'incoming' ? 'TrackingNo' : 'ReferenceNo';
const headers = getHeaders(sheet);
const col = headers.indexOf(colName);
if (col === -1) { Logger.log('No ' + colName + ' column found.'); return []; }
const lastRow = sheet.getLastRow();
if (lastRow < 2) { Logger.log('No data rows.'); return []; }
const range = sheet.getRange(2, col + 1, lastRow - 1, 1);
const values = range.getValues();
const prefix = kind === 'incoming' ? 'IN' : 'OUT';
const numPattern = /^[A-Z]+-(\d{4})-(\d{5})$/;
// Pass 1: find the highest sequence number already used, per year, across
// every row (duplicates included) — renumbered rows must never land on a
// number anything else already holds.
const maxByYear = {};
values.forEach(row => {
const m = String(row[0] || '').match(numPattern);
if (m) {
const year = m[1], n = parseInt(m[2], 10);
if (!maxByYear[year] || n > maxByYear[year]) maxByYear[year] = n;
}
});
// Pass 2: walk top to bottom, leave the first occurrence of each number
// alone, renumber every repeat to the next free number for its year.
const seen = new Set();
const changes = [];
values.forEach((row, i) => {
const val = String(row[0] || '');
if (!val) return; // blank cells are a separate concern, not this function's job
if (seen.has(val)) {
const m = val.match(numPattern);
const year = m ? m[1] : new Date().getFullYear().toString();
const nextN = (maxByYear[year] || 0) + 1;
maxByYear[year] = nextN;
const newVal = prefix + '-' + year + '-' + String(nextN).padStart(5, '0');
range.getCell(i + 1, 1).setValue(newVal);
changes.push({ row: i + 2, old: val, new: newVal });
seen.add(newVal);
} else {
seen.add(val);
}
});
const props = PropertiesService.getScriptProperties();
Object.keys(maxByYear).forEach(year => {
props.setProperty('seq_' + kind + '_' + year, String(maxByYear[year]));
});
Logger.log(JSON.stringify(changes, null, 2));
Logger.log(changes.length + ' duplicate ' + colName + ' value(s) renumbered.');
return changes;
}
// ── Audit trail ──────────────────────────────────────────────────────
// `when` should always be the client's machine-time timestamp (nowLocalISO()
// in script.js) — Google's servers have no notion of the end user's
// timezone, so new Date() here would silently record the wrong wall-clock
// time for anyone outside whatever zone the Apps Script project happens to
// be configured with. Only falls back to server time if a request is ever
// missing it (e.g. hand-rolled API calls) so logging never breaks.
function logAudit(actor, role, action, docKind, docRef, details, when){
const sheet = SS.getSheetByName(SHEET_AUDIT);
if (!sheet) return; // Tab not created yet — never let a missing log tab block the real action.
const lastRow = sheet.getLastRow();
const nextId = lastRow > 1 ? lastRow : 1;
sheet.appendRow([nextId, when || new Date().toISOString(), actor || '', role || '', action || '', docKind || '', docRef || '', details || '']);
}
// `filters` here is the raw e.parameter from doGet(), which always contains
// action:'getAuditLog' (the ROUTING key that got us here) — deliberately
// read as filters.auditAction, not filters.action, so that routing value
// can never collide with and be misread as an Action-column filter (which
// would silently zero out every result, since no audit row's Action is
// ever literally "getAuditLog").
function getAuditLog(filters){
const sheet = SS.getSheetByName(SHEET_AUDIT);
if (!sheet) return _json([]);
const rows = sheet.getDataRange().getValues();
const [headers, ...data] = rows;
let result = data.map(row => rowToObj(headers, row));
if (filters.actor) result = result.filter(r => String(r.Actor) === filters.actor);
if (filters.role) result = result.filter(r => String(r.Role) === filters.role);
if (filters.auditAction) result = result.filter(r => String(r.Action) === filters.auditAction);
if (filters.docKind) result = result.filter(r => String(r.DocKind) === filters.docKind);
return _json(result);
}
// ── Create ───────────────────────────────────────────────────────────
function addDoc(data, kind) {
const sheet = SS.getSheetByName(sheetFor(kind));
const headers = getHeaders(sheet);
const last = sheet.getLastRow();
const id = last > 1 ? last : 1;
// data.now is the client's machine-time timestamp (nowLocalISO() in
// script.js) — prefer it for both the Timestamp column and the sequence
// number's year, so a document logged just before/after midnight gets the
// year that matches the submitter's own calendar, not the Apps Script
// server's. Falls back to server time only if a request is ever missing it.
const now = data.now || new Date().toISOString();
const year = (data.now ? data.now.slice(0, 4) : new Date().getFullYear());
const values = {
ID: id,
Timestamp: now,
Section: data.section,
SubmittedBy: data.by,
Description: data.desc,
DocType: data.type,
Priority: data.priority,
EncoderRemarks: data.encRem || ''
};
let docRef;
if (kind === 'incoming') {
docRef = nextSequence('incoming', year);
values.DateReceived = data.date;
values.Status = 'Pending Review';
values.OriginatingOffice = data.originatingOffice || '';
values.TrackingNo = docRef;
if (data.assignedTo) {
values.AssignedTo = data.assignedTo;
values.AssignedDate = data.assignedDate || data.date;
}
} else {
docRef = nextSequence('outgoing', year);
values.DatePrepared = data.date;
values.ReferenceNo = docRef;
// Staff-submitted docs must pass Section Head review first; a Section
// Head authoring their own outgoing doc skips straight to Chief
// approval (they've implicitly already reviewed it by writing it).
values.Status = data.skipSectionHead ? 'Pending Chief Approval' : 'Pending Section Head Review';
// Captured at submission time now (Staff picks which of their own
// assigned incoming tasks this responds to) rather than guessed later
// at Release — see submitDoc() in script.js.
values.LinkedIncomingID = data.linkedIncomingId || '';
}
const row = headers.map(h => (values[h] !== undefined ? values[h] : ''));
// Column A is always the row ID by convention — write it positionally (not
// just via the "ID" header lookup above) so a header cell that's been
// renamed or mistyped (e.g. during the DateReleased->DatePrepared migration)
// can never leave this silently blank.
row[0] = id;
sheet.appendRow(row);
logAudit(data.by, data.role || '', 'Create', kind, docRef, data.desc, data.now);
// Submitting an outgoing doc linked to a specific incoming task IS the
// response to that task — auto-complete the incoming side instead of
// making the Staff separately click "Mark Done" on Assigned Tasks too.
if (kind === 'outgoing' && data.linkedIncomingId) {
completeLinkedIncoming(data.linkedIncomingId, data.now);
}
return _json({success:true, id, docRef});
}
// Marks the given Incoming row Completed (filling ClaimedDate too, if it was
// somehow never claimed, so the row's dates stay internally consistent with
// what claimDoc()/completeDoc() would have produced). Shared by addDoc()
// above and the one-time backfillLinkedIncomingCompletion() repair below.
function completeLinkedIncoming(incomingId, when){
const inSheet = SS.getSheetByName(SHEET_IN);
const found = findRow(inSheet, incomingId);
if (!found || found.obj.Status === 'Completed') return false;
const today = when ? when.slice(0, 10) : todayISO();
writeByHeader(inSheet, found.rowIndex, found.headers, {
Status: 'Completed',
ClaimedDate: found.obj.ClaimedDate || today,
ActionCompletedDate: found.obj.ActionCompletedDate || today
});
return true;
}
// Section Head/Chief-only: returns an incoming document to the Encoder who
// logged it, with a mandatory remark (wrong info, wrong assignment, etc.).
// Incoming documents no longer go through full Section Head/Chief approval,
// but this is the one action they keep from the Review Queue for flagging a
// problem doc — reuses the existing SupervisorRemarks/DateReviewed/ReviewedBy
// columns (unused for incoming since updateDoc() was removed) rather than
// adding new ones.
function returnIncomingDoc(data) {
const sheet = SS.getSheetByName(SHEET_IN);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
if (!data.remarks) return _json({error:'Please add a reason before returning.'});
writeByHeader(sheet, found.rowIndex, found.headers, {
Status: 'Returned',
SupervisorRemarks: data.remarks,
DateReviewed: data.date || todayISO(),
ReviewedBy: data.by
});
logAudit(data.by, data.role || 'Encoder', 'Return', 'incoming', found.obj.TrackingNo || String(data.rowId), data.remarks, data.now);
return _json({success:true});
}
function assignDoc(data) {
const sheet = SS.getSheetByName(SHEET_IN);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
writeByHeader(sheet, found.rowIndex, found.headers, {
AssignedTo: data.assignedTo,
AssignedDate: data.assignedDate || todayISO()
});
logAudit(data.by, data.role || '', 'Assign', 'incoming', found.obj.TrackingNo || String(data.rowId), 'Assigned to ' + data.assignedTo, data.now);
return _json({success:true});
}
function claimDoc(data) {
const sheet = SS.getSheetByName(SHEET_IN);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
// Status must actually advance to "Under Review" here — previously this
// only wrote ClaimedDate, so a claimed doc stayed stuck showing "Approved"
// forever, which made the Under Review tile permanently 0 and inflated
// Approved with docs that were really already in progress or done.
writeByHeader(sheet, found.rowIndex, found.headers, { Status: 'Under Review', ClaimedDate: data.claimedDate || todayISO() });
logAudit(data.by, data.role || '', 'Claim', 'incoming', found.obj.TrackingNo || String(data.rowId), '', data.now);
return _json({success:true});
}
function completeDoc(data) {
const sheet = SS.getSheetByName(SHEET_IN);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
// Same bug as claimDoc() — only ActionCompletedDate was being written, so
// Status never reached "Completed" and that tile could never show anything.
writeByHeader(sheet, found.rowIndex, found.headers, { Status: 'Completed', ActionCompletedDate: data.completedDate || todayISO() });
logAudit(data.by, data.role || '', 'Complete', 'incoming', found.obj.TrackingNo || String(data.rowId), '', data.now);
return _json({success:true});
}
// ── One-time cleanup: run manually from the Apps Script editor ─────────
// (select backfillIncomingStatus in the function dropdown, then Run) to fix
// rows claimed/completed BEFORE the Status fix above existed — those rows
// have a real ClaimedDate/ActionCompletedDate but are stuck showing
// Status="Approved" forever, which is exactly what made the incoming tile
// row (Under Review / Completed always 0) not tally with reality. Not
// reachable from the web app, so it only runs when you deliberately
// trigger it. Safe to run more than once — it only ever moves a row
// forward to match dates that are already there, never backward.
function backfillIncomingStatus(){
const sheet = SS.getSheetByName(SHEET_IN);
if (!sheet) { Logger.log('No Incoming sheet found.'); return []; }
const headers = getHeaders(sheet);
const lastRow = sheet.getLastRow();
if (lastRow < 2) { Logger.log('No data rows.'); return []; }
const rows = sheet.getRange(2, 1, lastRow - 1, headers.length).getValues();
const statusCol = headers.indexOf('Status');
const claimedCol = headers.indexOf('ClaimedDate');
const completedCol = headers.indexOf('ActionCompletedDate');
if (statusCol === -1 || claimedCol === -1 || completedCol === -1) {
Logger.log('Missing Status/ClaimedDate/ActionCompletedDate column.');
return [];
}
const changes = [];
rows.forEach((row, i) => {
const status = row[statusCol];
// Terminal states — never touch these. Everything else (Pending Review
// included: claimDoc()/completeDoc() never checked Status, only
// AssignedTo/ClaimedDate, so a doc assigned straight at creation could
// get claimed/completed before anyone ever reviewed it) can be stuck.
if (status === 'Completed' || status === 'Returned') return;
const claimed = row[claimedCol];
const completed = row[completedCol];
// "Approved" (or anything else non-terminal) with neither date set falls
// back to Pending Review, not left alone — Approved no longer has its
// own tile now that Section Head/Chief approval is gone, so a doc stuck
// there with no other signal would count toward Total but vanish from
// every visible tile. Pending Review and "Approved, not yet claimed" are
// functionally identical now (assigned, work not started).
const correctStatus = completed ? 'Completed' : claimed ? 'Under Review' : 'Pending Review';
if (correctStatus !== status) {
sheet.getRange(2 + i, statusCol + 1).setValue(correctStatus);
changes.push({ row: 2 + i, old: status, new: correctStatus });
}
});
Logger.log(JSON.stringify(changes, null, 2));
Logger.log(changes.length + ' row(s) corrected.');
return changes;
}
// ── One-time cleanup: run manually from the Apps Script editor ─────────
// (select backfillLinkedIncomingCompletion, then Run) to complete incoming
// docs that were linked to an outgoing doc BEFORE addDoc() started
// auto-completing them — i.e. outgoing docs created while the "linked
// incoming document" field existed but this auto-complete behavior didn't
// yet. Safe to run more than once; only ever moves a row forward to
// Completed, never touches one that's already there.
function backfillLinkedIncomingCompletion(){
const outSheet = SS.getSheetByName(SHEET_OUT);
if (!outSheet) { Logger.log('No Outgoing sheet found.'); return []; }
const headers = getHeaders(outSheet);
const lastRow = outSheet.getLastRow();
if (lastRow < 2) { Logger.log('No data rows.'); return []; }
const linkCol = headers.indexOf('LinkedIncomingID');
if (linkCol === -1) { Logger.log('No LinkedIncomingID column found.'); return []; }
const linkedIds = outSheet.getRange(2, linkCol + 1, lastRow - 1, 1).getValues()
.map(row => row[0]).filter(Boolean);
const changes = [];
linkedIds.forEach(id => {
if (completeLinkedIncoming(id)) changes.push({ incomingId: id });
});
Logger.log(JSON.stringify(changes, null, 2));
Logger.log(changes.length + ' incoming row(s) marked Completed.');
return changes;
}
// ── Diagnostic: run manually from the Apps Script editor ───────────────
// (select findIncomingStatusAnomalies, then Run) when the incoming tile row
// doesn't tally with Total — that happens when a row's Status cell isn't
// EXACTLY one of the 5 canonical strings (Pending Review / Under Review /
// Approved / Returned / Completed), usually a blank cell, typo, or stray
// whitespace from a row added/edited directly in the sheet rather than
// through the app. Read-only — logs the offending rows so you can see what
// they actually contain and fix them by hand (there's no way to guess the
// RIGHT status for a row that's missing one).
function findIncomingStatusAnomalies(){
const sheet = SS.getSheetByName(SHEET_IN);
if (!sheet) { Logger.log('No Incoming sheet found.'); return []; }
const headers = getHeaders(sheet);
const lastRow = sheet.getLastRow();
if (lastRow < 2) { Logger.log('No data rows.'); return []; }
const KNOWN = ['Pending Review', 'Under Review', 'Approved', 'Returned', 'Completed'];
const rows = sheet.getRange(2, 1, lastRow - 1, headers.length).getValues();
const statusCol = headers.indexOf('Status');
const descCol = headers.indexOf('Description');
const trackingCol = headers.indexOf('TrackingNo');
if (statusCol === -1) { Logger.log('No Status column found.'); return []; }
const anomalies = [];
rows.forEach((row, i) => {
if (!row[descCol]) return; // skip genuinely blank rows — not a real document
const status = row[statusCol];
if (KNOWN.indexOf(status) === -1) {
anomalies.push({
row: 2 + i,
trackingNo: trackingCol > -1 ? row[trackingCol] : '',
description: descCol > -1 ? row[descCol] : '',
statusValue: JSON.stringify(status) // quoted so blank/whitespace-only values are visible in the log
});
}
});
Logger.log(JSON.stringify(anomalies, null, 2));
Logger.log(anomalies.length + ' row(s) with an unrecognized Status value.');
return anomalies;
}
// ── Outgoing two-stage approval (Phase 1 core workflow) ─────────────
// Draft -> Pending Section Head Review -> (Returned to Staff loop) ->
// Pending Chief Approval -> (Returned to Section Head loop) -> Approved -> Released
function sectionHeadReview(data) {
const sheet = SS.getSheetByName(SHEET_OUT);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
const approve = data.decision === 'approve';
const newStatus = approve ? 'Pending Chief Approval' : 'Returned to Staff';
writeByHeader(sheet, found.rowIndex, found.headers, {
Status: newStatus,
SectionHeadRemarks: data.remarks || '',
SectionHeadReviewedBy: data.by,
SectionHeadReviewedDate: data.date || todayISO()
});
logAudit(data.by, data.role || 'SectionHead', 'SectionHeadReview:' + (approve ? 'Approve' : 'Return'), 'outgoing', found.obj.ReferenceNo || String(data.rowId), data.remarks || '', data.now);
return _json({success:true});
}
function chiefApprove(data) {
const sheet = SS.getSheetByName(SHEET_OUT);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
const approve = data.decision === 'approve';
const newStatus = approve ? 'Approved' : 'Returned to Section Head';
writeByHeader(sheet, found.rowIndex, found.headers, {
Status: newStatus,
ChiefRemarks: data.remarks || '',
ChiefReviewedBy: data.by,
ChiefReviewedDate: data.date || todayISO()
});
logAudit(data.by, data.role || 'Chief', 'ChiefApprove:' + (approve ? 'Approve' : 'Return'), 'outgoing', found.obj.ReferenceNo || String(data.rowId), data.remarks || '', data.now);
return _json({success:true});
}
// Lets Staff resubmit a "Returned to Staff" doc (goes back to Section
// Head review) and lets a Section Head resubmit a "Returned to Section
// Head" doc (goes straight back to Chief approval) — the new status is
// derived from the CURRENT status server-side, not trusted from the client.
function resubmitDoc(data) {
const sheet = SS.getSheetByName(SHEET_OUT);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
const current = found.obj.Status;
let newStatus;
if (current === 'Returned to Staff') newStatus = 'Pending Section Head Review';
else if (current === 'Returned to Section Head') newStatus = 'Pending Chief Approval';
else return _json({error:'This document is not in a returned state and cannot be resubmitted.'});
writeByHeader(sheet, found.rowIndex, found.headers, {
Status: newStatus,
DatePrepared: data.date || found.obj.DatePrepared,
Description: data.desc || found.obj.Description,
DocType: data.type || found.obj.DocType,
Priority: data.priority || found.obj.Priority,
EncoderRemarks: data.encRem !== undefined ? data.encRem : found.obj.EncoderRemarks
});
logAudit(data.by, data.role || '', 'Resubmit', 'outgoing', found.obj.ReferenceNo || String(data.rowId), 'Resubmitted -> ' + newStatus, data.now);
return _json({success:true});
}
// Encoder-only: edits and resubmits one of THEIR OWN Returned incoming
// documents — puts it back into the active pipeline at Pending Review.
// Only valid from Returned (mirrors resubmitDoc()'s outgoing equivalent).
function resubmitIncomingDoc(data) {
const sheet = SS.getSheetByName(SHEET_IN);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
if (found.obj.Status !== 'Returned') return _json({error:'Only Returned documents can be updated and resubmitted.'});
writeByHeader(sheet, found.rowIndex, found.headers, {
Status: 'Pending Review',
DateReceived: data.date || found.obj.DateReceived,
Description: data.desc || found.obj.Description,
DocType: data.type || found.obj.DocType,
Priority: data.priority || found.obj.Priority,
// A wrong assignment is a common reason a doc gets returned in the
// first place, so this lets the Encoder add/replace it here too.
AssignedTo: data.assignedTo || found.obj.AssignedTo,
AssignedDate: data.assignedTo ? (data.assignedDate || todayISO()) : found.obj.AssignedDate,
EncoderRemarks: data.encRem !== undefined ? data.encRem : found.obj.EncoderRemarks
});
logAudit(data.by, data.role || 'Encoder', 'Resubmit', 'incoming', found.obj.TrackingNo || String(data.rowId), 'Resubmitted -> Pending Review', data.now);
return _json({success:true});
}
// Encoder-only: permanently deletes one of THEIR OWN Returned incoming
// documents — the only real delete in this app, so scoped tightly (only
// from Returned, the one terminal state that's actually safe to discard)
// rather than allowed from any status.
function deleteIncomingDoc(data) {
const sheet = SS.getSheetByName(SHEET_IN);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
if (found.obj.Status !== 'Returned') return _json({error:'Only Returned documents can be deleted.'});
const ref = found.obj.TrackingNo || String(data.rowId);
sheet.deleteRow(found.rowIndex);
logAudit(data.by, data.role || 'Encoder', 'Delete', 'incoming', ref, 'Deleted Returned document: ' + (found.obj.Description || ''), data.now);
return _json({success:true});
}
// Encoder-only: records the actual physical release of an Approved
// outgoing document. Only valid from Approved.
function releaseDoc(data) {
const sheet = SS.getSheetByName(SHEET_OUT);
const found = findRow(sheet, data.rowId);
if (!found) return _json({error:'Not found'});
if (found.obj.Status !== 'Approved') return _json({error:'Only Approved documents can be released.'});
writeByHeader(sheet, found.rowIndex, found.headers, {
Status: 'Released',
ReceivingOffice: data.receivingOffice || '',
ReceivingPerson: data.receivingPerson || '',
ModeOfTransmission: data.mode || '',
DateReleased: data.dateReleased || todayISO(),
TimeReleased: data.timeReleased || nowTimeStr()
// LinkedIncomingID is intentionally untouched here — it's captured once
// at submission time (see addDoc()) and must survive unchanged through
// Section Head/Chief review and Release, not get overwritten (or wiped
// blank) at this later stage.
});
logAudit(data.by, data.role || 'Encoder', 'Release', 'outgoing', found.obj.ReferenceNo || String(data.rowId), 'Released to ' + (data.receivingPerson || '') + ' (' + (data.receivingOffice || '') + ')', data.now);
return _json({success:true});
}
// ── Auto-archiving ───────────────────────────────────────────────────
// Runs automatically every Sunday ~11pm via a time-driven trigger (see
// installArchiveTrigger() below) — moves any document older than 1 year
// (by DatePrepared/DateReceived, REGARDLESS of status — even a still-open
// Pending/Under Review doc gets archived once it crosses a year old) out
// of the live Outgoing/Incoming sheets into their own separate archive
// sheets. This keeps the live sheets small as years of data pile up, since
// several things in this app (duplicate-checking in nextSequence(), every
// "getAll" load, the audit-anomaly finders) do full-column/full-sheet
// scans. Archived rows are NOT visible anywhere in the live app afterward
// — they exist only in the archive sheet itself, for manual lookup.
function archiveOldDocuments(){
const cutoff = new Date();
cutoff.setFullYear(cutoff.getFullYear() - 1);
const outCount = _archiveOldRows('outgoing', SHEET_OUT, 'Outgoing Archive', 'DatePrepared', cutoff);
const inCount = _archiveOldRows('incoming', SHEET_IN, 'Incoming Archive', 'DateReceived', cutoff);
Logger.log('Archived ' + outCount + ' outgoing, ' + inCount + ' incoming document(s) older than ' + cutoff.toDateString() + '.');
return { outgoing: outCount, incoming: inCount };
}
function _archiveOldRows(kind, sourceName, archiveName, dateCol, cutoff){
const source = SS.getSheetByName(sourceName);
if (!source) return 0;
const lastRow = source.getLastRow();
if (lastRow < 2) return 0;
const headers = getHeaders(source);
const dateIdx = headers.indexOf(dateCol);
if (dateIdx === -1) { Logger.log('No ' + dateCol + ' column found in ' + sourceName + '.'); return 0; }
// Create the archive sheet (mirroring the source's own headers) the
// first time this ever finds something to move.
let archive = SS.getSheetByName(archiveName);
const rows = source.getRange(2, 1, lastRow - 1, headers.length).getValues();
const toArchive = [];
rows.forEach((row, i) => {
const d = _parseDate(row[dateIdx]);
if (d && d < cutoff) toArchive.push({ sheetRow: 2 + i, values: row });
});
if (!toArchive.length) return 0;
if (!archive) {
archive = SS.insertSheet(archiveName);
archive.appendRow(headers);
}
// Append to the archive first, then delete from the source bottom-up —
// deleting top-down would shift the row numbers of not-yet-processed
// matches out from under this loop.
toArchive.forEach(({values}) => archive.appendRow(values));
toArchive.slice().reverse().forEach(({sheetRow}) => source.deleteRow(sheetRow));
logAudit('System', 'System', 'Archive', kind, '', toArchive.length + ' document(s) older than 1 year moved to "' + archiveName + '"', new Date().toISOString());
return toArchive.length;
}
// Accepts either a real Date (Sheets auto-coerced the cell) or a plain
// "yyyy-MM-dd" string, and returns a comparable Date either way.
function _parseDate(value){
if (value instanceof Date) return value;
if (!value) return null;
const d = new Date(value);
return isNaN(d.getTime()) ? null : d;
}
// ── One-time setup: run manually from the Apps Script editor ───────────
// (select installArchiveTrigger, then Run) to schedule archiveOldDocuments()
// to run automatically every Sunday around 11pm. Only needs to be run
// ONCE — the trigger persists across redeployments on its own. Safe to
// re-run: it clears any existing archive trigger first so you never end up
// with the job running twice.
function installArchiveTrigger(){
uninstallArchiveTrigger();
ScriptApp.newTrigger('archiveOldDocuments')
.timeBased()
.onWeekDay(ScriptApp.WeekDay.SUNDAY)
.atHour(23)
.create();
Logger.log('Archive trigger installed: archiveOldDocuments() will run every Sunday around 11pm.');
}
// Run manually if you ever want to stop the automatic archiving.
function uninstallArchiveTrigger(){
ScriptApp.getProjectTriggers().forEach(t => {
if (t.getHandlerFunction() === 'archiveOldDocuments') ScriptApp.deleteTrigger(t);
});
}
// ── Auto-archiving: Audit Log ───────────────────────────────────────
// Separate job, separate schedule/retention from document archiving above:
// runs automatically on the 1st of every month ~11pm (see
// installAuditArchiveTrigger() below), moving any AuditLog entry older
// than 6 months into its own "AuditLog Archive" sheet. Every create /
// assign / claim / complete / return / release / etc. writes a row here
// forever, and the Audit Trail tab reads the whole sheet on every load, so
// this keeps that screen fast long-term. Reuses the same _archiveOldRows()
// helper as the document archiving — the AuditLog is just another flat,
// dated table.
function archiveOldAuditLog(){
const cutoff = new Date();
cutoff.setMonth(cutoff.getMonth() - 6);
const count = _archiveOldRows('audit', SHEET_AUDIT, 'AuditLog Archive', 'Timestamp', cutoff);
Logger.log('Archived ' + count + ' audit log entry/entries older than ' + cutoff.toDateString() + '.');
return { audit: count };
}
// ── One-time setup: run manually from the Apps Script editor ───────────
// (select installAuditArchiveTrigger, then Run) to schedule
// archiveOldAuditLog() to run automatically on the 1st of every month
// around 11pm. Only needs to be run ONCE. Safe to re-run — clears any
// existing audit-archive trigger first.
function installAuditArchiveTrigger(){
uninstallAuditArchiveTrigger();
ScriptApp.newTrigger('archiveOldAuditLog')
.timeBased()
.onMonthDay(1)
.atHour(23)
.create();
Logger.log('Audit log archive trigger installed: archiveOldAuditLog() will run on the 1st of every month around 11pm.');
}
// Run manually if you ever want to stop the automatic audit log archiving.
function uninstallAuditArchiveTrigger(){
ScriptApp.getProjectTriggers().forEach(t => {
if (t.getHandlerFunction() === 'archiveOldAuditLog') ScriptApp.deleteTrigger(t);
});
}
Click Save, then Deploy → New deployment → Web app.
Set Execute as: Me, Who has access: Anyone. Click Deploy and copy the Web App URL.
Already set up (migrating from the older single-stage version)? This is a bigger update than usual — do these in order: (1) In the Outgoing tab, rename the header cell that currently says DateReleased to DatePrepared — it kept its old meaning ("date the document was prepared"), it's just renamed so the new DateReleased column (added at the end, meaning "date actually released by the Encoder") isn't ambiguous. (2) Add the new columns listed in Step 1 to the end of your Outgoing and Incoming tabs. (3) Add the new AuditLog tab. (4) Paste this updated script over your existing one. (5) Use Deploy → Manage deployments → Edit → New version so the same URL picks up the change. Existing rows keep working — old Admin/ProjectManager role values in the Users tab still sign in fine.