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

1. Name it: ITR Documents Upload – FY 2024-25

Add the following Fields

1. Full Name (Short answer)
2. Email ID (Short answer)
3. PAN(Short answer)
4. Upload ITR Documents (File upload – allow multiple files, limit to PDFs, Excel, JPGs)
5. Link Form to a New Sheet
6. Go to Responses tab
7. Click the green Sheets icon (“Create Spreadsheet”)
8. Name it: Form Responses ITR 2024-25 (keep note of this sheet)
9. Copy Your Form ID
10. In your browser URL, find the long ID between /d/ and /edit
11. Example:
12. https://docs.google.com/forms/d/1AbCdEFghIJKlmnOPQRsTuVWxyz123456/edit
13. Form ID: 1AbCdEFghIJKlmnOPQRsTuVWxyz123456

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

1. Go to Google Drive
2. Create a folder named: ITR 2024-25 Uploads
3. Right-click → Get link (Viewer access is fine)
4. Copy the Folder ID (It’s the part after /folders/ in the URL)

📌 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:

1. As soon as a client submits the Google Form, a script runs in the background.
2. It checks if a folder already exists for the client; if not, it creates one.
3. It then moves the uploaded files from the form’s storage to the client’s specific folder.
4. The tracker sheet is updated with the submission date, folder link, and status.

Steps to Add Apps Script to Form Responses Sheet

1. Go to the Form Responses Sheet (created in Step 2 Point no 5)

1. Click Extensions → Apps Script

1. Delete any default code and paste the contents

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.");

}

}

1. Replace:
2. "PASTE_YOUR_MASTER_FOLDER_ID" with your actual folder ID
3. "PASTE_YOUR_FORM_ID" with your form ID

1. Save the project as ITR Automation

1. Click the clock icon 🕒 → Triggers
2. Add new Trigger:

→ 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:

1. The first script sends a personalized email invitation to every client with a link to the Google Form, requesting them to upload their documents.
2. The second script automatically sends reminder emails—every few days—only to clients who haven’t yet submitted their documents. Once a client submits, the reminders stop.

Steps to Add Email Scripts in the Client Tracker Sheet

1. Open the ITR 2024-25 Client Tracker Sheet
2. Click Extensions → Apps Script
3. Paste both:
4. sendInitialEmails.gs and sendReminderEmails.gs

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);

  }

 }

}

1. Replace PASTE_YOUR_GOOGLE_FORM_LINK with your live form link (not just the ID this time).
2. Click Save

🎯 You can now:

1. Manually run sendInitialEmails() once to send initial emails
2. Set a time-based trigger for sendReminderEmails() (like every 2 days)

Note: The above script is set for every 2 days

1. Click the clock icon 🕒 → Triggers
2. Add new Trigger:

→ 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:

1. Document collection is fast, secure, and organized.
2. Follow-up is automatic and hassle-free.
3. All files are stored in the right place, mapped to each client.
4. The system is scalable to handle hundreds of clients—no extra effort required.

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