Client Compliance Reminder Bot (Google Apps Script + Gmail + Twilio Record inserted or updated successfully.
AI & Audit

Client Compliance Reminder Bot (Google Apps Script + Gmail + Twilio

Author : CA. Tanwar Ratan Singh

Watch on Youtube

This solution automates compliance reminders for a CA practice using Google Sheets + Google Apps Script, delivering reminders via Email (Gmail) and WhatsApp (Twilio). It supports three operational modes:

  1. Manual Reminders – one-click dispatch for all eligible rows (Status pending/blank; Last Reminder On blank).
  2. Scheduled (D–5) – automatic reminders exactly 5 days before Due Date.
  3. Overdue Cadence – automatic reminders every 3 days after Due Date (bounded by a max overdue window).

The script updates Status and Last Reminder On for auditability and includes a test tool to validate WhatsApp.


Sheet Design

Create a Google Sheet with tab name Client Compliance Reminder Bot and headers in row 1:

A Client Name | B Email | C Mobile | D PAN/GSTIN | E Service | F Period | G Due Date | H Channels (Email | WhatsApp | Both) | I Status | J Last Reminder On

Tip: Keep Due Date as a proper date cell (or supported format like dd/mm/yyyy, mm/dd/yyyy, dd-MMM-yyyy).


One-Time Configuration

1) Apps Script binding

  1. Open the sheet → Extensions → Apps Script → paste the script (below) → Project Settings: set Time zone = Asia/Kolkata.

2) Twilio (WhatsApp)

  1. For demo: use Twilio WhatsApp Sandbox (Console → Messaging → Try it out → WhatsApp Sandbox).
  2. Note the sandbox number (e.g., +14155238886) and your join code.
  3. Each recipient must send the join code to the sandbox number once.
  4. For production: use a WhatsApp-approved sender in Twilio.

3) Store secrets in Script Properties

In Apps Script → Project Settings → Script properties:

  1. TWILIO_SID = your Account SID (e.g., AC…)
  2. TWILIO_AUTH = your Auth Token (or API Key/Secret)
  3. TWILIO_FROM = your WhatsApp sender (e.g., whatsapp:+14155238886 for Sandbox, or your approved number)

Using Script Properties keeps credentials out of source code and is appropriate for committee publication.


Operating Model

  1. Manual: Click Compliance Bot → Send Manual Reminders.
  2. Eligibility: Status blank or “pending” AND Last Reminder On blank.
  3. Scheduled (D–5): Trigger runs daily at 9:00 IST; sends when Due Date – Today = 5 days.
  4. Overdue: Trigger runs daily at 10:00 IST; sends every 3 days after Due Date up to a configured cap.
  5. Audit trail: On successful send, Status becomes “Reminder Sent – {stage}” and Last Reminder On records timestamp.


Complete Working Script (production-ready)

Paste this entire file into your Apps Script project.

/***************** CLIENT COMPLIANCE REMINDER BOT *****************

 Tab name: Client Compliance Reminder Bot

 Headers (row 1):

 A Client Name | B Email | C Mobile | D PAN/GSTIN | E Service | F Period

 G Due Date   | H Channels (Email | WhatsApp | Both) | I Status | J Last Reminder On

 Time zone: Asia/Kolkata

********************************************************************/


const CONFIG = {

 SHEET_NAME: 'Client Compliance Reminder Bot',

 TIMEZONE: 'Asia/Kolkata',


 ORG_NAME: 'Tanwar Ratan Singh & Associates',

 SENDER_NAME: 'CA Ratan Singh Tanwar',

 REPLY_TO: 'caratansinghtanwar@gmail.com',


DAYS_BEFORE_DUE_TO_SEND: 5,// scheduled reminder (D-5)

OVERDUE_REPEAT_EVERY_DAYS: 3,// overdue cadence

 MAX_OVERDUE_DAYS: 45,           // stop sending after this many days overdue


 TEST_MODE: false                // true = simulate only (no Email/WA); false = send live

};


// Column map (1-based)

const COL = {

 CLIENT: 1,    // A

 EMAIL: 2,     // B

 MOBILE: 3,    // C

 SERVICE: 5,   // E

 PERIOD: 6,    // F

 DUE: 7,       // G

 CHANNELS: 8,  // H: Email | WhatsApp | Both

 STATUS: 9,    // I

 LAST: 10      // J

};


/* ================= MENU ================= */

function onOpen() {

SpreadsheetApp.getUi()

.createMenu('Compliance Bot')

   .addItem('Send Manual Reminders (J blank)', 'sendManualReminders')

   .addItem('Run Scheduled Reminders (D-5)', 'runScheduledReminders')

   .addItem('Run Overdue Reminders (every 3 days)', 'runOverdueReminders')

   .addSeparator()

   .addItem('Send Test WhatsApp', 'sendTestWhatsApp')

   .addSeparator()

   .addItem('Create Daily Triggers', 'createDailyTriggers')

   .addItem('Remove All Triggers', 'removeAllTriggers')

   .addToUi();

}


/* ================= TRIGGERS ================= */

function createDailyTriggers() {

 removeAllTriggers();

ScriptApp.newTrigger('runScheduledReminders').timeBased().atHour(9).everyDays(1).create();

ScriptApp.newTrigger('runOverdueReminders').timeBased().atHour(10).everyDays(1).create();

SpreadsheetApp.getActive().toast('Daily triggers created: 9:00 (D-5), 10:00 (Overdue).');

}

function removeAllTriggers() {

ScriptApp.getProjectTriggers().forEach(t => ScriptApp.deleteTrigger(t));

SpreadsheetApp.getActive().toast('All triggers removed.');

}


/* ================= MANUAL REMINDERS ================= */

function sendManualReminders() {

 const sh = getSheet_();

 const values = sh.getDataRange().getValues();


 let sent = 0, skipped = 0;

 for (let r = 2; r <= values.length; r++) {

   const row = values[r - 1];


   const status = normalizeStatus_(row[COL.STATUS - 1]); // blank → pending

   const last  = row[COL.LAST - 1];

   const channel = String(row[COL.CHANNELS - 1] || 'Email').trim();

   const email = String(row[COL.EMAIL - 1] || '').trim();


   if (status !== 'pending') { skipped++; continue; }

   if (last) { skipped++; continue; }

   if (!email && !['WhatsApp','Both'].includes(channel)) { skipped++; continue; }


   if (sendForRow_(sh, r, row, 'Manual')) sent++; else skipped++;

 }


SpreadsheetApp.getActive().toast(`Manual: Sent ${sent}, Skipped ${skipped}`);

}


/* ================= SCHEDULED (D-5) ================= */

function runScheduledReminders() {

 const sh = getSheet_();

 const values = sh.getDataRange().getValues();

 const today = normalizeDate_(new Date());


 let sent = 0;

 for (let r = 2; r <= values.length; r++) {

   const row = values[r - 1];

   const status = normalizeStatus_(row[COL.STATUS - 1]);

   if (status !== 'pending') continue;


   const due = smartParseDate_(row[COL.DUE - 1]);

   if (!due) continue;


   const diffDays = daysBetween_(today, due); // due - today

   if (diffDays === CONFIG.DAYS_BEFORE_DUE_TO_SEND) {

     if (sendForRow_(sh, r, row, `D-${CONFIG.DAYS_BEFORE_DUE_TO_SEND}`)) sent++;

   }

 }

SpreadsheetApp.getActive().toast(`Scheduled (D-5): Sent ${sent}`);

}


/* ================= OVERDUE (every 3 days) ================= */

function runOverdueReminders() {

 const sh = getSheet_();

 const values = sh.getDataRange().getValues();

 const today = normalizeDate_(new Date());


 let sent = 0;

 for (let r = 2; r <= values.length; r++) {

   const row = values[r - 1];

   const status = normalizeStatus_(row[COL.STATUS - 1]);

   if (status !== 'pending') continue;


   const due = smartParseDate_(row[COL.DUE - 1]);

   if (!due) continue;


   const overdueDays = daysBetween_(due, today); // today - due

   if (overdueDays <= 0 || overdueDays > CONFIG.MAX_OVERDUE_DAYS) continue;


   const last = smartParseDate_(row[COL.LAST - 1]);

   const sinceLast = last ? daysBetween_(normalizeDate_(last), today) : null;


   if (sinceLast === null || sinceLast >= CONFIG.OVERDUE_REPEAT_EVERY_DAYS) {

     if (sendForRow_(sh, r, row, `D+${overdueDays}`)) sent++;

   }

 }

SpreadsheetApp.getActive().toast(`Overdue: Sent ${sent}`);

}


/* ================= SEND ONE ROW (Email / WhatsApp / Both) ================= */

function sendForRow_(sh, r, row, stageLabel) {

 try {

   const client = String(row[COL.CLIENT - 1] || '').trim();

   const email  = String(row[COL.EMAIL - 1]|| '').trim();

   const mobile = String(row[COL.MOBILE - 1] || '').trim();

   const service = String(row[COL.SERVICE - 1] || '').trim();

   const period = String(row[COL.PERIOD - 1] || '').trim();

   const due    = smartParseDate_(row[COL.DUE - 1]);

   const channel = String(row[COL.CHANNELS - 1] || 'Email').trim(); // Email | WhatsApp | Both


   if (!client) return false;

   const dueStr = due ? formatIST_(due, 'EEE, dd MMM yyyy') : '-';


   const subject = `[Reminder] ${service || 'Compliance'} for ${period || '-'} – Due ${dueStr}`;

   const textBody =

`Dear ${client},


This is a reminder for your ${service || 'compliance'} for ${period || '-'}.

Due Date: ${dueStr}


Kindly share the required documents/confirmations.

If already submitted, please ignore this message.


Regards,

${CONFIG.SENDER_NAME}

${CONFIG.ORG_NAME}`;


   // Email channel

   if (!CONFIG.TEST_MODE && (channel === 'Email' || channel === 'Both')) {

     if (email) {

GmailApp.sendEmail(email, subject, textBody, {

         name: CONFIG.SENDER_NAME,

         replyTo: CONFIG.REPLY_TO

       });

     }

   }


   // WhatsApp channel (Twilio)

   if (!CONFIG.TEST_MODE && (channel === 'WhatsApp' || channel === 'Both')) {

     const toWA = normalizeWhatsAppNumber_(mobile);

     if (!toWA) throw new Error('Invalid/blank mobile for WhatsApp');

sendWhatsApp_(toWA, textBody);

   }


   // Update audit columns upon success

   sh.getRange(r, COL.LAST).setValue(new Date());

   sh.getRange(r, COL.STATUS).setValue(`Reminder Sent – ${stageLabel}`);

   return true;


 } catch (err) {

   // Silent skip with log for operational robustness

   Logger.log(`Row ${r} send error: ${err}`);

   return false;

 }

}


/* ================= TEST TOOLING ================= */

function sendTestWhatsApp() {

 try {

   const sh = SpreadsheetApp.getActiveSheet();

   const sel = sh.getActiveRange();

   let rawMobile = '';

   if (sel && sel.getRow() > 1) {

     rawMobile = String(sh.getRange(sel.getRow(), COL.MOBILE).getValue() || '').trim();

   }

   if (!rawMobile) {

     const ui = SpreadsheetApp.getUi();

     const res = ui.prompt('Send Test WhatsApp',

       'Enter a mobile (India): 9876543210 / 919876543210 / +919876543210',

ui.ButtonSet.OK_CANCEL);

     if (res.getSelectedButton() !== ui.Button.OK) { ui.alert('Cancelled'); return; }

     rawMobile = (res.getResponseText() || '').trim();

     if (!rawMobile) { ui.alert('No number entered'); return; }

   }

   const toWA = normalizeWhatsAppNumber_(rawMobile);

   if (!toWA) throw new Error('Invalid mobile format for WhatsApp');


   const nowStr = Utilities.formatDate(new Date(), CONFIG.TIMEZONE, 'EEE, dd MMM yyyy HH:mm');

   const body = '[TEST] Compliance Bot – WhatsApp connectivity\nTime: ' + nowStr +

'\nThis is a test from ' + CONFIG.SENDER_NAME + '.';

   if (!CONFIG.TEST_MODE) sendWhatsApp_(toWA, body);

SpreadsheetApp.getActive().toast('Test WhatsApp sent to: ' + toWA);

 } catch (err) {

Logger.log('sendTestWhatsApp error: ' + err);

SpreadsheetApp.getActive().toast('Test WhatsApp failed. See Logs.');

 }

}


/* ================= HELPERS ================= */

function getSheet_() {

 const ss = SpreadsheetApp.getActive();

 const sh = ss.getSheetByName(CONFIG.SHEET_NAME);

 if (!sh) throw new Error('Sheet not found. Check CONFIG.SHEET_NAME.');

 return sh;

}

function normalizeStatus_(val) {

 const s = String(val || '').trim().toLowerCase();

 return (s === '' || s === 'pending') ? 'pending' : 'other';

}

// Robust date parser: Date object, Excel serials, dd/mm/yyyy, mm/dd/yyyy, dd-MMM-yyyy, ISO

function smartParseDate_(val) {

 if (!val) return null;

 if (val instanceof Date && !isNaN(val)) return val;

 if (typeof val === 'number') {

   const base = new Date(1899, 11, 30);

   const d = new Date(base.getTime() + val * 86400000);

   return isNaN(d) ? null : d;

 }

 const s = String(val).trim();


 let d = new Date(s);

 if (!isNaN(d)) return d;


 let m = s.match(/^(\d{1,2})[\/\-](\d{1,2})[\/\-](\d{4})$/);

 if (m) {

   // try dd/mm first

   let dd = parseInt(m[1],10), mm = parseInt(m[2],10)-1, yy = parseInt(m[3],10);

   d = new Date(yy, mm, dd); if (!isNaN(d)) return d;

   // fallback mm/dd

   dd = parseInt(m[2],10); mm = parseInt(m[1],10)-1; d = new Date(yy, mm, dd);

   if (!isNaN(d)) return d;

 }


 m = s.match(/^(\d{1,2})[\/\-\s]([A-Za-z]{3,})[\/\-\s](\d{4})$/);

 if (m) {

   const mon = m[2].substr(0,3).toLowerCase();

   const months = {jan:0,feb:1,mar:2,apr:3,may:4,jun:5,jul:6,aug:7,sep:8,oct:9,nov:10,dec:11};

   if (months.hasOwnProperty(mon)) {

     d = new Date(parseInt(m[3],10), months[mon], parseInt(m[1],10));

     if (!isNaN(d)) return d;

   }

 }

 return null;

}

function normalizeDate_(d) {

 const s = Utilities.formatDate(d, CONFIG.TIMEZONE, 'yyyy-MM-dd');

 return new Date(s + 'T00:00:00');

}

function daysBetween_(a, b) {

 return Math.round((normalizeDate_(b) - normalizeDate_(a)) / 86400000);

}

function formatIST_(date, pattern) {

 return Utilities.formatDate(date, CONFIG.TIMEZONE, pattern);

}


/* ================= WHATSAPP (Twilio) ================= */

function getTwilioProps_() {

 // Read from Script Properties for security

 const p = PropertiesService.getScriptProperties();

 const SID = p.getProperty('TWILIO_SID');

 const AUTH = p.getProperty('TWILIO_AUTH');

 const FROM = p.getProperty('TWILIO_FROM'); // e.g., 'whatsapp:+14155238886'

 if (!SID || !AUTH || !FROM) throw new Error('Twilio properties missing: set TWILIO_SID, TWILIO_AUTH, TWILIO_FROM.');

 return { SID, AUTH, FROM };

}

function normalizeWhatsAppNumber_(raw) {

 if (!raw) return '';

 const digits = String(raw).replace(/[^\d]/g, '');

 if (digits.length === 10) return 'whatsapp:+91' + digits;

 if (digits.length === 12 && digits.startsWith('91')) return 'whatsapp:+' + digits;

 if (digits.length === 13 && digits.startsWith('+91')) return 'whatsapp:' + digits;

 return '';

}

function sendWhatsApp_(toWhatsApp, body) {

 const { SID, AUTH, FROM } = getTwilioProps_();

 const url = 'https://api.twilio.com/2010-04-01/Accounts/' + SID + '/Messages.json';

 const payload = { To: toWhatsApp, From: FROM, Body: body };

 const options = {

   method: 'post',

muteHttpExceptions: true,

   payload,

   headers: { Authorization: 'Basic ' + Utilities.base64Encode(SID + ':' + AUTH) }

 };

 const res = UrlFetchApp.fetch(url, options);

 const code = res.getResponseCode();

 if (code < 200 || code >= 300) {

   // Common cause in sandbox: wrong FROM or recipient didn’t join sandbox

   Logger.log('Twilio error ' + code + ': ' + res.getContentText());

   throw new Error('Twilio WhatsApp send failed (HTTP ' + code + ').');

 }

}


Usage (concise)

  1. Populate the sheet with client rows (set Channels per client).
  2. Join sandbox from recipient phones (once).
  3. Run from menu:
  4. Send Test WhatsApp → validate channel
  5. Send Manual Reminders → updates Status & Last Reminder On
  6. Create Daily Triggers → enable D-5 and Overdue automation
  7. Monitor: Email “Sent” folder, WhatsApp inbox, and Sheet columns I/J.


Compliance & Security (publication note)

  1. Data minimization: message templates exclude sensitive identifiers.
  2. Secrets: stored in Script Properties (not code).
  3. Consent: for production, maintain WhatsApp opt-in records and use approved templates.
  4. Governance: align with DPDP Act and internal IS policies; enable MFA on Twilio; restrict geo-permissions; set usage alerts.



Recent Posts

© Copyrights 2025 ICAI. All rights reserved.