Email script and sequence script not working

[Eric Morandeau]

Hi!

I have this script that i'm going to use to send email to a list of clients that I haven't been able to get hold of over the phone.

Here is how it is supposed to work:

The script is time triggered and is sending a email sequence as you can see down below.

First email sent ---> if no response ----> send second email -----> if no response -----> send third email -----> if no response then stop

If they reply to the first or the second one then stop sending email.

Everything works except the last part and that is when someone replies. When someone replies it is supposed to record it and then stop the script.

I'm not a expert that it this type of script so all help and tips is highly appreciated.

Here is the spreadsheet: https://docs.google.com/spreadsheets/d/1gRjF4QjLunF5jRE1qGj2hTSlIwyvPsI3h78bacJBDHI/edit?usp=sharing

Here is the script: 

function sendEmails() {

// Replace the sheet ID and email column with your own

var sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1gRjF4QjLunF5jRE1qGj2hTSlIwyvPsI3h78bacJBDHI/edit#gid=0').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

}

}

}

}

[Chuck Grieshaber]

What is not working? Can you attach the error you are getting?

[Eric Morandeau]

Hi!

The script is supposed to add a timestamp to col c when someone response to stop the script from emailing that person.

I tried it out and emailed myself and replied but it doesn't work, there is no timestamp added so I would appreciate some help to see why that is.

All the other stuff is working fine.

--
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/lLBgvbC1o4k/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/cbbfb8d9-8374-4fd1-9b1a-7721dd60ee9dn%40googlegroups.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.

[Chuck Grieshaber]

Are you sure you have the trigger setup correctly?

[Eric Morandeau]

Hi Chuck!

That is a great question and I realized that it might not been setup correctly.

The first script works well when it comes to sending out email's in a sequence but it doesn't do a search in my inbox to look for threads that could trigger the script to record a response.

So I'm looking at this script to run and search for replies but I can't get it to work. I'm stuck at the loop part and if statement

Script down below:

function trackEmailResponses() {

// Replace the sheet ID and email columns with your own

var sheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1kkhRBGiW4SjUKC9AuKLYgxFtQWFYI_IylHJnoxXKM24/edit#gid=0').getSheetByName('Sheet1');

var emailColumn = sheet.getRange('A2:A').getValues().flat();

var sentColumn = sheet.getRange('B2:B').getValues().flat();

var responseColumn = sheet.getRange('C2:C').getValues().flat();

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

Logger.log(threads)

if (email && sent && !response)

var threads = GmailApp.search('from:'+ email );

var messages = threads[0].getMessages();

if (threads.length > 0) {

for (var j = 0; j < messages.length; j++) {

{

sheet.getRange(i + 1, responseColumn).setValue(new Date()); // Update response column with current date/time

break;

}

}

}

}

}