Workspace Automation: Payments & Reconciliation
Budget: ₹1,500 – ₹12,500 INR
Project Description:
I am seeking a Google Workspace / Apps Script expert to build a robust automation system for managing credit card expenses across 2 companies (approx. 15 cards total). We currently use Google Forms for receipt uploads and need to bridge the gap between those uploads and our monthly bank statements.
The Workflow & Requirements:
1. Automated File Management (Google Drive & Forms)
Intelligent Form: Optimize an existing Google Form to handle 15 cardholders across 2 domains.
Auto-Organize Receipts: Scripts must automatically rename form attachments (Format: CC_Month_Vendor_Amount) and move them to specific folders: Company > Cardholder > Month.
PDF Decryption: Our bank statements are password-protected. I need a script/utility to automatically decrypt these PDFs using a provided password lookup table.
Decrypted Statement Management: The "Clean" statement must be renamed (Format: CC_Statement_Month) and saved in the corresponding folder.
2. Reconciliation Dashboard (Google Sheets)
Auto-Matching: Create a script that matches Google Form entries against Bank Statement data based on Date and Amount.
Error Handling: Highlight missing receipts, mismatched amounts, or bank charges without form entries.
Manual Override: Provide a "Sync/Re-Run" button to allow the Accounts team to re-trigger the matching logic after they fix errors until the status is "NIL Mismatches."
3. Task & Alert System (Google Tasks/Calendar/Email)
Deadline Tracking: System must track Statement Dates and Payment Due Dates for each card.
Multi-Stage Alerts:
Stage 1 (Nudges): Email alerts to cardholders/accounts for missing details.
Stage 2 (Escalation): If a statement isn't 100% reconciled 7 days before the due date, send an escalation email to Seniors/Management.
Stage 3 (Payment Call): Automated reminder to the Finance team 3-5 days before the due date with specific bank/account details (pulled from a master sheet).
Closing the Loop: A "Payment Confirmation" checkbox in the sheet must mark the Google Task as complete and stop all further alerts.
Technical Requirements:
Expert-level Google Apps Script and Google Sheets.
Experience with PDF libraries for password removal.
Experience with Google Tasks API and Google Calendar API.
Must understand Cross-Domain permissions (Shared Drives).
I am seeking a Google Workspace / Apps Script expert to build a robust automation system for managing credit card expenses across 2 companies (approx. 15 cards total). We currently use Google Forms for receipt uploads and need to bridge the gap between those uploads and our monthly bank statements.
The Workflow & Requirements:
1. Automated File Management (Google Drive & Forms)
Intelligent Form: Optimize an existing Google Form to handle 15 cardholders across 2 domains.
Auto-Organize Receipts: Scripts must automatically rename form attachments (Format: CC_Month_Vendor_Amount) and move them to specific folders: Company > Cardholder > Month.
PDF Decryption: Our bank statements are password-protected. I need a script/utility to automatically decrypt these PDFs using a provided password lookup table.
Decrypted Statement Management: The "Clean" statement must be renamed (Format: CC_Statement_Month) and saved in the corresponding folder.
2. Reconciliation Dashboard (Google Sheets)
Auto-Matching: Create a script that matches Google Form entries against Bank Statement data based on Date and Amount.
Error Handling: Highlight missing receipts, mismatched amounts, or bank charges without form entries.
Manual Override: Provide a "Sync/Re-Run" button to allow the Accounts team to re-trigger the matching logic after they fix errors until the status is "NIL Mismatches."
3. Task & Alert System (Google Tasks/Calendar/Email)
Deadline Tracking: System must track Statement Dates and Payment Due Dates for each card.
Multi-Stage Alerts:
Stage 1 (Nudges): Email alerts to cardholders/accounts for missing details.
Stage 2 (Escalation): If a statement isn't 100% reconciled 7 days before the due date, send an escalation email to Seniors/Management.
Stage 3 (Payment Call): Automated reminder to the Finance team 3-5 days before the due date with specific bank/account details (pulled from a master sheet).
Closing the Loop: A "Payment Confirmation" checkbox in the sheet must mark the Google Task as complete and stop all further alerts.
Technical Requirements:
Expert-level Google Apps Script and Google Sheets.
Experience with PDF libraries for password removal.
Experience with Google Tasks API and Google Calendar API.
Must understand Cross-Domain permissions (Shared Drives).