auto-pay-reminder-system
Automate payment reminders with the Auto Pay Reminder System using Google Sheets, Apps Script, and Telegram Bot API. Ideal for tracking invoices and dues. 🛠️💻
AI Summary
Auto Pay Reminder System
An Apps Script that reads invoice data from Google Sheets, automatically sends payment reminders via Telegram, and generates WhatsApp links.
Target Users
Beginner-to-intermediate GAS users such as freelancers or small-business owners who track invoices in Google Sheets and want to automate payment reminders.
Problems Solved
Manually tracking due dates and sending payment reminders is time-consuming and error-prone.
Tags
Main Features
Automated Reminder Dispatch
The script checks approaching due dates and sends messages through the Telegram Bot API.
Personalized WhatsApp Links
Generates click-to-send WhatsApp links that embed customer names and amounts for direct payment communication.
No External Backend
All logic runs inside Google Sheets and Apps Script, eliminating the need for servers or extra infrastructure.
Usage Examples
Basic Reminder Sender
/**
* Find rows whose due date is within 3 days and send a reminder via Telegram.
*/
function sendPaymentReminders() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('payments');
const data = sheet.getDataRange().getValues();
const today = new Date();
const token = PropertiesService.getScriptProperties().getProperty('TELEGRAM_BOT_TOKEN');
const chatId = PropertiesService.getScriptProperties().getProperty('TELEGRAM_CHAT_ID');
// Skip header row (index 0)
data.slice(1).forEach(row => {
const [name, amount, dueDate, phone] = row;
if (!dueDate) return;
const diffDays = Math.ceil((dueDate - today) / (1000 * 60 * 60 * 24));
if (diffDays <= 3 && diffDays >= 0) {
const waUrl = `https://wa.me/${phone}?text=Hello%20${name}%2C%20this%20is%20a%20friendly%20reminder%20about%20your%20payment%20of%20${amount}.`;
const message = `Hi ${name}, your payment of ${amount} is due on ${Utilities.formatDate(dueDate, Session.getScriptTimeZone(), 'yyyy-MM-dd')}
Pay now: ${waUrl}`;
sendTelegramMessage(token, chatId, message);
}
});
}
/**
* Send a plain text message to Telegram
*/
function sendTelegramMessage(token, chatId, text) {
const url = `https://api.telegram.org/bot${token}/sendMessage`;
const payload = {
chat_id: chatId,
text,
disable_web_page_preview: true
};
const params = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(payload)
};
UrlFetchApp.fetch(url, params);
}Paste this script into the bound Apps Script project, store TELEGRAM_BOT_TOKEN and TELEGRAM_CHAT_ID in Script Properties, and reminders will be sent automatically three days before the due date.