/**
* ============================================================
* ORGANIZED SYNERGY — CLIENT PORTAL APPS SCRIPT (FINAL)
* Handles: document status + updates feed
* One script per client engagement Google Sheet
* ============================================================
*
* SETUP (do once per engagement):
*
* 1. Open the client's Google Sheet
* 2. Extensions → Apps Script → paste this file → Save
* 3. Update SHEET_TABS below to match your tab names
* 4. Deploy → New deployment → Web App
* Execute as: Me | Who has access: Anyone
* 5. Copy the Web App URL → paste into portal HTML config
* (ENGAGEMENT.appsScriptUrl)
*
* UPDATING: after any change, Deploy → Manage deployments
* → Edit → Version: New version → Deploy
*
* GOOGLE SHEET STRUCTURE REQUIRED:
* ─────────────────────────────────────────────────────
* One tab per document form (auto-created when form connects)
* One tab named exactly "Updates" with columns:
* A: date (text, e.g. "April 14, 2026")
* B: message (text)
* C: type (Milestone | Reminder | Info | Note) — case insensitive
* Row 1 = headers, data starts row 2, newest first
* ─────────────────────────────────────────────────────
* ============================================================ */
/* ★ UPDATE for each engagement
Key = portal ITEMS[].key
Value = exact Google Sheet tab name
multiSubmit = true → returns row count instead of received/outstanding */
const SHEET_TABS = {
"form_990": { tab: "form_990", multiSubmit: false },
"annual_report": { tab: "annual_report", multiSubmit: false },
"org_budget": { tab: "org_budget", multiSubmit: false },
"program_budgets": { tab: "program_budgets", multiSubmit: true },
"grant_largest": { tab: "grant_largest", multiSubmit: false },
"grant_recent_1": { tab: "grant_recent_1", multiSubmit: false },
"grant_recent_2": { tab: "grant_recent_2", multiSubmit: false },
"marketing": { tab: "marketing", multiSubmit: true },
"lm_ed": { tab: "lm_ed", multiSubmit: false },
"lm_pd1": { tab: "lm_pd1", multiSubmit: false },
"lm_pd2": { tab: "lm_pd2", multiSubmit: false },
"stakeholders": { tab: "stakeholders", multiSubmit: true },
"sched_prelim": { tab: "sched_prelim", multiSubmit: false },
};
/* Tab name for the updates feed */
const UPDATES_TAB = "Updates";
/**
* Main GET handler — routes by action param
*/
function doGet(e) {
const action = (e.parameter && e.parameter.action) || 'getStatus';
let result;
if (action === 'getStatus') result = getDocumentStatus();
else if (action === 'getUpdates') result = getUpdates();
else if (action === 'getStakeholders') result = getStakeholders();
else if (action === 'getAll') result = { status: getDocumentStatus(), updates: getUpdates().updates };
else result = { error: 'Unknown action: ' + action };
return ContentService
.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
/**
* Returns status for every document key.
* Single-submit rows: 'received' or 'outstanding'
* Multi-submit rows: count of submissions (number) or 0
*/
function getDocumentStatus() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const result = {};
for (const [key, config] of Object.entries(SHEET_TABS)) {
try {
const tabName = config.tab;
const isMulti = config.multiSubmit;
const override = getOverride(key);
if (override !== null) {
result[key] = override;
continue;
}
const sheet = ss.getSheetByName(tabName);
if (!sheet || sheet.getLastRow() < 2) {
result[key] = isMulti ? 0 : 'outstanding';
} else {
const rowCount = sheet.getLastRow() - 1; /* subtract header row */
result[key] = isMulti ? rowCount : 'received';
}
} catch(err) {
result[key] = 'outstanding';
Logger.log('Error checking ' + key + ': ' + err);
}
}
result['_checked_at'] = new Date().toISOString();
return result;
}
/**
* Returns updates feed from the Updates tab
* Newest first (reads top-down, so put newest rows at top of sheet)
*/
function getUpdates() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(UPDATES_TAB);
if (!sheet || sheet.getLastRow() < 2) {
return { updates: [] };
}
const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues();
const updates = rows
.filter(r => r[0] && r[1])
.map(r => ({
date: String(r[0]),
msg: String(r[1]),
type: String(r[2] || 'info'),
}));
return { updates, _checked_at: new Date().toISOString() };
}
/**
* Check _overrides tab for manual status overrides
* Returns the override value or null if none exists
*/
function getOverride(key) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('_overrides');
if (!sheet || sheet.getLastRow() < 2) return null;
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();
for (const row of data) {
if (row[0] === key && row[1]) return String(row[1]);
}
return null;
}
/**
* Manually mark any item as received or outstanding
* Use from Apps Script editor when needed:
* manualOverride("form_990", "received")
* manualOverride("sched_working", "received")
*/
function manualOverride(key, status) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName('_overrides');
if (!sheet) {
sheet = ss.insertSheet('_overrides');
sheet.appendRow(['key', 'status', 'updated_at']);
}
const data = sheet.getLastRow() > 1
? sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues()
: [];
for (let i = 0; i < data.length; i++) {
if (data[i][0] === key) {
sheet.getRange(i + 2, 2).setValue(status);
sheet.getRange(i + 2, 3).setValue(new Date().toISOString());
Logger.log('Updated: ' + key + ' → ' + status);
return;
}
}
sheet.appendRow([key, status, new Date().toISOString()]);
Logger.log('Added: ' + key + ' → ' + status);
}
/**
* Add an update to the feed programmatically
* Use from Apps Script editor:
* addUpdate("April 20, 2026", "Working session confirmed for April 29.", "milestone")
* Types: milestone | reminder | info | note
*/
function addUpdate(date, message, type) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName(UPDATES_TAB);
if (!sheet) {
sheet = ss.insertSheet(UPDATES_TAB);
sheet.appendRow(['date', 'message', 'type']);
}
/* Insert after header row so newest appears first */
sheet.insertRowAfter(1);
sheet.getRange(2, 1, 1, 3).setValues([[date, message, type || 'info']]);
Logger.log('Update added: ' + message);
}
/**
* POST handler — receives stakeholder data from the portal
* Called when client submits the stakeholder form
*/
function doPost(e) {
try {
const action = (e.parameter && e.parameter.action) || 'addStakeholders';
const body = JSON.parse(e.postData.contents);
let result;
if (action === 'addStakeholders') {
result = addStakeholders(body.stakeholders || []);
} else {
result = { error: 'Unknown POST action: ' + action };
}
return ContentService
.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
} catch(err) {
return ContentService
.createTextOutput(JSON.stringify({ error: String(err) }))
.setMimeType(ContentService.MimeType.JSON);
}
}
/**
* Returns all submitted stakeholders from the stakeholders tab
*/
function getStakeholders() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('stakeholders');
if (!sheet || sheet.getLastRow() < 2) return { stakeholders: [] };
const rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, 5).getValues();
const stakeholders = rows
.filter(r => r[1])
.map(r => ({
submittedAt: String(r[0] || ''),
name: String(r[1] || ''),
role: String(r[2] || ''),
email: String(r[3] || ''),
relationship: String(r[4] || ''),
}));
return { stakeholders, _checked_at: new Date().toISOString() };
}
/**
* Writes stakeholder rows to the stakeholders tab
* Each entry: { name, role, email }
* Returns count of rows written
*/
function addStakeholders(stakeholders) {
if (!stakeholders || stakeholders.length === 0) {
return { written: 0 };
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName('stakeholders');
/* Create tab with headers if it doesn't exist */
if (!sheet) {
sheet = ss.insertSheet('stakeholders');
sheet.appendRow(['Submitted at', 'Full name', 'Title / role', 'Email address', 'Relationship']);
sheet.getRange(1, 1, 1, 5).setFontWeight('bold');
} else if (sheet.getLastRow() < 1) {
sheet.appendRow(['Submitted at', 'Full name', 'Title / role', 'Email address', 'Relationship']);
sheet.getRange(1, 1, 1, 5).setFontWeight('bold');
}
const timestamp = new Date().toLocaleString('en-US', { timeZone: 'America/New_York' });
const rows = stakeholders.map(s => [
timestamp,
String(s.name || '').trim(),
String(s.role || '').trim(),
String(s.email || '').trim(),
String(s.relationship || '').trim(),
]);
sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 5).setValues(rows);
Logger.log('Stakeholders written: ' + rows.length);
return { written: rows.length };
}