Send rent reminders and weekly AI summaries with OpenAI, Sheets, Gmail and Telegram

Description

Quick overview
This workflow reads a tenant rent ledger from Google Sheets, uses OpenAI to draft overdue rent reminder emails, sends them via Gmail, and escalates firm/final cases to Telegram, while also producing a weekly AI-written collections summary sent by Gmail and Telegram.

How it works
Runs every day at 08:00 (Asia/Jakarta) and loads tenant rows from the TenantLedger tab in Google Sheets.
Validates that each tenant row contains tenant name, email, due day, and rent amount, and sends a Telegram alert if any required field is missing.
Normalizes tenant data, calculates the due date for the current month, and computes how many days the rent is overdue.
For tenants who are unpaid and due today or overdue, uses OpenAI to generate a short reminder email (gentle/firm/final) in JSON format.
Sends the drafted reminder via Gmail, appends a reminder entry to the ReminderLog tab in Google Sheets, and notifies the landlord in Telegram when the stage is firm or final.
Runs every Monday at 09:00, summarizes the current ledger in Google Sheets, uses OpenAI to write a weekly rent report, and sends it by Gmail and Telegram.

Setup
Connect Google Sheets OAuth2 credentials and update the spreadsheet ID and sheet names (TenantLedger and ReminderLog) to match your ledger file.
Connect a Gmail OAuth2 account and set the recipient/formatting for reminder emails and the weekly summary email.
Add an OpenAI API credential for the two OpenAI (gpt-4o-mini) steps that generate reminders and the weekly report.
Add a Telegram bot credential and replace the chat ID with your landlord/admin chat for alerts and weekly summaries.
Ensure TenantLedger includes columns for tenant_name, email, due_day, rent_amount, unit, and optionally status (paid/unpaid) and reminder_stage.

Requirements
A source system that triggers the workflow (form, sheet, email, or schedule)
Access to the target storage and messaging services
API key for the AI provider
Bot tokens or account credentials for notification channels

Customization
Change validation rules or decision thresholds in IF nodes
Edit the AI prompt to fit your domain and tone
Swap notification channels (email, Slack, Discord, Telegram)
Add or remove fields in the log destination

Additional info
Tested on n8n self-hosted and compatible with n8n cloud. Uses only built-in n8n nodes.
Created by: Khmuhtadin
Category: Finance Ops
Need custom workflows? Contact us.
Connect with the creator: Portfolio - Workflows - LinkedIn - Medium - Threads