Automate Payment Gateway & Database Transaction Reconciliation with Google Sheets

Description

How it works

This workflow fully automates the reconciliation process between your Local Database transactions and Payment Gateway transactions. It compares both data sources, identifies mismatches, categorizes discrepancies, logs them into Google Sheets, generates a final summary, and sends an automated reconciliation report to your finance team. This ensures accurate, consistent, and error-free financial reporting without manual work.

Key Features

Automatic data extraction from two Google Sheets
Transaction comparison with result categorization
Duplicate detection
Real-time discrepancy logging
Summary generation and storage
Automated email reporting
Zero manual effort required

Setup Steps
1. Connect Required Credentials


You must connect the following credentials:
Google Sheets (Service Account or OAuth)
Email SMTP (Gmail or custom)

2. Replace Default Values

Update the workflow with:
Your Google Sheet IDs
Your tab/sheet names
SMTP email, sender, and recipient
Optional: Custom domain or business branding

3. Customize Email Template


Modify subject, message body, or formatting style based on your reporting standards.

4. Adjust Trigger

You may choose:
Manual Trigger
Cron Trigger for daily/weekly reconciliation
Webhook Trigger integrated with your system

Detailed Process Flow

1. Fetch Local & Payment Gateway Transactions

The workflow reads all transaction records from the Local Database Sheet and Payment Gateway Sheet.

2. Compare both Transactions

Using the compare operation, the workflow splits result into
a. Valid Transactions
b. Invalid Transactions
c. Amount Differences Transactions
d. Missing Transactions

3. Duplicate Transaction Detection

The workflow scans local transactions to detect duplicate transaction and logs them seperately

5. Logging Transactions

Each category is appended to its respective Google Sheet:
DuplicateData
AmountDifference
DataNotInsert
Reconciliation Summary
RealData

6. Count all categories

The workflow counts:

Number of valid transactions
Number of invalid transactions
Number of missing transactions
Number of Amount mismatch transactions

The final summary row is appended to a dedicated Reconciliation Summary Sheet.

7. Send Final Email Report

The finance team receive a email of final summary report.

Final Output

At the end the workflow, you get:

Fully categorized reconciliation logs
Complete Summary Stored Sheets
Email Report
Clean audit ready data