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, including 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 it 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 and 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 for client communication, never personal Gmail");
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 your password and 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 →