Automating Document Collection for ITR Filing (FY 2024–25)
Author : CA Rahul Batra
A Step-by-Step System Using Google Forms, Sheets, Drive & Apps Script
STEP 1: Setting Up the Client Tracker
We started by creating a master Google Sheet called the “Client tracker Sheet.” Here, we list all client names, email IDs, PAN numbers, submission status, folder links, and important notes. This sheet acts as the central dashboard for tracking every client’s document collection status.
Rename the sheet as “ITR 2024-25 Client Tracker”
Step 2: Designing the Google Form
Next, we created a Google Form where clients can securely upload their documents. The form collects the client’s name, email, PAN, and allows them to upload multiple files—like Form 16, bank statements, and investment proofs. Each submission is linked to a response sheet for easy tracking.
Create a new Google Form with these fields: Here’s how to do it:
Set the Form Title
Add the following Fields
STEP 3: Organizing Google Drive
We set up a master folder in Google Drive named “ITR 2024-25 Uploads.” For each client who submits the form, the system automatically creates a unique folder inside this master folder, using the client’s name and PAN. All uploaded documents are stored in that client’s dedicated folder, keeping everything neatly organized and accessible.
Steps to create the Master Folder in Google Drive
📌 Example:
https://drive.google.com/drive/folders/1xYzZabc123456PQRS
→ Folder ID = 1xYzZabc123456PQRS
STEP 4: Automating Folder Creation and File Management
We used Google Apps Script to automate the entire process:
Steps to Add Apps Script to Form Responses Sheet
function onFormSubmit(e) {
var MASTER_FOLDER_ID = "___________________________________________";
var TRACKER_SHEET_ID = "_____________________________________________";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
var row = e.range.getRow();
var data = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues()[0];
var timestamp = data[0]; // Timestamp
var name = data[2]; // Full Name (Column 3)
var email = data[3]; // Email (Column 4)
var pan = data[4]; // PAN (Column 5)
var fileUrls = data[5]; // File URLs (Column 6)
var folderName = "ITR_2024-25_" + name + "_" + pan;
var parentFolder = DriveApp.getFolderById(MASTER_FOLDER_ID);
// ✅ Create or get client folder
var folders = parentFolder.getFoldersByName(folderName);
var clientFolder = folders.hasNext() ? folders.next() : parentFolder.createFolder(folderName);
// ✅ Move uploaded files to the client folder
if (fileUrls) {
var urls = fileUrls.split(", ");
for (var i = 0; i < urls.length; i++) {
var fileId = urls[i].match(/[-\w]{25,}/);
if (fileId && fileId[0]) {
var file = DriveApp.getFileById(fileId[0]);
file.moveTo(clientFolder);
}
}
}
// ✅ Update the Tracker (PAN only matching – case-insensitive)
var tracker = SpreadsheetApp.openById(TRACKER_SHEET_ID).getSheetByName("ITR 2024-25 Client Tracker");
var clients = tracker.getRange(2, 1, tracker.getLastRow() - 1, 3).getValues(); // A:C = Name, Email, PAN
Logger.log("Submitted PAN: " + pan);
var matchFound = false;
for (var j = 0; j < clients.length; j++) {
var trackerPan = clients[j][2] ? clients[j][2].toString().trim().toUpperCase() : "";
if (trackerPan === pan.trim().toUpperCase()) {
tracker.getRange(j + 2, 4).setValue("Submitted"); // Column D: Status
tracker.getRange(j + 2, 5).setValue(clientFolder.getUrl()); // Column E: Folder Link
tracker.getRange(j + 2, 6).setValue(timestamp); // Column F: Date
Logger.log("✔️ Match found with PAN – tracker updated at row " + (j + 2));
matchFound = true;
break;
}
}
if (!matchFound) {
Logger.log("❌ No match found for PAN in tracker.");
}
}
→ Function: onFormSubmit → Event: From form/Spreadsheet → On form submit
STEP 5: Automating Client Communication
To minimize manual follow-ups, we added two more scripts in the Client Tracker:
Steps to Add Email Scripts in the Client Tracker Sheet
Copy from here
function sendInitialEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ITR 2024-25 Client Tracker");
var data = sheet.getDataRange().getValues();
var formUrl = "___________________________________________________________________"; // 🔁 Replace this with your actual Form link
for (var i = 1; i < data.length; i++) {
var name = data[i][0];
var email = data[i][1];
var status = data[i][3];
if (status !== "Submitted" && email) {
var subject = "📄 Document Upload Link – ITR Filing for FY 2024-25";
var body = "Dear " + name + ",\n\n" +
"We are in the process of filing the income tax return for FY 2024-25. We request you to please upload your documents for ITR Filing for FY 2024-25 using the link below:\n\n" +
"📥 Google Form: " + formUrl + "\n\n" +
"Let us know in case you need any help.\n\n" +
"Regards,\nCA Rahul Batra\nRahul Batra & Co.";
MailApp.sendEmail(email, subject, body);
}
}
}
function sendReminderEmails() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ITR 2024-25 Client Tracker");
var data = sheet.getDataRange().getValues();
var formUrl = "____________________________________________________________________"; // 🔁 Replace with your actual form link
// Run only on every 2nd day since starting date
var today = new Date();
var startDate = new Date("2024-04-24"); // 🔁 Set this to your campaign start date
var diffDays = Math.floor((today - startDate) / (1000 * 60 * 60 * 24));
if (diffDays % 2 !== 0) return; // Only send reminders every 2nd day
for (var i = 1; i < data.length; i++) {
var name = data[i][0];
var email = data[i][1];
var status = data[i][3];
if (status !== "Submitted" && email) {
var subject = "⏳ Reminder: Please Upload ITR Documents – FY 2024-25";
var body = "Dear " + name + ",\n\n" +
"This is a gentle reminder to upload your documents for ITR Filing:\n\n" +
"📥 Google Form: " + formUrl + "\n\n" +
"If you have already submitted, kindly ignore this.\n\n" +
"Thanks,\nCA Replace Your Name\nYour Firm Name.";
MailApp.sendEmail(email, subject, body);
}
}
}
🎯 You can now:
Note: The above script is set for every 2 days
→ Function: sendReminderEmails → Event: Time Driven → Day timer
STEP 6: Real-Time Tracking and Ease of Use
All client submissions, folder links, and statuses are updated in real time in the tracker sheet.
This gives our team complete visibility and control, helping us stay on top of deadlines, improve compliance, and provide a smoother experience for both our clients and our staff.
In summary, with this automated workflow:
Note: You can use Chat Gpt and Claude or any other Generative AI Tool to create App Script according to your set up and needs
https://www.youtube.com/watch?v=WILS3hozzqA