A way to track HSA receipts in google sheets/drive

MedBillFiller.gs

// =============================================================================
// MedBillFiler — Google Apps Script
// =============================================================================
// HOW TO USE:
//   1. Paste this file into script.google.com (new standalone project)
//   2. Fill in SHEET_ID below (from your Google Sheet URL)
//   3. Confirm SHEET_NAME matches your tab name
//   4. Run setupTrigger() once from the editor to start automated processing
//   5. Forward scanned bills to me+healthbill@gmail.com with subject:
//        Provider/Comment, YYYYMMDD, Amount
//      Example: Mayo Clinic, 20260315, 312.50
// =============================================================================

const CONFIG = {
  DRIVE_ROOT_FOLDER: 'HSA Receipts', // top-level folder name in Google Drive
  SHEET_ID: '',                            // paste your Google Sheet ID here
  SHEET_NAME: 'Sheet1',                   // tab name within the sheet
  TARGET_ADDRESS_TAG: 'healthbill',        // the part after + in your plus-address
  PROCESSED_LABEL: 'HealthBill-Processed',
  ERROR_LABEL: 'HealthBill-Error',
  TRIGGER_INTERVAL_MINUTES: 1,            // 1 for testing; 1440 for daily production
};

// =============================================================================
// ENTRY POINT — called by the time-based trigger
// =============================================================================

function processHealthBills() {
  const processedLabel = getOrCreateLabel(CONFIG.PROCESSED_LABEL);
  const errorLabel = getOrCreateLabel(CONFIG.ERROR_LABEL);

  const query = `to:+${CONFIG.TARGET_ADDRESS_TAG} -label:${CONFIG.PROCESSED_LABEL} -label:${CONFIG.ERROR_LABEL}`;
  const threads = GmailApp.search(query);

  for (const thread of threads) {
    const messages = thread.getMessages();
    let threadFullyProcessed = true;

    for (const message of messages) {
      // Skip messages not actually addressed to the +healthbill address
      const toHeader = message.getTo();
      if (!toHeader.includes(`+${CONFIG.TARGET_ADDRESS_TAG}`)) continue;

      // Skip if this individual message was already handled via a label on the thread
      // (thread-level labels mean we track per-thread, so check the message date
      // is within an unprocessed window — simpler: we rely on the search query above)

      try {
        processMessage(message, processedLabel, errorLabel);
      } catch (err) {
        threadFullyProcessed = false;
        replyWithError(message, `Unexpected error: ${err.message}`);
        thread.addLabel(errorLabel);
      }
    }
  }
}

// =============================================================================
// PROCESS A SINGLE MESSAGE
// =============================================================================

function processMessage(message, processedLabel, errorLabel) {
  const thread = message.getThread();

  // --- Parse subject ---
  let parsed;
  try {
    parsed = parseSubject(message.getSubject());
  } catch (err) {
    replyWithError(message, err.message);
    thread.addLabel(errorLabel);
    return;
  }

  // --- Check for attachments ---
  const attachments = message.getAttachments();
  if (attachments.length === 0) {
    replyWithError(message, 'No attachment found. Please forward the email with the scanned document attached.');
    thread.addLabel(errorLabel);
    return;
  }

  // --- Process each attachment ---
  for (const attachment of attachments) {
    let saved = null;
    try {
      saved = saveFileToDrive(attachment, parsed.date, parsed.amount);
      appendToSheet(parsed.date, parsed.amount, saved.path, parsed.comment);
    } catch (err) {
      if (saved) saved.file.setTrashed(true); // roll back Drive file if sheet update failed
      throw err;
    }
  }

  // --- Mark as processed ---
  thread.addLabel(processedLabel);
}

// =============================================================================
// SUBJECT PARSING
// =============================================================================

function parseSubject(subject) {
  const parts = subject.split(',').map(s => s.trim());

  if (parts.length !== 3) {
    throw new Error(
      `Subject must have exactly 3 comma-separated fields: Comment, YYYYMMDD, Amount\n` +
      `Example: Mayo Clinic, 20260315, 312.50\n` +
      `Received: "${subject}"`
    );
  }

  const [comment, date, amount] = parts;

  if (!comment) {
    throw new Error(
      `Comment (first field) must not be empty.\n` +
      `Example: Mayo Clinic, 20260315, 312.50\n` +
      `Received: "${subject}"`
    );
  }

  if (!/^\d{8}$/.test(date)) {
    throw new Error(
      `Date (second field) must be 8 digits in YYYYMMDD format.\n` +
      `Example: 20260315\n` +
      `Received: "${date}"`
    );
  }

  if (!/^\d+(\.\d{1,2})?$/.test(amount)) {
    throw new Error(
      `Amount (third field) must be a number with up to 2 decimal places.\n` +
      `Example: 312.50\n` +
      `Received: "${amount}"`
    );
  }

  return { comment, date, amount };
}

// =============================================================================
// DRIVE FILE SAVING
// =============================================================================

function saveFileToDrive(attachment, date, amount) {
  const year = date.substring(0, 4);

  // Locate root folder
  const rootIterator = DriveApp.getFoldersByName(CONFIG.DRIVE_ROOT_FOLDER);
  if (!rootIterator.hasNext()) {
    throw new Error(`Google Drive folder "${CONFIG.DRIVE_ROOT_FOLDER}" not found. Please create it first.`);
  }
  const rootFolder = rootIterator.next();

  // Get or create year subfolder
  const yearFolder = getOrCreateFolder(rootFolder, year);

  // Determine file extension from attachment
  const originalName = attachment.getName();
  const ext = originalName.includes('.') ? originalName.split('.').pop().toLowerCase() : 'pdf';

  // Generate unique filename with incrementing counter
  const baseFilename = `${date}_${amount}`;
  let counter = 1;
  let filename;
  do {
    filename = `${baseFilename}.${counter}.${ext}`;
    counter++;
  } while (fileExistsInFolder(yearFolder, filename));

  // Save file
  const file = yearFolder.createFile(attachment.copyBlob().setName(filename));

  return { path: `${CONFIG.DRIVE_ROOT_FOLDER}/${year}/${filename}`, file };
}

function getOrCreateFolder(parentFolder, folderName) {
  const iterator = parentFolder.getFoldersByName(folderName);
  if (iterator.hasNext()) return iterator.next();
  return parentFolder.createFolder(folderName);
}

function fileExistsInFolder(folder, filename) {
  const iterator = folder.getFilesByName(filename);
  return iterator.hasNext();
}

// =============================================================================
// SHEET ROW INSERTION
// =============================================================================

function appendToSheet(date, amount, filePath, comment) {
  if (!CONFIG.SHEET_ID) {
    throw new Error('SHEET_ID is not set in CONFIG. Please add your Google Sheet ID.');
  }
  const sheet = SpreadsheetApp
    .openById(CONFIG.SHEET_ID)
    .getSheetByName(CONFIG.SHEET_NAME);

  if (!sheet) {
    throw new Error(`Sheet tab "${CONFIG.SHEET_NAME}" not found in the spreadsheet.`);
  }

  // Columns: Bill date | Amount paid | Bill file | Comment
  sheet.appendRow([date, parseFloat(amount), filePath, comment]);
}

// =============================================================================
// GMAIL LABEL HELPER
// =============================================================================

function getOrCreateLabel(name) {
  const existing = GmailApp.getUserLabelByName(name);
  if (existing) return existing;
  return GmailApp.createLabel(name);
}

// =============================================================================
// ERROR REPLY
// =============================================================================

function replyWithError(message, reason) {
  const body =
    `Your health bill could not be processed.\n\n` +
    `Reason: ${reason}\n\n` +
    `To retry: fix the issue and forward the bill again to +${CONFIG.TARGET_ADDRESS_TAG}.\n` +
    `The failed email has been labeled "${CONFIG.ERROR_LABEL}" in your inbox.`;

  message.reply(body);
}

// =============================================================================
// TRIGGER SETUP — run this once manually from the Apps Script editor
// =============================================================================

function setupTrigger() {
  // Remove any existing triggers for processHealthBills to avoid duplicates
  const triggers = ScriptApp.getProjectTriggers();
  for (const trigger of triggers) {
    if (trigger.getHandlerFunction() === 'processHealthBills') {
      ScriptApp.deleteTrigger(trigger);
    }
  }

  // Create new time-based trigger
  ScriptApp.newTrigger('processHealthBills')
    .timeBased()
    .everyMinutes(CONFIG.TRIGGER_INTERVAL_MINUTES)
    .create();

  Logger.log(`Trigger set: processHealthBills will run every ${CONFIG.TRIGGER_INTERVAL_MINUTES} minute(s).`);
}