How We Automated Customer Email Tracking at Saaragya (A Tiny Learning We Thought We'd Share!)

Hey everyone!

Running a small business is amazing, but let’s be real—it comes with its fair share of chaos. One of the things we struggled with at Saaragya was keeping track of customer emails. Between order queries, size exchanges, and the occasional "Where’s my order?" panic email, we needed a better way to stay on top of things without losing our sanity.

So, we built a super simple email ticketing system using Google Apps Script & Google Sheets—and honestly, it’s made life so much easier! We thought of sharing this in case it helps someone out there. Or you may use this system for an entirely different use case. 

Why We Built This System

We were spending way too much time manually tracking emails, responding to customers, and making sure no messages got lost. A proper ticketing system felt too expensive (plus, we love DIY solutions), so we thought—why not automate it ourselves?

The goal was simple:

  • Log customer emails in a Google Sheet
  • Assign a Ticket ID so we never lose track
  • Auto-reply with a confirmation email so customers know we’ve got their back 
  • Save time & keep our inbox stress-free!

How We Did It

Step 1: Set Up a Google Sheet

  1. Open Google Sheets.
  2. Create a new sheet and name it "Customer Support Tracker".
  3. Add these column headers in Row 1:
    • Ticket ID
    • Timestamp
    • Customer Email
    • Subject
    • Status (Open/In Progress/Closed)

Step 2: Add a "Last Checked" Timestamp

  1. Create a second sheet and name it "Last Checked".
  2. In cell A1, type: Last Checked Timestamp.
  3. In cell A2, enter: 01/01/2000 (just to start fresh).

Step 3: Automate the Process with Google Apps Script

  1. Open your Google Sheet.
  2. Click Extensions → Apps Script.
  3. Delete everything and paste this code:
function trackCustomerEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Customer Support Tracker");
  var lastCheckedSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Last Checked");
  
  var lastCheckedTime = new Date(lastCheckedSheet.getRange(2, 1).getValue());
  var threads = GmailApp.search("to:email@email.com is:unread after:" + Math.floor(lastCheckedTime.getTime() / 1000));
  var messages = GmailApp.getMessagesForThreads(threads);
  
  messages.forEach(thread => {
    thread.forEach(message => {
      var email = message.getFrom();
      var subject = message.getSubject();
      var timestamp = message.getDate();
      
      if (timestamp > lastCheckedTime) {
        var ticketID = "PREFIX-" + new Date().getTime();
        sheet.appendRow([ticketID, timestamp, email, subject, "Open"]);
        sendAcknowledgmentEmail(email, ticketID);
      }
    });
  });
  
  lastCheckedSheet.getRange(2, 1).setValue(new Date());
}

function sendAcknowledgmentEmail(email, ticketID) {
  var subject = "[COMPANY/BRAND] Support Ticket Acknowledgment - " + ticketID;
  var body = "Hey there! \n\nWe’ve received your message and assigned you Ticket ID: " + ticketID + ".\n\nOur team will get back to you within 24 hours (except holidays). Thanks for your patience! 🙌\n\nBest,\nSaaragya Support Team";
  MailApp.sendEmail(email, subject, body);
}

function setEmailTrigger() {
  ScriptApp.newTrigger("trackCustomerEmails")
    .timeBased()
    .everyMinutes(5)
    .create();
}

Step 4: Enable Script Permissions

  1. Click Save.
  2. Click Run → setEmailTrigger() (this sets up an automatic check every 5 minutes).
  3. Grant permissions when prompted.

What This Does

✔️ Every 5 minutes, it checks for new emails.
✔️ If a new email comes in, it logs it in the Google Sheet.
✔️ A Ticket ID is created, and the customer gets an auto-reply.
✔️ We now have zero inbox stress and no lost queries. 

What’s Next?

We’re thinking of adding:

  • WhatsApp notifications
  • Auto-closing tickets after resolution
  • A simple dashboard to track customer queries 

We’re just sharing this in case it helps other small business owners or teams who want to automate without spending on expensive tools. If you try this out, let us know how it works for you!

Back to blog

Leave a comment