Remittance Reconciliation

Overview

The Remittance Reconciliation processor is a web application that scans folders of remittance Excel files, compares values against source files, flags mismatches with warnings, and writes to a shared log matching the existing documentation process.

Built with Google Apps Script and deployed as a web app, it transforms a tedious file-by-file manual process into efficient batch processing with automated validation.

Features

Core capabilities for remittance processing.

• Folder Scanning - Point to any folder path to scan contents
• Batch Processing - Process multiple remittance files simultaneously
• Mismatch Detection - Automatic comparison against source values
• Shared Database - Team-wide access to processing log
• Warning System - Visual alerts for discrepancies
• One-click log entry matching existing process
• Timestamped audit trail with user identification

System Requirements

Required access and environment.

Architecture

System component overview.

• Frontend: Responsive HTML5 interface, CSS3 styling, JavaScript for dynamic updates
• Backend: Google Apps Script server functions, SheetJS for Excel parsing
• Data Layer: Shared Google Sheet as database, source file repository

Usage

Basic operation workflow.

• Open Application: Navigate to deployed web app URL, sign in with Google account
• Set Source Folder: Paste folder path containing remittance files, click "Load Folder"
• Review Files: System displays all Excel files with match/mismatch status
• Process Files: Click "Process" for batch validation, review warnings, click "Write to Log"

File Processing

Supported formats and extraction logic.

• Supported: .xls (Excel 97-2003), .xlsx (Excel 2007+), .xlsm (Macro-enabled)
• Load file via SheetJS → Navigate to target sheet/range
• Extract key values: Account identifier, remittance amount, date/period
• Compare against source database → Flag discrepancies

Results

Performance improvements achieved.

Technology Stack

Technologies used.

• Google Apps Script - Backend platform
• SheetJS - Excel file parsing
• HTML5/CSS3 - User interface
• JavaScript - Frontend logic
• Google Sheets - Database layer