Smart EMR - Templates & Resources

Copy the files you need to complete your Smart EMR setup.

Setup Files — This page contains the files you need to complete your Smart EMR setup. Follow the complete documentation for instructions on where to use these files.

Document Templates

Smart EMR uses 6 Google Docs templates for printing prescriptions, lab orders, medical reports, and referral letters. You need to copy these templates into your Smart EMR folder.

📂 Open Shared Templates Folder

📄

Medical_Report_Template A4

Full-page medical report with letterhead

📄

Referral_Letter_Template A4

Referral letter with letterhead

📄

Rx_Full_Template A5

Prescription with full letterhead

📄

Rx_Content_Template A5

Prescription content only (for pre-printed paper)

📄

Orders_Full_Template A5

Lab/imaging orders with full letterhead

📄

Orders_Content_Template A5

Orders content only (for pre-printed paper)

How to copy the templates to your Smart EMR folder:

Open the shared folder above
Select all 6 files → Right-click → Make a copy
The copies will appear in your My Drive (named "Copy of ..." at first)
Navigate to your Smart EMR folder: My Drive > AppSheet > data > SmartEMR-XXXXX
Inside that folder, create a subfolder called Templates
Move all 6 copied files into the Templates subfolder
Rename each file to remove the "Copy of " prefix

⚠️ Important: Do NOT download and re-upload the files. Downloading converts them to .docx format which will not work with Smart EMR. Always use "Make a copy" inside Google Drive to keep them as native Google Docs.

Integration Script (Apps Script)

This script connects Smart EMR to the AI Clinical Documentation Assistant (CDA). You need to paste this code into your Google Sheet's Apps Script editor.

CDA_Integration.js

Version 4.10 — 1,372 lines

How to add the integration script:

Open your Visits spreadsheet in Google Sheets
Click Extensions → Apps Script
Select all existing code (Ctrl+A) and delete it
Click the "Copy Script Code" button above
Paste into the Apps Script editor (Ctrl+V)
Click the save icon (💾) or press Ctrl+S
Select setupIntegration from the function dropdown
Click the ▶ Run button and authorize when prompted
You should see "CDA Integration Active!" in the execution log

Need Help? Follow the complete documentation for step-by-step setup instructions, or check the FAQ for common questions. Contact us at support@ai4docs.ai.

// ============================================
// EMR CORE ↔ CDA INTEGRATION
// Method: Watcher/onChange Trigger
// Version: 4.10 - Fixed Logo Aspect Ratio, CDA Letterhead Strip, Age from DOB
// ============================================
//
// TRIGGER VALUES:
// - CDA_Status = "Fetch"   → Downloads completed note (CHECKED FIRST!)
// - CDA_START = "Start CDA"  → Creates new session
//
// STATUS VALUES (aligned with AppSheet enum):
// - "Ready"      → Initial state
// - "Creating session..." → In progress
// - "Session Created" → Session ready, URL available
// - "Fetch"      → User wants note (triggers fetch)
// - "Downloading..."  → Fetching in progress
// - "Fetched"     → Note retrieved successfully
// - "Error: xxx"    → Something failed
//
// CRITICAL: Fetch is checked FIRST because CDA_START stays
// "Start CDA" forever (Enum can't be written to).
// ============================================

// --- CONFIGURATION ---
const CDA_API_URL = 'https://api.ai4docs.ai';

// ============================================
// 1. SETUP FUNCTION - Doctor runs this ONCE
// ============================================
function setupIntegration() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

// 1. NUKE ALL TRIGGERS (Safety Step)
  const triggers = ScriptApp.getProjectTriggers();
  console.log(`Found ${triggers.length} existing triggers. Deleting...`);
  triggers.forEach(t => {
    try {
      ScriptApp.deleteTrigger(t);
    } catch (e) {
      console.error("Could not delete trigger: " + e.message);
    }
  });

// 2. CREATE NEW WATCHER
  ScriptApp.newTrigger('processQueue')
    .forSpreadsheet(ss)
    .onChange()
    .create();

// 3. FINISH (Non-blocking!)
  ss.toast('✅ CDA Integration Active!', 'Success', 5);
  console.log('Setup Complete. Integration is active.');
}

// ============================================
// 2. THE WATCHER - Runs automatically on change
// ============================================
function processQueue(e) {
  // Skip format-only changes
  if (e && e.changeType === 'FORMAT') return;

// LOCKING: Prevent simultaneous runs
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(2000)) {
    console.log("Could not get lock - skipping (already processing)");
    return;
  }

try {
    _processQueueInternal();
  } catch (err) {
    console.error("Watcher Error: " + err.message);
  } finally {
    lock.releaseLock();
  }
}

function _processQueueInternal() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Visits");
  if (!sheet) {
    console.log("ERROR: Visits sheet not found!");
    return;
  }

const data = sheet.getDataRange().getValues();
  const headers = data[0];
  console.log("Headers found: " + JSON.stringify(headers));

// Find column indexes (case-insensitive)
  const findCol = (name) => {
    const lowerName = name.toLowerCase();
    for (let i = 0; i < headers.length; i++) {
      if (String(headers[i]).toLowerCase() === lowerName) return i;
    }
    return -1;
  };

// Safety check - need at least VisitID
  if (visitIdCol === -1) {
    console.log("ERROR: VisitID column not found");
    return;
  }

// Loop through rows (skip header)
  console.log(`Scanning ${data.length - 1} rows...`);

for (let i = 1; i < data.length; i++) {
    const currentRow = i + 1;
    const cdaStart = startCol !== -1 ? data[i][startCol] : null;
    const cdaStatus = statusCol !== -1 ? data[i][statusCol] : null;
    const visitID = data[i][visitIdCol];
    const patientID = patientIdCol !== -1 ? data[i][patientIdCol] : null;
    const cdaStartLower = cdaStart ? String(cdaStart).toLowerCase() : "";
    const cdaStatusLower = cdaStatus ? String(cdaStatus).toLowerCase() : "";

// Log what we see
    if (cdaStart || cdaStatus) {
      console.log(`Row ${currentRow}: CDA_START="${cdaStart}", CDA_Status="${cdaStatus}", VisitID="${visitID}"`);
    }

// ========================================
    // ACTION 1: FETCH NOTE (Check FIRST!)
    // ========================================
    if (cdaStatusLower === "fetch" || cdaStatusLower === "fetching...") {
      console.log(`>>> Row ${currentRow}: FETCH triggered for VisitID=${visitID}`);
      sheet.getRange(currentRow, statusCol + 1).setValue("Downloading...");
      SpreadsheetApp.flush();
      fetchNote(sheet, currentRow, visitID, headers);
      continue;
    }

// ========================================
    // ACTION 2: CREATE SESSION
    // ========================================
    const alreadyProcessing = cdaStatusLower.includes("creating") ||
                  cdaStatusLower.includes("processing") ||
                  cdaStatusLower.includes("downloading") ||
                  cdaStatusLower.includes("session created") ||
                  cdaStatusLower.includes("session ready") ||
                  cdaStatusLower.includes("fetched") ||
                  cdaStatusLower.includes("note retrieved") ||
                  cdaStatusLower.includes("error");

if (cdaStartLower === "start cda" && !alreadyProcessing) {
      console.log(`>>> Row ${currentRow}: CREATE triggered for VisitID=${visitID}`);
      if (statusCol !== -1) {
        sheet.getRange(currentRow, statusCol + 1).setValue("Creating session...");
        console.log("Set CDA_Status = Creating session...");
      }
      SpreadsheetApp.flush();
      createSession(sheet, currentRow, visitID, patientID, headers);
    }
  }

console.log("Scan complete.");

// === PRINT ENGINE (added for document generation) ===
  handlePrintRequests();
}

// ============================================
// 3. CREATE SESSION - Calls backend API
// ============================================
function createSession(sheet, row, visitID, patientID, headers) {
  console.log(`createSession called: row=${row}, visitID=${visitID}, patientID=${patientID}`);
  try {
    const settings = getSettings();
    console.log(`Settings: apiKey=${settings.apiKey ? "YES" : "NO"}, doctorEmail=${settings.doctorEmail}`);

if (!settings.apiKey) {
      console.log("ERROR: No API key found!");
      sheet.getRange(row, headers.indexOf("CDA_Status") + 1).setValue("Error: No API key");
      return;
    }

const patientContext = getPatientContext(patientID);
    console.log(`Patient: ${patientContext.name}, Age: ${patientContext.age}`);

const previousNotes = getPreviousNotes(patientID, visitID);
    console.log(`Previous notes: ${previousNotes ? "YES" : "NO"}`);

const payload = {
      apiKey: settings.apiKey,
      doctorEmail: settings.doctorEmail || Session.getActiveUser().getEmail(),
      visitId: String(visitID),
      patientContext: patientContext,
      previousNotes: previousNotes
    };

console.log(`Calling backend: ${CDA_API_URL}/api/session/create`);
    console.log(`Payload visitId: ${payload.visitId}`);

const response = UrlFetchApp.fetch(CDA_API_URL + '/api/session/create', {
      method: 'POST',
      contentType: 'application/json',
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

const responseText = response.getContentText();
    console.log(`Backend response: ${responseText.substring(0, 500)}`);
    const result = JSON.parse(responseText);

if (result.success) {
      console.log(`SUCCESS! sessionId=${result.sessionId}, sessionUrl=${result.sessionUrl}`);
      const sessionIdCol = headers.indexOf("CDA_Session_ID");
      const sessionUrlCol = headers.indexOf("CDA_Session_URL");
      const statusCol = headers.indexOf("CDA_Status");
      console.log(`Writing columns: sessionIdCol=${sessionIdCol}, sessionUrlCol=${sessionUrlCol}, statusCol=${statusCol}`);

if (sessionIdCol !== -1) {
        sheet.getRange(row, sessionIdCol + 1).setValue(result.sessionId);
        console.log(`Wrote sessionId to column ${sessionIdCol + 1}`);
      }
      if (sessionUrlCol !== -1) {
        sheet.getRange(row, sessionUrlCol + 1).setValue(result.sessionUrl);
        console.log(`Wrote sessionUrl to column ${sessionUrlCol + 1}`);
      }
      if (statusCol !== -1) {
        sheet.getRange(row, statusCol + 1).setValue("Session Created");
        console.log(`Wrote status to column ${statusCol + 1}`);
      }
    } else {
      console.log(`FAILED: ${result.error}`);
      const statusCol = headers.indexOf("CDA_Status");
      if (statusCol !== -1) {
        sheet.getRange(row, statusCol + 1).setValue("Error: " + (result.error || "Unknown"));
      }
    }
  } catch (err) {
    console.log(`EXCEPTION: ${err.message}`);
    const statusCol = headers.indexOf("CDA_Status");
    if (statusCol !== -1) {
      sheet.getRange(row, statusCol + 1).setValue("Error: " + err.message);
    }
  }
}

// ============================================
// 4. FETCH NOTE - Gets completed note
// ============================================
function fetchNote(sheet, row, visitID, headers) {
  try {
    const settings = getSettings();
    if (!settings.apiKey) {
      sheet.getRange(row, headers.indexOf("CDA_Status") + 1).setValue("Error: No API key");
      return;
    }

const sessionId = "emr_" + visitID;
    console.log(`Computed sessionId: ${sessionId}`);

const response = UrlFetchApp.fetch(CDA_API_URL + '/api/session/export', {
      method: 'POST',
      contentType: 'application/json',
      payload: JSON.stringify({
        apiKey: settings.apiKey,
        sessionId: sessionId
      }),
      muteHttpExceptions: true
    });

const result = JSON.parse(response.getContentText());

if (result.success) {
      const noteContent = result.clinicalNoteMarkdown || result.clinicalNote || "";
      const notesCol = headers.indexOf("ClinicalNotes");
      if (notesCol !== -1) {
        sheet.getRange(row, notesCol + 1).setValue(noteContent);
      }

const rxCol = headers.indexOf("Prescription");
      const ordersCol = headers.indexOf("Orders");
      const dxCol = headers.indexOf("Diagnosis");

if (result.prescription && rxCol !== -1) {
        sheet.getRange(row, rxCol + 1).setValue(result.prescription);
      }
      if (result.orders && ordersCol !== -1) {
        sheet.getRange(row, ordersCol + 1).setValue(result.orders);
      }
      if (result.diagnosis && dxCol !== -1) {
        sheet.getRange(row, dxCol + 1).setValue(result.diagnosis);
      }

sheet.getRange(row, headers.indexOf("CDA_Status") + 1).setValue("Fetched");
    } else {
      sheet.getRange(row, headers.indexOf("CDA_Status") + 1).setValue("Error: " + (result.error || "Not ready"));
    }
  } catch (err) {
    sheet.getRange(row, headers.indexOf("CDA_Status") + 1).setValue("Error: " + err.message);
  }
}

// ============================================
// 5. HELPER FUNCTIONS
// ============================================
function getSettings() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Settings");
  if (!sheet) return { apiKey: "", doctorEmail: "" };

const data = sheet.getDataRange().getValues();
  const config = {};
  for (let i = 0; i < data.length; i++) {
    config[String(data[i][0]).trim()] = data[i][1];
  }

return {
    apiKey: config["API_Key"] || config["ApiKey"] || config["api_key"] || "",
    doctorEmail: config["Doctor_Email"] || config["DoctorEmail"] || ""
  };
}

const data = patientsSheet.getDataRange().getValues();
    const headers = data[0];
    const idCol = headers.indexOf("PatientID") !== -1 ? headers.indexOf("PatientID") : headers.indexOf("Patient_ID");
    const nameCol = headers.indexOf("FullName") !== -1 ? headers.indexOf("FullName") : headers.indexOf("PatientName");
    const dobCol = headers.indexOf("DateOfBirth") !== -1 ? headers.indexOf("DateOfBirth") : headers.indexOf("DOB");
    const ageCol = headers.indexOf("Age"); // fallback if DOB not available
    const genderCol = headers.indexOf("Gender");

if (idCol === -1) {
      console.log("PatientID column not found in Patients table");
      return { name: "Unknown", age: "", gender: "" };
    }

for (let i = 1; i < data.length; i++) {
      if (String(data[i][idCol]) === String(patientId)) {
        const name = nameCol !== -1 ? data[i][nameCol] : "";
        const gender = genderCol !== -1 ? data[i][genderCol] : "";

// Calculate age from DOB (replicates AppSheet VC: FLOOR(HOUR(TODAY()-[DateOfBirth])/8760))
        let age = "";
        if (dobCol !== -1 && data[i][dobCol]) {
          try {
            const dob = new Date(data[i][dobCol]);
            if (!isNaN(dob.getTime())) {
              const now = new Date();
              const msPerHour = 1000 * 60 * 60;
              const hoursDiff = (now.getTime() - dob.getTime()) / msPerHour;
              age = String(Math.floor(hoursDiff / 8760));
            }
          } catch (e) {
            console.log("Error calculating age from DOB: " + e.message);
          }
        }
        // Fallback: use Age column if DOB calculation failed
        if (!age && ageCol !== -1 && data[i][ageCol]) {
          age = String(data[i][ageCol]);
        }

console.log(`Found patient: ${name}, Age: ${age}, Gender: ${gender}`);
        return { name: name || "Unknown", age: age, gender: String(gender) };
      }
    }

console.log(`Patient ${patientId} not found in Patients table`);
    return { name: "Unknown", age: "", gender: "" };
  } catch (err) {
    console.log(`Error getting patient context: ${err.message}`);
    return { name: "Unknown", age: "", gender: "" };
  }
}

function getPreviousNotes(patientId, currentVisitId) {
  if (!patientId) return "";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Visits");
  if (!sheet) return "";

const data = sheet.getDataRange().getValues();
  const headers = data[0];

const findCol = (names) => {
    for (const name of names) {
      const idx = headers.indexOf(name);
      if (idx !== -1) return idx;
    }
    return -1;
  };

const patientIdCol = findCol(["PatientID", "Patient_ID"]);
  const visitIdCol = findCol(["VisitID", "Visit_ID"]);
  const notesCol = findCol(["ClinicalNotes", "Clinical_Notes"]);
  const dateCol = findCol(["VisitDate", "Visit_Date", "Date"]);

if (patientIdCol === -1 || notesCol === -1) return "";

const previousVisits = [];
  for (let i = 1; i < data.length; i++) {
    const rowPatientId = String(data[i][patientIdCol]);
    const rowVisitId = visitIdCol !== -1 ? String(data[i][visitIdCol]) : "";
    const rowNotes = data[i][notesCol];
    const rowDate = dateCol !== -1 ? data[i][dateCol] : "";

if (rowPatientId === String(patientId) &&
      rowVisitId !== String(currentVisitId) &&
      rowNotes && String(rowNotes).trim().length > 0) {
      previousVisits.push({ visitId: rowVisitId, date: rowDate, notes: rowNotes });
    }
  }

if (previousVisits.length === 0) return "";

previousVisits.sort((a, b) => new Date(a.date) - new Date(b.date));

let formatted = "=== PREVIOUS CLINICAL NOTES ===\n";
  formatted += `(Complete patient history: ${previousVisits.length} previous visit${previousVisits.length > 1 ? 's' : ''}. Static sections like PMH, allergies, surgical history are already documented - do not repeat unless changed.)\n\n`;

previousVisits.forEach((visit, index) => {
    const dateStr = visit.date ? new Date(visit.date).toLocaleDateString() : "Unknown";
    const label = index === 0 ? "FIRST VISIT" : `VISIT ${index + 1}`;
    formatted += `--- ${label} (${dateStr}) ---\n${visit.notes}\n\n`;
  });

console.log(`Prepared previous notes: ALL ${previousVisits.length} previous visits`);
  return formatted;
}

// ============================================
// PRINT ENGINE — Google Doc → PDF Generation
// Version: 3.0 - Header/Footer + VC Parsing + Logo Aspect Ratio + Markdown Clean + Age from DOB
// Triggered by: PrintRequest column changes
// ============================================

// --- Template name → file link column mapping ---
const PRINT_CONFIG = {
  'Report':      { template: 'Medical_Report_Template',  linkCol: 'Report_File_Link' },
  'Referral':    { template: 'Referral_Letter_Template', linkCol: 'Referral_File_Link' },
  'Rx_Full':     { template: 'Rx_Full_Template',         linkCol: 'Rx_File_Link' },
  'Rx_Content':  { template: 'Rx_Content_Template',      linkCol: 'Rx_File_Link' },
  'Orders_Full': { template: 'Orders_Full_Template',     linkCol: 'Orders_File_Link' },
  'Orders_Content': { template: 'Orders_Content_Template', linkCol: 'Orders_File_Link' }
};

// ============================================
// VC PARSING: Extract & Clean from ClinicalNotes
// Replicates AppSheet VCs in JavaScript
// ============================================

// --- Layer 1: Extract raw Prescription from ClinicalNotes ---
// Finds "**prescription" heading, starts from first bullet "•", ends before "**orders" (if after)
function extractPrescription(clinicalNotes) {
  if (!clinicalNotes) return '';
  const lower = clinicalNotes.toLowerCase();
  const rxStart = lower.indexOf('**prescription');
  if (rxStart === -1) return '';

// Find first bullet after the heading
  const afterHeading = clinicalNotes.substring(rxStart, rxStart + 200);
  const bulletOffset = afterHeading.indexOf('•');
  if (bulletOffset === -1) return '';

const contentStart = rxStart + bulletOffset;

// Find where to end: at "**orders" if it comes AFTER prescription
  const ordersPos = lower.indexOf('**orders');
  let contentEnd;
  if (ordersPos !== -1 && ordersPos > rxStart) {
    contentEnd = ordersPos - 5; // -5 to trim whitespace/newlines before **orders
  } else {
    contentEnd = clinicalNotes.length;
  }

return clinicalNotes.substring(contentStart, Math.max(contentStart, contentEnd)).trim();
}

// --- Layer 1: Extract raw Orders from ClinicalNotes ---
// Finds "**orders" heading, starts from first bullet "•", ends before "**prescription" (if after)
function extractOrders(clinicalNotes) {
  if (!clinicalNotes) return '';
  const lower = clinicalNotes.toLowerCase();
  const ordersStart = lower.indexOf('**orders');
  if (ordersStart === -1) return '';

// Find first bullet after the heading
  const afterHeading = clinicalNotes.substring(ordersStart, ordersStart + 200);
  const bulletOffset = afterHeading.indexOf('•');
  if (bulletOffset === -1) return '';

const contentStart = ordersStart + bulletOffset;

// Find where to end: at "**prescription" if it comes AFTER orders
  const rxPos = lower.indexOf('**prescription');
  let contentEnd;
  if (rxPos !== -1 && rxPos > ordersStart) {
    contentEnd = rxPos - 5;
  } else {
    contentEnd = clinicalNotes.length;
  }

return clinicalNotes.substring(contentStart, Math.max(contentStart, contentEnd)).trim();
}

// --- Layer 1: Extract raw Diagnosis from ClinicalNotes ---
// Finds "active problems:" heading, extracts until next section header
function extractDiagnosis(clinicalNotes) {
  if (!clinicalNotes) return '';
  const lower = clinicalNotes.toLowerCase();

// Find "active problems:" (with or without extra space)
  let startPos = -1;
  let offset = 0;
  const ap1 = lower.indexOf('active problems:');
  if (ap1 !== -1) {
    startPos = ap1;
    offset = 'active problems:'.length;
  }
  // Check alternate spacing just in case
  if (startPos === -1) {
    const ap2 = lower.indexOf('active problems:');
    if (ap2 !== -1) {
      startPos = ap2;
      offset = 'active problems:'.length;
    }
  }
  if (startPos === -1) return '';

const contentStart = startPos + offset;

// Find the nearest next-section boundary
  const boundaries = [
    '**key findings', 'key findings:',
    '**key changes since last visit', 'key changes since last visit:',
    '**current management', 'current management:',
    '**action items', 'action items:',
    '**action plan', 'action plan:',
    '**summary', 'summary:'
  ];

let contentEnd = clinicalNotes.length;
  for (const boundary of boundaries) {
    const pos = lower.indexOf(boundary, contentStart);
    if (pos !== -1 && pos < contentEnd) {
      contentEnd = pos;
    }
  }

return clinicalNotes.substring(contentStart, contentEnd).trim();
}

// --- Layer 2: Clean Prescription (remove markdown from extracted text) ---
// Finds "Medications:" inside the extracted prescription, strips "**" bold markers
function cleanPrescription(rawPrescription) {
  if (!rawPrescription) return '';
  const medIdx = rawPrescription.indexOf('Medications:');
  let text;
  if (medIdx !== -1) {
    text = rawPrescription.substring(medIdx + 'Medications:'.length);
  } else {
    // "Medications:" not found — use the whole text
    text = rawPrescription;
  }
  // Strip ** markdown bold
  text = text.replace(/\*\*/g, '');
  return text.trim();
}

// --- Layer 2: Clean Orders (remove markdown from extracted text) ---
// Finds "Investigations:" inside the extracted orders, strips "**" bold markers
function cleanOrders(rawOrders) {
  if (!rawOrders) return '';
  const invIdx = rawOrders.indexOf('Investigations:');
  let text;
  if (invIdx !== -1) {
    text = rawOrders.substring(invIdx + 'Investigations:'.length);
  } else {
    // "Investigations:" not found — use the whole text
    text = rawOrders;
  }
  // Strip ** markdown bold
  text = text.replace(/\*\*/g, '');
  return text.trim();
}

// --- Layer 2: Clean Diagnosis (remove markdown from extracted text) ---
// Strips "**" and fixes bullet spacing
function cleanDiagnosis(rawDiagnosis) {
  if (!rawDiagnosis) return '';
  let text = rawDiagnosis;
  // Strip ** markdown bold
  text = text.replace(/\*\*/g, '');
  // Fix " •" → "•" (remove extra space before bullets)
  text = text.replace(/ •/g, '•');
  return text.trim();
}

// --- Clean full ClinicalNotes for Medical Report & Referral Letter ---
// Strips markdown formatting, CDA letterhead, and sincerely block
// so the printed report is clean (template already provides letterhead + signature)
function cleanClinicalNotes(notes, patientName, patientAge, profile) {
  if (!notes) return '';
  let text = notes;

// Strip ** markdown bold markers
  text = text.replace(/\*\*/g, '');

// Strip --- horizontal rules
  text = text.replace(/^-{3,}$/gm, '');

// Strip ### ## # markdown headings (keep the text)
  text = text.replace(/^#{1,3}\s*/gm, '');

// === STRIP CDA LETTERHEAD BLOCK AT THE TOP ===
  // CDA generates: [Doctor Name] / [Title/Specialty] / [Institution] / Date: XX/XX/XXXX
  // then "MEDICAL REPORT" or "REFERRAL LETTER" heading
  // then "To Whom It May Concern," or "Dear Dr.," etc.
  // The Smart EMR template already has its own letterhead and heading,
  // so strip everything before the actual content.
  //
  // Strategy: find "To Whom It May Concern" or "Dear " and keep from there.
  // If not found, find "MEDICAL REPORT" or "REFERRAL LETTER" and skip past it.
  const lowerText = text.toLowerCase();
  const toWhomIdx = lowerText.indexOf('to whom it may concern');
  const dearIdx = lowerText.indexOf('dear ');

if (toWhomIdx !== -1 && (dearIdx === -1 || toWhomIdx < dearIdx)) {
    text = text.substring(toWhomIdx);
  } else if (dearIdx !== -1) {
    text = text.substring(dearIdx);
  } else {
    // Fallback: try to find "MEDICAL REPORT" or "REFERRAL LETTER" and skip past it
    const reportIdx = lowerText.indexOf('medical report');
    const referralIdx = lowerText.indexOf('referral letter');
    const headingIdx = reportIdx !== -1 ? reportIdx : referralIdx;
    if (headingIdx !== -1) {
      // Find the end of that line and start from the next line
      const lineEnd = text.indexOf('\n', headingIdx);
      if (lineEnd !== -1) {
        text = text.substring(lineEnd + 1);
      }
    }
  }

// === STRIP "SINCERELY" BLOCK AT THE BOTTOM ===
  // CDA generates: "Sincerely,\n\nDr. [Doctor Name]" or "Sincerely,\nDr. Alaa Meshref"
  // The template already has its own signature (Dr. DoctorName / Date)
  const sincerelyCut = text.toLowerCase().lastIndexOf('sincerely');
  if (sincerelyCut !== -1) {
    text = text.substring(0, sincerelyCut);
  }

// === REPLACE REMAINING PLACEHOLDERS ===
  // These may appear inside the note body itself (not just letterhead)
  if (patientName) {
    text = text.replace(/\[patient name\]/gi, patientName);
    text = text.replace(/\[patient's name\]/gi, patientName);
  }
  if (patientAge) {
    text = text.replace(/\[age\]/gi, patientAge);
  }
  // Replace doctor placeholders that may remain in the note body
  if (profile) {
    if (profile.name) {
      text = text.replace(/\[doctor name\]/gi, profile.name);
    }
    if (profile.title) {
      text = text.replace(/\[title\/specialty\]/gi, profile.title);
    }
    if (profile.specialty) {
      text = text.replace(/\[specialty\]/gi, profile.specialty);
    }
    if (profile.institution) {
      text = text.replace(/\[institution\]/gi, profile.institution);
    }
  }

// Clean up excessive blank lines (3+ newlines → 2)
  text = text.replace(/\n{3,}/g, '\n\n');

return text.trim();
}

// ============================================
// HEADER/FOOTER: Replace placeholders in Doc Header & Footer sections
// ============================================

// Replace text placeholders in a Header or Footer section
function replaceTextInSection(section, placeholder, value) {
  if (!section) return;
  try {
    section.replaceText('\\{\\{' + placeholder + '\\}\\}', value || '');
  } catch (e) {
    console.log('Print: Could not replace {{' + placeholder + '}} in section: ' + e.message);
  }
}

// Replace image placeholder in a Header or Footer section
// Returns true if found and replaced, false if not found
// targetHeight: desired pixel height — width calculated proportionally from natural aspect ratio
function replaceImageInSection(section, placeholder, fileId, targetHeight) {
  if (!section || !fileId) return false;
  try {
    const blob = DriveApp.getFileById(fileId).getBlob();
    const numChildren = section.getNumChildren();
    for (let i = 0; i < numChildren; i++) {
      const child = section.getChild(i);
      if (child.getType() === DocumentApp.ElementType.PARAGRAPH) {
        const text = child.asParagraph().getText();
        if (text.includes(placeholder)) {
          child.asParagraph().clear();
          const img = child.asParagraph().appendInlineImage(blob);
          resizeImageToHeight(img, targetHeight);
          return true;
        }
      }
      if (child.getType() === DocumentApp.ElementType.TABLE) {
        const table = child.asTable();
        for (let r = 0; r < table.getNumRows(); r++) {
          for (let c = 0; c < table.getRow(r).getNumCells(); c++) {
            const cell = table.getRow(r).getCell(c);
            if (cell.getText().includes(placeholder)) {
              cell.clear();
              const img = cell.appendImage(blob);
              resizeImageToHeight(img, targetHeight);
              return true;
            }
          }
        }
      }
    }
    return false;
  } catch (err) {
    console.log('Print: Error replacing image in section: ' + err.message);
    return false;
  }
}

// ============================================
// MAIN: Scan for print requests
// ============================================
function handlePrintRequests() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Visits');
  if (!sheet) return;

const data = sheet.getDataRange().getValues();
  const headers = data[0];

const findCol = (name) => {
    const lower = name.toLowerCase();
    for (let i = 0; i < headers.length; i++) {
      if (String(headers[i]).toLowerCase() === lower) return i;
    }
    return -1;
  };

const printReqCol = findCol('PrintRequest');
  if (printReqCol === -1) {
    console.log('Print: PrintRequest column not found — skipping');
    return;
  }

for (let i = 1; i < data.length; i++) {
    const requestType = String(data[i][printReqCol]).trim();
    if (requestType && requestType !== '' && PRINT_CONFIG[requestType]) {
      const rowNum = i + 1;
      console.log(`Print: Row ${rowNum} — request="${requestType}"`);

sheet.getRange(rowNum, printReqCol + 1).setValue('Generating...');
      SpreadsheetApp.flush();

try {
        processPrintRequest(sheet, rowNum, data[i], headers, requestType);
        sheet.getRange(rowNum, printReqCol + 1).setValue('');
        console.log(`Print: Row ${rowNum} — SUCCESS, cleared PrintRequest`);
      } catch (err) {
        console.error(`Print: Row ${rowNum} — ERROR: ${err.message}`);
        sheet.getRange(rowNum, printReqCol + 1).setValue('Error: ' + err.message);
      }
    }
  }
}

// ============================================
// PROCESS: Generate one document
// ============================================
function processPrintRequest(sheet, rowNum, rowData, headers, requestType) {
  const config = PRINT_CONFIG[requestType];

// --- Gather data ---
  const visitId = rowData[findCol('VisitID')] || '';
  const patientId = rowData[findCol('PatientID')] || '';
  const visitDate = rowData[findCol('VisitDate')];
  const clinicalNotes = String(rowData[findCol('ClinicalNotes')] || '');

// --- VC Parsing: Extract and clean from ClinicalNotes ---
  const rawPrescription = extractPrescription(clinicalNotes);
  const rawOrders = extractOrders(clinicalNotes);
  const rawDiagnosis = extractDiagnosis(clinicalNotes);

const prescriptionClean = cleanPrescription(rawPrescription);
  const ordersClean = cleanOrders(rawOrders);
  const diagnosisClean = cleanDiagnosis(rawDiagnosis);

console.log(`Print: Parsed — Rx=${prescriptionClean.length} chars, Orders=${ordersClean.length} chars, Dx=${diagnosisClean.length} chars`);

// Patient data (from Patients sheet — age calculated from DOB)
  const patient = getPatientContext(patientId);

// Profile data (from Profile sheet)
  const profile = getProfileData();

// Footer data (from Workplaces sheet)
  const footerA4 = getFooterText(true);   // with email
  const footerA5 = getFooterText(false);  // without email
  const isA4 = (requestType === 'Report' || requestType === 'Referral');
  const footerText = isA4 ? footerA4 : footerA5;

// --- Find and copy template ---
  const templateFile = getTemplateByName(config.template);
  if (!templateFile) {
    throw new Error('Template not found: ' + config.template);
  }

const outputFolder = getOutputFolder();
  const timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd_HHmmss');
  const outputName = config.template.replace('_Template', '') + '_' + visitId + '_' + timestamp;
  const docCopy = templateFile.makeCopy(outputName, outputFolder);
  const doc = DocumentApp.openById(docCopy.getId());
  const body = doc.getBody();
  const header = doc.getHeader();
  const footer = doc.getFooter();

// ========================================
  // REPLACE IN BODY (patient data, clinical content)
  // ========================================
  body.replaceText('\\{\\{PatientName\\}\\}', patient.name || '');
  body.replaceText('\\{\\{PatientAge\\}\\}', patient.age || '');
  body.replaceText('\\{\\{PatientGender\\}\\}', patient.gender || '');
  body.replaceText('\\{\\{VisitDate\\}\\}', formatDate(visitDate));
  body.replaceText('\\{\\{PrintDate\\}\\}', formatDate(new Date()));
  // For Medical Report & Referral Letter: clean markdown from full ClinicalNotes
  // Also replaces [patient name] and [age] baked into the CDA-generated note
  const isFullReport = (requestType === 'Report' || requestType === 'Referral');
  const notesForTemplate = isFullReport
    ? cleanClinicalNotes(clinicalNotes, patient.name, patient.age, profile)
    : clinicalNotes;
  body.replaceText('\\{\\{ClinicalNotes\\}\\}', notesForTemplate);
  body.replaceText('\\{\\{Diagnosis\\}\\}', diagnosisClean);
  body.replaceText('\\{\\{Prescription\\}\\}', prescriptionClean);
  body.replaceText('\\{\\{Orders\\}\\}', ordersClean);

// Profile fields in body too (in case template has them in body)
  body.replaceText('\\{\\{DoctorTitle\\}\\}', profile.title || '');
  body.replaceText('\\{\\{DoctorName\\}\\}', profile.name || '');
  body.replaceText('\\{\\{Specialty\\}\\}', profile.specialty || '');
  body.replaceText('\\{\\{Institution\\}\\}', profile.institution || '');
  body.replaceText('\\{\\{LicenseNumber\\}\\}', profile.licenseNumber || '');
  body.replaceText('\\{\\{Footer\\}\\}', footerText);

// ========================================
  // REPLACE IN HEADER (letterhead: profile + logo)
  // ========================================
  if (header) {
    replaceTextInSection(header, 'DoctorTitle', profile.title);
    replaceTextInSection(header, 'DoctorName', profile.name);
    replaceTextInSection(header, 'Specialty', profile.specialty);
    replaceTextInSection(header, 'Institution', profile.institution);
    replaceTextInSection(header, 'LicenseNumber', profile.licenseNumber);
    // Also replace patient/date fields in case they appear in header
    replaceTextInSection(header, 'PatientName', patient.name);
    replaceTextInSection(header, 'VisitDate', formatDate(visitDate));
    replaceTextInSection(header, 'PrintDate', formatDate(new Date()));
  }

// ========================================
  // REPLACE IN FOOTER (footer text)
  // ========================================
  if (footer) {
    replaceTextInSection(footer, 'Footer', footerText);
    replaceTextInSection(footer, 'DoctorName', profile.name);
    replaceTextInSection(footer, 'LicenseNumber', profile.licenseNumber);
  }

// ========================================
  // REPLACE IMAGES (Logo, Signature) — check header first, then body
  // ========================================
  if (profile.logoFileId) {
    // Insert logo, then resize to 60px height preserving aspect ratio
    // Try header first (most likely location for logo)
    const foundInHeader = replaceImageInSection(header, '{{Logo}}', profile.logoFileId, 60);
    if (!foundInHeader) {
      // Fallback: try body
      replaceWithImage(body, '{{Logo}}', profile.logoFileId, 60);
    }
  } else {
    // Clear placeholder if no logo
    body.replaceText('\\{\\{Logo\\}\\}', '');
    if (header) {
      try { header.replaceText('\\{\\{Logo\\}\\}', ''); } catch (e) { /* ignore */ }
    }
  }

if (profile.signatureFileId) {
    // Signature is typically in body (above footer area) — 40px height
    const foundInBody = replaceWithImage(body, '{{Signature}}', profile.signatureFileId, 40);
    if (!foundInBody && footer) {
      // Fallback: try footer
      replaceImageInSection(footer, '{{Signature}}', profile.signatureFileId, 40);
    }
  } else {
    body.replaceText('\\{\\{Signature\\}\\}', '');
  }

// --- Clean up empty optional lines ---
  cleanEmptyLines(body);

// --- Save and convert to PDF ---
  doc.saveAndClose();
  const pdfBlob = DriveApp.getFileById(docCopy.getId()).getAs('application/pdf');
  const pdfFile = outputFolder.createFile(pdfBlob).setName(outputName + '.pdf');

// Delete the temporary Doc copy (keep only the PDF)
  DriveApp.getFileById(docCopy.getId()).setTrashed(true);

// --- Store the PDF link ---
  const linkCol = findCol(config.linkCol);
  if (linkCol !== -1) {
    const pdfUrl = pdfFile.getUrl();
    sheet.getRange(rowNum, linkCol + 1).setValue(pdfUrl);
    console.log(`Print: Stored PDF link in ${config.linkCol}: ${pdfUrl}`);
  }
}

// ============================================
// HELPER: Get Profile data
// ============================================
function getProfileData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Profile');
  if (!sheet) return {};

const data = sheet.getDataRange().getValues();
  const headers = data[0];
  if (data.length < 2) return {};

const row = data[1]; // Profile has one row (the doctor)

const findCol = (name) => {
    for (let i = 0; i < headers.length; i++) {
      if (String(headers[i]).toLowerCase() === name.toLowerCase()) return i;
    }
    return -1;
  };

const logoPath = findCol('Logo') !== -1 ? String(row[findCol('Logo')]) : '';
  const sigPath = findCol('Signature') !== -1 ? String(row[findCol('Signature')]) : '';

return {
    name: findCol('Name') !== -1 ? row[findCol('Name')] : '',
    title: findCol('Title') !== -1 ? row[findCol('Title')] : '',
    specialty: findCol('Specialty') !== -1 ? row[findCol('Specialty')] : '',
    institution: findCol('Institution') !== -1 ? row[findCol('Institution')] : '',
    licenseNumber: findCol('LicenseNumber') !== -1 ? row[findCol('LicenseNumber')] : '',
    printModeRx: findCol('PrintMode_Rx') !== -1 ? row[findCol('PrintMode_Rx')] : 'Full Letterhead',
    printModeOrders: findCol('PrintMode_Orders') !== -1 ? row[findCol('PrintMode_Orders')] : 'Full Letterhead',
    logoFileId: logoPath ? getImageFileId(logoPath) : null,
    signatureFileId: sigPath ? getImageFileId(sigPath) : null
  };
}

// ============================================
// HELPER: Get footer text from Workplaces
// ============================================
function getFooterText(includeEmail) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Workplaces');
  if (!sheet) return '';

const data = sheet.getDataRange().getValues();
  const headers = data[0];

const findCol = (name) => {
    for (let i = 0; i < headers.length; i++) {
      if (String(headers[i]).toLowerCase() === name.toLowerCase()) return i;
    }
    return -1;
  };

const nameCol = findCol('Name');
  const addressCol = findCol('Address');
  const phoneCol = findCol('Phone');
  const emailCol = findCol('Email');
  const showCol = findCol('ShowInFooter');

if (nameCol === -1) return '';

const lines = [];
  for (let i = 1; i < data.length; i++) {
    const show = showCol !== -1 ? String(data[i][showCol]).toLowerCase() : 'false';
    if (show === 'true' || show === 'yes') {
      let line = String(data[i][nameCol] || '');
      if (addressCol !== -1 && data[i][addressCol]) line += ' | ' + data[i][addressCol];
      if (phoneCol !== -1 && data[i][phoneCol]) line += ' | Tel: ' + data[i][phoneCol];
      if (includeEmail && emailCol !== -1 && data[i][emailCol]) line += ' | ' + data[i][emailCol];
      lines.push(line);
    }
  }

return lines.join('\n');
}

// ============================================
// HELPER: Resize an InlineImage to a target height, preserving aspect ratio
// Must be called AFTER the image is already inserted (appendInlineImage)
// The InlineImage object has getWidth()/getHeight() for natural dimensions
// ============================================
function resizeImageToHeight(img, targetHeight) {
  try {
    const naturalWidth = img.getWidth();
    const naturalHeight = img.getHeight();
    if (naturalHeight <= 0 || naturalWidth <= 0) {
      img.setWidth(targetHeight);
      img.setHeight(targetHeight);
      return;
    }
    const scaledWidth = Math.round(naturalWidth * (targetHeight / naturalHeight));
    console.log(`Print: Image — natural ${naturalWidth}x${naturalHeight}, scaled to ${scaledWidth}x${targetHeight}`);
    img.setWidth(scaledWidth);
    img.setHeight(targetHeight);
  } catch (err) {
    console.log('Print: Error resizing image: ' + err.message);
  }
}

// ============================================
// HELPER: Get image file ID from AppSheet path
// ============================================
function getImageFileId(imagePath) {
  if (!imagePath || imagePath === '') return null;
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ssFile = DriveApp.getFileById(ss.getId());
    const appFolder = ssFile.getParents().next();

const parts = imagePath.split('/');
    let currentFolder = appFolder;
    for (let i = 0; i < parts.length - 1; i++) {
      const subFolders = currentFolder.getFoldersByName(parts[i]);
      if (!subFolders.hasNext()) {
        console.log('Print: Image folder not found: ' + parts[i]);
        return null;
      }
      currentFolder = subFolders.next();
    }

const fileName = parts[parts.length - 1];
    const files = currentFolder.getFilesByName(fileName);
    if (files.hasNext()) {
      return files.next().getId();
    }

console.log('Print: Image file not found: ' + imagePath);
    return null;
  } catch (err) {
    console.log('Print: Error finding image: ' + err.message);
    return null;
  }
}

// ============================================
// HELPER: Replace text placeholder with image in body
// Returns true if found and replaced, false if not found
// targetHeight: desired pixel height — width calculated proportionally
// ============================================
function replaceWithImage(body, placeholder, fileId, targetHeight) {
  try {
    const blob = DriveApp.getFileById(fileId).getBlob();
    const searchResult = body.findText(placeholder.replace(/[{}]/g, '\\$&'));

if (!searchResult) {
      // Try without escaping (findText with literal)
      const elements = body.getNumChildren();
      for (let i = 0; i < elements; i++) {
        const child = body.getChild(i);
        if (child.getType() === DocumentApp.ElementType.PARAGRAPH) {
          const text = child.asParagraph().getText();
          if (text.includes(placeholder)) {
            child.asParagraph().clear();
            const img = child.asParagraph().appendInlineImage(blob);
            resizeImageToHeight(img, targetHeight);
            return true;
          }
        }
        if (child.getType() === DocumentApp.ElementType.TABLE) {
          const table = child.asTable();
          for (let r = 0; r < table.getNumRows(); r++) {
            for (let c = 0; c < table.getRow(r).getNumCells(); c++) {
              const cell = table.getRow(r).getCell(c);
              const cellText = cell.getText();
              if (cellText.includes(placeholder)) {
                cell.clear();
                const img = cell.appendImage(blob);
                resizeImageToHeight(img, targetHeight);
                return true;
              }
            }
          }
        }
      }
      console.log('Print: Placeholder not found in body: ' + placeholder);
      return false;
    }

// Found via findText — replace in paragraph
    const element = searchResult.getElement();
    const parent = element.getParent();
    parent.asParagraph().clear();
    const img = parent.asParagraph().appendInlineImage(blob);
    resizeImageToHeight(img, targetHeight);
    return true;

} catch (err) {
    console.log('Print: Error replacing image ' + placeholder + ': ' + err.message);
    body.replaceText(placeholder.replace(/[{}]/g, '\\$&'), '');
    return false;
  }
}

// ============================================
// HELPER: Clean up empty lines from blank optional fields
// ============================================
function cleanEmptyLines(body) {
  body.replaceText('License No: \\s*$', '');
}

// ============================================
// HELPER: Get template file by name
// ============================================
function getTemplateByName(templateName) {
  try {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const ssFile = DriveApp.getFileById(ss.getId());
    const appFolder = ssFile.getParents().next();

const templateFolders = appFolder.getFoldersByName('Templates');
    if (templateFolders.hasNext()) {
      const templatesFolder = templateFolders.next();
      const files = templatesFolder.getFilesByName(templateName);
      if (files.hasNext()) return files.next();
    }

// Fallback: look in app folder root
    const files = appFolder.getFilesByName(templateName);
    if (files.hasNext()) return files.next();

console.log('Print: Template not found: ' + templateName);
    return null;
  } catch (err) {
    console.log('Print: Error finding template: ' + err.message);
    return null;
  }
}

// ============================================
// HELPER: Get or create output folder for PDFs
// ============================================
function getOutputFolder() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssFile = DriveApp.getFileById(ss.getId());
  const appFolder = ssFile.getParents().next();

const folderName = 'Generated_Documents';
  const existing = appFolder.getFoldersByName(folderName);
  if (existing.hasNext()) return existing.next();

return appFolder.createFolder(folderName);
}

// ============================================
// HELPER: Format date
// ============================================
function formatDate(date) {
  if (!date) return '';
  try {
    const d = new Date(date);
    if (isNaN(d.getTime())) return String(date);
    return Utilities.formatDate(d, Session.getScriptTimeZone(), 'dd/MM/yyyy');
  } catch (e) {
    return String(date);
  }
}

// ============================================
// 6. TEST FUNCTIONS
// ============================================
function testAuthorization() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.toast('Authorization OK! Now run setupIntegration.', 'Success', 5);
  console.log('Authorization test passed.');
}

function checkStatus() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const triggers = ScriptApp.getProjectTriggers();
  const msg = triggers.length === 0
    ? 'NOT active. Run setupIntegration.'
    : 'ACTIVE! Triggers: ' + triggers.map(t => t.getHandlerFunction()).join(', ');
  ss.toast(msg, 'Status', 5);
  console.log(msg);
}

function manualProcess() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  processQueue(null);
  ss.toast('Done. Check the sheet.', 'Complete', 3);
}

function testFindTemplate() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssFile = DriveApp.getFileById(ss.getId());
  const appFolder = ssFile.getParents().next();
  console.log("Spreadsheet parent folder: " + appFolder.getName());

const subfolders = appFolder.getFolders();
  while (subfolders.hasNext()) {
    console.log("  Subfolder: " + subfolders.next().getName());
  }

function testProfileAndTable() {
  const profile = getProfileData();
  console.log("Profile data: " + JSON.stringify(profile));

const footerA4 = getFooterText(true);
  const footerA5 = getFooterText(false);
  console.log("Footer A4: " + footerA4);
  console.log("Footer A5: " + footerA5);
}

function testTemplateContent() {
  const template = getTemplateByName('Rx_Full_Template');
  const doc = DocumentApp.openById(template.getId());
  const body = doc.getBody();
  const header = doc.getHeader();
  const footer = doc.getFooter();

console.log("=== BODY ===");
  for (var i = 0; i < body.getNumChildren(); i++) {
    var child = body.getChild(i);
    if (child.getType() === DocumentApp.ElementType.TABLE) {
      var table = child.asTable();
      for (var r = 0; r < table.getNumRows(); r++) {
        for (var c = 0; c < table.getRow(r).getNumCells(); c++) {
          var cellText = table.getRow(r).getCell(c).getText();
          if (cellText.length > 0) {
            console.log("Table[" + r + "][" + c + "]: '" + cellText + "'");
          }
        }
      }
    }
    if (child.getType() === DocumentApp.ElementType.PARAGRAPH) {
      var text = child.asParagraph().getText();
      if (text.length > 0) {
        console.log("Body paragraph: '" + text + "'");
      }
    }
  }

console.log("=== HEADER ===");
  if (header) {
    for (var i = 0; i < header.getNumChildren(); i++) {
      var child = header.getChild(i);
      if (child.getType() === DocumentApp.ElementType.PARAGRAPH) {
        console.log("Header paragraph: '" + child.asParagraph().getText() + "'");
      }
      if (child.getType() === DocumentApp.ElementType.TABLE) {
        var table = child.asTable();
        for (var r = 0; r < table.getNumRows(); r++) {
          for (var c = 0; c < table.getRow(r).getNumCells(); c++) {
            console.log("Header Table[" + r + "][" + c + "]: '" + table.getRow(r).getCell(c).getText() + "'");
          }
        }
      }
    }
  } else {
    console.log("No header section");
  }

console.log("=== FOOTER ===");
  if (footer) {
    for (var i = 0; i < footer.getNumChildren(); i++) {
      var child = footer.getChild(i);
      if (child.getType() === DocumentApp.ElementType.PARAGRAPH) {
        console.log("Footer paragraph: '" + child.asParagraph().getText() + "'");
      }
    }
  } else {
    console.log("No footer section");
  }
}

// Test the VC parsing functions with sample ClinicalNotes text
function testVcParsing() {
  // Read ClinicalNotes from first row that has data
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Visits');
  if (!sheet) { console.log("No Visits sheet"); return; }

const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const notesCol = headers.indexOf('ClinicalNotes');
  if (notesCol === -1) { console.log("No ClinicalNotes column"); return; }

// Find first row with ClinicalNotes
  for (let i = 1; i < data.length; i++) {
    const notes = String(data[i][notesCol] || '');
    if (notes.length > 50) {
      console.log("=== Testing row " + (i + 1) + " ===");
      console.log("ClinicalNotes length: " + notes.length);

const rawRx = extractPrescription(notes);
      const rawOrders = extractOrders(notes);
      const rawDx = extractDiagnosis(notes);

console.log("--- Raw Prescription (" + rawRx.length + " chars) ---");
      console.log(rawRx.substring(0, 300));

console.log("--- Raw Orders (" + rawOrders.length + " chars) ---");
      console.log(rawOrders.substring(0, 300));

console.log("--- Raw Diagnosis (" + rawDx.length + " chars) ---");
      console.log(rawDx.substring(0, 300));

console.log("--- Clean Prescription ---");
      console.log(cleanPrescription(rawRx));

console.log("--- Clean Orders ---");
      console.log(cleanOrders(rawOrders));

console.log("--- Clean Diagnosis ---");
      console.log(cleanDiagnosis(rawDx));

break; // Only test first row with data
    }
  }
}