Free Resource
Private Practice Set-Up Guide
Two Google Apps Scripts to build your complete practice infrastructure in under 60 seconds. Run Step 1 once to create your Google Drive folder structure and all your documents. Then open your Client_Index spreadsheet and install Step 2 to get the Practice Tools menu.
Step 01
Drive Setup
Go to script.google.com, create a new project, paste this script, fill in your details in the CONFIG section, then click Run → runDriveSetup.
Step 1 — Drive Setup (run once from script.google.com)
/**
* ============================================================
* PRACTICE INFRASTRUCTURE — STEP 1: DRIVE SETUP
* Version 3.2
* ============================================================
* Run this ONCE from script.google.com to build your
* complete Google Drive folder structure and all documents.
*
* BEFORE RUNNING:
* 1. Fill in your details in the CONFIGURATION section below
* 2. Click Run → runDriveSetup
* 3. Approve permissions when prompted
* 4. Wait ~60 seconds for everything to build
* 5. Open Google Drive — find your Practice Infrastructure folder
*
* THEN: Open the Client_Index spreadsheet and follow the
* instructions inside to install the Practice Tools menu.
*
* Run this script ONCE only. Running again creates duplicates.
* ============================================================
*/
// ============================================================
// CONFIGURATION — Fill in before running
// ============================================================
var CONFIG = {
practiceName : "My Practice", // e.g. "Sarah Jones Therapy"
therapistName : "Your Name", // e.g. "Sarah Jones"
practiceEmail : "hello@mypractice.com", // Your Workspace email
country : "UK", // "UK", "Ireland" or "US"
defaultFee : 80, // Your standard session fee (number only)
currency : "£", // "£", "€" or "$"
sessionLength : 50, // Standard session length in minutes
version : "3.2"
};
var SETUP_DATE = new Date().toDateString();
// ============================================================
// MAIN — Run this
// ============================================================
function runDriveSetup() {
log("================================================");
log("Practice Infrastructure Setup v" + CONFIG.version);
log("Practice: " + CONFIG.practiceName);
log("Country: " + CONFIG.country);
log("Date: " + SETUP_DATE);
log("================================================");
var root = DriveApp.getRootFolder();
var practiceFolder = createFolder(root, "🏥 " + CONFIG.practiceName + " — Practice Infrastructure");
// Top level folders
var clinical = createFolder(practiceFolder, "1_Clinical_Records");
var admin = createFolder(practiceFolder, "2_Administrative");
var finance = createFolder(practiceFolder, "3_Finance");
var templates = createFolder(practiceFolder, "4_Templates_Policies");
var archives = createFolder(practiceFolder, "5_Archives");
// Build each section
buildStartHere(practiceFolder);
buildClinical(clinical);
buildAdministrative(admin, practiceFolder);
buildFinance(finance);
buildTemplates(templates);
buildArchives(archives);
log("================================================");
log("✅ DRIVE SETUP COMPLETE");
log("Next step: Open Client_Index in 2_Administrative");
log("Go to Extensions → Apps Script → paste STEP2 script");
log("================================================");
}
// ============================================================
// ⭐ START HERE
// ============================================================
function buildStartHere(parent) {
var doc = DocumentApp.create("⭐ START HERE — Your Practice Infrastructure Guide");
var b = doc.getBody();
h1(b, "Welcome to Your Practice Infrastructure");
b.appendParagraph("Practice: " + CONFIG.practiceName);
b.appendParagraph("Therapist: " + CONFIG.therapistName);
b.appendParagraph("Set up: " + SETUP_DATE + " | Version: " + CONFIG.version);
b.appendParagraph("");
h2(b, "What This System Is");
b.appendParagraph("This Google Drive folder is your complete practice management system. Everything related to your practice — client records, session notes, finance, policies and templates — lives here in one secure, organised place in your own Google Workspace account. Nobody else has access unless you grant it.");
b.appendParagraph("");
h2(b, "Your 5 Folders");
bold(b, "📁 1_Clinical_Records");
b.appendParagraph("One folder per client. Session notes, consent forms, intake forms and correspondence. Your most sensitive folder — never share publicly.");
b.appendParagraph("");
bold(b, "📁 2_Administrative");
b.appendParagraph("Your Client Index spreadsheet — the control centre for your entire practice. Contains client records, session log, billing, supervision log, CPD log, income dashboard and all automation tools.");
b.appendParagraph("");
bold(b, "📁 3_Finance");
b.appendParagraph("Invoices, receipts, Stripe summaries and tax documents. Safe to share with an accountant — this folder only, never Clinical Records.");
b.appendParagraph("");
bold(b, "📁 4_Templates_Policies");
b.appendParagraph("Consent form, privacy notice, intake form, session note, superbill, insurance receipt, referral letter, ending summary and all compliance policies. Always copy — never write in the originals.");
b.appendParagraph("");
bold(b, "📁 5_Archives");
b.appendParagraph("Closed client folders. Move here when therapy ends. Records stay until retention deadline, then permanently deleted and logged.");
b.appendParagraph("");
h2(b, "Your Two-Step Setup");
bold(b, "Step 1 — Drive Setup (done)");
b.appendParagraph("Your folder structure and all documents have been created.");
b.appendParagraph("");
bold(b, "Step 2 — Install Practice Tools Menu");
b.appendParagraph("Open 2_Administrative → Client_Index. Go to Extensions → Apps Script. Paste the STEP2 script provided by your practice support. Click Save, then Run → installPracticeTools. Reload the spreadsheet — you will see a Practice Tools menu appear.");
b.appendParagraph("");
b.appendParagraph("Once installed, use Practice Tools for everything: adding clients, logging sessions, generating superbills, closing cases and viewing your income dashboard.");
b.appendParagraph("");
h2(b, "Daily Workflow");
b.appendParagraph("After each session: Practice Tools → Log Session");
b.appendParagraph("New client: Practice Tools → Add New Client");
b.appendParagraph("End of month: Practice Tools → Generate Superbill / Invoice");
b.appendParagraph("Closing a case: Practice Tools → Close Client Case");
b.appendParagraph("Annual review: 4_Templates_Policies → Policies → Annual Compliance Checklist");
b.appendParagraph("");
h2(b, "Security Rules");
b.appendParagraph("✅ Always use your Workspace email — never personal Gmail — for client communication");
b.appendParagraph("✅ Keep two-factor authentication enabled on your Google account at all times");
b.appendParagraph("✅ Never share client folders publicly or via a shareable link");
b.appendParagraph("✅ Never discuss clinical content via WhatsApp, SMS or social media");
b.appendParagraph("✅ If you suspect unauthorised access, change your password immediately");
b.appendParagraph("");
h2(b, "If a Data Breach Occurs");
b.appendParagraph("1. Note date and time of discovery");
b.appendParagraph("2. Secure your account — change password, check active sessions in Google");
b.appendParagraph("3. Assess what data may be affected");
b.appendParagraph("4. Notify supervisory authority within 72 hours if personal data was involved:");
b.appendParagraph(" UK: ico.org.uk | Ireland: dataprotection.ie | US: hhs.gov/hipaa");
b.appendParagraph("5. Notify affected clients without undue delay if they are at risk");
b.appendParagraph("6. Document everything in 2_Administrative → Data_Breach_Log");
b.appendParagraph("");
b.appendParagraph("Last updated: " + SETUP_DATE);
doc.saveAndClose();
moveFile(doc.getId(), parent);
log("Created: ⭐ START HERE");
}
// ============================================================
// 1. CLINICAL RECORDS
// ============================================================
function buildClinical(parent) {
var example = createFolder(parent, "C001-EXAMPLE — Copy this folder for each new client");
createFolder(example, "01_Admin");
createFolder(example, "02_Consent_Forms");
createFolder(example, "03_Intake_Forms");
createFolder(example, "04_Session_Notes");
createFolder(example, "05_Client_Materials");
createFolder(example, "06_Correspondence");
var doc = DocumentApp.create("📋 Clinical Records — Quick Reference");
var b = doc.getBody();
h1(b, "Clinical Records — Quick Reference");
b.appendParagraph("One sub-folder per client, named: ClientCode-Initials");
b.appendParagraph("Example: C002-AB = client code C002, initials A.B.");
b.appendParagraph("");
bold(b, "File naming convention:");
b.appendParagraph("YYYY-MM-DD_C001_SessionNote");
b.appendParagraph("YYYY-MM-DD_C001_ConsentForm");
b.appendParagraph("YYYY-MM-DD_C001_IntakeForm");
b.appendParagraph("");
bold(b, "Sub-folders:");
b.appendParagraph("01_Admin — referral letters, appointment log");
b.appendParagraph("02_Consent_Forms — signed consent and privacy notice");
b.appendParagraph("03_Intake_Forms — completed intake responses");
b.appendParagraph("04_Session_Notes — one note per session, dated");
b.appendParagraph("05_Client_Materials — resources and worksheets");
b.appendParagraph("06_Correspondence — emails and letters");
b.appendParagraph("");
b.appendParagraph("⚠️ Special category health data. Never share publicly. Never move to a personal account.");
doc.saveAndClose();
moveFile(doc.getId(), parent);
log("Created: Clinical Records");
}
// ============================================================
// 2. ADMINISTRATIVE — Folder + Client Index Spreadsheet
// ============================================================
function buildAdministrative(parent, practiceFolder) {
createFolder(parent, "Practice_Policies");
createFolder(parent, "Insurance_Referrals");
createFolder(parent, "Forms_Not_Tied_To_Client");
createFolder(parent, "Subject_Access_Requests");
createFolder(parent, "Data_Breach_Log");
var ss = SpreadsheetApp.create("Client_Index — " + CONFIG.practiceName);
// ── Tab: Settings ──
buildSettingsTab(ss);
// ── Tab: Clients ──
buildClientsTab(ss);
// ── Tab: Waitlist ──
buildWaitlistTab(ss);
// ── Tab: Session_Log ──
buildSessionLogTab(ss);
// ── Tab: Income_Dashboard ──
buildIncomeDashboardTab(ss);
// ── Tab: Supervision_Log ──
buildSupervisionLogTab(ss);
// ── Tab: CPD_Log ──
buildCPDLogTab(ss);
// ── Tab: DNA_Log ──
buildDNALogTab(ss);
// ── Tab: Deletion_Log ──
buildDeletionLogTab(ss);
// ── Tab: SAR_Log ──
buildSARLogTab(ss);
// ── Tab: How To Use ──
buildHowToUseTab(ss);
// Remove default blank sheet if present
try {
var defaultSheet = ss.getSheetByName("Sheet1");
if (defaultSheet) ss.deleteSheet(defaultSheet);
} catch(e) {}
// Move to Administrative folder
moveFile(ss.getId(), parent);
// Build SAR procedure doc
buildSARProcedure(parent);
log("Created: Administrative folder and Client Index");
}
function buildSettingsTab(ss) {
var s = ss.insertSheet("⚙️ Settings");
s.getRange("A1").setValue("PRACTICE SETTINGS").setFontWeight("bold").setFontSize(14);
s.getRange("A2").setValue("Edit these values to configure your practice. Do not rename this tab.");
s.getRange("A2").setFontStyle("italic").setFontColor("#888888");
var settings = [
["Practice Name", CONFIG.practiceName],
["Therapist Name", CONFIG.therapistName],
["Practice Email", CONFIG.practiceEmail],
["Country", CONFIG.country],
["Default Session Fee", CONFIG.defaultFee],
["Currency Symbol", CONFIG.currency],
["Standard Session Length (mins)", CONFIG.sessionLength],
["Script Version", CONFIG.version],
["Setup Date", SETUP_DATE]
];
for (var i = 0; i < settings.length; i++) {
s.getRange(i + 4, 1).setValue(settings[i][0]).setFontWeight("bold");
s.getRange(i + 4, 2).setValue(settings[i][1]);
}
s.setColumnWidth(1, 260);
s.setColumnWidth(2, 300);
s.getRange("A4:A12").setBackground("#f0f4f8");
}
function buildClientsTab(ss) {
var s = ss.insertSheet("👥 Clients");
var headers = [
"Client Code","First Name","Last Name","Email","Phone",
"Date of Birth","Start Date","End Date","Status",
"Default Fee (" + CONFIG.currency + ")","Retention Deadline",
"Folder Link","Referral Source","Admin Notes"
];
styleHeaderRow(s, headers, "#1a3a5c");
// No example row — starts clean so Income Dashboard is accurate from day one
var widths = [100,110,110,200,140,120,110,110,100,130,140,260,140,220];
setColumnWidths(s, widths);
s.setFrozenRows(1);
// Status dropdown
applyDropdown(s, 2, 9, 200, ["Active","Closed","On Hold","Enquiry","Waitlist"]);
// Closed = grey
applyConditionalFormat(s, "Closed", "#f5f5f5", "#999999", 2, 1, 200, headers.length);
}
function buildWaitlistTab(ss) {
var s = ss.insertSheet("⏳ Waitlist");
var headers = [
"Name","Email","Phone","Date Added","Presenting Issue (brief)",
"Preferred Days/Times","Follow-up Date","Status","Notes"
];
styleHeaderRow(s, headers, "#3a5c1a");
var widths = [160,200,140,120,220,180,130,120,220];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 8, 100, ["Waiting","Offered Slot","Started","Declined","Unavailable"]);
addNote(s, "A1", "Track prospective clients here before they become active. Never record clinical detail — brief presenting issue only.");
}
function buildSessionLogTab(ss) {
var s = ss.insertSheet("📅 Session_Log");
var headers = [
"Date","Client Code","Session No","Duration (mins)",
"Format","Fee (" + CONFIG.currency + ")","Paid","Payment Date",
"Invoice Ref","CPT Code (US)","Notes"
];
styleHeaderRow(s, headers, "#1a3a5c");
var widths = [120,110,100,140,130,120,80,130,130,130,220];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 5, 500, ["Video — Google Meet","In Person","Phone","Group"]);
applyDropdown(s, 2, 7, 500, ["Yes","No","Waived"]);
// No example row — starts clean so Income Dashboard is accurate from day one
addNote(s, "A1", "Log every session here. CPT Code is for US therapists only — leave blank if not applicable. Common codes: 90837 (60 min), 90834 (45 min), 90832 (30 min).");
}
function buildIncomeDashboardTab(ss) {
var s = ss.insertSheet("📊 Income_Dashboard");
s.getRange("A1").setValue("INCOME DASHBOARD").setFontWeight("bold").setFontSize(16);
s.getRange("A2").setValue("Auto-calculated from Session_Log. Do not edit this sheet directly.");
s.getRange("A2").setFontStyle("italic").setFontColor("#888888");
s.getRange("A1:F1").merge().setBackground("#1a3a5c").setFontColor("#ffffff");
// ── Year selector ──
s.getRange("D4").setValue("VIEWING YEAR").setFontWeight("bold").setFontSize(11);
s.getRange("E4").setValue(new Date().getFullYear());
s.getRange("E4").setFontSize(14).setFontWeight("bold").setBackground("#fff3cd").setHorizontalAlignment("center");
s.getRange("F4").setValue("← edit this to see another year").setFontStyle("italic").setFontColor("#888888").setFontSize(9);
s.setColumnWidth(5, 120);
s.setColumnWidth(6, 220);
// Current month section
s.getRange("A4").setValue("CURRENT MONTH").setFontWeight("bold").setFontSize(12);
s.getRange("A5").setValue("Sessions completed:");
s.getRange("A6").setValue("Income earned (" + CONFIG.currency + "):");
s.getRange("A7").setValue("Outstanding payments (" + CONFIG.currency + "):");
s.getRange("A8").setValue("Sessions unpaid:");
// Formulas for current month (pull from Session_Log)
s.getRange("B5").setFormula('=COUNTIFS(\'📅 Session_Log\'!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),\'📅 Session_Log\'!A:A,"<="&EOMONTH(TODAY(),0),\'📅 Session_Log\'!B:B,"<>Client Code")');
s.getRange("B6").setFormula('=SUMPRODUCT((MONTH(\'📅 Session_Log\'!A2:A500)=MONTH(TODAY()))*(YEAR(\'📅 Session_Log\'!A2:A500)=YEAR(TODAY()))*(\'📅 Session_Log\'!G2:G500="Yes")*(\'📅 Session_Log\'!F2:F500))');
s.getRange("B7").setFormula('=SUMPRODUCT((MONTH(\'📅 Session_Log\'!A2:A500)=MONTH(TODAY()))*(YEAR(\'📅 Session_Log\'!A2:A500)=YEAR(TODAY()))*(\'📅 Session_Log\'!G2:G500="No")*(\'📅 Session_Log\'!F2:F500))');
s.getRange("B8").setFormula('=COUNTIFS(\'📅 Session_Log\'!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1),\'📅 Session_Log\'!A:A,"<="&EOMONTH(TODAY(),0),\'📅 Session_Log\'!G:G,"No",\'📅 Session_Log\'!B:B,"<>Client Code")');
// Year summary — all formulas reference $E$4 (year selector)
s.getRange("A10").setValue("YEAR SUMMARY").setFontWeight("bold").setFontSize(12);
s.getRange("A11").setValue("Sessions completed:");
s.getRange("A12").setValue("Total income (" + CONFIG.currency + "):");
s.getRange("A13").setValue("Outstanding payments (" + CONFIG.currency + "):");
s.getRange("A14").setValue("Active clients:");
s.getRange("A15").setValue("Average fee per session (" + CONFIG.currency + "):");
s.getRange("B11").setFormula('=COUNTIFS(\'📅 Session_Log\'!A:A,">="&DATE($E$4,1,1),\'📅 Session_Log\'!A:A,"<="&DATE($E$4,12,31),\'📅 Session_Log\'!B:B,"<>Client Code")');
s.getRange("B12").setFormula('=SUMPRODUCT((YEAR(\'📅 Session_Log\'!A2:A500)=$E$4)*(\'📅 Session_Log\'!G2:G500="Yes")*(\'📅 Session_Log\'!F2:F500))');
s.getRange("B13").setFormula('=SUMPRODUCT((YEAR(\'📅 Session_Log\'!A2:A500)=$E$4)*(\'📅 Session_Log\'!G2:G500="No")*(\'📅 Session_Log\'!F2:F500))');
s.getRange("B14").setFormula('=COUNTIF(\'👥 Clients\'!I:I,"Active")');
s.getRange("B15").setFormula('=IFERROR(B12/B11,0)');
// Monthly breakdown — references $E$4
s.getRange("A17").setValue("MONTHLY BREAKDOWN").setFontWeight("bold").setFontSize(12);
s.getRange("B17").setFormula('="— "&$E$4').setFontWeight("bold").setFontSize(12);
var monthHeaders = ["Month","Sessions","Income (" + CONFIG.currency + ")","Outstanding (" + CONFIG.currency + ")","DNAs"];
for (var i = 0; i < monthHeaders.length; i++) {
s.getRange(18, i + 1).setValue(monthHeaders[i]).setFontWeight("bold").setBackground("#e8f0fe");
}
var months = ["January","February","March","April","May","June",
"July","August","September","October","November","December"];
for (var m = 0; m < 12; m++) {
var row = 19 + m;
s.getRange(row, 1).setValue(months[m]);
s.getRange(row, 2).setFormula('=COUNTIFS(\'📅 Session_Log\'!A:A,">="&DATE($E$4,' + (m+1) + ',1),\'📅 Session_Log\'!A:A,"<="&EOMONTH(DATE($E$4,' + (m+1) + ',1),0),\'📅 Session_Log\'!B:B,"<>Client Code")');
s.getRange(row, 3).setFormula('=SUMPRODUCT((MONTH(\'📅 Session_Log\'!A2:A500)=' + (m+1) + ')*(YEAR(\'📅 Session_Log\'!A2:A500)=$E$4)*(\'📅 Session_Log\'!G2:G500="Yes")*(\'📅 Session_Log\'!F2:F500))');
s.getRange(row, 4).setFormula('=SUMPRODUCT((MONTH(\'📅 Session_Log\'!A2:A500)=' + (m+1) + ')*(YEAR(\'📅 Session_Log\'!A2:A500)=$E$4)*(\'📅 Session_Log\'!G2:G500="No")*(\'📅 Session_Log\'!F2:F500))');
s.getRange(row, 5).setFormula('=COUNTIFS(\'📋 DNA_Log\'!A:A,">="&DATE($E$4,' + (m+1) + ',1),\'📋 DNA_Log\'!A:A,"<="&EOMONTH(DATE($E$4,' + (m+1) + ',1),0))');
}
s.setColumnWidth(1, 160);
s.setColumnWidth(2, 160);
s.setColumnWidth(3, 180);
s.setColumnWidth(4, 200);
s.setColumnWidth(5, 120);
// Style value cells
s.getRange("B5:B8").setBackground("#e8f4e8").setFontWeight("bold");
s.getRange("B11:B15").setBackground("#e8f4e8").setFontWeight("bold");
}
function buildSupervisionLogTab(ss) {
var s = ss.insertSheet("🎓 Supervision_Log");
var headers = [
"Date","Supervisor Name","Duration (hrs)","Format",
"Cases Discussed (codes only)","Key Themes","Actions Agreed","Signature/Confirm"
];
styleHeaderRow(s, headers, "#3a1a5c");
var widths = [120,180,120,130,220,220,220,160];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 4, 200, ["In Person","Video","Phone","Group"]);
addNote(s, "A1", "Record all supervision here. Use client codes only — never client names. Required by BACP, IACP and most professional bodies. Keep this log as evidence of ongoing supervision for registration renewal.");
}
function buildCPDLogTab(ss) {
var s = ss.insertSheet("📚 CPD_Log");
var headers = [
"Date","Activity Title","Provider","Type",
"Hours","Notes","Certificate/Evidence"
];
styleHeaderRow(s, headers, "#5c3a1a");
var widths = [120,240,180,160,80,240,200];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 4, 200, ["Training Course","Workshop","Webinar","Reading","Personal Therapy","Supervision","Peer Consultation","Conference","Other"]);
// CPD total
s.getRange("I1").setValue("Total CPD Hours (this year):");
s.getRange("J1").setFormula('=SUMPRODUCT((YEAR(A2:A500)=YEAR(TODAY()))*(E2:E500))');
s.getRange("I1").setFontWeight("bold");
s.getRange("J1").setBackground("#e8f4e8").setFontWeight("bold");
addNote(s, "A1", "Log all CPD activity here. BACP requires 30 hours per year. IACP requires 30 hours. The total at top right calculates automatically for the current year.");
}
function buildDNALogTab(ss) {
var s = ss.insertSheet("📋 DNA_Log");
var headers = [
"Date","Client Code","Session No","Contact Attempted",
"Client Response","Action Taken","Safeguarding Concern","Notes"
];
styleHeaderRow(s, headers, "#5c1a1a");
var widths = [120,110,100,160,160,200,160,220];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 4, 200, ["Yes — Email","Yes — Phone","Yes — Both","No contact attempted"]);
applyDropdown(s, 2, 7, 200, ["No","Yes — noted","Yes — escalated"]);
addNote(s, "A1", "Log every Did Not Attend (DNA) and late cancellation here. Important for safeguarding — if a vulnerable client repeatedly disappears, this record supports any duty of care action taken.");
}
function buildDeletionLogTab(ss) {
var s = ss.insertSheet("🗑️ Deletion_Log");
var headers = [
"Client Code","Retention Deadline","Deletion Date","Deleted By","Method","Confirmation"
];
styleHeaderRow(s, headers, "#3a3a3a");
var widths = [120,160,140,160,200,280];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 5, 200, ["Google Drive permanent delete + Trash emptied","Manual deletion confirmed"]);
addNote(s, "A1", "Log every client record deletion here. This log must be kept permanently — even after the client records themselves are deleted. It is your audit trail for GDPR compliance.");
}
function buildSARLogTab(ss) {
var s = ss.insertSheet("📨 SAR_Log");
var headers = [
"Client Code","Request Date","Response Due (30 days)","Response Sent","Method","Notes"
];
styleHeaderRow(s, headers, "#1a3a5c");
var widths = [120,140,180,140,160,280];
setColumnWidths(s, widths);
s.setFrozenRows(1);
applyDropdown(s, 2, 5, 200, ["Workspace email","Secure folder share","Post"]);
addNote(s, "A1", "Log all Subject Access Requests here. You have 30 days to respond. See SAR Procedure document in 2_Administrative for step-by-step instructions.");
}
function buildHowToUseTab(ss) {
var s = ss.insertSheet("ℹ️ How To Use");
s.getRange("A1").setValue("CLIENT INDEX — HOW TO USE").setFontWeight("bold").setFontSize(14);
var content = [
[""],
["⚙️ SETTINGS TAB"],
["Edit your practice details here. These values are used throughout the system."],
[""],
["👥 CLIENTS TAB"],
["One row per client. Assign codes sequentially: C001, C002, C003 etc."],
["Never reuse a code, even after a client has been deleted."],
["Default Fee auto-populates session log when you add a new session via Practice Tools."],
["Retention Deadline = End Date + 7 years. Add when closing a case."],
["Paste the Google Drive folder link in Folder Link for one-click access."],
[""],
["⏳ WAITLIST TAB"],
["Track prospective clients before they start. Brief presenting issue only — no clinical detail."],
[""],
["📅 SESSION_LOG TAB"],
["Log every session here. Use Practice Tools → Log Session for a guided form."],
["CPT Code is for US therapists only. Common codes: 90837 (60 min), 90834 (45 min), 90832 (30 min)."],
["Fee auto-fills from the client's Default Fee in the Clients tab."],
[""],
["📊 INCOME_DASHBOARD TAB"],
["Auto-calculated from Session_Log. Never edit this tab directly."],
["Shows current month totals and year-to-date breakdown."],
[""],
["🎓 SUPERVISION_LOG TAB"],
["Record all supervision. Use client codes only — never client names."],
["Required by BACP (1.5 hrs/month min), IACP and most professional bodies."],
[""],
["📚 CPD_LOG TAB"],
["Log all CPD activity. Total hours for current year calculated automatically."],
["BACP and IACP require 30 hours per year."],
[""],
["📋 DNA_LOG TAB"],
["Log every Did Not Attend and late cancellation."],
["Flag safeguarding concerns — important if a vulnerable client stops responding."],
[""],
["🗑️ DELETION_LOG TAB"],
["Log every permanent deletion of client records. Keep this log permanently."],
[""],
["📨 SAR_LOG TAB"],
["Log all Subject Access Requests. 30-day response deadline."],
[""],
["IMPORTANT"],
["This spreadsheet contains personal data. Keep it private."],
["Do not share with anyone not authorised to see client information."],
];
for (var i = 0; i < content.length; i++) {
s.getRange(i + 2, 1).setValue(content[i][0]);
if (content[i][0] && content[i][0].match(/^[⚙📅📊🎓📚📋🗑📨👥⏳ℹ]/)) {
s.getRange(i + 2, 1).setFontWeight("bold").setFontSize(11);
}
}
s.setColumnWidth(1, 650);
}
function buildSARProcedure(parent) {
var doc = DocumentApp.create("Procedure — Subject Access Requests");
var b = doc.getBody();
h1(b, "Subject Access Request (SAR) Procedure");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Last reviewed: " + SETUP_DATE);
b.appendParagraph("");
h2(b, "What is a Subject Access Request?");
b.appendParagraph("Under GDPR (UK/Ireland) and HIPAA (US), clients have the right to request a copy of all personal data you hold about them. You must respond within 30 days at no charge.");
b.appendParagraph("");
h2(b, "Step-by-Step Response");
b.appendParagraph("1. Verify identity — confirm name, date of birth and contact email before releasing any data.");
b.appendParagraph("2. Log the request — open Client_Index → SAR_Log tab. Add client code, request date and 30-day deadline.");
b.appendParagraph("3. Gather the data — go to their folder in 1_Clinical_Records. Collect all documents containing their personal data.");
b.appendParagraph("4. Send via Workspace email — compile into PDF or secure folder share. Never use personal Gmail or WhatsApp.");
b.appendParagraph("5. Log the response — update SAR_Log with the date sent.");
b.appendParagraph("");
h2(b, "What You Are Not Required to Provide");
b.appendParagraph("• Personal reflective or supervision notes not held in the client file");
b.appendParagraph("• Data about third parties mentioned in notes");
b.appendParagraph("• Information where disclosure would prejudice legal proceedings");
b.appendParagraph("");
b.appendParagraph("If in doubt, consult your professional body or a GDPR adviser before responding.");
doc.saveAndClose();
moveFile(doc.getId(), parent);
}
// ============================================================
// 3. FINANCE
// ============================================================
function buildFinance(parent) {
createFolder(parent, "Invoices");
createFolder(parent, "Receipts");
createFolder(parent, "Tax_Documents");
createFolder(parent, "Stripe_Summaries");
createFolder(parent, "Superbills");
var doc = DocumentApp.create("📋 Finance — Quick Reference");
var b = doc.getBody();
h1(b, "Finance Folder — Quick Reference");
bold(b, "Folder contents:");
b.appendParagraph("Invoices — client invoices. Name: YYYY-MM-DD_C001_Invoice");
b.appendParagraph("Receipts — business expense receipts");
b.appendParagraph("Tax_Documents — annual returns, accountant correspondence");
b.appendParagraph("Stripe_Summaries — monthly Stripe payout summaries");
b.appendParagraph("Superbills — US superbills generated for clients");
b.appendParagraph("");
bold(b, "Accountant access:");
b.appendParagraph("Share this Finance folder only. Right-click → Share → Viewer. Never share Clinical Records.");
b.appendParagraph("");
bold(b, "Invoice content:");
b.appendParagraph("Reference client codes only — never diagnoses or session content.");
b.appendParagraph("Example: 'Therapy session — C001 — 50 minutes'");
doc.saveAndClose();
moveFile(doc.getId(), parent);
log("Created: Finance folder");
}
// ============================================================
// 4. TEMPLATES & POLICIES
// ============================================================
function buildTemplates(parent) {
var consentFolder = createFolder(parent, "Consent_Templates");
var intakeFolder = createFolder(parent, "Intake_Templates");
var noteFolder = createFolder(parent, "Session_Note_Templates");
var billingFolder = createFolder(parent, "Billing_Templates");
var clinicalFolder = createFolder(parent, "Clinical_Templates");
var policyFolder = createFolder(parent, "Policies");
buildConsentForm(consentFolder);
buildPrivacyNotice(consentFolder);
buildIntakeForm(intakeFolder);
buildSessionNoteTemplate(noteFolder);
buildSuperbill(billingFolder);
buildInsuranceReceipt(billingFolder);
buildReferralLetter(clinicalFolder);
buildEndingSummary(clinicalFolder);
buildRiskRegister(clinicalFolder);
buildDataRetentionPolicy(policyFolder);
buildTelehealthPolicy(policyFolder);
buildAnnualComplianceChecklist(policyFolder);
log("Created: All templates and policies");
}
function buildConsentForm(folder) {
var doc = DocumentApp.create("TEMPLATE — Therapy Consent Form");
var b = doc.getBody();
var w1 = b.appendParagraph("⚠️ TEMPLATE — Fill in all [BRACKETED] fields before sending to each client. Save signed copy to client's 02_Consent_Forms folder.");
w1.setItalic(true); setRed(w1);
b.appendParagraph("");
h1(b, "Therapy Consent Form");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Therapist: " + CONFIG.therapistName);
b.appendParagraph("Date: ___________ | Client Code: ___________ (therapist use only)");
b.appendParagraph("");
var pi = b.appendParagraph("Please read carefully before signing."); pi.setItalic(true);
b.appendParagraph("");
h2(b, "1. Confidentiality");
b.appendParagraph("Everything discussed in sessions is confidential. I will not share information about you without your consent, except:");
b.appendParagraph("• Where there is serious risk of harm to you or another person");
b.appendParagraph("• Where required by law or court order");
b.appendParagraph("• In clinical supervision — discussed anonymously with a qualified supervisor");
b.appendParagraph("");
h2(b, "2. Sessions");
b.appendParagraph("Sessions are [LENGTH] minutes, held [FREQUENCY] via Google Meet. Please be in a private, confidential space.");
b.appendParagraph("");
h2(b, "3. Cancellation");
b.appendParagraph("Please give [X hours/days] notice to cancel or reschedule. Late cancellations are charged at the full rate.");
b.appendParagraph("");
h2(b, "4. Payment");
b.appendParagraph("Fee: [" + CONFIG.currency + " AMOUNT] per session. Payment collected in advance via Stripe.");
b.appendParagraph("");
h2(b, "5. Between Sessions");
b.appendParagraph("Contact via email at " + CONFIG.practiceEmail + " only. I do not provide therapeutic support between sessions.");
b.appendParagraph("In crisis: contact your GP, call 999 (UK/Ireland) or 911 (US), or Samaritans: 116 123.");
b.appendParagraph("");
h2(b, "6. Your Data");
b.appendParagraph("Records are stored securely in Google Workspace. You may request a copy of all data held about you at any time. See my Privacy Notice for full details.");
b.appendParagraph("");
h2(b, "7. Professional Registration");
b.appendParagraph("I am registered with [PROFESSIONAL BODY] and hold professional indemnity insurance. Concerns may be referred to [PROFESSIONAL BODY].");
b.appendParagraph("");
h2(b, "Agreement");
b.appendParagraph("I confirm I have read and understood this consent form and agree to its terms.");
b.appendParagraph("");
b.appendParagraph("Name (print): ___________________________________________");
b.appendParagraph("");
b.appendParagraph("Signature: ___________________________________________");
b.appendParagraph("");
b.appendParagraph("Date: ___________________________________________");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildPrivacyNotice(folder) {
var doc = DocumentApp.create("TEMPLATE — Privacy Notice");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ TEMPLATE — Review annually. Send to each new client alongside consent form."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Privacy Notice");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Data Controller: " + CONFIG.therapistName);
b.appendParagraph("Contact: " + CONFIG.practiceEmail + " | Last reviewed: " + SETUP_DATE);
b.appendParagraph("");
h2(b, "1. Who I Am");
b.appendParagraph(CONFIG.therapistName + " is the data controller for " + CONFIG.practiceName + " and is responsible for how your personal data is used.");
b.appendParagraph("");
h2(b, "2. What Data I Collect");
b.appendParagraph("• Identity — name, date of birth, emergency contact");
b.appendParagraph("• Contact — email, phone number");
b.appendParagraph("• Health data — mental and physical health information (special category under GDPR)");
b.appendParagraph("• Financial — payment records via Stripe");
b.appendParagraph("• Appointment — booking records via Cal.com");
b.appendParagraph("");
h2(b, "3. Legal Basis");
b.appendParagraph("Processing of health data is under Article 9(2)(h) GDPR — necessary for health or social care. Financial data processed for contract performance.");
b.appendParagraph("");
h2(b, "4. Where Data Is Stored");
b.appendParagraph("• Google Workspace — clinical records, email, video sessions (Google DPA in place)");
b.appendParagraph("• Cal.com — booking (name and email only)");
b.appendParagraph("• Stripe — payments (financial data only)");
b.appendParagraph("• Framer — public website (no client data stored)");
b.appendParagraph("");
h2(b, "5. How Long I Keep Your Data");
b.appendParagraph("Adult clients: 7 years after end of therapy. Minor clients: until age 25 or 7 years, whichever is later.");
b.appendParagraph("");
h2(b, "6. Your Rights");
b.appendParagraph("You have the right to access, correct or request deletion of your data, and to withdraw consent at any time.");
b.appendParagraph("Contact: " + CONFIG.practiceEmail);
b.appendParagraph("Supervisory authorities: UK: ico.org.uk | Ireland: dataprotection.ie | US: hhs.gov/hipaa");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildIntakeForm(folder) {
var doc = DocumentApp.create("TEMPLATE — Client Intake Form");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ TEMPLATE — This is a suggested starting point. Adapt it to your practice. Use in person, send as a PDF, or complete verbally. Save completed forms to client's 03_Intake_Forms folder."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Client Intake Form");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Therapist: " + CONFIG.therapistName);
b.appendParagraph("Date: ___________ | Client Code: ___________ (therapist use)");
b.appendParagraph("");
var pi = b.appendParagraph("All information is confidential and stored securely in line with our Privacy Notice."); pi.setItalic(true);
b.appendParagraph("");
h2(b, "Personal Details");
b.appendParagraph("Full name: ___________________________________________");
b.appendParagraph("Date of birth: ___________________________________________");
b.appendParagraph("Email: ___________________________________________");
b.appendParagraph("Phone: ___________________________________________");
b.appendParagraph("Emergency contact (name and number): ___________________________________________");
b.appendParagraph("");
h2(b, "GP Details");
b.appendParagraph("GP name: ___________________________________________");
b.appendParagraph("GP surgery: ___________________________________________");
b.appendParagraph("Under psychiatrist or mental health service? ☐ Yes ☐ No");
b.appendParagraph("If yes: ___________________________________________");
b.appendParagraph("");
h2(b, "Current Situation");
b.appendParagraph("What brings you to therapy now?");
b.appendParagraph(" ");
b.appendParagraph(" ");
b.appendParagraph("How long have you been experiencing this? ___________________________________________");
b.appendParagraph("Previous therapy? ☐ Yes ☐ No If yes: ___________________________________________");
b.appendParagraph("");
h2(b, "Health Information");
b.appendParagraph("Current medication? ☐ Yes ☐ No If yes: ___________________________________________");
b.appendParagraph("Relevant physical health conditions? ☐ Yes ☐ No If yes: ___________________________________________");
b.appendParagraph("Thoughts of self-harm or suicide? ☐ Yes ☐ No Currently? ☐ Yes ☐ No");
b.appendParagraph("Context: ___________________________________________");
b.appendParagraph("");
h2(b, "What You're Looking For");
b.appendParagraph("What would you like to get from therapy?");
b.appendParagraph(" ");
b.appendParagraph(" ");
b.appendParagraph("Anything else you'd like me to know?");
b.appendParagraph(" ");
b.appendParagraph("");
h2(b, "Declaration");
b.appendParagraph("I confirm this information is accurate and consent to it being stored securely for therapy purposes.");
b.appendParagraph("");
b.appendParagraph("Signature: ___________________________________________ Date: ___________");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildSessionNoteTemplate(folder) {
var doc = DocumentApp.create("TEMPLATE — Session Note");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ TEMPLATE — Make a copy. Never write in this file. Save copy to client's 04_Session_Notes folder as YYYY-MM-DD_C001_SessionNote."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Session Note");
b.appendParagraph("Client Code: ___________ Session No: ___________");
b.appendParagraph("Date: ___________ Duration: ___________");
b.appendParagraph("Format: ☐ Video — Google Meet ☐ In Person ☐ Phone");
b.appendParagraph("");
h2(b, "Presenting themes this session");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "Interventions used");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "Client response and engagement");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "Risk assessment");
b.appendParagraph("Risk to self: ☐ None identified ☐ Low ☐ Medium ☐ High");
b.appendParagraph("Risk to others: ☐ None identified ☐ Low ☐ Medium ☐ High");
b.appendParagraph("Action taken: ___________________________________________");
b.appendParagraph("");
h2(b, "Plan for next session");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "Supervision");
b.appendParagraph("Bring to supervision: ☐ Yes ☐ No");
b.appendParagraph("Notes: ___________________________________________");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildSuperbill(folder) {
var doc = DocumentApp.create("TEMPLATE — Superbill (US)");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ US THERAPISTS ONLY — Make a copy per client per billing period. Fill in all fields. Diagnosis code must be assigned by a licensed clinician only."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Superbill");
b.appendParagraph("Statement Date: ___________ | Statement No: ___________");
b.appendParagraph("");
h2(b, "Provider Information");
b.appendParagraph("Provider Name: " + CONFIG.therapistName);
b.appendParagraph("Practice Name: " + CONFIG.practiceName);
b.appendParagraph("Credentials: ___________ (e.g. LCSW, LPC, PhD, PsyD)");
b.appendParagraph("NPI Number: ___________ (required for insurance submission)");
b.appendParagraph("Tax ID / EIN: ___________");
b.appendParagraph("Address: ___________________________________________");
b.appendParagraph("Phone: ___________________________________________");
b.appendParagraph("Email: " + CONFIG.practiceEmail);
b.appendParagraph("");
h2(b, "Client Information");
b.appendParagraph("Client Name: ___________________________________________");
b.appendParagraph("Date of Birth: ___________________________________________");
b.appendParagraph("Client ID / Code: ___________________________________________");
b.appendParagraph("Insurance Provider: ___________________________________________");
b.appendParagraph("Insurance Member ID: ___________________________________________");
b.appendParagraph("");
h2(b, "Diagnosis");
b.appendParagraph("Primary Diagnosis (ICD-10): ___________ — ___________________________________________");
b.appendParagraph("Secondary Diagnosis (if applicable): ___________ — ___________________________________________");
b.appendParagraph("");
var pi = b.appendParagraph("⚠️ Diagnosis codes must be assigned by a licensed clinician. Only include a diagnosis code if you are licensed and qualified to diagnose in your jurisdiction."); pi.setItalic(true);
b.appendParagraph("");
h2(b, "Services Rendered");
// Table header row as text (Apps Script tables are complex — plain text is cleaner)
b.appendParagraph("Date of Service | CPT Code | Service Description | Duration | Fee | Amount Paid");
b.appendParagraph("─────────────────────────────────────────────────────────────────────────────");
b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("─────────────────────────────────────────────────────────────────────────────");
b.appendParagraph("TOTAL CHARGED: " + CONFIG.currency + "___________ | TOTAL PAID: " + CONFIG.currency + "___________ | BALANCE: " + CONFIG.currency + "___________");
b.appendParagraph("");
h2(b, "Common CPT Codes (Psychotherapy)");
b.appendParagraph("90837 — Individual psychotherapy, 60 minutes");
b.appendParagraph("90834 — Individual psychotherapy, 45 minutes");
b.appendParagraph("90832 — Individual psychotherapy, 30 minutes");
b.appendParagraph("90847 — Family psychotherapy with client present");
b.appendParagraph("90846 — Family psychotherapy without client present");
b.appendParagraph("90853 — Group psychotherapy");
b.appendParagraph("99213 — Office visit, established patient, moderate complexity");
b.appendParagraph("");
h2(b, "Place of Service Codes");
b.appendParagraph("02 — Telehealth (patient is not at home)");
b.appendParagraph("10 — Telehealth (patient is at home)");
b.appendParagraph("11 — Office");
b.appendParagraph("");
h2(b, "Provider Signature");
b.appendParagraph("I certify that the services listed above were medically necessary and personally rendered by me.");
b.appendParagraph("");
b.appendParagraph("Signature: ___________________________________________ Date: ___________");
b.appendParagraph("");
var pi = b.appendParagraph("📝 Save to: 3_Finance → Superbills → YYYY-MM_C001_Superbill"); pi.setItalic(true);
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildInsuranceReceipt(folder) {
var doc = DocumentApp.create("TEMPLATE — Insurance Receipt (UK and Ireland)");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ UK AND IRELAND — Make a copy per client per billing period. For clients submitting to private health insurers (Bupa, AXA, VHI, Laya etc)."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Therapy Receipt for Insurance Reimbursement");
b.appendParagraph("Receipt Date: ___________ | Receipt No: ___________");
b.appendParagraph("");
h2(b, "Therapist Details");
b.appendParagraph("Name: " + CONFIG.therapistName);
b.appendParagraph("Practice: " + CONFIG.practiceName);
b.appendParagraph("Qualifications: ___________ (e.g. MSc Counselling Psychology, MBACP)");
b.appendParagraph("Professional Registration: ___________ (e.g. BACP No. 123456)");
b.appendParagraph("Email: " + CONFIG.practiceEmail);
b.appendParagraph("");
h2(b, "Client Details");
b.appendParagraph("Client Name: ___________________________________________");
b.appendParagraph("Date of Birth: ___________________________________________");
b.appendParagraph("Insurance Provider: ___________________________________________");
b.appendParagraph("Policy Number: ___________________________________________");
b.appendParagraph("Authorisation Number (if applicable): ___________________________________________");
b.appendParagraph("");
h2(b, "Sessions");
b.appendParagraph("Date | Duration | Service | Fee | Paid");
b.appendParagraph("─────────────────────────────────────────────────");
b.appendParagraph("___________ | _____ min | Individual Therapy | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("___________ | _____ min | Individual Therapy | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("___________ | _____ min | Individual Therapy | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
b.appendParagraph("─────────────────────────────────────────────────");
b.appendParagraph("TOTAL PAID: " + CONFIG.currency + "___________");
b.appendParagraph("");
var pi = b.appendParagraph("Note: Diagnosis codes are not routinely included on therapy receipts in the UK and Ireland. If your insurer requires a clinical diagnosis, this must be obtained from a psychiatrist or GP, not from your therapist."); pi.setItalic(true);
b.appendParagraph("");
h2(b, "Declaration");
b.appendParagraph("I confirm that the therapy sessions listed above were provided by me.");
b.appendParagraph("");
b.appendParagraph("Signature: ___________________________________________ Date: ___________");
b.appendParagraph("");
var pi = b.appendParagraph("📝 Save to: 3_Finance → Invoices → YYYY-MM_C001_InsuranceReceipt"); pi.setItalic(true);
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildReferralLetter(folder) {
var doc = DocumentApp.create("TEMPLATE — Referral Letter");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ TEMPLATE — Make a copy. Fill in all [BRACKETED] fields. Save copy to client's 06_Correspondence folder."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
b.appendParagraph(CONFIG.practiceName);
b.appendParagraph(CONFIG.practiceEmail);
b.appendParagraph("[DATE]");
b.appendParagraph("");
b.appendParagraph("Dear [RECIPIENT NAME / Dr / To Whom It May Concern],");
b.appendParagraph("");
b.appendParagraph("Re: [CLIENT — use initials only unless client has consented to full name being shared]");
b.appendParagraph("Date of Birth: [DOB]");
b.appendParagraph("");
b.appendParagraph("I am writing to refer the above-named client, who has been receiving [individual / couples / family] therapy with me since [START DATE].");
b.appendParagraph("");
b.appendParagraph("Presenting concerns:");
b.appendParagraph("[Brief, professional summary of presenting issues]");
b.appendParagraph("");
b.appendParagraph("Reason for referral:");
b.appendParagraph("[Why you are referring — e.g. client requires assessment, medication review, higher level of care, specialist support]");
b.appendParagraph("");
b.appendParagraph("Current risk level: [Low / Medium / High — and brief rationale]");
b.appendParagraph("");
b.appendParagraph("I am happy to discuss this referral further if helpful.");
b.appendParagraph("");
b.appendParagraph("Yours sincerely,");
b.appendParagraph("");
b.appendParagraph(CONFIG.therapistName);
b.appendParagraph("[Qualifications]");
b.appendParagraph("[Professional Body Registration]");
b.appendParagraph(CONFIG.practiceEmail);
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildEndingSummary(folder) {
var doc = DocumentApp.create("TEMPLATE — Therapy Ending Summary");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ TEMPLATE — Complete when therapy ends. Save to client's 01_Admin folder. Send to GP if clinically appropriate and client has consented."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Therapy Ending Summary");
b.appendParagraph("Client Code: ___________ | Date of Final Session: ___________");
b.appendParagraph("Therapist: " + CONFIG.therapistName + " | Practice: " + CONFIG.practiceName);
b.appendParagraph("Total Sessions: ___________ | Duration of Work: ___________");
b.appendParagraph("");
h2(b, "1. Presenting Issues at Start of Therapy");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "2. Therapeutic Approach Used");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "3. Progress and Outcomes");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "4. Risk at Ending");
b.appendParagraph("Risk to self: ☐ None ☐ Low ☐ Medium ☐ High");
b.appendParagraph("Risk to others: ☐ None ☐ Low ☐ Medium ☐ High");
b.appendParagraph("Notes: ___________________________________________");
b.appendParagraph("");
h2(b, "5. Reason for Ending");
b.appendParagraph("☐ Planned ending — goals achieved");
b.appendParagraph("☐ Planned ending — natural conclusion");
b.appendParagraph("☐ Client-initiated ending");
b.appendParagraph("☐ Therapist-initiated ending");
b.appendParagraph("☐ Did not attend — case closed after [X] missed sessions");
b.appendParagraph("☐ Referred on to: ___________________________________________");
b.appendParagraph("");
h2(b, "6. Recommendations");
b.appendParagraph(" "); b.appendParagraph(" ");
h2(b, "7. Client Feedback (if provided)");
b.appendParagraph(" "); b.appendParagraph(" ");
b.appendParagraph("");
b.appendParagraph("Therapist signature: ___________________________________________ Date: ___________");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildRiskRegister(folder) {
var doc = DocumentApp.create("TEMPLATE — Risk Register");
var b = doc.getBody();
var wp = b.appendParagraph("⚠️ TEMPLATE — Make a copy for any client where ongoing risk is identified. Save to client's 01_Admin folder. Review at every session where risk is present."); wp.setItalic(true); setRed(wp);
b.appendParagraph("");
h1(b, "Risk Register");
b.appendParagraph("Client Code: ___________ | Date opened: ___________");
b.appendParagraph("Therapist: " + CONFIG.therapistName);
b.appendParagraph("");
h2(b, "Risk Summary");
b.appendParagraph("Nature of risk: ___________________________________________");
b.appendParagraph("Initial risk level: ☐ Low ☐ Medium ☐ High");
b.appendParagraph("Protective factors: ___________________________________________");
b.appendParagraph("");
h2(b, "Risk Review Log");
b.appendParagraph("Date | Risk Level | Notes | Action Taken | Next Review");
b.appendParagraph("─────────────────────────────────────────────────────────────────");
for (var i = 0; i < 10; i++) {
b.appendParagraph("___________ | ☐ Low ☐ Med ☐ High | _______________ | _______________ | ___________");
}
b.appendParagraph("");
h2(b, "Escalation Record");
b.appendParagraph("Date | Action | Person Contacted | Outcome");
b.appendParagraph("─────────────────────────────────────────────────────────────────");
b.appendParagraph("___________ | _________________________________ | _________________ | _________________");
b.appendParagraph("___________ | _________________________________ | _________________ | _________________");
b.appendParagraph("");
var pi = b.appendParagraph("Bring to supervision whenever risk level is Medium or above."); pi.setItalic(true);
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildDataRetentionPolicy(folder) {
var doc = DocumentApp.create("Policy — Data Retention");
var b = doc.getBody();
h1(b, "Data Retention Policy");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Controller: " + CONFIG.therapistName);
b.appendParagraph("Last reviewed: " + SETUP_DATE);
b.appendParagraph("");
h2(b, "1. Purpose");
b.appendParagraph("This policy sets out how " + CONFIG.practiceName + " retains and deletes client data in compliance with GDPR (UK/Ireland) and HIPAA (US).");
b.appendParagraph("");
h2(b, "2. Retention Periods");
b.appendParagraph("Adult clients: 7 years after final session.");
b.appendParagraph("Minor clients: Until age 25 or 7 years after final session, whichever is later.");
b.appendParagraph("Financial records: 7 years for tax purposes.");
b.appendParagraph("");
h2(b, "3. Storage");
b.appendParagraph("All records in Google Workspace under Google's Data Processing Agreement. Two-factor authentication enforced.");
b.appendParagraph("");
h2(b, "4. Deletion Procedure");
b.appendParagraph("1. Permanently delete client folder from 5_Archives");
b.appendParagraph("2. Empty Google Drive Trash");
b.appendParagraph("3. Log in Client_Index → Deletion_Log tab");
b.appendParagraph("4. Update status to Deleted in Clients tab");
b.appendParagraph("");
h2(b, "5. Data Processors");
b.appendParagraph("• Google Workspace — email, Drive, Meet, Forms");
b.appendParagraph("• Cal.com — appointment scheduling");
b.appendParagraph("• Stripe — payment processing");
b.appendParagraph("• Framer — public website (no client data)");
b.appendParagraph("");
h2(b, "6. Breach Notification");
b.appendParagraph("Notify supervisory authority within 72 hours:");
b.appendParagraph("UK: ico.org.uk | Ireland: dataprotection.ie | US: hhs.gov/hipaa");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildTelehealthPolicy(folder) {
var doc = DocumentApp.create("Policy — Telehealth");
var b = doc.getBody();
h1(b, "Telehealth Policy");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Last reviewed: " + SETUP_DATE);
b.appendParagraph("");
h2(b, "Platform");
b.appendParagraph("All remote sessions via Google Meet. Sessions are not recorded. Encrypted in transit.");
b.appendParagraph("");
h2(b, "Client Responsibilities");
b.appendParagraph("• Be in a private, confidential space");
b.appendParagraph("• Use a secure, private internet connection");
b.appendParagraph("• Do not record without prior written consent");
b.appendParagraph("• Confirm your physical location at the start of each session");
b.appendParagraph("");
h2(b, "Suitability");
b.appendParagraph("Telehealth is not suitable for all presentations. If remote sessions become clinically inappropriate, an alternative will be agreed.");
b.appendParagraph("");
h2(b, "Crisis Protocol");
b.appendParagraph("If a client is in crisis, the therapist will assess risk and contact emergency services if there is immediate risk to life (999 UK/Ireland, 911 US). Physical location is confirmed at session start for this reason.");
b.appendParagraph("");
h2(b, "Between-Session Communication");
b.appendParagraph("Workspace email only. No therapeutic support via WhatsApp, SMS or social media.");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
function buildAnnualComplianceChecklist(folder) {
var doc = DocumentApp.create("Annual Compliance Review Checklist");
var b = doc.getBody();
h1(b, "Annual Compliance Review Checklist");
b.appendParagraph("Practice: " + CONFIG.practiceName + " | Therapist: " + CONFIG.therapistName);
b.appendParagraph("Review date: ___________");
b.appendParagraph("");
var pi = b.appendParagraph("Complete once per year. Keep a signed copy here in Policies."); pi.setItalic(true);
b.appendParagraph("");
h2(b, "Google Workspace Security");
b.appendParagraph("☐ Two-factor authentication enabled");
b.appendParagraph("☐ No unauthorised devices have access");
b.appendParagraph("☐ Sharing set to restricted — no public links active");
b.appendParagraph("☐ Password changed in the last 12 months");
b.appendParagraph("");
h2(b, "Client Records");
b.appendParagraph("☐ All active clients have a folder in 1_Clinical_Records");
b.appendParagraph("☐ All closed clients moved to 5_Archives");
b.appendParagraph("☐ Client Index is current and accurate");
b.appendParagraph("☐ Retention deadlines recorded for all closed clients");
b.appendParagraph("☐ Clients past retention deadline deleted and logged");
b.appendParagraph("");
h2(b, "Policies");
b.appendParagraph("☐ Privacy Notice reviewed and current");
b.appendParagraph("☐ Consent form reviewed and current");
b.appendParagraph("☐ Data Retention Policy reviewed");
b.appendParagraph("☐ Telehealth Policy reviewed");
b.appendParagraph("☐ Processor list accurate (Google, Cal.com, Stripe, Framer)");
b.appendParagraph("");
h2(b, "Third Party Tools");
b.appendParagraph("☐ Google Workspace DPA in place (admin.google.com → Account → Legal)");
b.appendParagraph("☐ Cal.com — name and email only, no clinical data");
b.appendParagraph("☐ Stripe — financial data only, no clinical content");
b.appendParagraph("☐ No new tools introduced storing client data without a DPA");
b.appendParagraph("");
h2(b, "Professional");
b.appendParagraph("☐ Professional indemnity insurance current");
b.appendParagraph("☐ Professional body registration current");
b.appendParagraph("☐ Supervision in place and up to date");
b.appendParagraph("☐ CPD hours on track (30 hours/year — check CPD_Log tab)");
b.appendParagraph("");
b.appendParagraph("Signature: ___________________________________________ Date: ___________");
doc.saveAndClose();
moveFile(doc.getId(), folder);
}
// ============================================================
// 5. ARCHIVES
// ============================================================
function buildArchives(parent) {
createFolder(parent, "Clinical_Records_Archived");
createFolder(parent, "Finance_Archived");
var doc = DocumentApp.create("📋 Archives — Quick Reference");
var b = doc.getBody();
h1(b, "Archives — Quick Reference");
bold(b, "When to archive:");
b.appendParagraph("When therapy ends — planned ending, withdrawal or non-attendance.");
b.appendParagraph("");
bold(b, "How to archive:");
b.appendParagraph("Option A (recommended): Practice Tools → Close Client Case in Client_Index");
b.appendParagraph("Option B (manual): Update status to Closed → add end date → add retention deadline → move folder here");
b.appendParagraph("");
bold(b, "When to delete:");
b.appendParagraph("Only after retention deadline has passed.");
b.appendParagraph("UK/Ireland: 7 years after final session.");
b.appendParagraph("Minors: Until age 25 or 7 years, whichever is later.");
b.appendParagraph("");
bold(b, "How to delete:");
b.appendParagraph("1. Permanently delete client folder");
b.appendParagraph("2. Empty Google Drive Trash — this is permanent");
b.appendParagraph("3. Log deletion in Client_Index → Deletion_Log");
b.appendParagraph("4. Update status to Deleted");
b.appendParagraph("");
var pb = b.appendParagraph("⚠️ Emptying Trash is irreversible. Confirm retention period has passed first."); pb.setBold(true);
doc.saveAndClose();
moveFile(doc.getId(), parent);
log("Created: Archives folder");
}
// ============================================================
// UTILITY FUNCTIONS
// ============================================================
function createFolder(parent, name) {
var f = parent.createFolder(name);
log("Folder: " + name);
return f;
}
function moveFile(fileId, folder) {
var file = DriveApp.getFileById(fileId);
folder.addFile(file);
DriveApp.getRootFolder().removeFile(file);
}
function styleHeaderRow(sheet, headers, bgColor) {
sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
sheet.getRange(1, 1, 1, headers.length)
.setBackground(bgColor).setFontColor("#ffffff")
.setFontWeight("bold").setFontSize(11);
sheet.setFrozenRows(1);
}
function setColumnWidths(sheet, widths) {
for (var i = 0; i < widths.length; i++) {
sheet.setColumnWidth(i + 1, widths[i]);
}
}
function applyDropdown(sheet, startRow, col, numRows, values) {
var rule = SpreadsheetApp.newDataValidation()
.requireValueInList(values, true).build();
sheet.getRange(startRow, col, numRows, 1).setDataValidation(rule);
}
function applyConditionalFormat(sheet, triggerText, bgColor, fontColor, startRow, startCol, numRows, numCols) {
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo(triggerText)
.setBackground(bgColor).setFontColor(fontColor)
.setRanges([sheet.getRange(startRow, startCol, numRows, numCols)]).build();
sheet.setConditionalFormatRules([rule]);
}
function addNote(sheet, cell, note) {
sheet.getRange(cell).setNote(note);
}
function h1(body, text) {
body.appendParagraph(text).setHeading(DocumentApp.ParagraphHeading.HEADING1);
}
function h2(body, text) {
body.appendParagraph(text).setHeading(DocumentApp.ParagraphHeading.HEADING2);
}
function bold(body, text) {
body.appendParagraph(text).setBold(true);
}
function setRed(par) {
var attrs = {};
attrs[DocumentApp.Attribute.FOREGROUND_COLOR] = "#cc0000";
par.setAttributes(attrs);
}
function log(msg) {
Logger.log(msg);
}"
language="javascriptStep 02
Practice Tools Menu
Open your new Client_Index spreadsheet, click Extensions → Apps Script, paste this script, save, then click Run → installPracticeTools. Reload the sheet and you'll see the 🏥 Practice Tools menu.
Step 2 — Practice Tools Menu (install inside Client_Index)
/**
* ============================================================
* PRACTICE INFRASTRUCTURE — STEP 2: PRACTICE TOOLS MENU
* Version 3.3
* ============================================================
* This script lives INSIDE the Client_Index spreadsheet.
*
* HOW TO INSTALL:
* 1. Open your Client_Index spreadsheet
* 2. Click Extensions → Apps Script
* 3. Delete any existing code in the editor
* 4. Paste this entire script
* 5. Click Save (floppy disk icon)
* 6. Click Run → installPracticeTools
* 7. Approve permissions when prompted
* 8. Close the Apps Script tab
* 9. Reload your spreadsheet
* 10. You will see "🏥 Practice Tools" in the menu bar
*
* From then on, the menu loads automatically every time
* you open the spreadsheet.
* ============================================================
*/
// ============================================================
// MENU INSTALLATION — Runs on every open + first install
// ============================================================
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("🏥 Practice Tools")
.addItem("➕ Add New Client", "addNewClient")
.addSeparator()
.addItem("📅 Log Session", "logSession")
.addItem("📋 Log DNA / Late Cancellation", "logDNA")
.addSeparator()
.addItem("📁 Close Client Case", "closeClientCase")
.addSeparator()
.addItem("📄 Generate Superbill (US)", "generateSuperbill")
.addItem("🧾 Generate Invoice / Receipt", "generateInvoice")
.addSeparator()
.addItem("ℹ️ Help", "showHelp")
.addToUi();
}
function installPracticeTools() {
onOpen();
showAlert("✅ Practice Tools Installed",
"The Practice Tools menu has been added to your spreadsheet.\n\n" +
"Close this tab and reload your spreadsheet to see the menu in the top bar.\n\n" +
"Start with: 🏥 Practice Tools → Add New Client");
}
// ============================================================
// HELPER — Get settings from Settings tab
// ============================================================
function getSettings() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("⚙️ Settings");
if (!s) throw new Error("Settings tab not found. Make sure you ran STEP1 correctly.");
var data = s.getRange("A4:B12").getValues();
var settings = {};
data.forEach(function(row) { if (row[0]) settings[row[0]] = row[1]; });
return settings;
}
function getNextClientCode() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("👥 Clients");
var data = s.getDataRange().getValues();
var maxCode = 0;
for (var i = 1; i < data.length; i++) {
var code = String(data[i][0]);
if (code.match(/^C\d+$/)) {
var num = parseInt(code.replace("C", ""));
if (num > maxCode) maxCode = num;
}
}
var next = maxCode + 1;
return "C" + String(next).padStart(3, "0");
}
function getClientList() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("👥 Clients");
var data = s.getDataRange().getValues();
var clients = [];
for (var i = 1; i < data.length; i++) {
if (data[i][0] && data[i][0] !== "Client Code" && data[i][8] === "Active") {
clients.push(data[i][0] + " — " + data[i][1] + " " + data[i][2]);
}
}
return clients;
}
function getClientFee(clientCode) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("👥 Clients");
var data = s.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
if (data[i][0] === clientCode) {
return data[i][9] || 0; // Default Fee column
}
}
return 0;
}
function getNextSessionNumber(clientCode) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("📅 Session_Log");
var data = s.getDataRange().getValues();
var max = 0;
for (var i = 1; i < data.length; i++) {
if (data[i][1] === clientCode) {
var n = parseInt(data[i][2]);
if (n > max) max = n;
}
}
return max + 1;
}
// ============================================================
// ADD NEW CLIENT
// ============================================================
function addNewClient() {
var ui = SpreadsheetApp.getUi();
var settings = getSettings();
var nextCode = getNextClientCode();
var firstName = askUser(ui, "ADD NEW CLIENT (1/7)", "Client first name:");
if (!firstName) return;
var lastName = askUser(ui, "ADD NEW CLIENT (2/7)", "Client last name:");
if (!lastName) return;
var email = askUser(ui, "ADD NEW CLIENT (3/7)", "Client email address:");
if (!email) return;
// ── Duplicate email check ──
var ss = SpreadsheetApp.getActiveSpreadsheet();
var clientSheet = ss.getSheetByName("👥 Clients");
var existingData = clientSheet.getDataRange().getValues();
for (var e = 1; e < existingData.length; e++) {
if (existingData[e][3] && existingData[e][3].toString().toLowerCase() === email.toLowerCase()) {
var existingCode = existingData[e][0];
var existingName = existingData[e][1] + " " + existingData[e][2];
var dupResponse = ui.alert(
"⚠️ Duplicate Email Detected",
"This email address is already on record for:\n" +
existingCode + " — " + existingName + "\n\n" +
"Do you want to continue adding this as a new client anyway?",
ui.ButtonSet.YES_NO
);
if (dupResponse !== ui.Button.YES) return;
break;
}
}
var phone = askUser(ui, "ADD NEW CLIENT (4/7)", "Client phone number (or press OK to skip):");
if (!phone) phone = "";
var dobRaw = askUser(ui, "ADD NEW CLIENT (5/7)",
"Client date of birth (used to calculate retention deadline for minor clients).\n\n" +
"Accepted formats:\n" +
" 05/03/1990 | 1990-03-05 | 5 March 1990\n\n" +
"Press OK to skip if not known.");
var dob = dobRaw ? (parseFlexibleDate(dobRaw) || "") : "";
if (dobRaw && !dob) {
showAlert("Invalid Date", "\"" + dobRaw + "\" was not recognised. Date of birth left blank — you can add it manually in the Clients tab.");
dob = "";
}
var startDate = askDate(ui, "ADD NEW CLIENT (6/7)", "Start date:");
if (!startDate) startDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
var feeInput = askUser(ui, "ADD NEW CLIENT (7/7)",
"Session fee (" + settings["Currency Symbol"] + ") — default is " + settings["Default Session Fee"] + ":\n(Press OK to use default)");
var fee;
if (!feeInput) {
fee = parseFloat(settings["Default Session Fee"]) || 0;
} else {
fee = parseFloat(feeInput);
if (isNaN(fee)) {
showAlert("Invalid Fee", "Please enter a number for the fee.");
return;
}
}
// ── Build client folder ──
var clientFolderName = nextCode + "-" + getInitials(firstName, lastName);
var clinicalFolder = getClinicalRecordsFolder();
if (!clinicalFolder) {
showAlert("Folder Error",
"Could not find the 1_Clinical_Records folder.\n\n" +
"Make sure this spreadsheet is inside the Practice Infrastructure folder structure created by the setup script.");
return;
}
var clientFolder = clinicalFolder.createFolder(clientFolderName);
clientFolder.createFolder("01_Admin");
clientFolder.createFolder("02_Consent_Forms");
clientFolder.createFolder("03_Intake_Forms");
clientFolder.createFolder("04_Session_Notes");
clientFolder.createFolder("05_Client_Materials");
clientFolder.createFolder("06_Correspondence");
var folderUrl = "https://drive.google.com/drive/folders/" + clientFolder.getId();
// ── Add to Clients tab ──
var newRow = [
nextCode, firstName, lastName, email, phone,
dob, startDate, "", "Active",
fee, "",
folderUrl,
"", ""
];
clientSheet.appendRow(newRow);
var lastRow = clientSheet.getLastRow();
clientSheet.getRange(lastRow, 1, 1, newRow.length)
.setFontColor("#000000").setFontStyle("normal");
showAlert("✅ Client Added — " + nextCode,
"Client " + firstName + " " + lastName + " has been added as " + nextCode + ".\n\n" +
"Their folder has been created automatically:\n" +
clientFolderName + "\n\n" +
"NEXT STEPS:\n" +
"1. Send them the intake form\n" +
"2. Send them the consent form and privacy notice\n" +
" (4_Templates_Policies → Consent_Templates)\n" +
"3. Save completed forms to their Drive folder when returned");
}
// Navigate from this spreadsheet up to find 1_Clinical_Records
function getPracticeFolder() {
try {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssFile = DriveApp.getFileById(ss.getId());
var parents = ssFile.getParents();
while (parents.hasNext()) {
var adminFolder = parents.next();
var grandParents = adminFolder.getParents();
while (grandParents.hasNext()) {
var practiceFolder = grandParents.next();
var subs = practiceFolder.getFolders();
while (subs.hasNext()) {
var sub = subs.next();
if (sub.getName().indexOf("1_Clinical_Records") !== -1) {
return practiceFolder;
}
}
}
}
return null;
} catch(e) {
return null;
}
}
function getClinicalRecordsFolder() {
try {
var practiceFolder = getPracticeFolder();
if (!practiceFolder) return null;
var subs = practiceFolder.getFolders();
while (subs.hasNext()) {
var sub = subs.next();
if (sub.getName().indexOf("1_Clinical_Records") !== -1) return sub;
}
return null;
} catch(e) {
return null;
}
}
// ============================================================
// LOG SESSION
// ============================================================
function logSession() {
var ui = SpreadsheetApp.getUi();
var settings = getSettings();
var clients = getClientList();
if (clients.length === 0) {
showAlert("No Active Clients", "Add a client first using Practice Tools → Add New Client.");
return;
}
var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
var clientIdx = askNumber(ui, "LOG SESSION (1/6)",
"Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
if (clientIdx === null) return;
if (clientIdx < 1 || clientIdx > clients.length) {
showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
return;
}
var clientCode = clients[clientIdx - 1].split(" — ")[0];
var clientFee = getClientFee(clientCode);
var nextSession = getNextSessionNumber(clientCode);
var sessionDate = askDate(ui, "LOG SESSION (2/6)", "Session date:");
if (!sessionDate) return;
var formatIdx = askNumber(ui, "LOG SESSION (3/6)",
"Type the NUMBER for session format:\n\n" +
"1. Video — Google Meet\n2. In Person\n3. Phone\n4. Group");
if (formatIdx === null) return;
var formats = ["Video — Google Meet", "In Person", "Phone", "Group"];
if (formatIdx < 1 || formatIdx > 4) {
showAlert("Invalid Selection", "Please enter a number between 1 and 4.");
return;
}
var format = formats[formatIdx - 1];
var defaultDuration = settings["Standard Session Length (mins)"] || 50;
var durationInput = askUser(ui, "LOG SESSION (4/6)",
"Session duration in minutes.\nPress OK to use standard " + defaultDuration + " mins:");
var duration = durationInput ? parseInt(durationInput) : defaultDuration;
if (isNaN(duration) || duration <= 0) duration = defaultDuration;
var feeInput = askUser(ui, "LOG SESSION (5/6)",
"Fee charged (" + settings["Currency Symbol"] + ").\n" +
"Standard fee for this client is " + settings["Currency Symbol"] + clientFee + ".\n" +
"Press OK to use standard fee:");
var fee;
if (!feeInput) {
fee = clientFee;
} else {
fee = parseFloat(feeInput);
if (isNaN(fee)) {
showAlert("Invalid Fee", "Please enter a number for the fee.");
return;
}
}
var paidIdx = askNumber(ui, "LOG SESSION (6/6)",
"Type the NUMBER for payment status:\n\n1. Yes\n2. No\n3. Waived");
if (paidIdx === null) return;
var paidOptions = ["Yes", "No", "Waived"];
if (paidIdx < 1 || paidIdx > 3) {
showAlert("Invalid Selection", "Please enter 1, 2, or 3.");
return;
}
var paid = paidOptions[paidIdx - 1];
var paymentDate = (paid === "Yes") ? sessionDate : "";
var invoiceRef = "INV-" + clientCode + "-" + String(nextSession).padStart(3, "0");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var logSheet = ss.getSheetByName("📅 Session_Log");
logSheet.appendRow([
sessionDate, clientCode, nextSession,
duration, format, fee, paid, paymentDate, invoiceRef, "", ""
]);
var lastRow = logSheet.getLastRow();
logSheet.getRange(lastRow, 1, 1, 11).setFontColor("#000000").setFontStyle("normal");
if (paid === "No") {
logSheet.getRange(lastRow, 7).setBackground("#fff3cd");
}
showAlert("✅ Session Logged",
"Session " + nextSession + " for " + clientCode + " logged on " + formatDisplayDate(sessionDate) + ".\n" +
"Fee: " + settings["Currency Symbol"] + fee + " | Paid: " + paid + "\n\n" +
"Remember to write your session note and save it to:\n" +
"1_Clinical_Records → " + clientCode + " → 04_Session_Notes\n" +
"Filename: " + sessionDate + "_" + clientCode + "_SessionNote");
}
// ============================================================
// LOG DNA / LATE CANCELLATION
// ============================================================
function logDNA() {
var ui = SpreadsheetApp.getUi();
var clients = getClientList();
if (clients.length === 0) {
showAlert("No Active Clients", "No active clients found.");
return;
}
var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
var clientIdx = askNumber(ui, "LOG DNA (1/4)",
"Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
if (clientIdx === null) return;
if (clientIdx < 1 || clientIdx > clients.length) {
showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
return;
}
var clientCode = clients[clientIdx - 1].split(" — ")[0];
var dnaDate = askDate(ui, "LOG DNA (2/4)", "Date of missed session:");
if (!dnaDate) return;
var sessionNo = getNextSessionNumber(clientCode);
var contactIdx = askNumber(ui, "LOG DNA (3/4)",
"Type the NUMBER for contact attempted:\n\n" +
"1. Yes — Email\n2. Yes — Phone\n3. Yes — Both\n4. No contact attempted");
if (contactIdx === null) return;
var contactOptions = ["Yes — Email", "Yes — Phone", "Yes — Both", "No contact attempted"];
if (contactIdx < 1 || contactIdx > 4) {
showAlert("Invalid Selection", "Please enter a number between 1 and 4.");
return;
}
var contact = contactOptions[contactIdx - 1];
var safeguardingIdx = askNumber(ui, "LOG DNA (4/4)",
"Type the NUMBER for safeguarding concern:\n\n1. No\n2. Yes — noted\n3. Yes — escalated");
if (safeguardingIdx === null) return;
var safeguardingOptions = ["No", "Yes — noted", "Yes — escalated"];
if (safeguardingIdx < 1 || safeguardingIdx > 3) {
showAlert("Invalid Selection", "Please enter 1, 2, or 3.");
return;
}
var safeguarding = safeguardingOptions[safeguardingIdx - 1];
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dnaSheet = ss.getSheetByName("📋 DNA_Log");
dnaSheet.appendRow([dnaDate, clientCode, sessionNo, contact, "", "", safeguarding, ""]);
var lastRow = dnaSheet.getLastRow();
dnaSheet.getRange(lastRow, 1, 1, 8).setFontColor("#000000").setFontStyle("normal");
if (safeguarding !== "No") {
dnaSheet.getRange(lastRow, 7).setBackground("#ffcccc");
showAlert("⚠️ Safeguarding Concern Flagged",
"DNA logged for " + clientCode + " on " + formatDisplayDate(dnaDate) + ".\n\n" +
"A safeguarding concern has been noted. Consider:\n" +
"• Bringing to supervision\n" +
"• Contacting the client's emergency contact if appropriate\n" +
"• Documenting all actions taken in the DNA_Log notes column");
} else {
showAlert("✅ DNA Logged",
"Did Not Attend logged for " + clientCode + " on " + formatDisplayDate(dnaDate) + ".");
}
}
// ============================================================
// CLOSE CLIENT CASE
// ============================================================
function closeClientCase() {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet(); // ← declared at top to avoid reference bug
var clients = getClientList();
if (clients.length === 0) {
showAlert("No Active Clients", "No active clients to close.");
return;
}
var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
var clientIdx = askNumber(ui, "CLOSE CLIENT CASE (1/3)",
"Type the NUMBER of the client to close (e.g. type 1, not the client name):\n\n" + clientMenu);
if (clientIdx === null) return;
if (clientIdx < 1 || clientIdx > clients.length) {
showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
return;
}
var clientCode = clients[clientIdx - 1].split(" — ")[0];
var endDate = askDate(ui, "CLOSE CLIENT CASE (2/3)", "Date of final session:");
if (!endDate) return;
var minorIdx = askNumber(ui, "CLOSE CLIENT CASE (3/3)",
"Type the NUMBER for minor status:\n\n" +
"1. No — adult client\n2. Yes — was a minor at any point during therapy");
if (minorIdx === null) return;
if (minorIdx < 1 || minorIdx > 2) {
showAlert("Invalid Selection", "Please enter 1 or 2.");
return;
}
var wasMinor = (minorIdx === 2);
// Calculate retention deadline
var endDateObj = new Date(endDate);
var sevenYearsAfterEnd = new Date(endDateObj);
sevenYearsAfterEnd.setFullYear(sevenYearsAfterEnd.getFullYear() + 7);
var retentionDate = sevenYearsAfterEnd;
var retentionNote = "7 years after final session";
if (wasMinor) {
var clientSheet2 = ss.getSheetByName("👥 Clients");
var clientData2 = clientSheet2.getDataRange().getValues();
var clientDOB = null;
for (var k = 1; k < clientData2.length; k++) {
if (clientData2[k][0] === clientCode && clientData2[k][5]) {
clientDOB = new Date(clientData2[k][5]);
break;
}
}
if (clientDOB && !isNaN(clientDOB)) {
var age25Date = new Date(clientDOB);
age25Date.setFullYear(age25Date.getFullYear() + 25);
if (age25Date > sevenYearsAfterEnd) {
retentionDate = age25Date;
retentionNote = "Until age 25 (longer than 7-year rule)";
} else {
retentionNote = "7 years after final session (longer than until age 25)";
}
} else {
var fallbackDate = new Date(endDateObj);
fallbackDate.setFullYear(fallbackDate.getFullYear() + 25);
retentionDate = fallbackDate;
retentionNote = "Minor — no DOB recorded. Conservative 25-year deadline set. Update once DOB confirmed.";
}
}
var retentionStr = Utilities.formatDate(retentionDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
// Update Clients tab
var clientSheet = ss.getSheetByName("👥 Clients");
var data = clientSheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
if (data[i][0] === clientCode) {
clientSheet.getRange(i + 1, 8).setValue(endDate);
clientSheet.getRange(i + 1, 9).setValue("Closed");
clientSheet.getRange(i + 1, 11).setValue(retentionStr);
break;
}
}
// ── Auto-move client folder to 5_Archives ──
var moved = false;
try {
var practiceFolder = getPracticeFolder();
var clinicalFolder = null;
var archivesFolder = null;
if (practiceFolder) {
var subs = practiceFolder.getFolders();
while (subs.hasNext()) {
var sub = subs.next();
var subName = sub.getName();
if (subName.indexOf("1_Clinical_Records") !== -1) clinicalFolder = sub;
if (subName.indexOf("5_Archives") !== -1) archivesFolder = sub;
}
}
var archivedSubfolder = null;
if (archivesFolder) {
var archiveSubs = archivesFolder.getFolders();
while (archiveSubs.hasNext()) {
var archiveSub = archiveSubs.next();
if (archiveSub.getName().indexOf("Clinical_Records_Archived") !== -1) {
archivedSubfolder = archiveSub;
}
}
}
if (clinicalFolder && archivedSubfolder) {
var clientFolders = clinicalFolder.getFolders();
while (clientFolders.hasNext()) {
var clientFolder = clientFolders.next();
if (clientFolder.getName().indexOf(clientCode) !== -1) {
archivedSubfolder.addFolder(clientFolder);
clinicalFolder.removeFolder(clientFolder);
moved = true;
break;
}
}
}
} catch(e) {}
var moveMsg = moved
? "✅ Their folder has been moved to 5_Archives automatically."
: "⚠️ Could not move folder automatically.\nPlease move it manually:\nFROM: 1_Clinical_Records → " + clientCode + "\nTO: 5_Archives → Clinical_Records_Archived";
showAlert("✅ Client Case Closed — " + clientCode,
"Status: Closed\n" +
"End date: " + formatDisplayDate(endDate) + "\n" +
"Retention deadline: " + formatDisplayDate(retentionStr) + "\n" +
"Basis: " + retentionNote + "\n\n" +
moveMsg + "\n\n" +
"Reminder: Complete the Therapy Ending Summary template\n" +
"(4_Templates_Policies → Clinical_Templates)");
}
// ============================================================
// GENERATE SUPERBILL (US)
// ============================================================
function generateSuperbill() {
var ui = SpreadsheetApp.getUi();
var settings = getSettings();
var clients = getClientList();
if (clients.length === 0) {
showAlert("No Active Clients", "No active clients found.");
return;
}
var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
var clientIdx = askNumber(ui, "GENERATE SUPERBILL (1/3)",
"Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
if (clientIdx === null) return;
if (clientIdx < 1 || clientIdx > clients.length) {
showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
return;
}
var clientCode = clients[clientIdx - 1].split(" — ")[0];
var clientName = clients[clientIdx - 1].split(" — ")[1];
var periodInput = askUser(ui, "GENERATE SUPERBILL (2/3)",
"Billing period (e.g. January 2026 or 2026-01):");
if (!periodInput) return;
var diagnosisInput = askUser(ui, "GENERATE SUPERBILL (3/3)",
"Primary ICD-10 diagnosis code (optional — leave blank if not applicable):\n\n" +
"⚠️ Only enter if you are licensed to diagnose in your jurisdiction.");
if (!diagnosisInput) diagnosisInput = "[Not provided — see note]";
// Pull sessions for this client from Session_Log
var ss = SpreadsheetApp.getActiveSpreadsheet();
var logSheet = ss.getSheetByName("📅 Session_Log");
var logData = logSheet.getDataRange().getValues();
var sessions = [];
var totalCharged = 0;
var totalPaid = 0;
// Parse billing period for filtering — accept "January 2026" or "2026-01"
var filterMonth = -1;
var filterYear = -1;
var monthNames = ["january","february","march","april","may","june","july","august","september","october","november","december"];
var periodLower = periodInput.toLowerCase().trim();
var periodParts = periodLower.split(/[\s\-\/]+/);
for (var p = 0; p < periodParts.length; p++) {
var mIdx = monthNames.indexOf(periodParts[p]);
if (mIdx !== -1) filterMonth = mIdx + 1;
var maybeYear = parseInt(periodParts[p]);
if (!isNaN(maybeYear) && maybeYear > 2000) filterYear = maybeYear;
}
// "2026-01" format
if (filterMonth === -1 && periodParts.length >= 2) {
var y = parseInt(periodParts[0]), m = parseInt(periodParts[1]);
if (!isNaN(y) && !isNaN(m)) { filterYear = y; filterMonth = m; }
}
for (var i = 1; i < logData.length; i++) {
if (logData[i][1] === clientCode && logData[i][0] !== "Date" && logData[i][0] !== "") {
var rawDate = logData[i][0];
var rowDate = (rawDate instanceof Date) ? rawDate : new Date(rawDate);
if (isNaN(rowDate.getTime())) continue;
// Apply period filter if we successfully parsed it
if (filterMonth !== -1 && filterYear !== -1) {
if (rowDate.getMonth() + 1 !== filterMonth || rowDate.getFullYear() !== filterYear) continue;
}
var sessionDate = Utilities.formatDate(rowDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
var fee = parseFloat(logData[i][5]) || 0;
var paid = logData[i][6];
var cptCode = logData[i][9] || "90837";
sessions.push({
date: sessionDate,
duration: logData[i][3],
fee: fee,
paid: paid === "Yes" ? fee : 0,
cpt: cptCode
});
totalCharged += fee;
if (paid === "Yes") totalPaid += fee;
}
}
if (sessions.length === 0) {
showAlert("No Sessions Found", "No logged sessions found for " + clientCode + ".");
return;
}
// Get client details
var clientSheet = ss.getSheetByName("👥 Clients");
var clientData = clientSheet.getDataRange().getValues();
var clientDOB = "";
for (var j = 1; j < clientData.length; j++) {
if (clientData[j][0] === clientCode) {
clientDOB = clientData[j][5] || "Not recorded";
break;
}
}
// Create superbill document
var doc = DocumentApp.create("Superbill — " + clientCode + " — " + periodInput);
var b = doc.getBody();
b.appendParagraph("SUPERBILL").setHeading(DocumentApp.ParagraphHeading.HEADING1);
b.appendParagraph("Statement Date: " + formatDisplayDate(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd")));
b.appendParagraph("Billing Period: " + periodInput);
b.appendParagraph("");
b.appendParagraph("Provider Information").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph("Provider: " + settings["Therapist Name"]);
b.appendParagraph("Practice: " + settings["Practice Name"]);
b.appendParagraph("Credentials: [ADD CREDENTIALS]");
b.appendParagraph("NPI: [ADD NPI NUMBER]");
b.appendParagraph("Tax ID / EIN: [ADD TAX ID]");
b.appendParagraph("Email: " + settings["Practice Email"]);
b.appendParagraph("");
b.appendParagraph("Client Information").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph("Client: " + clientName);
b.appendParagraph("Client Code: " + clientCode);
b.appendParagraph("Date of Birth: " + clientDOB);
b.appendParagraph("Insurance Provider: [CLIENT TO ADD]");
b.appendParagraph("Member ID: [CLIENT TO ADD]");
b.appendParagraph("");
b.appendParagraph("Diagnosis").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph("Primary ICD-10: " + diagnosisInput);
b.appendParagraph("⚠️ Diagnosis codes must be assigned by a licensed clinician only.").setItalic(true);
b.appendParagraph("");
b.appendParagraph("Services Rendered").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph("Date | CPT | Description | Mins | Fee | Paid");
b.appendParagraph("─────────────────────────────────────────────────────────────");
sessions.forEach(function(s) {
b.appendParagraph(
s.date + " | " + s.cpt + " | Individual Psychotherapy | " +
s.duration + " | " + settings["Currency Symbol"] + s.fee +
" | " + settings["Currency Symbol"] + s.paid
);
});
b.appendParagraph("─────────────────────────────────────────────────────────────");
b.appendParagraph("TOTAL CHARGED: " + settings["Currency Symbol"] + totalCharged.toFixed(2) +
" | TOTAL PAID: " + settings["Currency Symbol"] + totalPaid.toFixed(2) +
" | BALANCE: " + settings["Currency Symbol"] + (totalCharged - totalPaid).toFixed(2));
b.appendParagraph("");
b.appendParagraph("Provider Certification").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph("I certify that the services listed above were medically necessary and personally rendered by me.");
b.appendParagraph("");
b.appendParagraph("Signature: ___________________________________________ Date: ___________");
doc.saveAndClose();
// Auto-move to 3_Finance → Superbills
var superbillMoved = false;
try {
var practiceFolder = getPracticeFolder();
if (practiceFolder) {
var pSubs = practiceFolder.getFolders();
while (pSubs.hasNext()) {
var pSub = pSubs.next();
if (pSub.getName().indexOf("3_Finance") !== -1) {
var fSubs = pSub.getFolders();
while (fSubs.hasNext()) {
var fSub = fSubs.next();
if (fSub.getName() === "Superbills") {
var docFile = DriveApp.getFileById(doc.getId());
fSub.addFile(docFile);
DriveApp.getRootFolder().removeFile(docFile);
superbillMoved = true;
break;
}
}
break;
}
}
}
} catch(e) {}
var sbLocationMsg = superbillMoved
? "Saved to: 3_Finance → Superbills"
: "Created in your Drive root — move to: 3_Finance → Superbills";
showAlert("✅ Superbill Created",
"Superbill generated for " + clientCode + " — " + periodInput + ".\n\n" +
"Total charged: " + settings["Currency Symbol"] + totalCharged.toFixed(2) + "\n" +
"Total paid: " + settings["Currency Symbol"] + totalPaid.toFixed(2) + "\n\n" +
sbLocationMsg + "\n\n" +
"Remember to add your NPI number and credentials before sending.");
}
// ============================================================
// GENERATE INVOICE / RECEIPT
// ============================================================
function generateInvoice() {
var ui = SpreadsheetApp.getUi();
var settings = getSettings();
var clients = getClientList();
if (clients.length === 0) {
showAlert("No Active Clients", "No active clients found.");
return;
}
var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
var clientIdx = askNumber(ui, "GENERATE INVOICE (1/2)",
"Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
if (clientIdx === null) return;
if (clientIdx < 1 || clientIdx > clients.length) {
showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
return;
}
var clientCode = clients[clientIdx - 1].split(" — ")[0];
var clientName = clients[clientIdx - 1].split(" — ")[1];
var periodInput = askUser(ui, "GENERATE INVOICE (2/2)", "Billing period (e.g. January 2026):");
if (!periodInput) return;
// Pull sessions from log
var ss = SpreadsheetApp.getActiveSpreadsheet();
var logSheet = ss.getSheetByName("📅 Session_Log");
var logData = logSheet.getDataRange().getValues();
var sessions = [];
var totalFee = 0;
var totalPaid = 0;
// Parse billing period for filtering
var filterMonth = -1;
var filterYear = -1;
var monthNames = ["january","february","march","april","may","june","july","august","september","october","november","december"];
var periodLower = periodInput.toLowerCase().trim();
var periodParts = periodLower.split(/[\s\-\/]+/);
for (var p = 0; p < periodParts.length; p++) {
var mIdx = monthNames.indexOf(periodParts[p]);
if (mIdx !== -1) filterMonth = mIdx + 1;
var maybeYear = parseInt(periodParts[p]);
if (!isNaN(maybeYear) && maybeYear > 2000) filterYear = maybeYear;
}
if (filterMonth === -1 && periodParts.length >= 2) {
var y = parseInt(periodParts[0]), m = parseInt(periodParts[1]);
if (!isNaN(y) && !isNaN(m)) { filterYear = y; filterMonth = m; }
}
for (var i = 1; i < logData.length; i++) {
if (logData[i][1] === clientCode && logData[i][0] !== "Date" && logData[i][0] !== "") {
var rawDate = logData[i][0];
var rowDate = (rawDate instanceof Date) ? rawDate : new Date(rawDate);
if (isNaN(rowDate.getTime())) continue;
if (filterMonth !== -1 && filterYear !== -1) {
if (rowDate.getMonth() + 1 !== filterMonth || rowDate.getFullYear() !== filterYear) continue;
}
var fee = parseFloat(logData[i][5]) || 0;
var paid = logData[i][6];
var displayDate = Utilities.formatDate(rowDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
sessions.push({
date: displayDate,
duration: logData[i][3],
fee: fee,
invoiceRef: logData[i][8],
paid: paid
});
totalFee += fee;
if (paid === "Yes") totalPaid += fee;
}
}
if (sessions.length === 0) {
showAlert("No Sessions Found", "No logged sessions for " + clientCode + ".");
return;
}
var invoiceNumber = "INV-" + clientCode + "-" + new Date().getFullYear() +
"-" + String(new Date().getMonth() + 1).padStart(2, "0");
var doc = DocumentApp.create("Invoice — " + clientCode + " — " + periodInput);
var b = doc.getBody();
b.appendParagraph("INVOICE").setHeading(DocumentApp.ParagraphHeading.HEADING1);
b.appendParagraph("Invoice No: " + invoiceNumber);
b.appendParagraph("Date: " + formatDisplayDate(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd")));
b.appendParagraph("Period: " + periodInput);
b.appendParagraph("");
b.appendParagraph("From").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph(settings["Therapist Name"]);
b.appendParagraph(settings["Practice Name"]);
b.appendParagraph(settings["Practice Email"]);
b.appendParagraph("");
b.appendParagraph("To").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph(clientName + " (Client Ref: " + clientCode + ")");
b.appendParagraph("");
b.appendParagraph("Sessions").setHeading(DocumentApp.ParagraphHeading.HEADING2);
b.appendParagraph("Date | Duration | Description | Fee | Status");
b.appendParagraph("─────────────────────────────────────────────────────");
sessions.forEach(function(s) {
b.appendParagraph(
s.date + " | " + s.duration + " min | Therapy Session | " +
settings["Currency Symbol"] + s.fee + " | " + s.paid
);
});
b.appendParagraph("─────────────────────────────────────────────────────");
b.appendParagraph("TOTAL: " + settings["Currency Symbol"] + totalFee.toFixed(2) +
" | PAID: " + settings["Currency Symbol"] + totalPaid.toFixed(2) +
" | OUTSTANDING: " + settings["Currency Symbol"] + (totalFee - totalPaid).toFixed(2));
b.appendParagraph("");
doc.saveAndClose();
// Auto-move to 3_Finance → Invoices
var invoiceMoved = false;
try {
var practiceFolder = getPracticeFolder();
if (practiceFolder) {
var pSubs = practiceFolder.getFolders();
while (pSubs.hasNext()) {
var pSub = pSubs.next();
if (pSub.getName().indexOf("3_Finance") !== -1) {
var fSubs = pSub.getFolders();
while (fSubs.hasNext()) {
var fSub = fSubs.next();
if (fSub.getName() === "Invoices") {
var docFile = DriveApp.getFileById(doc.getId());
fSub.addFile(docFile);
DriveApp.getRootFolder().removeFile(docFile);
invoiceMoved = true;
break;
}
}
break;
}
}
}
} catch(e) {}
var locationMsg = invoiceMoved
? "Saved to: 3_Finance → Invoices"
: "Created in your Drive root — move to: 3_Finance → Invoices";
showAlert("✅ Invoice Created",
"Invoice generated for " + clientCode + " — " + periodInput + ".\n\n" +
"Total: " + settings["Currency Symbol"] + totalFee.toFixed(2) + "\n" +
"Outstanding: " + settings["Currency Symbol"] + (totalFee - totalPaid).toFixed(2) + "\n\n" +
locationMsg);
}
// ============================================================
// HELP
// ============================================================
function showHelp() {
showAlert("🏥 Practice Tools — Help",
"MENU OPTIONS:\n\n" +
"➕ Add New Client\n" +
"Assigns a client code, creates their Drive folder, and adds them to the Client Index.\n\n" +
"📅 Log Session\n" +
"Records a completed session with date, format, fee and payment status.\n\n" +
"📋 Log DNA\n" +
"Records a missed or cancelled session and flags safeguarding concerns.\n\n" +
"📁 Close Client Case\n" +
"Updates client status, records end date, calculates retention deadline, and moves folder to Archives.\n\n" +
"📄 Generate Superbill (US)\n" +
"Creates a superbill from logged sessions for insurance reimbursement. US therapists only.\n\n" +
"🧾 Generate Invoice\n" +
"Creates an invoice from logged sessions. You may want to format it before sending.\n\n" +
"─────────────────────────\n" +
"TIPS:\n" +
"• When selecting from a numbered list, type the NUMBER only (e.g. type 2, not the client name).\n" +
"• Dates accept many formats: today, yesterday, 05/03/2026, 5 March 2026.\n" +
"• The Income Dashboard year can be changed in cell E4 of that tab.\n" +
"• Invoice and superbill generation pull from Session_Log automatically.\n\n" +
"For support, contact your practice infrastructure provider.");
}
// ============================================================
// ON EDIT — Clear yellow highlight when Paid status changes
// ============================================================
function onEdit(e) {
try {
var sheet = e.range.getSheet();
if (sheet.getName() !== "📅 Session_Log") return;
// Column 7 = Paid
if (e.range.getColumn() !== 7) return;
var row = e.range.getRow();
if (row < 2) return; // skip header
var value = e.range.getValue();
var rowRange = sheet.getRange(row, 1, 1, 11);
if (value === "Yes" || value === "Waived") {
rowRange.setBackground("#ffffff");
} else if (value === "No") {
sheet.getRange(row, 7).setBackground("#fff3cd"); // yellow on paid cell only
}
} catch(err) {
// Silent — onEdit must never throw to the user
}
}
// ============================================================
// UTILITY
// ============================================================
function askUser(ui, title, message) {
var response = ui.prompt(title, message, ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() !== ui.Button.OK) return null;
var text = response.getResponseText().trim();
return text || null;
}
function showAlert(title, message) {
SpreadsheetApp.getUi().alert(title, message, SpreadsheetApp.getUi().ButtonSet.OK);
}
function getInitials(firstName, lastName) {
return (firstName.charAt(0) + lastName.charAt(0)).toUpperCase();
}
/**
* Parses a flexible date input into a YYYY-MM-DD string.
* Accepts: "today", "yesterday", "2026-03-05", "05/03/2026",
* "5 March 2026", "March 5 2026", "05-03-2026"
* Returns null if unparseable.
*/
function parseFlexibleDate(input) {
if (!input) return null;
var s = input.trim().toLowerCase();
if (s === "today") {
return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
}
if (s === "yesterday") {
var y = new Date(); y.setDate(y.getDate() - 1);
return Utilities.formatDate(y, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
var monthNames = {
january:1, jan:1, february:2, feb:2, march:3, mar:3,
april:4, apr:4, may:5, june:6, jun:6,
july:7, jul:7, august:8, aug:8, september:9, sep:9, sept:9,
october:10, oct:10, november:11, nov:11, december:12, dec:12
};
// YYYY-MM-DD or YYYY/MM/DD or YYYY-DD-MM (try both orderings)
var isoMatch = s.match(/^(\d{4})[-\/](\d{1,2})[-\/](\d{1,2})$/);
if (isoMatch) {
var yr = parseInt(isoMatch[1]);
var a = parseInt(isoMatch[2]);
var b = parseInt(isoMatch[3]);
// Assume YYYY-MM-DD
var d = new Date(yr, a - 1, b);
if (!isNaN(d.getTime())) return Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
// DD/MM/YYYY or DD-MM-YYYY
var dmyMatch = s.match(/^(\d{1,2})[-\/](\d{1,2})[-\/](\d{4})$/);
if (dmyMatch) {
var d2 = new Date(parseInt(dmyMatch[3]), parseInt(dmyMatch[2]) - 1, parseInt(dmyMatch[1]));
if (!isNaN(d2.getTime())) return Utilities.formatDate(d2, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
// "5 March 2026" or "March 5 2026"
var parts = s.split(/[\s,]+/);
var day = null, month = null, year = null;
parts.forEach(function(p) {
var n = parseInt(p);
if (!isNaN(n) && n > 31) { year = n; }
else if (!isNaN(n) && n >= 1 && n <= 31 && day === null) { day = n; }
else if (monthNames[p]) { month = monthNames[p]; }
});
if (day && month && year) {
var d3 = new Date(year, month - 1, day);
if (!isNaN(d3.getTime())) return Utilities.formatDate(d3, Session.getScriptTimeZone(), "yyyy-MM-dd");
}
return null;
}
function formatDisplayDate(isoString) {
if (!isoString) return "";
var d = new Date(isoString);
if (isNaN(d.getTime())) return isoString;
var months = ["January","February","March","April","May","June",
"July","August","September","October","November","December"];
return d.getDate() + " " + months[d.getMonth()] + " " + d.getFullYear();
}
function askDate(ui, stepLabel, promptText) {
var raw = askUser(ui, stepLabel,
promptText + "\n\nAccepted formats:\n" +
" today | yesterday\n" +
" 05/03/2026 | 2026-03-05\n" +
" 5 March 2026");
if (!raw) return null;
var parsed = parseFlexibleDate(raw);
if (!parsed) {
showAlert("Invalid Date", "\"" + raw + "\" was not recognised as a valid date.\n\nTry: today, 05/03/2026, or 5 March 2026.");
return null;
}
return parsed;
}
function askNumber(ui, stepLabel, promptText) {
var raw = askUser(ui, stepLabel, promptText);
if (!raw) return null;
var n = parseInt(raw);
if (isNaN(n)) {
showAlert("Invalid Entry", "Please type a NUMBER only (e.g. type 1, not the full text).");
return null;
}
return n;
}"
language="javascriptWant this done for you?
We set up the whole infrastructure — Drive, booking, payments, website — so you can focus on your clients.
Start a Project →