Track email replies to stop automated emails

[Eric Morandeau]

Hi!

I'm planing to use Google sheet script to send out emails that is working as intended but I'm also exploring if its possible to automate it and if its possible to track a email response so I don't spam the ones that gets emailed.

Is there a solution for this?

Looking forward for some answers.

// Eric

[Jonathan Butler]

Personally, if the emails you sent all have the same subject and were sent on a certain date I would use GmailApp filters to get the emails you have already sent. From there you can get the Gmail Thread ID for all of the emails that come back in the query. Optionally, you can apply a custom label to the emails so you can search by that next time or store the threads Id's in a google sheet to keep track of and search for next time.

Now that you have either the threads id's or the label applied to those emails I would create a function that checks the thread for additional messages. If the message array's length is greater than 1, they have not replied. If it is, they have replied and you can mark it in your sheet. Then you can set a trigger so that function runs once a day or every few hours keeping the sheet up to date.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a2b88161-395e-4620-b6f3-683befcd40dbn%40googlegroups.com.

[Eric Morandeau]

Hi Jonathan!

Thank you for replying.

I have read about that before and it sounds like that could be a solution. What I'm looking for is also to automate it, so when someone replies the script stops automatically to send email?

What I find interesting in your reply is If the message array's length is greater than 1, they have not replied. If it is, they have replied.  

So basically you could create a IF statement in a script to break if it is greater than 1?

[DME]

Yes, it is possible to automate sending emails from Google Sheets using Apps Script, and to track email responses as well. Here are some suggestions on how to achieve both:

Automating sending emails:
You can use Apps Script's built-in MailApp service to send emails from a Google Sheet. Here is an example script that sends an email to each row in a sheet:

function sendEmails() {
  // Replace the sheet ID and email column with your own
  var sheet = SpreadsheetApp.openById('YOUR_SHEET_ID_HERE').getSheetByName('Sheet1');
  var emails = sheet.getRange('A2:A').getValues().flat();
 
  // Replace the email subject and body with your own
  var subject = 'Your Subject Here';
  var body = 'Your Email Body Here';
 
  for (var i = 0; i < emails.length; i++) {
    if (emails[i]) { // Make sure the cell is not empty
      MailApp.sendEmail(emails[i], subject, body);
      Utilities.sleep(1000); // Wait for 1 second to avoid Gmail's sending limits
    }
  }
}

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

This script will send an email to each email address in the A column of Sheet1 in the specified Google Sheet. You can modify the email subject and body to suit your needs.

Tracking email responses:
To track email responses, you can add a column to your sheet to mark when an email has been sent, and another column to record when a response is received. Here is an example script that updates the sent and response columns accordingly:

function trackEmailResponses() {
  // Replace the sheet ID and email columns with your own
  var sheet = SpreadsheetApp.openById('YOUR_SHEET_ID_HERE').getSheetByName('Sheet1');
  var emailColumn = 1; // A column
  var sentColumn = 2; // B column
  var responseColumn = 3; // C column
 
  // Replace the email subject and body with your own
  var subject = 'Your Subject Here';
  var body = 'Your Email Body Here';
 
  var data = sheet.getDataRange().getValues();
 
  for (var i = 1; i < data.length; i++) { // Start at row 2 (i.e. index 1)
    var email = data[i][emailColumn - 1]; // -1 to convert to 0-index
    var sent = data[i][sentColumn - 1];
    var response = data[i][responseColumn - 1];
   
    if (email && !sent) { // Make sure email is not empty and email hasn't been sent yet
      MailApp.sendEmail(email, subject, body);
      sheet.getRange(i + 1, sentColumn).setValue(new Date()); // Update sent column with current date/time
      Utilities.sleep(1000); // Wait for 1 second to avoid Gmail's sending limits
    }
   
    if (email && sent && !response) { // Make sure email is not empty, email has been sent, and response hasn't been recorded yet
      var threads = GmailApp.search('to:' + email + ' subject:' + subject + ' after:' + sent.getTime());
      if (threads.length > 0) {
        var messages = threads[0].getMessages();
        for (var j = 0; j < messages.length; j++) {
          if (messages[j].getDate().getTime() > sent.getTime()) {
            sheet.getRange(i + 1, responseColumn).setValue(new Date()); // Update response column with current date/time
            break;
          }
        }
     

--

[Eric Morandeau]

Hi!

This is great! 

Is it also possible to stop sending to the ones that has replied?

The solution I'm looking for is to make a sequence of 3 emails and the process looks like this.

First email -----> Second email sent if the first is not replied -------> Third email sent if the second is not replied ------> Stop send or stop sending if any of the emails gets replied

So if you can send emails and also track the replies this should be possible using IF arguments in the script right?

[DME]

Yes, it is possible to modify the script to stop sending emails to those who have already replied. You can add an additional check in the script to skip sending emails to those who have already responded.

Here's an updated version of the script that implements the logic you described:

function sendEmails() {
  // Replace the sheet ID and email column with your own
  var sheet = SpreadsheetApp.openById('YOUR_SHEET_ID_HERE').getSheetByName('Sheet1');
  var emails = sheet.getRange('A2:A').getValues().flat();

  var sentDates = sheet.getRange('B2:B').getValues().flat();
  var responseDates = sheet.getRange('C2:C').getValues().flat();
 
  // Replace the email subjects and bodies with your own
  var subject1 = 'First Email Subject';
  var body1 = 'First Email Body';
  var subject2 = 'Second Email Subject';
  var body2 = 'Second Email Body';
  var subject3 = 'Third Email Subject';
  var body3 = 'Third Email Body';

 
  for (var i = 0; i < emails.length; i++) {
    if (emails[i]) { // Make sure the cell is not empty

      if (responseDates[i]) {
        continue; // Skip if response already recorded
      }
      if (!sentDates[i]) {
        // Send first email
        MailApp.sendEmail(emails[i], subject1, body1);
        sheet.getRange(i + 2, 2).setValue(new Date()); // Record sent date/time in column B

        Utilities.sleep(1000); // Wait for 1 second to avoid Gmail's sending limits

      } else if (!responseDates[i] && new Date() - sentDates[i] > 3 * 24 * 60 * 60 * 1000) {
        // Send third email if no response after 3 days
        MailApp.sendEmail(emails[i], subject3, body3);
        sheet.getRange(i + 2, 3).setValue(new Date()); // Record response date/time in column C

        Utilities.sleep(1000); // Wait for 1 second to avoid Gmail's sending limits

      } else if (!responseDates[i] && new Date() - sentDates[i] > 24 * 60 * 60 * 1000) {
        // Send second email if no response after 1 day
        MailApp.sendEmail(emails[i], subject2, body2);
        sheet.getRange(i + 2, 2).setValue(new Date()); // Record sent date/time in column B

        Utilities.sleep(1000); // Wait for 1 second to avoid Gmail's sending limits
      }
    }
  }
}

This script checks if a response has already been recorded for an email address, and skips sending emails to that address if a response has been recorded. If no response has been recorded, it checks the date of the last email sent to that address and sends the appropriate email based on how much time has passed since the last email was sent. The script also records the date and time when an email is sent or a response is received in the corresponding columns of the sheet.

Note that this script assumes that the sheet has columns A, B, and C for email addresses, sent dates, and response dates, respectively. You will need to replace "YOUR_SHEET_ID_HERE" with the ID of your sheet, and customize the email subjects and bodies as needed.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/aa0d604f-6618-436d-896f-bfe68f3a1d9bn%40googlegroups.com.

[Eric Morandeau]

Thank you Forex Trader! This is EXACTLY what was I was looking for.

Can't wait to implement this in my sheet later.

I let you know if I do something wrong, again thank you

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/koDcKqFY2jI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CADK4hKkrvaad8LoaDZUut7vOGsE0qectFh1K3vmhOc%3Du6V0Q-g%40mail.gmail.com.

--

 

Eric Morandeau
Account Executive

Mob nr: 070-781 92 34

E-mail: eric.mo...@mynewsdesk.com

Rosenlundsgatan 40
118 53 Stockholm
Sverige

DIGITAL COMMUNICATIONS & PR MADE EASY
Simplify your workflow, expand your influence and get results with one complete communications solution.
Monitor what’s being said. Create and publish stories. Engage with your audience. Analyze results.

www.mynewsdesk.com

Your personal data will be processed in accordance with GDPR (EU 2016/679). For more information, please read our Privacy Policy.

[Eric Morandeau]

Hi!

I tried the script and the first part works great. It sends the email accordingly to the list but when someone replies it doesn't update Col C when it got replied.

Maybe I missed something here, I only implemented the last script that you wrote and I can see the variable Responsedate is declared but no instructions to set a date in that Col if a response has been recorded. 

You wrote a function for that in the second script that you wrote but I only used the last one since you said that you updated it so I guess I'm missing something here.

Any ideas?

[Eric Morandeau]

I'm thinking that this part is missing in the last script to set the timestamp in Col C but again I'm not the expert here.

if (email && sent && !response) { // Make sure email is not empty, email has been sent, and response hasn't been recorded yet
      var threads = GmailApp.search('to:' + email + ' subject:' + subject + ' after:' + sent.getTime());
      if (threads.length > 0) {
        var messages = threads[0].getMessages();
        for (var j = 0; j < messages.length; j++) {
          if (messages[j].getDate().getTime() > sent.getTime()) {
            sheet.getRange(i + 1, responseColumn).setValue(new Date()); // Update response column with current date/time
            break;

[Joe Guzman]

@ Eric Morandeau Did you figure this out? I am trying the same thing myself.

[Fabrice Faucheux]

Hello

I found the request very interesting; I've been working this afternoon on a complete answer (I hope) that may help you.

https://atelier-informatique.com/2025/automatisez-vos-relances-demails-avec-google-apps-script-et-sheets/