I have made a questionnaire using Google Forms. I have a set of emails (e.g. [email protected], [email protected], ...) as targets to send the questionnaire to. I receive the respondents' email address if they submit their responses. The responses can be saved in a google spreadsheet. I would like to send a reminder email only to those who do not respond until a day after they received their email.

I am using the following code in the script.google.com that can send the email and works with a customized trigger in the G Suit Developer Hub. But it just sends a reminder email to all the emails listed in the following code. How can I add a condition to it that satisfies what I described?

function sendFormEmail() {
var emailAddress = "[email protected], [email protected]";
var htmlMessage = HtmlService.createHtmlOutputFromFile("Reminder_email.html").getContent();
var subject = "Participation reminder";
var message = "Hi, please be reminded to submit your response";
MailApp.sendEmail(emailAddress, subject, message, {
    htmlBody: htmlMessage
});
}

1 Answers

0
RZM On Best Solutions

I managed to do it using two spreadsheets, one including the email of all participants, the other for the participants who have participated (extracted from the automatic spreadsheet made by Google Form when someone submits a response)

The function to send the reminder email is as follows. It can be triggered automatically using Google triggers embedded in the Google scripts. Here is the code for the function I wrote.

function sendRemEmail() 

// Load all the emails from the spreadsheet including all emails in the first column
{
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/...').getActiveSheet();
  var All_lr = ss.getLastRow();
  var Alladdresses = [];
  for (var i = 2; i<=All_lr;i++){
    var emailAddress = ss.getRange(i,1).getValue();
    Alladdresses.push(emailAddress);
  }

  // Load emails from the spreadsheet linked to the questionnaire
  var ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/...').getActiveSheet();
  var sub_lr = ss.getLastRow();
  var All_submitted_emails = [];
  for (var i = 2; i<=sub_lr;i++){
    var emailAddress = ss.getRange(i,2).getValue();
    All_submitted_emails.push(emailAddress)
  }
  var rem_email_list = [];
  for (var i = 0; i<=All_lr-1;i++){
    if (All_submitted_emails.indexOf(Alladdresses[i])===-1){
      rem_email_list.push(Alladdresses[i]);
    }
  }
  var rem_email_list = rem_email_list.filter(function (el) {
  return el != null;
});
  Logger.log(rem_email_list);
  var htmlMessage = HtmlService.createHtmlOutputFromFile("Reminder_email.html").getContent();
  var subject = "Reminder";
  var message = "Hi, please be reminded to submit your response";
  for (var i = 0; i<rem_email_list.length;i++){
    var emailAddress = rem_email_list[i];
    MailApp.sendEmail(emailAddress, subject, message, {htmlBody: htmlMessage, from:'[email protected]', name: 'X Y',replyTo:'[email protected]le.com'});
  }
}

I have used a customized email (htmlMessage) written in HTML which is in the same project.