Step 1
1/**
2 * ============================================================
3 * PRACTICE INFRASTRUCTURE — STEP 1: DRIVE SETUP
4 * Version 3.2
5 * ============================================================
6 * Run this ONCE from script.google.com to build your
7 * complete Google Drive folder structure and all documents.
8 *
9 * BEFORE RUNNING:
10 * 1. Fill in your details in the CONFIGURATION section below
11 * 2. Click Run → runDriveSetup
12 * 3. Approve permissions when prompted
13 * 4. Wait ~60 seconds for everything to build
14 * 5. Open Google Drive — find your Practice Infrastructure folder
15 *
16 * THEN: Open the Client_Index spreadsheet and follow the
17 * instructions inside to install the Practice Tools menu.
18 *
19 * Run this script ONCE only. Running again creates duplicates.
20 * ============================================================
21 */
22 
23 
24// ============================================================
25// CONFIGURATION — Fill in before running
26// ============================================================
27 
28var CONFIG = {
29 practiceName : "My Practice", // e.g. "Sarah Jones Therapy"
30 therapistName : "Your Name", // e.g. "Sarah Jones"
31 practiceEmail : "hello@mypractice.com", // Your Workspace email
32 country : "UK", // "UK", "Ireland" or "US"
33 defaultFee : 80, // Your standard session fee (number only)
34 currency : "£", // "£", "€" or "$"
35 sessionLength : 50, // Standard session length in minutes
36 version : "3.2"
37};
38 
39var SETUP_DATE = new Date().toDateString();
40 
41 
42// ============================================================
43// MAIN — Run this
44// ============================================================
45 
46function runDriveSetup() {
47 log("================================================");
48 log("Practice Infrastructure Setup v" + CONFIG.version);
49 log("Practice: " + CONFIG.practiceName);
50 log("Country: " + CONFIG.country);
51 log("Date: " + SETUP_DATE);
52 log("================================================");
53 
54 var root = DriveApp.getRootFolder();
55 var practiceFolder = createFolder(root, "🏥 " + CONFIG.practiceName + " — Practice Infrastructure");
56 
57 // Top level folders
58 var clinical = createFolder(practiceFolder, "1_Clinical_Records");
59 var admin = createFolder(practiceFolder, "2_Administrative");
60 var finance = createFolder(practiceFolder, "3_Finance");
61 var templates = createFolder(practiceFolder, "4_Templates_Policies");
62 var archives = createFolder(practiceFolder, "5_Archives");
63 
64 // Build each section
65 buildStartHere(practiceFolder);
66 buildClinical(clinical);
67 buildAdministrative(admin, practiceFolder);
68 buildFinance(finance);
69 buildTemplates(templates);
70 buildArchives(archives);
71 
72 log("================================================");
73 log("✅ DRIVE SETUP COMPLETE");
74 log("Next step: Open Client_Index in 2_Administrative");
75 log("Go to Extensions → Apps Script → paste STEP2 script");
76 log("================================================");
77}
78 
79 
80// ============================================================
81// ⭐ START HERE
82// ============================================================
83 
84function buildStartHere(parent) {
85 var doc = DocumentApp.create("⭐ START HERE — Your Practice Infrastructure Guide");
86 var b = doc.getBody();
87 
88 h1(b, "Welcome to Your Practice Infrastructure");
89 b.appendParagraph("Practice: " + CONFIG.practiceName);
90 b.appendParagraph("Therapist: " + CONFIG.therapistName);
91 b.appendParagraph("Set up: " + SETUP_DATE + " | Version: " + CONFIG.version);
92 b.appendParagraph("");
93 
94 h2(b, "What This System Is");
95 b.appendParagraph("This Google Drive folder is your complete practice management system. Everything related to your practice — client records, session notes, finance, policies and templates — lives here in one secure, organised place in your own Google Workspace account. Nobody else has access unless you grant it.");
96 b.appendParagraph("");
97 
98 h2(b, "Your 5 Folders");
99 bold(b, "📁 1_Clinical_Records");
100 b.appendParagraph("One folder per client. Session notes, consent forms, intake forms and correspondence. Your most sensitive folder — never share publicly.");
101 b.appendParagraph("");
102 bold(b, "📁 2_Administrative");
103 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.");
104 b.appendParagraph("");
105 bold(b, "📁 3_Finance");
106 b.appendParagraph("Invoices, receipts, Stripe summaries and tax documents. Safe to share with an accountant — this folder only, never Clinical Records.");
107 b.appendParagraph("");
108 bold(b, "📁 4_Templates_Policies");
109 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.");
110 b.appendParagraph("");
111 bold(b, "📁 5_Archives");
112 b.appendParagraph("Closed client folders. Move here when therapy ends. Records stay until retention deadline, then permanently deleted and logged.");
113 b.appendParagraph("");
114 
115 h2(b, "Your Two-Step Setup");
116 bold(b, "Step 1 — Drive Setup (done)");
117 b.appendParagraph("Your folder structure and all documents have been created.");
118 b.appendParagraph("");
119 bold(b, "Step 2 — Install Practice Tools Menu");
120 b.appendParagraph("Open 2_Administrative → Client_Index. Go to Extensions → Apps Script. Paste the STEP2 script provided by your practice support. Click Save, then Run → installPracticeTools. Reload the spreadsheet — you will see a Practice Tools menu appear.");
121 b.appendParagraph("");
122 b.appendParagraph("Once installed, use Practice Tools for everything: adding clients, logging sessions, generating superbills, closing cases and viewing your income dashboard.");
123 b.appendParagraph("");
124 
125 h2(b, "Daily Workflow");
126 b.appendParagraph("After each session: Practice Tools → Log Session");
127 b.appendParagraph("New client: Practice Tools → Add New Client");
128 b.appendParagraph("End of month: Practice Tools → Generate Superbill / Invoice");
129 b.appendParagraph("Closing a case: Practice Tools → Close Client Case");
130 b.appendParagraph("Annual review: 4_Templates_Policies → Policies → Annual Compliance Checklist");
131 b.appendParagraph("");
132 
133 h2(b, "Security Rules");
134 b.appendParagraph("✅ Always use your Workspace email — never personal Gmail — for client communication");
135 b.appendParagraph("✅ Keep two-factor authentication enabled on your Google account at all times");
136 b.appendParagraph("✅ Never share client folders publicly or via a shareable link");
137 b.appendParagraph("✅ Never discuss clinical content via WhatsApp, SMS or social media");
138 b.appendParagraph("✅ If you suspect unauthorised access, change your password immediately");
139 b.appendParagraph("");
140 
141 h2(b, "If a Data Breach Occurs");
142 b.appendParagraph("1. Note date and time of discovery");
143 b.appendParagraph("2. Secure your account — change password, check active sessions in Google");
144 b.appendParagraph("3. Assess what data may be affected");
145 b.appendParagraph("4. Notify supervisory authority within 72 hours if personal data was involved:");
146 b.appendParagraph(" UK: ico.org.uk | Ireland: dataprotection.ie | US: hhs.gov/hipaa");
147 b.appendParagraph("5. Notify affected clients without undue delay if they are at risk");
148 b.appendParagraph("6. Document everything in 2_Administrative → Data_Breach_Log");
149 b.appendParagraph("");
150 b.appendParagraph("Last updated: " + SETUP_DATE);
151 
152 doc.saveAndClose();
153 moveFile(doc.getId(), parent);
154 log("Created: ⭐ START HERE");
155}
156 
157 
158// ============================================================
159// 1. CLINICAL RECORDS
160// ============================================================
161 
162function buildClinical(parent) {
163 var example = createFolder(parent, "C001-EXAMPLE — Copy this folder for each new client");
164 createFolder(example, "01_Admin");
165 createFolder(example, "02_Consent_Forms");
166 createFolder(example, "03_Intake_Forms");
167 createFolder(example, "04_Session_Notes");
168 createFolder(example, "05_Client_Materials");
169 createFolder(example, "06_Correspondence");
170 
171 var doc = DocumentApp.create("📋 Clinical Records — Quick Reference");
172 var b = doc.getBody();
173 h1(b, "Clinical Records — Quick Reference");
174 b.appendParagraph("One sub-folder per client, named: ClientCode-Initials");
175 b.appendParagraph("Example: C002-AB = client code C002, initials A.B.");
176 b.appendParagraph("");
177 bold(b, "File naming convention:");
178 b.appendParagraph("YYYY-MM-DD_C001_SessionNote");
179 b.appendParagraph("YYYY-MM-DD_C001_ConsentForm");
180 b.appendParagraph("YYYY-MM-DD_C001_IntakeForm");
181 b.appendParagraph("");
182 bold(b, "Sub-folders:");
183 b.appendParagraph("01_Admin — referral letters, appointment log");
184 b.appendParagraph("02_Consent_Forms — signed consent and privacy notice");
185 b.appendParagraph("03_Intake_Forms — completed intake responses");
186 b.appendParagraph("04_Session_Notes — one note per session, dated");
187 b.appendParagraph("05_Client_Materials — resources and worksheets");
188 b.appendParagraph("06_Correspondence — emails and letters");
189 b.appendParagraph("");
190 b.appendParagraph("⚠️ Special category health data. Never share publicly. Never move to a personal account.");
191 doc.saveAndClose();
192 moveFile(doc.getId(), parent);
193 log("Created: Clinical Records");
194}
195 
196 
197// ============================================================
198// 2. ADMINISTRATIVE — Folder + Client Index Spreadsheet
199// ============================================================
200 
201function buildAdministrative(parent, practiceFolder) {
202 createFolder(parent, "Practice_Policies");
203 createFolder(parent, "Insurance_Referrals");
204 createFolder(parent, "Forms_Not_Tied_To_Client");
205 createFolder(parent, "Subject_Access_Requests");
206 createFolder(parent, "Data_Breach_Log");
207 
208 var ss = SpreadsheetApp.create("Client_Index — " + CONFIG.practiceName);
209 
210 // ── Tab: Settings ──
211 buildSettingsTab(ss);
212 
213 // ── Tab: Clients ──
214 buildClientsTab(ss);
215 
216 // ── Tab: Waitlist ──
217 buildWaitlistTab(ss);
218 
219 // ── Tab: Session_Log ──
220 buildSessionLogTab(ss);
221 
222 // ── Tab: Income_Dashboard ──
223 buildIncomeDashboardTab(ss);
224 
225 // ── Tab: Supervision_Log ──
226 buildSupervisionLogTab(ss);
227 
228 // ── Tab: CPD_Log ──
229 buildCPDLogTab(ss);
230 
231 // ── Tab: DNA_Log ──
232 buildDNALogTab(ss);
233 
234 // ── Tab: Deletion_Log ──
235 buildDeletionLogTab(ss);
236 
237 // ── Tab: SAR_Log ──
238 buildSARLogTab(ss);
239 
240 // ── Tab: How To Use ──
241 buildHowToUseTab(ss);
242 
243 // Remove default blank sheet if present
244 try {
245 var defaultSheet = ss.getSheetByName("Sheet1");
246 if (defaultSheet) ss.deleteSheet(defaultSheet);
247 } catch(e) {}
248 
249 // Move to Administrative folder
250 moveFile(ss.getId(), parent);
251 
252 // Build SAR procedure doc
253 buildSARProcedure(parent);
254 
255 log("Created: Administrative folder and Client Index");
256}
257 
258function buildSettingsTab(ss) {
259 var s = ss.insertSheet("⚙️ Settings");
260 s.getRange("A1").setValue("PRACTICE SETTINGS").setFontWeight("bold").setFontSize(14);
261 s.getRange("A2").setValue("Edit these values to configure your practice. Do not rename this tab.");
262 s.getRange("A2").setFontStyle("italic").setFontColor("#888888");
263 
264 var settings = [
265 ["Practice Name", CONFIG.practiceName],
266 ["Therapist Name", CONFIG.therapistName],
267 ["Practice Email", CONFIG.practiceEmail],
268 ["Country", CONFIG.country],
269 ["Default Session Fee", CONFIG.defaultFee],
270 ["Currency Symbol", CONFIG.currency],
271 ["Standard Session Length (mins)", CONFIG.sessionLength],
272 ["Script Version", CONFIG.version],
273 ["Setup Date", SETUP_DATE]
274 ];
275 
276 for (var i = 0; i < settings.length; i++) {
277 s.getRange(i + 4, 1).setValue(settings[i][0]).setFontWeight("bold");
278 s.getRange(i + 4, 2).setValue(settings[i][1]);
279 }
280 
281 s.setColumnWidth(1, 260);
282 s.setColumnWidth(2, 300);
283 s.getRange("A4:A12").setBackground("#f0f4f8");
284}
285 
286function buildClientsTab(ss) {
287 var s = ss.insertSheet("👥 Clients");
288 var headers = [
289 "Client Code","First Name","Last Name","Email","Phone",
290 "Date of Birth","Start Date","End Date","Status",
291 "Default Fee (" + CONFIG.currency + ")","Retention Deadline",
292 "Folder Link","Referral Source","Admin Notes"
293 ];
294 styleHeaderRow(s, headers, "#1a3a5c");
295 
296 // No example row — starts clean so Income Dashboard is accurate from day one
297 
298 var widths = [100,110,110,200,140,120,110,110,100,130,140,260,140,220];
299 setColumnWidths(s, widths);
300 s.setFrozenRows(1);
301 
302 // Status dropdown
303 applyDropdown(s, 2, 9, 200, ["Active","Closed","On Hold","Enquiry","Waitlist"]);
304 
305 // Closed = grey
306 applyConditionalFormat(s, "Closed", "#f5f5f5", "#999999", 2, 1, 200, headers.length);
307}
308 
309function buildWaitlistTab(ss) {
310 var s = ss.insertSheet("⏳ Waitlist");
311 var headers = [
312 "Name","Email","Phone","Date Added","Presenting Issue (brief)",
313 "Preferred Days/Times","Follow-up Date","Status","Notes"
314 ];
315 styleHeaderRow(s, headers, "#3a5c1a");
316 var widths = [160,200,140,120,220,180,130,120,220];
317 setColumnWidths(s, widths);
318 s.setFrozenRows(1);
319 applyDropdown(s, 2, 8, 100, ["Waiting","Offered Slot","Started","Declined","Unavailable"]);
320 addNote(s, "A1", "Track prospective clients here before they become active. Never record clinical detail — brief presenting issue only.");
321}
322 
323function buildSessionLogTab(ss) {
324 var s = ss.insertSheet("📅 Session_Log");
325 var headers = [
326 "Date","Client Code","Session No","Duration (mins)",
327 "Format","Fee (" + CONFIG.currency + ")","Paid","Payment Date",
328 "Invoice Ref","CPT Code (US)","Notes"
329 ];
330 styleHeaderRow(s, headers, "#1a3a5c");
331 var widths = [120,110,100,140,130,120,80,130,130,130,220];
332 setColumnWidths(s, widths);
333 s.setFrozenRows(1);
334 applyDropdown(s, 2, 5, 500, ["Video — Google Meet","In Person","Phone","Group"]);
335 applyDropdown(s, 2, 7, 500, ["Yes","No","Waived"]);
336 
337 // No example row — starts clean so Income Dashboard is accurate from day one
338 
339 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).");
340}
341 
342function buildIncomeDashboardTab(ss) {
343 var s = ss.insertSheet("📊 Income_Dashboard");
344 
345 s.getRange("A1").setValue("INCOME DASHBOARD").setFontWeight("bold").setFontSize(16);
346 s.getRange("A2").setValue("Auto-calculated from Session_Log. Do not edit this sheet directly.");
347 s.getRange("A2").setFontStyle("italic").setFontColor("#888888");
348 s.getRange("A1:F1").merge().setBackground("#1a3a5c").setFontColor("#ffffff");
349 
350 // ── Year selector ──
351 s.getRange("D4").setValue("VIEWING YEAR").setFontWeight("bold").setFontSize(11);
352 s.getRange("E4").setValue(new Date().getFullYear());
353 s.getRange("E4").setFontSize(14).setFontWeight("bold").setBackground("#fff3cd").setHorizontalAlignment("center");
354 s.getRange("F4").setValue("← edit this to see another year").setFontStyle("italic").setFontColor("#888888").setFontSize(9);
355 s.setColumnWidth(5, 120);
356 s.setColumnWidth(6, 220);
357 
358 // Current month section
359 s.getRange("A4").setValue("CURRENT MONTH").setFontWeight("bold").setFontSize(12);
360 s.getRange("A5").setValue("Sessions completed:");
361 s.getRange("A6").setValue("Income earned (" + CONFIG.currency + "):");
362 s.getRange("A7").setValue("Outstanding payments (" + CONFIG.currency + "):");
363 s.getRange("A8").setValue("Sessions unpaid:");
364 
365 // Formulas for current month (pull from Session_Log)
366 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")');
367 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))');
368 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))');
369 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")');
370 
371 // Year summary — all formulas reference $E$4 (year selector)
372 s.getRange("A10").setValue("YEAR SUMMARY").setFontWeight("bold").setFontSize(12);
373 s.getRange("A11").setValue("Sessions completed:");
374 s.getRange("A12").setValue("Total income (" + CONFIG.currency + "):");
375 s.getRange("A13").setValue("Outstanding payments (" + CONFIG.currency + "):");
376 s.getRange("A14").setValue("Active clients:");
377 s.getRange("A15").setValue("Average fee per session (" + CONFIG.currency + "):");
378 
379 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")');
380 s.getRange("B12").setFormula('=SUMPRODUCT((YEAR(\'📅 Session_Log\'!A2:A500)=$E$4)*(\'📅 Session_Log\'!G2:G500="Yes")*(\'📅 Session_Log\'!F2:F500))');
381 s.getRange("B13").setFormula('=SUMPRODUCT((YEAR(\'📅 Session_Log\'!A2:A500)=$E$4)*(\'📅 Session_Log\'!G2:G500="No")*(\'📅 Session_Log\'!F2:F500))');
382 s.getRange("B14").setFormula('=COUNTIF(\'👥 Clients\'!I:I,"Active")');
383 s.getRange("B15").setFormula('=IFERROR(B12/B11,0)');
384 
385 // Monthly breakdown — references $E$4
386 s.getRange("A17").setValue("MONTHLY BREAKDOWN").setFontWeight("bold").setFontSize(12);
387 s.getRange("B17").setFormula('="— "&$E$4').setFontWeight("bold").setFontSize(12);
388 var monthHeaders = ["Month","Sessions","Income (" + CONFIG.currency + ")","Outstanding (" + CONFIG.currency + ")","DNAs"];
389 for (var i = 0; i < monthHeaders.length; i++) {
390 s.getRange(18, i + 1).setValue(monthHeaders[i]).setFontWeight("bold").setBackground("#e8f0fe");
391 }
392 
393 var months = ["January","February","March","April","May","June",
394 "July","August","September","October","November","December"];
395 for (var m = 0; m < 12; m++) {
396 var row = 19 + m;
397 s.getRange(row, 1).setValue(months[m]);
398 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")');
399 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))');
400 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))');
401 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))');
402 }
403 
404 s.setColumnWidth(1, 160);
405 s.setColumnWidth(2, 160);
406 s.setColumnWidth(3, 180);
407 s.setColumnWidth(4, 200);
408 s.setColumnWidth(5, 120);
409 
410 // Style value cells
411 s.getRange("B5:B8").setBackground("#e8f4e8").setFontWeight("bold");
412 s.getRange("B11:B15").setBackground("#e8f4e8").setFontWeight("bold");
413}
414 
415function buildSupervisionLogTab(ss) {
416 var s = ss.insertSheet("🎓 Supervision_Log");
417 var headers = [
418 "Date","Supervisor Name","Duration (hrs)","Format",
419 "Cases Discussed (codes only)","Key Themes","Actions Agreed","Signature/Confirm"
420 ];
421 styleHeaderRow(s, headers, "#3a1a5c");
422 var widths = [120,180,120,130,220,220,220,160];
423 setColumnWidths(s, widths);
424 s.setFrozenRows(1);
425 applyDropdown(s, 2, 4, 200, ["In Person","Video","Phone","Group"]);
426 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.");
427}
428 
429function buildCPDLogTab(ss) {
430 var s = ss.insertSheet("📚 CPD_Log");
431 var headers = [
432 "Date","Activity Title","Provider","Type",
433 "Hours","Notes","Certificate/Evidence"
434 ];
435 styleHeaderRow(s, headers, "#5c3a1a");
436 var widths = [120,240,180,160,80,240,200];
437 setColumnWidths(s, widths);
438 s.setFrozenRows(1);
439 applyDropdown(s, 2, 4, 200, ["Training Course","Workshop","Webinar","Reading","Personal Therapy","Supervision","Peer Consultation","Conference","Other"]);
440 
441 // CPD total
442 s.getRange("I1").setValue("Total CPD Hours (this year):");
443 s.getRange("J1").setFormula('=SUMPRODUCT((YEAR(A2:A500)=YEAR(TODAY()))*(E2:E500))');
444 s.getRange("I1").setFontWeight("bold");
445 s.getRange("J1").setBackground("#e8f4e8").setFontWeight("bold");
446 
447 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.");
448}
449 
450function buildDNALogTab(ss) {
451 var s = ss.insertSheet("📋 DNA_Log");
452 var headers = [
453 "Date","Client Code","Session No","Contact Attempted",
454 "Client Response","Action Taken","Safeguarding Concern","Notes"
455 ];
456 styleHeaderRow(s, headers, "#5c1a1a");
457 var widths = [120,110,100,160,160,200,160,220];
458 setColumnWidths(s, widths);
459 s.setFrozenRows(1);
460 applyDropdown(s, 2, 4, 200, ["Yes — Email","Yes — Phone","Yes — Both","No contact attempted"]);
461 applyDropdown(s, 2, 7, 200, ["No","Yes — noted","Yes — escalated"]);
462 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.");
463}
464 
465function buildDeletionLogTab(ss) {
466 var s = ss.insertSheet("🗑️ Deletion_Log");
467 var headers = [
468 "Client Code","Retention Deadline","Deletion Date","Deleted By","Method","Confirmation"
469 ];
470 styleHeaderRow(s, headers, "#3a3a3a");
471 var widths = [120,160,140,160,200,280];
472 setColumnWidths(s, widths);
473 s.setFrozenRows(1);
474 applyDropdown(s, 2, 5, 200, ["Google Drive permanent delete + Trash emptied","Manual deletion confirmed"]);
475 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.");
476}
477 
478function buildSARLogTab(ss) {
479 var s = ss.insertSheet("📨 SAR_Log");
480 var headers = [
481 "Client Code","Request Date","Response Due (30 days)","Response Sent","Method","Notes"
482 ];
483 styleHeaderRow(s, headers, "#1a3a5c");
484 var widths = [120,140,180,140,160,280];
485 setColumnWidths(s, widths);
486 s.setFrozenRows(1);
487 applyDropdown(s, 2, 5, 200, ["Workspace email","Secure folder share","Post"]);
488 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.");
489}
490 
491function buildHowToUseTab(ss) {
492 var s = ss.insertSheet("ℹ️ How To Use");
493 s.getRange("A1").setValue("CLIENT INDEX — HOW TO USE").setFontWeight("bold").setFontSize(14);
494 
495 var content = [
496 [""],
497 ["⚙️ SETTINGS TAB"],
498 ["Edit your practice details here. These values are used throughout the system."],
499 [""],
500 ["👥 CLIENTS TAB"],
501 ["One row per client. Assign codes sequentially: C001, C002, C003 etc."],
502 ["Never reuse a code, even after a client has been deleted."],
503 ["Default Fee auto-populates session log when you add a new session via Practice Tools."],
504 ["Retention Deadline = End Date + 7 years. Add when closing a case."],
505 ["Paste the Google Drive folder link in Folder Link for one-click access."],
506 [""],
507 ["⏳ WAITLIST TAB"],
508 ["Track prospective clients before they start. Brief presenting issue only — no clinical detail."],
509 [""],
510 ["📅 SESSION_LOG TAB"],
511 ["Log every session here. Use Practice Tools → Log Session for a guided form."],
512 ["CPT Code is for US therapists only. Common codes: 90837 (60 min), 90834 (45 min), 90832 (30 min)."],
513 ["Fee auto-fills from the client's Default Fee in the Clients tab."],
514 [""],
515 ["📊 INCOME_DASHBOARD TAB"],
516 ["Auto-calculated from Session_Log. Never edit this tab directly."],
517 ["Shows current month totals and year-to-date breakdown."],
518 [""],
519 ["🎓 SUPERVISION_LOG TAB"],
520 ["Record all supervision. Use client codes only — never client names."],
521 ["Required by BACP (1.5 hrs/month min), IACP and most professional bodies."],
522 [""],
523 ["📚 CPD_LOG TAB"],
524 ["Log all CPD activity. Total hours for current year calculated automatically."],
525 ["BACP and IACP require 30 hours per year."],
526 [""],
527 ["📋 DNA_LOG TAB"],
528 ["Log every Did Not Attend and late cancellation."],
529 ["Flag safeguarding concerns — important if a vulnerable client stops responding."],
530 [""],
531 ["🗑️ DELETION_LOG TAB"],
532 ["Log every permanent deletion of client records. Keep this log permanently."],
533 [""],
534 ["📨 SAR_LOG TAB"],
535 ["Log all Subject Access Requests. 30-day response deadline."],
536 [""],
537 ["IMPORTANT"],
538 ["This spreadsheet contains personal data. Keep it private."],
539 ["Do not share with anyone not authorised to see client information."],
540 ];
541 
542 for (var i = 0; i < content.length; i++) {
543 s.getRange(i + 2, 1).setValue(content[i][0]);
544 if (content[i][0] && content[i][0].match(/^[⚙����������������⏳ℹ]/)) {
545 s.getRange(i + 2, 1).setFontWeight("bold").setFontSize(11);
546 }
547 }
548 s.setColumnWidth(1, 650);
549}
550 
551function buildSARProcedure(parent) {
552 var doc = DocumentApp.create("Procedure — Subject Access Requests");
553 var b = doc.getBody();
554 h1(b, "Subject Access Request (SAR) Procedure");
555 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Last reviewed: " + SETUP_DATE);
556 b.appendParagraph("");
557 h2(b, "What is a Subject Access Request?");
558 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.");
559 b.appendParagraph("");
560 h2(b, "Step-by-Step Response");
561 b.appendParagraph("1. Verify identity — confirm name, date of birth and contact email before releasing any data.");
562 b.appendParagraph("2. Log the request — open Client_Index → SAR_Log tab. Add client code, request date and 30-day deadline.");
563 b.appendParagraph("3. Gather the data — go to their folder in 1_Clinical_Records. Collect all documents containing their personal data.");
564 b.appendParagraph("4. Send via Workspace email — compile into PDF or secure folder share. Never use personal Gmail or WhatsApp.");
565 b.appendParagraph("5. Log the response — update SAR_Log with the date sent.");
566 b.appendParagraph("");
567 h2(b, "What You Are Not Required to Provide");
568 b.appendParagraph("• Personal reflective or supervision notes not held in the client file");
569 b.appendParagraph("• Data about third parties mentioned in notes");
570 b.appendParagraph("• Information where disclosure would prejudice legal proceedings");
571 b.appendParagraph("");
572 b.appendParagraph("If in doubt, consult your professional body or a GDPR adviser before responding.");
573 doc.saveAndClose();
574 moveFile(doc.getId(), parent);
575}
576 
577 
578// ============================================================
579// 3. FINANCE
580// ============================================================
581 
582function buildFinance(parent) {
583 createFolder(parent, "Invoices");
584 createFolder(parent, "Receipts");
585 createFolder(parent, "Tax_Documents");
586 createFolder(parent, "Stripe_Summaries");
587 createFolder(parent, "Superbills");
588 
589 var doc = DocumentApp.create("📋 Finance — Quick Reference");
590 var b = doc.getBody();
591 h1(b, "Finance Folder — Quick Reference");
592 bold(b, "Folder contents:");
593 b.appendParagraph("Invoices — client invoices. Name: YYYY-MM-DD_C001_Invoice");
594 b.appendParagraph("Receipts — business expense receipts");
595 b.appendParagraph("Tax_Documents — annual returns, accountant correspondence");
596 b.appendParagraph("Stripe_Summaries — monthly Stripe payout summaries");
597 b.appendParagraph("Superbills — US superbills generated for clients");
598 b.appendParagraph("");
599 bold(b, "Accountant access:");
600 b.appendParagraph("Share this Finance folder only. Right-click → Share → Viewer. Never share Clinical Records.");
601 b.appendParagraph("");
602 bold(b, "Invoice content:");
603 b.appendParagraph("Reference client codes only — never diagnoses or session content.");
604 b.appendParagraph("Example: 'Therapy session — C001 — 50 minutes'");
605 doc.saveAndClose();
606 moveFile(doc.getId(), parent);
607 log("Created: Finance folder");
608}
609 
610 
611// ============================================================
612// 4. TEMPLATES & POLICIES
613// ============================================================
614 
615function buildTemplates(parent) {
616 var consentFolder = createFolder(parent, "Consent_Templates");
617 var intakeFolder = createFolder(parent, "Intake_Templates");
618 var noteFolder = createFolder(parent, "Session_Note_Templates");
619 var billingFolder = createFolder(parent, "Billing_Templates");
620 var clinicalFolder = createFolder(parent, "Clinical_Templates");
621 var policyFolder = createFolder(parent, "Policies");
622 
623 buildConsentForm(consentFolder);
624 buildPrivacyNotice(consentFolder);
625 buildIntakeForm(intakeFolder);
626 buildSessionNoteTemplate(noteFolder);
627 buildSuperbill(billingFolder);
628 buildInsuranceReceipt(billingFolder);
629 buildReferralLetter(clinicalFolder);
630 buildEndingSummary(clinicalFolder);
631 buildRiskRegister(clinicalFolder);
632 buildDataRetentionPolicy(policyFolder);
633 buildTelehealthPolicy(policyFolder);
634 buildAnnualComplianceChecklist(policyFolder);
635 
636 log("Created: All templates and policies");
637}
638 
639function buildConsentForm(folder) {
640 var doc = DocumentApp.create("TEMPLATE — Therapy Consent Form");
641 var b = doc.getBody();
642 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.");
643 w1.setItalic(true); setRed(w1);
644 b.appendParagraph("");
645 h1(b, "Therapy Consent Form");
646 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Therapist: " + CONFIG.therapistName);
647 b.appendParagraph("Date: ___________ | Client Code: ___________ (therapist use only)");
648 b.appendParagraph("");
649 var pi = b.appendParagraph("Please read carefully before signing."); pi.setItalic(true);
650 b.appendParagraph("");
651 h2(b, "1. Confidentiality");
652 b.appendParagraph("Everything discussed in sessions is confidential. I will not share information about you without your consent, except:");
653 b.appendParagraph("• Where there is serious risk of harm to you or another person");
654 b.appendParagraph("• Where required by law or court order");
655 b.appendParagraph("• In clinical supervision — discussed anonymously with a qualified supervisor");
656 b.appendParagraph("");
657 h2(b, "2. Sessions");
658 b.appendParagraph("Sessions are [LENGTH] minutes, held [FREQUENCY] via Google Meet. Please be in a private, confidential space.");
659 b.appendParagraph("");
660 h2(b, "3. Cancellation");
661 b.appendParagraph("Please give [X hours/days] notice to cancel or reschedule. Late cancellations are charged at the full rate.");
662 b.appendParagraph("");
663 h2(b, "4. Payment");
664 b.appendParagraph("Fee: [" + CONFIG.currency + " AMOUNT] per session. Payment collected in advance via Stripe.");
665 b.appendParagraph("");
666 h2(b, "5. Between Sessions");
667 b.appendParagraph("Contact via email at " + CONFIG.practiceEmail + " only. I do not provide therapeutic support between sessions.");
668 b.appendParagraph("In crisis: contact your GP, call 999 (UK/Ireland) or 911 (US), or Samaritans: 116 123.");
669 b.appendParagraph("");
670 h2(b, "6. Your Data");
671 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.");
672 b.appendParagraph("");
673 h2(b, "7. Professional Registration");
674 b.appendParagraph("I am registered with [PROFESSIONAL BODY] and hold professional indemnity insurance. Concerns may be referred to [PROFESSIONAL BODY].");
675 b.appendParagraph("");
676 h2(b, "Agreement");
677 b.appendParagraph("I confirm I have read and understood this consent form and agree to its terms.");
678 b.appendParagraph("");
679 b.appendParagraph("Name (print): ___________________________________________");
680 b.appendParagraph("");
681 b.appendParagraph("Signature: ___________________________________________");
682 b.appendParagraph("");
683 b.appendParagraph("Date: ___________________________________________");
684 doc.saveAndClose();
685 moveFile(doc.getId(), folder);
686}
687 
688function buildPrivacyNotice(folder) {
689 var doc = DocumentApp.create("TEMPLATE — Privacy Notice");
690 var b = doc.getBody();
691 var wp = b.appendParagraph("⚠️ TEMPLATE — Review annually. Send to each new client alongside consent form."); wp.setItalic(true); setRed(wp);
692 b.appendParagraph("");
693 h1(b, "Privacy Notice");
694 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Data Controller: " + CONFIG.therapistName);
695 b.appendParagraph("Contact: " + CONFIG.practiceEmail + " | Last reviewed: " + SETUP_DATE);
696 b.appendParagraph("");
697 h2(b, "1. Who I Am");
698 b.appendParagraph(CONFIG.therapistName + " is the data controller for " + CONFIG.practiceName + " and is responsible for how your personal data is used.");
699 b.appendParagraph("");
700 h2(b, "2. What Data I Collect");
701 b.appendParagraph("• Identity — name, date of birth, emergency contact");
702 b.appendParagraph("• Contact — email, phone number");
703 b.appendParagraph("• Health data — mental and physical health information (special category under GDPR)");
704 b.appendParagraph("• Financial — payment records via Stripe");
705 b.appendParagraph("• Appointment — booking records via Cal.com");
706 b.appendParagraph("");
707 h2(b, "3. Legal Basis");
708 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.");
709 b.appendParagraph("");
710 h2(b, "4. Where Data Is Stored");
711 b.appendParagraph("• Google Workspace — clinical records, email, video sessions (Google DPA in place)");
712 b.appendParagraph("• Cal.com — booking (name and email only)");
713 b.appendParagraph("• Stripe — payments (financial data only)");
714 b.appendParagraph("• Framer — public website (no client data stored)");
715 b.appendParagraph("");
716 h2(b, "5. How Long I Keep Your Data");
717 b.appendParagraph("Adult clients: 7 years after end of therapy. Minor clients: until age 25 or 7 years, whichever is later.");
718 b.appendParagraph("");
719 h2(b, "6. Your Rights");
720 b.appendParagraph("You have the right to access, correct or request deletion of your data, and to withdraw consent at any time.");
721 b.appendParagraph("Contact: " + CONFIG.practiceEmail);
722 b.appendParagraph("Supervisory authorities: UK: ico.org.uk | Ireland: dataprotection.ie | US: hhs.gov/hipaa");
723 doc.saveAndClose();
724 moveFile(doc.getId(), folder);
725}
726 
727function buildIntakeForm(folder) {
728 var doc = DocumentApp.create("TEMPLATE — Client Intake Form");
729 var b = doc.getBody();
730 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);
731 b.appendParagraph("");
732 h1(b, "Client Intake Form");
733 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Therapist: " + CONFIG.therapistName);
734 b.appendParagraph("Date: ___________ | Client Code: ___________ (therapist use)");
735 b.appendParagraph("");
736 var pi = b.appendParagraph("All information is confidential and stored securely in line with our Privacy Notice."); pi.setItalic(true);
737 b.appendParagraph("");
738 h2(b, "Personal Details");
739 b.appendParagraph("Full name: ___________________________________________");
740 b.appendParagraph("Date of birth: ___________________________________________");
741 b.appendParagraph("Email: ___________________________________________");
742 b.appendParagraph("Phone: ___________________________________________");
743 b.appendParagraph("Emergency contact (name and number): ___________________________________________");
744 b.appendParagraph("");
745 h2(b, "GP Details");
746 b.appendParagraph("GP name: ___________________________________________");
747 b.appendParagraph("GP surgery: ___________________________________________");
748 b.appendParagraph("Under psychiatrist or mental health service? ☐ Yes ☐ No");
749 b.appendParagraph("If yes: ___________________________________________");
750 b.appendParagraph("");
751 h2(b, "Current Situation");
752 b.appendParagraph("What brings you to therapy now?");
753 b.appendParagraph(" ");
754 b.appendParagraph(" ");
755 b.appendParagraph("How long have you been experiencing this? ___________________________________________");
756 b.appendParagraph("Previous therapy? ☐ Yes ☐ No If yes: ___________________________________________");
757 b.appendParagraph("");
758 h2(b, "Health Information");
759 b.appendParagraph("Current medication? ☐ Yes ☐ No If yes: ___________________________________________");
760 b.appendParagraph("Relevant physical health conditions? ☐ Yes ☐ No If yes: ___________________________________________");
761 b.appendParagraph("Thoughts of self-harm or suicide? ☐ Yes ☐ No Currently? ☐ Yes ☐ No");
762 b.appendParagraph("Context: ___________________________________________");
763 b.appendParagraph("");
764 h2(b, "What You're Looking For");
765 b.appendParagraph("What would you like to get from therapy?");
766 b.appendParagraph(" ");
767 b.appendParagraph(" ");
768 b.appendParagraph("Anything else you'd like me to know?");
769 b.appendParagraph(" ");
770 b.appendParagraph("");
771 h2(b, "Declaration");
772 b.appendParagraph("I confirm this information is accurate and consent to it being stored securely for therapy purposes.");
773 b.appendParagraph("");
774 b.appendParagraph("Signature: ___________________________________________ Date: ___________");
775 doc.saveAndClose();
776 moveFile(doc.getId(), folder);
777}
778 
779function buildSessionNoteTemplate(folder) {
780 var doc = DocumentApp.create("TEMPLATE — Session Note");
781 var b = doc.getBody();
782 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);
783 b.appendParagraph("");
784 h1(b, "Session Note");
785 b.appendParagraph("Client Code: ___________ Session No: ___________");
786 b.appendParagraph("Date: ___________ Duration: ___________");
787 b.appendParagraph("Format: ☐ Video — Google Meet ☐ In Person ☐ Phone");
788 b.appendParagraph("");
789 h2(b, "Presenting themes this session");
790 b.appendParagraph(" "); b.appendParagraph(" ");
791 h2(b, "Interventions used");
792 b.appendParagraph(" "); b.appendParagraph(" ");
793 h2(b, "Client response and engagement");
794 b.appendParagraph(" "); b.appendParagraph(" ");
795 h2(b, "Risk assessment");
796 b.appendParagraph("Risk to self: ☐ None identified ☐ Low ☐ Medium ☐ High");
797 b.appendParagraph("Risk to others: ☐ None identified ☐ Low ☐ Medium ☐ High");
798 b.appendParagraph("Action taken: ___________________________________________");
799 b.appendParagraph("");
800 h2(b, "Plan for next session");
801 b.appendParagraph(" "); b.appendParagraph(" ");
802 h2(b, "Supervision");
803 b.appendParagraph("Bring to supervision: ☐ Yes ☐ No");
804 b.appendParagraph("Notes: ___________________________________________");
805 doc.saveAndClose();
806 moveFile(doc.getId(), folder);
807}
808 
809function buildSuperbill(folder) {
810 var doc = DocumentApp.create("TEMPLATE — Superbill (US)");
811 var b = doc.getBody();
812 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);
813 b.appendParagraph("");
814 h1(b, "Superbill");
815 b.appendParagraph("Statement Date: ___________ | Statement No: ___________");
816 b.appendParagraph("");
817 h2(b, "Provider Information");
818 b.appendParagraph("Provider Name: " + CONFIG.therapistName);
819 b.appendParagraph("Practice Name: " + CONFIG.practiceName);
820 b.appendParagraph("Credentials: ___________ (e.g. LCSW, LPC, PhD, PsyD)");
821 b.appendParagraph("NPI Number: ___________ (required for insurance submission)");
822 b.appendParagraph("Tax ID / EIN: ___________");
823 b.appendParagraph("Address: ___________________________________________");
824 b.appendParagraph("Phone: ___________________________________________");
825 b.appendParagraph("Email: " + CONFIG.practiceEmail);
826 b.appendParagraph("");
827 h2(b, "Client Information");
828 b.appendParagraph("Client Name: ___________________________________________");
829 b.appendParagraph("Date of Birth: ___________________________________________");
830 b.appendParagraph("Client ID / Code: ___________________________________________");
831 b.appendParagraph("Insurance Provider: ___________________________________________");
832 b.appendParagraph("Insurance Member ID: ___________________________________________");
833 b.appendParagraph("");
834 h2(b, "Diagnosis");
835 b.appendParagraph("Primary Diagnosis (ICD-10): ___________ — ___________________________________________");
836 b.appendParagraph("Secondary Diagnosis (if applicable): ___________ — ___________________________________________");
837 b.appendParagraph("");
838 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);
839 b.appendParagraph("");
840 h2(b, "Services Rendered");
841 
842 // Table header row as text (Apps Script tables are complex — plain text is cleaner)
843 b.appendParagraph("Date of Service | CPT Code | Service Description | Duration | Fee | Amount Paid");
844 b.appendParagraph("─────────────────────────────────────────────────────────────────────────────");
845 b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
846 b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
847 b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
848 b.appendParagraph("___________ | _______ | _________________________________ | _____ min | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
849 b.appendParagraph("─────────────────────────────────────────────────────────────────────────────");
850 b.appendParagraph("TOTAL CHARGED: " + CONFIG.currency + "___________ | TOTAL PAID: " + CONFIG.currency + "___________ | BALANCE: " + CONFIG.currency + "___________");
851 b.appendParagraph("");
852 h2(b, "Common CPT Codes (Psychotherapy)");
853 b.appendParagraph("90837 — Individual psychotherapy, 60 minutes");
854 b.appendParagraph("90834 — Individual psychotherapy, 45 minutes");
855 b.appendParagraph("90832 — Individual psychotherapy, 30 minutes");
856 b.appendParagraph("90847 — Family psychotherapy with client present");
857 b.appendParagraph("90846 — Family psychotherapy without client present");
858 b.appendParagraph("90853 — Group psychotherapy");
859 b.appendParagraph("99213 — Office visit, established patient, moderate complexity");
860 b.appendParagraph("");
861 h2(b, "Place of Service Codes");
862 b.appendParagraph("02 — Telehealth (patient is not at home)");
863 b.appendParagraph("10 — Telehealth (patient is at home)");
864 b.appendParagraph("11 — Office");
865 b.appendParagraph("");
866 h2(b, "Provider Signature");
867 b.appendParagraph("I certify that the services listed above were medically necessary and personally rendered by me.");
868 b.appendParagraph("");
869 b.appendParagraph("Signature: ___________________________________________ Date: ___________");
870 b.appendParagraph("");
871 var pi = b.appendParagraph("📝 Save to: 3_Finance → Superbills → YYYY-MM_C001_Superbill"); pi.setItalic(true);
872 doc.saveAndClose();
873 moveFile(doc.getId(), folder);
874}
875 
876function buildInsuranceReceipt(folder) {
877 var doc = DocumentApp.create("TEMPLATE — Insurance Receipt (UK and Ireland)");
878 var b = doc.getBody();
879 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);
880 b.appendParagraph("");
881 h1(b, "Therapy Receipt for Insurance Reimbursement");
882 b.appendParagraph("Receipt Date: ___________ | Receipt No: ___________");
883 b.appendParagraph("");
884 h2(b, "Therapist Details");
885 b.appendParagraph("Name: " + CONFIG.therapistName);
886 b.appendParagraph("Practice: " + CONFIG.practiceName);
887 b.appendParagraph("Qualifications: ___________ (e.g. MSc Counselling Psychology, MBACP)");
888 b.appendParagraph("Professional Registration: ___________ (e.g. BACP No. 123456)");
889 b.appendParagraph("Email: " + CONFIG.practiceEmail);
890 b.appendParagraph("");
891 h2(b, "Client Details");
892 b.appendParagraph("Client Name: ___________________________________________");
893 b.appendParagraph("Date of Birth: ___________________________________________");
894 b.appendParagraph("Insurance Provider: ___________________________________________");
895 b.appendParagraph("Policy Number: ___________________________________________");
896 b.appendParagraph("Authorisation Number (if applicable): ___________________________________________");
897 b.appendParagraph("");
898 h2(b, "Sessions");
899 b.appendParagraph("Date | Duration | Service | Fee | Paid");
900 b.appendParagraph("─────────────────────────────────────────────────");
901 b.appendParagraph("___________ | _____ min | Individual Therapy | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
902 b.appendParagraph("___________ | _____ min | Individual Therapy | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
903 b.appendParagraph("___________ | _____ min | Individual Therapy | " + CONFIG.currency + "_______ | " + CONFIG.currency + "_______");
904 b.appendParagraph("─────────────────────────────────────────────────");
905 b.appendParagraph("TOTAL PAID: " + CONFIG.currency + "___________");
906 b.appendParagraph("");
907 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);
908 b.appendParagraph("");
909 h2(b, "Declaration");
910 b.appendParagraph("I confirm that the therapy sessions listed above were provided by me.");
911 b.appendParagraph("");
912 b.appendParagraph("Signature: ___________________________________________ Date: ___________");
913 b.appendParagraph("");
914 var pi = b.appendParagraph("📝 Save to: 3_Finance → Invoices → YYYY-MM_C001_InsuranceReceipt"); pi.setItalic(true);
915 doc.saveAndClose();
916 moveFile(doc.getId(), folder);
917}
918 
919function buildReferralLetter(folder) {
920 var doc = DocumentApp.create("TEMPLATE — Referral Letter");
921 var b = doc.getBody();
922 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);
923 b.appendParagraph("");
924 b.appendParagraph(CONFIG.practiceName);
925 b.appendParagraph(CONFIG.practiceEmail);
926 b.appendParagraph("[DATE]");
927 b.appendParagraph("");
928 b.appendParagraph("Dear [RECIPIENT NAME / Dr / To Whom It May Concern],");
929 b.appendParagraph("");
930 b.appendParagraph("Re: [CLIENT — use initials only unless client has consented to full name being shared]");
931 b.appendParagraph("Date of Birth: [DOB]");
932 b.appendParagraph("");
933 b.appendParagraph("I am writing to refer the above-named client, who has been receiving [individual / couples / family] therapy with me since [START DATE].");
934 b.appendParagraph("");
935 b.appendParagraph("Presenting concerns:");
936 b.appendParagraph("[Brief, professional summary of presenting issues]");
937 b.appendParagraph("");
938 b.appendParagraph("Reason for referral:");
939 b.appendParagraph("[Why you are referring — e.g. client requires assessment, medication review, higher level of care, specialist support]");
940 b.appendParagraph("");
941 b.appendParagraph("Current risk level: [Low / Medium / High — and brief rationale]");
942 b.appendParagraph("");
943 b.appendParagraph("I am happy to discuss this referral further if helpful.");
944 b.appendParagraph("");
945 b.appendParagraph("Yours sincerely,");
946 b.appendParagraph("");
947 b.appendParagraph(CONFIG.therapistName);
948 b.appendParagraph("[Qualifications]");
949 b.appendParagraph("[Professional Body Registration]");
950 b.appendParagraph(CONFIG.practiceEmail);
951 doc.saveAndClose();
952 moveFile(doc.getId(), folder);
953}
954 
955function buildEndingSummary(folder) {
956 var doc = DocumentApp.create("TEMPLATE — Therapy Ending Summary");
957 var b = doc.getBody();
958 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);
959 b.appendParagraph("");
960 h1(b, "Therapy Ending Summary");
961 b.appendParagraph("Client Code: ___________ | Date of Final Session: ___________");
962 b.appendParagraph("Therapist: " + CONFIG.therapistName + " | Practice: " + CONFIG.practiceName);
963 b.appendParagraph("Total Sessions: ___________ | Duration of Work: ___________");
964 b.appendParagraph("");
965 h2(b, "1. Presenting Issues at Start of Therapy");
966 b.appendParagraph(" "); b.appendParagraph(" ");
967 h2(b, "2. Therapeutic Approach Used");
968 b.appendParagraph(" "); b.appendParagraph(" ");
969 h2(b, "3. Progress and Outcomes");
970 b.appendParagraph(" "); b.appendParagraph(" ");
971 h2(b, "4. Risk at Ending");
972 b.appendParagraph("Risk to self: ☐ None ☐ Low ☐ Medium ☐ High");
973 b.appendParagraph("Risk to others: ☐ None ☐ Low ☐ Medium ☐ High");
974 b.appendParagraph("Notes: ___________________________________________");
975 b.appendParagraph("");
976 h2(b, "5. Reason for Ending");
977 b.appendParagraph("☐ Planned ending — goals achieved");
978 b.appendParagraph("☐ Planned ending — natural conclusion");
979 b.appendParagraph("☐ Client-initiated ending");
980 b.appendParagraph("☐ Therapist-initiated ending");
981 b.appendParagraph("☐ Did not attend — case closed after [X] missed sessions");
982 b.appendParagraph("☐ Referred on to: ___________________________________________");
983 b.appendParagraph("");
984 h2(b, "6. Recommendations");
985 b.appendParagraph(" "); b.appendParagraph(" ");
986 h2(b, "7. Client Feedback (if provided)");
987 b.appendParagraph(" "); b.appendParagraph(" ");
988 b.appendParagraph("");
989 b.appendParagraph("Therapist signature: ___________________________________________ Date: ___________");
990 doc.saveAndClose();
991 moveFile(doc.getId(), folder);
992}
993 
994function buildRiskRegister(folder) {
995 var doc = DocumentApp.create("TEMPLATE — Risk Register");
996 var b = doc.getBody();
997 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);
998 b.appendParagraph("");
999 h1(b, "Risk Register");
1000 b.appendParagraph("Client Code: ___________ | Date opened: ___________");
1001 b.appendParagraph("Therapist: " + CONFIG.therapistName);
1002 b.appendParagraph("");
1003 h2(b, "Risk Summary");
1004 b.appendParagraph("Nature of risk: ___________________________________________");
1005 b.appendParagraph("Initial risk level: ☐ Low ☐ Medium ☐ High");
1006 b.appendParagraph("Protective factors: ___________________________________________");
1007 b.appendParagraph("");
1008 h2(b, "Risk Review Log");
1009 b.appendParagraph("Date | Risk Level | Notes | Action Taken | Next Review");
1010 b.appendParagraph("─────────────────────────────────────────────────────────────────");
1011 for (var i = 0; i < 10; i++) {
1012 b.appendParagraph("___________ | ☐ Low ☐ Med ☐ High | _______________ | _______________ | ___________");
1013 }
1014 b.appendParagraph("");
1015 h2(b, "Escalation Record");
1016 b.appendParagraph("Date | Action | Person Contacted | Outcome");
1017 b.appendParagraph("─────────────────────────────────────────────────────────────────");
1018 b.appendParagraph("___________ | _________________________________ | _________________ | _________________");
1019 b.appendParagraph("___________ | _________________________________ | _________________ | _________________");
1020 b.appendParagraph("");
1021 var pi = b.appendParagraph("Bring to supervision whenever risk level is Medium or above."); pi.setItalic(true);
1022 doc.saveAndClose();
1023 moveFile(doc.getId(), folder);
1024}
1025 
1026function buildDataRetentionPolicy(folder) {
1027 var doc = DocumentApp.create("Policy — Data Retention");
1028 var b = doc.getBody();
1029 h1(b, "Data Retention Policy");
1030 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Controller: " + CONFIG.therapistName);
1031 b.appendParagraph("Last reviewed: " + SETUP_DATE);
1032 b.appendParagraph("");
1033 h2(b, "1. Purpose");
1034 b.appendParagraph("This policy sets out how " + CONFIG.practiceName + " retains and deletes client data in compliance with GDPR (UK/Ireland) and HIPAA (US).");
1035 b.appendParagraph("");
1036 h2(b, "2. Retention Periods");
1037 b.appendParagraph("Adult clients: 7 years after final session.");
1038 b.appendParagraph("Minor clients: Until age 25 or 7 years after final session, whichever is later.");
1039 b.appendParagraph("Financial records: 7 years for tax purposes.");
1040 b.appendParagraph("");
1041 h2(b, "3. Storage");
1042 b.appendParagraph("All records in Google Workspace under Google's Data Processing Agreement. Two-factor authentication enforced.");
1043 b.appendParagraph("");
1044 h2(b, "4. Deletion Procedure");
1045 b.appendParagraph("1. Permanently delete client folder from 5_Archives");
1046 b.appendParagraph("2. Empty Google Drive Trash");
1047 b.appendParagraph("3. Log in Client_Index → Deletion_Log tab");
1048 b.appendParagraph("4. Update status to Deleted in Clients tab");
1049 b.appendParagraph("");
1050 h2(b, "5. Data Processors");
1051 b.appendParagraph("• Google Workspace — email, Drive, Meet, Forms");
1052 b.appendParagraph("• Cal.com — appointment scheduling");
1053 b.appendParagraph("• Stripe — payment processing");
1054 b.appendParagraph("• Framer — public website (no client data)");
1055 b.appendParagraph("");
1056 h2(b, "6. Breach Notification");
1057 b.appendParagraph("Notify supervisory authority within 72 hours:");
1058 b.appendParagraph("UK: ico.org.uk | Ireland: dataprotection.ie | US: hhs.gov/hipaa");
1059 doc.saveAndClose();
1060 moveFile(doc.getId(), folder);
1061}
1062 
1063function buildTelehealthPolicy(folder) {
1064 var doc = DocumentApp.create("Policy — Telehealth");
1065 var b = doc.getBody();
1066 h1(b, "Telehealth Policy");
1067 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Last reviewed: " + SETUP_DATE);
1068 b.appendParagraph("");
1069 h2(b, "Platform");
1070 b.appendParagraph("All remote sessions via Google Meet. Sessions are not recorded. Encrypted in transit.");
1071 b.appendParagraph("");
1072 h2(b, "Client Responsibilities");
1073 b.appendParagraph("• Be in a private, confidential space");
1074 b.appendParagraph("• Use a secure, private internet connection");
1075 b.appendParagraph("• Do not record without prior written consent");
1076 b.appendParagraph("• Confirm your physical location at the start of each session");
1077 b.appendParagraph("");
1078 h2(b, "Suitability");
1079 b.appendParagraph("Telehealth is not suitable for all presentations. If remote sessions become clinically inappropriate, an alternative will be agreed.");
1080 b.appendParagraph("");
1081 h2(b, "Crisis Protocol");
1082 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.");
1083 b.appendParagraph("");
1084 h2(b, "Between-Session Communication");
1085 b.appendParagraph("Workspace email only. No therapeutic support via WhatsApp, SMS or social media.");
1086 doc.saveAndClose();
1087 moveFile(doc.getId(), folder);
1088}
1089 
1090function buildAnnualComplianceChecklist(folder) {
1091 var doc = DocumentApp.create("Annual Compliance Review Checklist");
1092 var b = doc.getBody();
1093 h1(b, "Annual Compliance Review Checklist");
1094 b.appendParagraph("Practice: " + CONFIG.practiceName + " | Therapist: " + CONFIG.therapistName);
1095 b.appendParagraph("Review date: ___________");
1096 b.appendParagraph("");
1097 var pi = b.appendParagraph("Complete once per year. Keep a signed copy here in Policies."); pi.setItalic(true);
1098 b.appendParagraph("");
1099 h2(b, "Google Workspace Security");
1100 b.appendParagraph("☐ Two-factor authentication enabled");
1101 b.appendParagraph("☐ No unauthorised devices have access");
1102 b.appendParagraph("☐ Sharing set to restricted — no public links active");
1103 b.appendParagraph("☐ Password changed in the last 12 months");
1104 b.appendParagraph("");
1105 h2(b, "Client Records");
1106 b.appendParagraph("☐ All active clients have a folder in 1_Clinical_Records");
1107 b.appendParagraph("☐ All closed clients moved to 5_Archives");
1108 b.appendParagraph("☐ Client Index is current and accurate");
1109 b.appendParagraph("☐ Retention deadlines recorded for all closed clients");
1110 b.appendParagraph("☐ Clients past retention deadline deleted and logged");
1111 b.appendParagraph("");
1112 h2(b, "Policies");
1113 b.appendParagraph("☐ Privacy Notice reviewed and current");
1114 b.appendParagraph("☐ Consent form reviewed and current");
1115 b.appendParagraph("☐ Data Retention Policy reviewed");
1116 b.appendParagraph("☐ Telehealth Policy reviewed");
1117 b.appendParagraph("☐ Processor list accurate (Google, Cal.com, Stripe, Framer)");
1118 b.appendParagraph("");
1119 h2(b, "Third Party Tools");
1120 b.appendParagraph("☐ Google Workspace DPA in place (admin.google.com → Account → Legal)");
1121 b.appendParagraph("☐ Cal.com — name and email only, no clinical data");
1122 b.appendParagraph("☐ Stripe — financial data only, no clinical content");
1123 b.appendParagraph("☐ No new tools introduced storing client data without a DPA");
1124 b.appendParagraph("");
1125 h2(b, "Professional");
1126 b.appendParagraph("☐ Professional indemnity insurance current");
1127 b.appendParagraph("☐ Professional body registration current");
1128 b.appendParagraph("☐ Supervision in place and up to date");
1129 b.appendParagraph("☐ CPD hours on track (30 hours/year — check CPD_Log tab)");
1130 b.appendParagraph("");
1131 b.appendParagraph("Signature: ___________________________________________ Date: ___________");
1132 doc.saveAndClose();
1133 moveFile(doc.getId(), folder);
1134}
1135 
1136 
1137// ============================================================
1138// 5. ARCHIVES
1139// ============================================================
1140 
1141function buildArchives(parent) {
1142 createFolder(parent, "Clinical_Records_Archived");
1143 createFolder(parent, "Finance_Archived");
1144 
1145 var doc = DocumentApp.create("📋 Archives — Quick Reference");
1146 var b = doc.getBody();
1147 h1(b, "Archives — Quick Reference");
1148 bold(b, "When to archive:");
1149 b.appendParagraph("When therapy ends — planned ending, withdrawal or non-attendance.");
1150 b.appendParagraph("");
1151 bold(b, "How to archive:");
1152 b.appendParagraph("Option A (recommended): Practice Tools → Close Client Case in Client_Index");
1153 b.appendParagraph("Option B (manual): Update status to Closed → add end date → add retention deadline → move folder here");
1154 b.appendParagraph("");
1155 bold(b, "When to delete:");
1156 b.appendParagraph("Only after retention deadline has passed.");
1157 b.appendParagraph("UK/Ireland: 7 years after final session.");
1158 b.appendParagraph("Minors: Until age 25 or 7 years, whichever is later.");
1159 b.appendParagraph("");
1160 bold(b, "How to delete:");
1161 b.appendParagraph("1. Permanently delete client folder");
1162 b.appendParagraph("2. Empty Google Drive Trash — this is permanent");
1163 b.appendParagraph("3. Log deletion in Client_Index → Deletion_Log");
1164 b.appendParagraph("4. Update status to Deleted");
1165 b.appendParagraph("");
1166 var pb = b.appendParagraph("⚠️ Emptying Trash is irreversible. Confirm retention period has passed first."); pb.setBold(true);
1167 doc.saveAndClose();
1168 moveFile(doc.getId(), parent);
1169 log("Created: Archives folder");
1170}
1171 
1172 
1173// ============================================================
1174// UTILITY FUNCTIONS
1175// ============================================================
1176 
1177function createFolder(parent, name) {
1178 var f = parent.createFolder(name);
1179 log("Folder: " + name);
1180 return f;
1181}
1182 
1183function moveFile(fileId, folder) {
1184 var file = DriveApp.getFileById(fileId);
1185 folder.addFile(file);
1186 DriveApp.getRootFolder().removeFile(file);
1187}
1188 
1189function styleHeaderRow(sheet, headers, bgColor) {
1190 sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
1191 sheet.getRange(1, 1, 1, headers.length)
1192 .setBackground(bgColor).setFontColor("#ffffff")
1193 .setFontWeight("bold").setFontSize(11);
1194 sheet.setFrozenRows(1);
1195}
1196 
1197function setColumnWidths(sheet, widths) {
1198 for (var i = 0; i < widths.length; i++) {
1199 sheet.setColumnWidth(i + 1, widths[i]);
1200 }
1201}
1202 
1203function applyDropdown(sheet, startRow, col, numRows, values) {
1204 var rule = SpreadsheetApp.newDataValidation()
1205 .requireValueInList(values, true).build();
1206 sheet.getRange(startRow, col, numRows, 1).setDataValidation(rule);
1207}
1208 
1209function applyConditionalFormat(sheet, triggerText, bgColor, fontColor, startRow, startCol, numRows, numCols) {
1210 var rule = SpreadsheetApp.newConditionalFormatRule()
1211 .whenTextEqualTo(triggerText)
1212 .setBackground(bgColor).setFontColor(fontColor)
1213 .setRanges([sheet.getRange(startRow, startCol, numRows, numCols)]).build();
1214 sheet.setConditionalFormatRules([rule]);
1215}
1216 
1217function addNote(sheet, cell, note) {
1218 sheet.getRange(cell).setNote(note);
1219}
1220 
1221function h1(body, text) {
1222 body.appendParagraph(text).setHeading(DocumentApp.ParagraphHeading.HEADING1);
1223}
1224 
1225function h2(body, text) {
1226 body.appendParagraph(text).setHeading(DocumentApp.ParagraphHeading.HEADING2);
1227}
1228 
1229function bold(body, text) {
1230 body.appendParagraph(text).setBold(true);
1231}
1232 
1233function setRed(par) {
1234 var attrs = {};
1235 attrs[DocumentApp.Attribute.FOREGROUND_COLOR] = "#cc0000";
1236 par.setAttributes(attrs);
1237}
1238 
1239function log(msg) {
1240 Logger.log(msg);
1241}
Step 2
1/**
2 * ============================================================
3 * PRACTICE INFRASTRUCTURE — STEP 2: PRACTICE TOOLS MENU
4 * Version 3.3
5 * ============================================================
6 * This script lives INSIDE the Client_Index spreadsheet.
7 *
8 * HOW TO INSTALL:
9 * 1. Open your Client_Index spreadsheet
10 * 2. Click Extensions → Apps Script
11 * 3. Delete any existing code in the editor
12 * 4. Paste this entire script
13 * 5. Click Save (floppy disk icon)
14 * 6. Click Run → installPracticeTools
15 * 7. Approve permissions when prompted
16 * 8. Close the Apps Script tab
17 * 9. Reload your spreadsheet
18 * 10. You will see "🏥 Practice Tools" in the menu bar
19 *
20 * From then on, the menu loads automatically every time
21 * you open the spreadsheet.
22 * ============================================================
23 */
24 
25 
26// ============================================================
27// MENU INSTALLATION — Runs on every open + first install
28// ============================================================
29 
30function onOpen() {
31 SpreadsheetApp.getUi()
32 .createMenu("🏥 Practice Tools")
33 .addItem("➕ Add New Client", "addNewClient")
34 .addSeparator()
35 .addItem("📅 Log Session", "logSession")
36 .addItem("📋 Log DNA / Late Cancellation", "logDNA")
37 .addSeparator()
38 .addItem("📁 Close Client Case", "closeClientCase")
39 .addSeparator()
40 .addItem("📄 Generate Superbill (US)", "generateSuperbill")
41 .addItem("🧾 Generate Invoice / Receipt", "generateInvoice")
42 .addSeparator()
43 .addItem("ℹ️ Help", "showHelp")
44 .addToUi();
45}
46 
47function installPracticeTools() {
48 onOpen();
49 showAlert("✅ Practice Tools Installed",
50 "The Practice Tools menu has been added to your spreadsheet.\n\n" +
51 "Close this tab and reload your spreadsheet to see the menu in the top bar.\n\n" +
52 "Start with: 🏥 Practice Tools → Add New Client");
53}
54 
55 
56// ============================================================
57// HELPER — Get settings from Settings tab
58// ============================================================
59 
60function getSettings() {
61 var ss = SpreadsheetApp.getActiveSpreadsheet();
62 var s = ss.getSheetByName("⚙️ Settings");
63 if (!s) throw new Error("Settings tab not found. Make sure you ran STEP1 correctly.");
64 var data = s.getRange("A4:B12").getValues();
65 var settings = {};
66 data.forEach(function(row) { if (row[0]) settings[row[0]] = row[1]; });
67 return settings;
68}
69 
70function getNextClientCode() {
71 var ss = SpreadsheetApp.getActiveSpreadsheet();
72 var s = ss.getSheetByName("👥 Clients");
73 var data = s.getDataRange().getValues();
74 var maxCode = 0;
75 for (var i = 1; i < data.length; i++) {
76 var code = String(data[i][0]);
77 if (code.match(/^C\d+$/)) {
78 var num = parseInt(code.replace("C", ""));
79 if (num > maxCode) maxCode = num;
80 }
81 }
82 var next = maxCode + 1;
83 return "C" + String(next).padStart(3, "0");
84}
85 
86function getClientList() {
87 var ss = SpreadsheetApp.getActiveSpreadsheet();
88 var s = ss.getSheetByName("👥 Clients");
89 var data = s.getDataRange().getValues();
90 var clients = [];
91 for (var i = 1; i < data.length; i++) {
92 if (data[i][0] && data[i][0] !== "Client Code" && data[i][8] === "Active") {
93 clients.push(data[i][0] + " — " + data[i][1] + " " + data[i][2]);
94 }
95 }
96 return clients;
97}
98 
99function getClientFee(clientCode) {
100 var ss = SpreadsheetApp.getActiveSpreadsheet();
101 var s = ss.getSheetByName("👥 Clients");
102 var data = s.getDataRange().getValues();
103 for (var i = 1; i < data.length; i++) {
104 if (data[i][0] === clientCode) {
105 return data[i][9] || 0; // Default Fee column
106 }
107 }
108 return 0;
109}
110 
111function getNextSessionNumber(clientCode) {
112 var ss = SpreadsheetApp.getActiveSpreadsheet();
113 var s = ss.getSheetByName("📅 Session_Log");
114 var data = s.getDataRange().getValues();
115 var max = 0;
116 for (var i = 1; i < data.length; i++) {
117 if (data[i][1] === clientCode) {
118 var n = parseInt(data[i][2]);
119 if (n > max) max = n;
120 }
121 }
122 return max + 1;
123}
124 
125 
126// ============================================================
127// ADD NEW CLIENT
128// ============================================================
129 
130function addNewClient() {
131 var ui = SpreadsheetApp.getUi();
132 var settings = getSettings();
133 var nextCode = getNextClientCode();
134 
135 var firstName = askUser(ui, "ADD NEW CLIENT (1/7)", "Client first name:");
136 if (!firstName) return;
137 
138 var lastName = askUser(ui, "ADD NEW CLIENT (2/7)", "Client last name:");
139 if (!lastName) return;
140 
141 var email = askUser(ui, "ADD NEW CLIENT (3/7)", "Client email address:");
142 if (!email) return;
143 
144 // ── Duplicate email check ──
145 var ss = SpreadsheetApp.getActiveSpreadsheet();
146 var clientSheet = ss.getSheetByName("👥 Clients");
147 var existingData = clientSheet.getDataRange().getValues();
148 for (var e = 1; e < existingData.length; e++) {
149 if (existingData[e][3] && existingData[e][3].toString().toLowerCase() === email.toLowerCase()) {
150 var existingCode = existingData[e][0];
151 var existingName = existingData[e][1] + " " + existingData[e][2];
152 var dupResponse = ui.alert(
153 "⚠️ Duplicate Email Detected",
154 "This email address is already on record for:\n" +
155 existingCode + " — " + existingName + "\n\n" +
156 "Do you want to continue adding this as a new client anyway?",
157 ui.ButtonSet.YES_NO
158 );
159 if (dupResponse !== ui.Button.YES) return;
160 break;
161 }
162 }
163 
164 var phone = askUser(ui, "ADD NEW CLIENT (4/7)", "Client phone number (or press OK to skip):");
165 if (!phone) phone = "";
166 
167 var dobRaw = askUser(ui, "ADD NEW CLIENT (5/7)",
168 "Client date of birth (used to calculate retention deadline for minor clients).\n\n" +
169 "Accepted formats:\n" +
170 " 05/03/1990 | 1990-03-05 | 5 March 1990\n\n" +
171 "Press OK to skip if not known.");
172 var dob = dobRaw ? (parseFlexibleDate(dobRaw) || "") : "";
173 if (dobRaw && !dob) {
174 showAlert("Invalid Date", "\"" + dobRaw + "\" was not recognised. Date of birth left blank — you can add it manually in the Clients tab.");
175 dob = "";
176 }
177 
178 var startDate = askDate(ui, "ADD NEW CLIENT (6/7)", "Start date:");
179 if (!startDate) startDate = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
180 
181 var feeInput = askUser(ui, "ADD NEW CLIENT (7/7)",
182 "Session fee (" + settings["Currency Symbol"] + ") — default is " + settings["Default Session Fee"] + ":\n(Press OK to use default)");
183 var fee;
184 if (!feeInput) {
185 fee = parseFloat(settings["Default Session Fee"]) || 0;
186 } else {
187 fee = parseFloat(feeInput);
188 if (isNaN(fee)) {
189 showAlert("Invalid Fee", "Please enter a number for the fee.");
190 return;
191 }
192 }
193 
194 // ── Build client folder ──
195 var clientFolderName = nextCode + "-" + getInitials(firstName, lastName);
196 var clinicalFolder = getClinicalRecordsFolder();
197 
198 if (!clinicalFolder) {
199 showAlert("Folder Error",
200 "Could not find the 1_Clinical_Records folder.\n\n" +
201 "Make sure this spreadsheet is inside the Practice Infrastructure folder structure created by the setup script.");
202 return;
203 }
204 
205 var clientFolder = clinicalFolder.createFolder(clientFolderName);
206 clientFolder.createFolder("01_Admin");
207 clientFolder.createFolder("02_Consent_Forms");
208 clientFolder.createFolder("03_Intake_Forms");
209 clientFolder.createFolder("04_Session_Notes");
210 clientFolder.createFolder("05_Client_Materials");
211 clientFolder.createFolder("06_Correspondence");
212 
213 var folderUrl = "https://drive.google.com/drive/folders/" + clientFolder.getId();
214 
215 // ── Add to Clients tab ──
216 var newRow = [
217 nextCode, firstName, lastName, email, phone,
218 dob, startDate, "", "Active",
219 fee, "",
220 folderUrl,
221 "", ""
222 ];
223 clientSheet.appendRow(newRow);
224 
225 var lastRow = clientSheet.getLastRow();
226 clientSheet.getRange(lastRow, 1, 1, newRow.length)
227 .setFontColor("#000000").setFontStyle("normal");
228 
229 showAlert("✅ Client Added — " + nextCode,
230 "Client " + firstName + " " + lastName + " has been added as " + nextCode + ".\n\n" +
231 "Their folder has been created automatically:\n" +
232 clientFolderName + "\n\n" +
233 "NEXT STEPS:\n" +
234 "1. Send them the intake form\n" +
235 "2. Send them the consent form and privacy notice\n" +
236 " (4_Templates_Policies → Consent_Templates)\n" +
237 "3. Save completed forms to their Drive folder when returned");
238}
239 
240// Navigate from this spreadsheet up to find 1_Clinical_Records
241function getPracticeFolder() {
242 try {
243 var ss = SpreadsheetApp.getActiveSpreadsheet();
244 var ssFile = DriveApp.getFileById(ss.getId());
245 var parents = ssFile.getParents();
246 while (parents.hasNext()) {
247 var adminFolder = parents.next();
248 var grandParents = adminFolder.getParents();
249 while (grandParents.hasNext()) {
250 var practiceFolder = grandParents.next();
251 var subs = practiceFolder.getFolders();
252 while (subs.hasNext()) {
253 var sub = subs.next();
254 if (sub.getName().indexOf("1_Clinical_Records") !== -1) {
255 return practiceFolder;
256 }
257 }
258 }
259 }
260 return null;
261 } catch(e) {
262 return null;
263 }
264}
265 
266function getClinicalRecordsFolder() {
267 try {
268 var practiceFolder = getPracticeFolder();
269 if (!practiceFolder) return null;
270 var subs = practiceFolder.getFolders();
271 while (subs.hasNext()) {
272 var sub = subs.next();
273 if (sub.getName().indexOf("1_Clinical_Records") !== -1) return sub;
274 }
275 return null;
276 } catch(e) {
277 return null;
278 }
279}
280 
281 
282// ============================================================
283// LOG SESSION
284// ============================================================
285 
286function logSession() {
287 var ui = SpreadsheetApp.getUi();
288 var settings = getSettings();
289 var clients = getClientList();
290 
291 if (clients.length === 0) {
292 showAlert("No Active Clients", "Add a client first using Practice Tools → Add New Client.");
293 return;
294 }
295 
296 var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
297 var clientIdx = askNumber(ui, "LOG SESSION (1/6)",
298 "Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
299 if (clientIdx === null) return;
300 if (clientIdx < 1 || clientIdx > clients.length) {
301 showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
302 return;
303 }
304 
305 var clientCode = clients[clientIdx - 1].split(" — ")[0];
306 var clientFee = getClientFee(clientCode);
307 var nextSession = getNextSessionNumber(clientCode);
308 
309 var sessionDate = askDate(ui, "LOG SESSION (2/6)", "Session date:");
310 if (!sessionDate) return;
311 
312 var formatIdx = askNumber(ui, "LOG SESSION (3/6)",
313 "Type the NUMBER for session format:\n\n" +
314 "1. Video — Google Meet\n2. In Person\n3. Phone\n4. Group");
315 if (formatIdx === null) return;
316 var formats = ["Video — Google Meet", "In Person", "Phone", "Group"];
317 if (formatIdx < 1 || formatIdx > 4) {
318 showAlert("Invalid Selection", "Please enter a number between 1 and 4.");
319 return;
320 }
321 var format = formats[formatIdx - 1];
322 
323 var defaultDuration = settings["Standard Session Length (mins)"] || 50;
324 var durationInput = askUser(ui, "LOG SESSION (4/6)",
325 "Session duration in minutes.\nPress OK to use standard " + defaultDuration + " mins:");
326 var duration = durationInput ? parseInt(durationInput) : defaultDuration;
327 if (isNaN(duration) || duration <= 0) duration = defaultDuration;
328 
329 var feeInput = askUser(ui, "LOG SESSION (5/6)",
330 "Fee charged (" + settings["Currency Symbol"] + ").\n" +
331 "Standard fee for this client is " + settings["Currency Symbol"] + clientFee + ".\n" +
332 "Press OK to use standard fee:");
333 var fee;
334 if (!feeInput) {
335 fee = clientFee;
336 } else {
337 fee = parseFloat(feeInput);
338 if (isNaN(fee)) {
339 showAlert("Invalid Fee", "Please enter a number for the fee.");
340 return;
341 }
342 }
343 
344 var paidIdx = askNumber(ui, "LOG SESSION (6/6)",
345 "Type the NUMBER for payment status:\n\n1. Yes\n2. No\n3. Waived");
346 if (paidIdx === null) return;
347 var paidOptions = ["Yes", "No", "Waived"];
348 if (paidIdx < 1 || paidIdx > 3) {
349 showAlert("Invalid Selection", "Please enter 1, 2, or 3.");
350 return;
351 }
352 var paid = paidOptions[paidIdx - 1];
353 var paymentDate = (paid === "Yes") ? sessionDate : "";
354 
355 var invoiceRef = "INV-" + clientCode + "-" + String(nextSession).padStart(3, "0");
356 
357 var ss = SpreadsheetApp.getActiveSpreadsheet();
358 var logSheet = ss.getSheetByName("📅 Session_Log");
359 logSheet.appendRow([
360 sessionDate, clientCode, nextSession,
361 duration, format, fee, paid, paymentDate, invoiceRef, "", ""
362 ]);
363 
364 var lastRow = logSheet.getLastRow();
365 logSheet.getRange(lastRow, 1, 1, 11).setFontColor("#000000").setFontStyle("normal");
366 
367 if (paid === "No") {
368 logSheet.getRange(lastRow, 7).setBackground("#fff3cd");
369 }
370 
371 showAlert("✅ Session Logged",
372 "Session " + nextSession + " for " + clientCode + " logged on " + formatDisplayDate(sessionDate) + ".\n" +
373 "Fee: " + settings["Currency Symbol"] + fee + " | Paid: " + paid + "\n\n" +
374 "Remember to write your session note and save it to:\n" +
375 "1_Clinical_Records → " + clientCode + " → 04_Session_Notes\n" +
376 "Filename: " + sessionDate + "_" + clientCode + "_SessionNote");
377}
378 
379 
380// ============================================================
381// LOG DNA / LATE CANCELLATION
382// ============================================================
383 
384function logDNA() {
385 var ui = SpreadsheetApp.getUi();
386 var clients = getClientList();
387 
388 if (clients.length === 0) {
389 showAlert("No Active Clients", "No active clients found.");
390 return;
391 }
392 
393 var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
394 var clientIdx = askNumber(ui, "LOG DNA (1/4)",
395 "Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
396 if (clientIdx === null) return;
397 if (clientIdx < 1 || clientIdx > clients.length) {
398 showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
399 return;
400 }
401 var clientCode = clients[clientIdx - 1].split(" — ")[0];
402 
403 var dnaDate = askDate(ui, "LOG DNA (2/4)", "Date of missed session:");
404 if (!dnaDate) return;
405 
406 var sessionNo = getNextSessionNumber(clientCode);
407 
408 var contactIdx = askNumber(ui, "LOG DNA (3/4)",
409 "Type the NUMBER for contact attempted:\n\n" +
410 "1. Yes — Email\n2. Yes — Phone\n3. Yes — Both\n4. No contact attempted");
411 if (contactIdx === null) return;
412 var contactOptions = ["Yes — Email", "Yes — Phone", "Yes — Both", "No contact attempted"];
413 if (contactIdx < 1 || contactIdx > 4) {
414 showAlert("Invalid Selection", "Please enter a number between 1 and 4.");
415 return;
416 }
417 var contact = contactOptions[contactIdx - 1];
418 
419 var safeguardingIdx = askNumber(ui, "LOG DNA (4/4)",
420 "Type the NUMBER for safeguarding concern:\n\n1. No\n2. Yes — noted\n3. Yes — escalated");
421 if (safeguardingIdx === null) return;
422 var safeguardingOptions = ["No", "Yes — noted", "Yes — escalated"];
423 if (safeguardingIdx < 1 || safeguardingIdx > 3) {
424 showAlert("Invalid Selection", "Please enter 1, 2, or 3.");
425 return;
426 }
427 var safeguarding = safeguardingOptions[safeguardingIdx - 1];
428 
429 var ss = SpreadsheetApp.getActiveSpreadsheet();
430 var dnaSheet = ss.getSheetByName("📋 DNA_Log");
431 dnaSheet.appendRow([dnaDate, clientCode, sessionNo, contact, "", "", safeguarding, ""]);
432 
433 var lastRow = dnaSheet.getLastRow();
434 dnaSheet.getRange(lastRow, 1, 1, 8).setFontColor("#000000").setFontStyle("normal");
435 
436 if (safeguarding !== "No") {
437 dnaSheet.getRange(lastRow, 7).setBackground("#ffcccc");
438 showAlert("⚠️ Safeguarding Concern Flagged",
439 "DNA logged for " + clientCode + " on " + formatDisplayDate(dnaDate) + ".\n\n" +
440 "A safeguarding concern has been noted. Consider:\n" +
441 "• Bringing to supervision\n" +
442 "• Contacting the client's emergency contact if appropriate\n" +
443 "• Documenting all actions taken in the DNA_Log notes column");
444 } else {
445 showAlert("✅ DNA Logged",
446 "Did Not Attend logged for " + clientCode + " on " + formatDisplayDate(dnaDate) + ".");
447 }
448}
449 
450 
451// ============================================================
452// CLOSE CLIENT CASE
453// ============================================================
454 
455function closeClientCase() {
456 var ui = SpreadsheetApp.getUi();
457 var ss = SpreadsheetApp.getActiveSpreadsheet(); // ← declared at top to avoid reference bug
458 var clients = getClientList();
459 
460 if (clients.length === 0) {
461 showAlert("No Active Clients", "No active clients to close.");
462 return;
463 }
464 
465 var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
466 var clientIdx = askNumber(ui, "CLOSE CLIENT CASE (1/3)",
467 "Type the NUMBER of the client to close (e.g. type 1, not the client name):\n\n" + clientMenu);
468 if (clientIdx === null) return;
469 if (clientIdx < 1 || clientIdx > clients.length) {
470 showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
471 return;
472 }
473 var clientCode = clients[clientIdx - 1].split(" — ")[0];
474 
475 var endDate = askDate(ui, "CLOSE CLIENT CASE (2/3)", "Date of final session:");
476 if (!endDate) return;
477 
478 var minorIdx = askNumber(ui, "CLOSE CLIENT CASE (3/3)",
479 "Type the NUMBER for minor status:\n\n" +
480 "1. No — adult client\n2. Yes — was a minor at any point during therapy");
481 if (minorIdx === null) return;
482 if (minorIdx < 1 || minorIdx > 2) {
483 showAlert("Invalid Selection", "Please enter 1 or 2.");
484 return;
485 }
486 var wasMinor = (minorIdx === 2);
487 
488 // Calculate retention deadline
489 var endDateObj = new Date(endDate);
490 var sevenYearsAfterEnd = new Date(endDateObj);
491 sevenYearsAfterEnd.setFullYear(sevenYearsAfterEnd.getFullYear() + 7);
492 
493 var retentionDate = sevenYearsAfterEnd;
494 var retentionNote = "7 years after final session";
495 
496 if (wasMinor) {
497 var clientSheet2 = ss.getSheetByName("👥 Clients");
498 var clientData2 = clientSheet2.getDataRange().getValues();
499 var clientDOB = null;
500 for (var k = 1; k < clientData2.length; k++) {
501 if (clientData2[k][0] === clientCode && clientData2[k][5]) {
502 clientDOB = new Date(clientData2[k][5]);
503 break;
504 }
505 }
506 if (clientDOB && !isNaN(clientDOB)) {
507 var age25Date = new Date(clientDOB);
508 age25Date.setFullYear(age25Date.getFullYear() + 25);
509 if (age25Date > sevenYearsAfterEnd) {
510 retentionDate = age25Date;
511 retentionNote = "Until age 25 (longer than 7-year rule)";
512 } else {
513 retentionNote = "7 years after final session (longer than until age 25)";
514 }
515 } else {
516 var fallbackDate = new Date(endDateObj);
517 fallbackDate.setFullYear(fallbackDate.getFullYear() + 25);
518 retentionDate = fallbackDate;
519 retentionNote = "Minor — no DOB recorded. Conservative 25-year deadline set. Update once DOB confirmed.";
520 }
521 }
522 
523 var retentionStr = Utilities.formatDate(retentionDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
524 
525 // Update Clients tab
526 var clientSheet = ss.getSheetByName("👥 Clients");
527 var data = clientSheet.getDataRange().getValues();
528 for (var i = 1; i < data.length; i++) {
529 if (data[i][0] === clientCode) {
530 clientSheet.getRange(i + 1, 8).setValue(endDate);
531 clientSheet.getRange(i + 1, 9).setValue("Closed");
532 clientSheet.getRange(i + 1, 11).setValue(retentionStr);
533 break;
534 }
535 }
536 
537 // ── Auto-move client folder to 5_Archives ──
538 var moved = false;
539 try {
540 var practiceFolder = getPracticeFolder();
541 var clinicalFolder = null;
542 var archivesFolder = null;
543 if (practiceFolder) {
544 var subs = practiceFolder.getFolders();
545 while (subs.hasNext()) {
546 var sub = subs.next();
547 var subName = sub.getName();
548 if (subName.indexOf("1_Clinical_Records") !== -1) clinicalFolder = sub;
549 if (subName.indexOf("5_Archives") !== -1) archivesFolder = sub;
550 }
551 }
552 var archivedSubfolder = null;
553 if (archivesFolder) {
554 var archiveSubs = archivesFolder.getFolders();
555 while (archiveSubs.hasNext()) {
556 var archiveSub = archiveSubs.next();
557 if (archiveSub.getName().indexOf("Clinical_Records_Archived") !== -1) {
558 archivedSubfolder = archiveSub;
559 }
560 }
561 }
562 if (clinicalFolder && archivedSubfolder) {
563 var clientFolders = clinicalFolder.getFolders();
564 while (clientFolders.hasNext()) {
565 var clientFolder = clientFolders.next();
566 if (clientFolder.getName().indexOf(clientCode) !== -1) {
567 archivedSubfolder.addFolder(clientFolder);
568 clinicalFolder.removeFolder(clientFolder);
569 moved = true;
570 break;
571 }
572 }
573 }
574 } catch(e) {}
575 
576 var moveMsg = moved
577 ? "✅ Their folder has been moved to 5_Archives automatically."
578 : "⚠️ Could not move folder automatically.\nPlease move it manually:\nFROM: 1_Clinical_Records → " + clientCode + "\nTO: 5_Archives → Clinical_Records_Archived";
579 
580 showAlert("✅ Client Case Closed — " + clientCode,
581 "Status: Closed\n" +
582 "End date: " + formatDisplayDate(endDate) + "\n" +
583 "Retention deadline: " + formatDisplayDate(retentionStr) + "\n" +
584 "Basis: " + retentionNote + "\n\n" +
585 moveMsg + "\n\n" +
586 "Reminder: Complete the Therapy Ending Summary template\n" +
587 "(4_Templates_Policies → Clinical_Templates)");
588}
589 
590 
591// ============================================================
592// GENERATE SUPERBILL (US)
593// ============================================================
594 
595function generateSuperbill() {
596 var ui = SpreadsheetApp.getUi();
597 var settings = getSettings();
598 var clients = getClientList();
599 
600 if (clients.length === 0) {
601 showAlert("No Active Clients", "No active clients found.");
602 return;
603 }
604 
605 var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
606 var clientIdx = askNumber(ui, "GENERATE SUPERBILL (1/3)",
607 "Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
608 if (clientIdx === null) return;
609 if (clientIdx < 1 || clientIdx > clients.length) {
610 showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
611 return;
612 }
613 var clientCode = clients[clientIdx - 1].split(" — ")[0];
614 var clientName = clients[clientIdx - 1].split(" — ")[1];
615 
616 var periodInput = askUser(ui, "GENERATE SUPERBILL (2/3)",
617 "Billing period (e.g. January 2026 or 2026-01):");
618 if (!periodInput) return;
619 
620 var diagnosisInput = askUser(ui, "GENERATE SUPERBILL (3/3)",
621 "Primary ICD-10 diagnosis code (optional — leave blank if not applicable):\n\n" +
622 "⚠️ Only enter if you are licensed to diagnose in your jurisdiction.");
623 if (!diagnosisInput) diagnosisInput = "[Not provided — see note]";
624 
625 // Pull sessions for this client from Session_Log
626 var ss = SpreadsheetApp.getActiveSpreadsheet();
627 var logSheet = ss.getSheetByName("📅 Session_Log");
628 var logData = logSheet.getDataRange().getValues();
629 
630 var sessions = [];
631 var totalCharged = 0;
632 var totalPaid = 0;
633 
634 // Parse billing period for filtering — accept "January 2026" or "2026-01"
635 var filterMonth = -1;
636 var filterYear = -1;
637 var monthNames = ["january","february","march","april","may","june","july","august","september","october","november","december"];
638 var periodLower = periodInput.toLowerCase().trim();
639 var periodParts = periodLower.split(/[\s\-\/]+/);
640 for (var p = 0; p < periodParts.length; p++) {
641 var mIdx = monthNames.indexOf(periodParts[p]);
642 if (mIdx !== -1) filterMonth = mIdx + 1;
643 var maybeYear = parseInt(periodParts[p]);
644 if (!isNaN(maybeYear) && maybeYear > 2000) filterYear = maybeYear;
645 }
646 // "2026-01" format
647 if (filterMonth === -1 && periodParts.length >= 2) {
648 var y = parseInt(periodParts[0]), m = parseInt(periodParts[1]);
649 if (!isNaN(y) && !isNaN(m)) { filterYear = y; filterMonth = m; }
650 }
651 
652 for (var i = 1; i < logData.length; i++) {
653 if (logData[i][1] === clientCode && logData[i][0] !== "Date" && logData[i][0] !== "") {
654 var rawDate = logData[i][0];
655 var rowDate = (rawDate instanceof Date) ? rawDate : new Date(rawDate);
656 if (isNaN(rowDate.getTime())) continue;
657 // Apply period filter if we successfully parsed it
658 if (filterMonth !== -1 && filterYear !== -1) {
659 if (rowDate.getMonth() + 1 !== filterMonth || rowDate.getFullYear() !== filterYear) continue;
660 }
661 var sessionDate = Utilities.formatDate(rowDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
662 var fee = parseFloat(logData[i][5]) || 0;
663 var paid = logData[i][6];
664 var cptCode = logData[i][9] || "90837";
665 sessions.push({
666 date: sessionDate,
667 duration: logData[i][3],
668 fee: fee,
669 paid: paid === "Yes" ? fee : 0,
670 cpt: cptCode
671 });
672 totalCharged += fee;
673 if (paid === "Yes") totalPaid += fee;
674 }
675 }
676 
677 if (sessions.length === 0) {
678 showAlert("No Sessions Found", "No logged sessions found for " + clientCode + ".");
679 return;
680 }
681 
682 // Get client details
683 var clientSheet = ss.getSheetByName("👥 Clients");
684 var clientData = clientSheet.getDataRange().getValues();
685 var clientDOB = "";
686 for (var j = 1; j < clientData.length; j++) {
687 if (clientData[j][0] === clientCode) {
688 clientDOB = clientData[j][5] || "Not recorded";
689 break;
690 }
691 }
692 
693 // Create superbill document
694 var doc = DocumentApp.create("Superbill — " + clientCode + " — " + periodInput);
695 var b = doc.getBody();
696 
697 b.appendParagraph("SUPERBILL").setHeading(DocumentApp.ParagraphHeading.HEADING1);
698 b.appendParagraph("Statement Date: " + formatDisplayDate(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd")));
699 b.appendParagraph("Billing Period: " + periodInput);
700 b.appendParagraph("");
701 
702 b.appendParagraph("Provider Information").setHeading(DocumentApp.ParagraphHeading.HEADING2);
703 b.appendParagraph("Provider: " + settings["Therapist Name"]);
704 b.appendParagraph("Practice: " + settings["Practice Name"]);
705 b.appendParagraph("Credentials: [ADD CREDENTIALS]");
706 b.appendParagraph("NPI: [ADD NPI NUMBER]");
707 b.appendParagraph("Tax ID / EIN: [ADD TAX ID]");
708 b.appendParagraph("Email: " + settings["Practice Email"]);
709 b.appendParagraph("");
710 
711 b.appendParagraph("Client Information").setHeading(DocumentApp.ParagraphHeading.HEADING2);
712 b.appendParagraph("Client: " + clientName);
713 b.appendParagraph("Client Code: " + clientCode);
714 b.appendParagraph("Date of Birth: " + clientDOB);
715 b.appendParagraph("Insurance Provider: [CLIENT TO ADD]");
716 b.appendParagraph("Member ID: [CLIENT TO ADD]");
717 b.appendParagraph("");
718 
719 b.appendParagraph("Diagnosis").setHeading(DocumentApp.ParagraphHeading.HEADING2);
720 b.appendParagraph("Primary ICD-10: " + diagnosisInput);
721 b.appendParagraph("⚠️ Diagnosis codes must be assigned by a licensed clinician only.").setItalic(true);
722 b.appendParagraph("");
723 
724 b.appendParagraph("Services Rendered").setHeading(DocumentApp.ParagraphHeading.HEADING2);
725 b.appendParagraph("Date | CPT | Description | Mins | Fee | Paid");
726 b.appendParagraph("─────────────────────────────────────────────────────────────");
727 
728 sessions.forEach(function(s) {
729 b.appendParagraph(
730 s.date + " | " + s.cpt + " | Individual Psychotherapy | " +
731 s.duration + " | " + settings["Currency Symbol"] + s.fee +
732 " | " + settings["Currency Symbol"] + s.paid
733 );
734 });
735 
736 b.appendParagraph("─────────────────────────────────────────────────────────────");
737 b.appendParagraph("TOTAL CHARGED: " + settings["Currency Symbol"] + totalCharged.toFixed(2) +
738 " | TOTAL PAID: " + settings["Currency Symbol"] + totalPaid.toFixed(2) +
739 " | BALANCE: " + settings["Currency Symbol"] + (totalCharged - totalPaid).toFixed(2));
740 b.appendParagraph("");
741 
742 b.appendParagraph("Provider Certification").setHeading(DocumentApp.ParagraphHeading.HEADING2);
743 b.appendParagraph("I certify that the services listed above were medically necessary and personally rendered by me.");
744 b.appendParagraph("");
745 b.appendParagraph("Signature: ___________________________________________ Date: ___________");
746 
747 doc.saveAndClose();
748 
749 // Auto-move to 3_Finance → Superbills
750 var superbillMoved = false;
751 try {
752 var practiceFolder = getPracticeFolder();
753 if (practiceFolder) {
754 var pSubs = practiceFolder.getFolders();
755 while (pSubs.hasNext()) {
756 var pSub = pSubs.next();
757 if (pSub.getName().indexOf("3_Finance") !== -1) {
758 var fSubs = pSub.getFolders();
759 while (fSubs.hasNext()) {
760 var fSub = fSubs.next();
761 if (fSub.getName() === "Superbills") {
762 var docFile = DriveApp.getFileById(doc.getId());
763 fSub.addFile(docFile);
764 DriveApp.getRootFolder().removeFile(docFile);
765 superbillMoved = true;
766 break;
767 }
768 }
769 break;
770 }
771 }
772 }
773 } catch(e) {}
774 
775 var sbLocationMsg = superbillMoved
776 ? "Saved to: 3_Finance → Superbills"
777 : "Created in your Drive root — move to: 3_Finance → Superbills";
778 
779 showAlert("✅ Superbill Created",
780 "Superbill generated for " + clientCode + " — " + periodInput + ".\n\n" +
781 "Total charged: " + settings["Currency Symbol"] + totalCharged.toFixed(2) + "\n" +
782 "Total paid: " + settings["Currency Symbol"] + totalPaid.toFixed(2) + "\n\n" +
783 sbLocationMsg + "\n\n" +
784 "Remember to add your NPI number and credentials before sending.");
785}
786 
787 
788// ============================================================
789// GENERATE INVOICE / RECEIPT
790// ============================================================
791 
792function generateInvoice() {
793 var ui = SpreadsheetApp.getUi();
794 var settings = getSettings();
795 var clients = getClientList();
796 
797 if (clients.length === 0) {
798 showAlert("No Active Clients", "No active clients found.");
799 return;
800 }
801 
802 var clientMenu = clients.map(function(c, i) { return (i+1) + ". " + c; }).join("\n");
803 var clientIdx = askNumber(ui, "GENERATE INVOICE (1/2)",
804 "Type the NUMBER of the client (e.g. type 1, not the client name):\n\n" + clientMenu);
805 if (clientIdx === null) return;
806 if (clientIdx < 1 || clientIdx > clients.length) {
807 showAlert("Invalid Selection", "Please enter a number between 1 and " + clients.length + ".");
808 return;
809 }
810 var clientCode = clients[clientIdx - 1].split(" — ")[0];
811 var clientName = clients[clientIdx - 1].split(" — ")[1];
812 
813 var periodInput = askUser(ui, "GENERATE INVOICE (2/2)", "Billing period (e.g. January 2026):");
814 if (!periodInput) return;
815 
816 // Pull sessions from log
817 var ss = SpreadsheetApp.getActiveSpreadsheet();
818 var logSheet = ss.getSheetByName("📅 Session_Log");
819 var logData = logSheet.getDataRange().getValues();
820 
821 var sessions = [];
822 var totalFee = 0;
823 var totalPaid = 0;
824 
825 // Parse billing period for filtering
826 var filterMonth = -1;
827 var filterYear = -1;
828 var monthNames = ["january","february","march","april","may","june","july","august","september","october","november","december"];
829 var periodLower = periodInput.toLowerCase().trim();
830 var periodParts = periodLower.split(/[\s\-\/]+/);
831 for (var p = 0; p < periodParts.length; p++) {
832 var mIdx = monthNames.indexOf(periodParts[p]);
833 if (mIdx !== -1) filterMonth = mIdx + 1;
834 var maybeYear = parseInt(periodParts[p]);
835 if (!isNaN(maybeYear) && maybeYear > 2000) filterYear = maybeYear;
836 }
837 if (filterMonth === -1 && periodParts.length >= 2) {
838 var y = parseInt(periodParts[0]), m = parseInt(periodParts[1]);
839 if (!isNaN(y) && !isNaN(m)) { filterYear = y; filterMonth = m; }
840 }
841 
842 for (var i = 1; i < logData.length; i++) {
843 if (logData[i][1] === clientCode && logData[i][0] !== "Date" && logData[i][0] !== "") {
844 var rawDate = logData[i][0];
845 var rowDate = (rawDate instanceof Date) ? rawDate : new Date(rawDate);
846 if (isNaN(rowDate.getTime())) continue;
847 if (filterMonth !== -1 && filterYear !== -1) {
848 if (rowDate.getMonth() + 1 !== filterMonth || rowDate.getFullYear() !== filterYear) continue;
849 }
850 var fee = parseFloat(logData[i][5]) || 0;
851 var paid = logData[i][6];
852 var displayDate = Utilities.formatDate(rowDate, Session.getScriptTimeZone(), "yyyy-MM-dd");
853 sessions.push({
854 date: displayDate,
855 duration: logData[i][3],
856 fee: fee,
857 invoiceRef: logData[i][8],
858 paid: paid
859 });
860 totalFee += fee;
861 if (paid === "Yes") totalPaid += fee;
862 }
863 }
864 
865 if (sessions.length === 0) {
866 showAlert("No Sessions Found", "No logged sessions for " + clientCode + ".");
867 return;
868 }
869 
870 var invoiceNumber = "INV-" + clientCode + "-" + new Date().getFullYear() +
871 "-" + String(new Date().getMonth() + 1).padStart(2, "0");
872 
873 var doc = DocumentApp.create("Invoice — " + clientCode + " — " + periodInput);
874 var b = doc.getBody();
875 
876 b.appendParagraph("INVOICE").setHeading(DocumentApp.ParagraphHeading.HEADING1);
877 b.appendParagraph("Invoice No: " + invoiceNumber);
878 b.appendParagraph("Date: " + formatDisplayDate(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd")));
879 b.appendParagraph("Period: " + periodInput);
880 b.appendParagraph("");
881 
882 b.appendParagraph("From").setHeading(DocumentApp.ParagraphHeading.HEADING2);
883 b.appendParagraph(settings["Therapist Name"]);
884 b.appendParagraph(settings["Practice Name"]);
885 b.appendParagraph(settings["Practice Email"]);
886 b.appendParagraph("");
887 
888 b.appendParagraph("To").setHeading(DocumentApp.ParagraphHeading.HEADING2);
889 b.appendParagraph(clientName + " (Client Ref: " + clientCode + ")");
890 b.appendParagraph("");
891 
892 b.appendParagraph("Sessions").setHeading(DocumentApp.ParagraphHeading.HEADING2);
893 b.appendParagraph("Date | Duration | Description | Fee | Status");
894 b.appendParagraph("─────────────────────────────────────────────────────");
895 
896 sessions.forEach(function(s) {
897 b.appendParagraph(
898 s.date + " | " + s.duration + " min | Therapy Session | " +
899 settings["Currency Symbol"] + s.fee + " | " + s.paid
900 );
901 });
902 
903 b.appendParagraph("─────────────────────────────────────────────────────");
904 b.appendParagraph("TOTAL: " + settings["Currency Symbol"] + totalFee.toFixed(2) +
905 " | PAID: " + settings["Currency Symbol"] + totalPaid.toFixed(2) +
906 " | OUTSTANDING: " + settings["Currency Symbol"] + (totalFee - totalPaid).toFixed(2));
907 b.appendParagraph("");
908 
909 doc.saveAndClose();
910 
911 // Auto-move to 3_Finance → Invoices
912 var invoiceMoved = false;
913 try {
914 var practiceFolder = getPracticeFolder();
915 if (practiceFolder) {
916 var pSubs = practiceFolder.getFolders();
917 while (pSubs.hasNext()) {
918 var pSub = pSubs.next();
919 if (pSub.getName().indexOf("3_Finance") !== -1) {
920 var fSubs = pSub.getFolders();
921 while (fSubs.hasNext()) {
922 var fSub = fSubs.next();
923 if (fSub.getName() === "Invoices") {
924 var docFile = DriveApp.getFileById(doc.getId());
925 fSub.addFile(docFile);
926 DriveApp.getRootFolder().removeFile(docFile);
927 invoiceMoved = true;
928 break;
929 }
930 }
931 break;
932 }
933 }
934 }
935 } catch(e) {}
936 
937 var locationMsg = invoiceMoved
938 ? "Saved to: 3_Finance → Invoices"
939 : "Created in your Drive root — move to: 3_Finance → Invoices";
940 
941 showAlert("✅ Invoice Created",
942 "Invoice generated for " + clientCode + " — " + periodInput + ".\n\n" +
943 "Total: " + settings["Currency Symbol"] + totalFee.toFixed(2) + "\n" +
944 "Outstanding: " + settings["Currency Symbol"] + (totalFee - totalPaid).toFixed(2) + "\n\n" +
945 locationMsg);
946}
947 
948 
949// ============================================================
950// HELP
951// ============================================================
952 
953function showHelp() {
954 showAlert("🏥 Practice Tools — Help",
955 "MENU OPTIONS:\n\n" +
956 "➕ Add New Client\n" +
957 "Assigns a client code, creates their Drive folder, and adds them to the Client Index.\n\n" +
958 "📅 Log Session\n" +
959 "Records a completed session with date, format, fee and payment status.\n\n" +
960 "📋 Log DNA\n" +
961 "Records a missed or cancelled session and flags safeguarding concerns.\n\n" +
962 "📁 Close Client Case\n" +
963 "Updates client status, records end date, calculates retention deadline, and moves folder to Archives.\n\n" +
964 "📄 Generate Superbill (US)\n" +
965 "Creates a superbill from logged sessions for insurance reimbursement. US therapists only.\n\n" +
966 "🧾 Generate Invoice\n" +
967 "Creates an invoice from logged sessions. You may want to format it before sending.\n\n" +
968 "─────────────────────────\n" +
969 "TIPS:\n" +
970 "• When selecting from a numbered list, type the NUMBER only (e.g. type 2, not the client name).\n" +
971 "• Dates accept many formats: today, yesterday, 05/03/2026, 5 March 2026.\n" +
972 "• The Income Dashboard year can be changed in cell E4 of that tab.\n" +
973 "• Invoice and superbill generation pull from Session_Log automatically.\n\n" +
974 "For support, contact your practice infrastructure provider.");
975}
976 
977 
978// ============================================================
979// ON EDIT — Clear yellow highlight when Paid status changes
980// ============================================================
981 
982function onEdit(e) {
983 try {
984 var sheet = e.range.getSheet();
985 if (sheet.getName() !== "📅 Session_Log") return;
986 
987 // Column 7 = Paid
988 if (e.range.getColumn() !== 7) return;
989 
990 var row = e.range.getRow();
991 if (row < 2) return; // skip header
992 
993 var value = e.range.getValue();
994 var rowRange = sheet.getRange(row, 1, 1, 11);
995 
996 if (value === "Yes" || value === "Waived") {
997 rowRange.setBackground("#ffffff");
998 } else if (value === "No") {
999 sheet.getRange(row, 7).setBackground("#fff3cd"); // yellow on paid cell only
1000 }
1001 } catch(err) {
1002 // Silent — onEdit must never throw to the user
1003 }
1004}
1005 
1006 
1007// ============================================================
1008// UTILITY
1009// ============================================================
1010 
1011function askUser(ui, title, message) {
1012 var response = ui.prompt(title, message, ui.ButtonSet.OK_CANCEL);
1013 if (response.getSelectedButton() !== ui.Button.OK) return null;
1014 var text = response.getResponseText().trim();
1015 return text || null;
1016}
1017 
1018function showAlert(title, message) {
1019 SpreadsheetApp.getUi().alert(title, message, SpreadsheetApp.getUi().ButtonSet.OK);
1020}
1021 
1022function getInitials(firstName, lastName) {
1023 return (firstName.charAt(0) + lastName.charAt(0)).toUpperCase();
1024}
1025 
1026/**
1027 * Parses a flexible date input into a YYYY-MM-DD string.
1028 * Accepts: "today", "yesterday", "2026-03-05", "05/03/2026",
1029 * "5 March 2026", "March 5 2026", "05-03-2026"
1030 * Returns null if unparseable.
1031 */
1032function parseFlexibleDate(input) {
1033 if (!input) return null;
1034 var s = input.trim().toLowerCase();
1035 
1036 if (s === "today") {
1037 return Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "yyyy-MM-dd");
1038 }
1039 if (s === "yesterday") {
1040 var y = new Date(); y.setDate(y.getDate() - 1);
1041 return Utilities.formatDate(y, Session.getScriptTimeZone(), "yyyy-MM-dd");
1042 }
1043 
1044 var monthNames = {
1045 january:1, jan:1, february:2, feb:2, march:3, mar:3,
1046 april:4, apr:4, may:5, june:6, jun:6,
1047 july:7, jul:7, august:8, aug:8, september:9, sep:9, sept:9,
1048 october:10, oct:10, november:11, nov:11, december:12, dec:12
1049 };
1050 
1051 // YYYY-MM-DD or YYYY/MM/DD or YYYY-DD-MM (try both orderings)
1052 var isoMatch = s.match(/^(\d{4})[-\/](\d{1,2})[-\/](\d{1,2})$/);
1053 if (isoMatch) {
1054 var yr = parseInt(isoMatch[1]);
1055 var a = parseInt(isoMatch[2]);
1056 var b = parseInt(isoMatch[3]);
1057 // Assume YYYY-MM-DD
1058 var d = new Date(yr, a - 1, b);
1059 if (!isNaN(d.getTime())) return Utilities.formatDate(d, Session.getScriptTimeZone(), "yyyy-MM-dd");
1060 }
1061 
1062 // DD/MM/YYYY or DD-MM-YYYY
1063 var dmyMatch = s.match(/^(\d{1,2})[-\/](\d{1,2})[-\/](\d{4})$/);
1064 if (dmyMatch) {
1065 var d2 = new Date(parseInt(dmyMatch[3]), parseInt(dmyMatch[2]) - 1, parseInt(dmyMatch[1]));
1066 if (!isNaN(d2.getTime())) return Utilities.formatDate(d2, Session.getScriptTimeZone(), "yyyy-MM-dd");
1067 }
1068 
1069 // "5 March 2026" or "March 5 2026"
1070 var parts = s.split(/[\s,]+/);
1071 var day = null, month = null, year = null;
1072 parts.forEach(function(p) {
1073 var n = parseInt(p);
1074 if (!isNaN(n) && n > 31) { year = n; }
1075 else if (!isNaN(n) && n >= 1 && n <= 31 && day === null) { day = n; }
1076 else if (monthNames[p]) { month = monthNames[p]; }
1077 });
1078 if (day && month && year) {
1079 var d3 = new Date(year, month - 1, day);
1080 if (!isNaN(d3.getTime())) return Utilities.formatDate(d3, Session.getScriptTimeZone(), "yyyy-MM-dd");
1081 }
1082 
1083 return null;
1084}
1085 
1086function formatDisplayDate(isoString) {
1087 if (!isoString) return "";
1088 var d = new Date(isoString);
1089 if (isNaN(d.getTime())) return isoString;
1090 var months = ["January","February","March","April","May","June",
1091 "July","August","September","October","November","December"];
1092 return d.getDate() + " " + months[d.getMonth()] + " " + d.getFullYear();
1093}
1094 
1095function askDate(ui, stepLabel, promptText) {
1096 var raw = askUser(ui, stepLabel,
1097 promptText + "\n\nAccepted formats:\n" +
1098 " today | yesterday\n" +
1099 " 05/03/2026 | 2026-03-05\n" +
1100 " 5 March 2026");
1101 if (!raw) return null;
1102 var parsed = parseFlexibleDate(raw);
1103 if (!parsed) {
1104 showAlert("Invalid Date", "\"" + raw + "\" was not recognised as a valid date.\n\nTry: today, 05/03/2026, or 5 March 2026.");
1105 return null;
1106 }
1107 return parsed;
1108}
1109 
1110function askNumber(ui, stepLabel, promptText) {
1111 var raw = askUser(ui, stepLabel, promptText);
1112 if (!raw) return null;
1113 var n = parseInt(raw);
1114 if (isNaN(n)) {
1115 showAlert("Invalid Entry", "Please type a NUMBER only (e.g. type 1, not the full text).");
1116 return null;
1117 }
1118 return n;
1119}