Triggered email script in Google sheets

0
votes

I'm looking for help with a script that will send an email when a condition is met in a cell.

The example sheet is linked below. The survey responses dump into tab "Form Responses 1," and the data for the email that I want to send is in tab "Owners."

When Column I says "Yes" I want Column K sent in the body of an email to the email address in Column J.

Sheet example here

1
google-sheets
Here's an example I did recently. – Cooper

1 Answers

0
votes

Sending Email Based Upon Cell Values

Using Timed Based Triggers

This function insures that all necessary data is properly entered prior to authorization sending each email. In particular it also checks to see that DateSent is blank and that SendIfYes is 'Yes'. Inaddition it is setup to provide log entries every ten minutes and to check for and set Emails that have the correct data and also have DateSent='' and SendIfYes='Yes'.

In order to get this to work for you, first you must create and timebased trigger for it. I tested it with a 10 minute trigger and it worked fine. You may setup these triggers in your Script Editor Edit/Current Project Triggers menu or you may simply run the setupEmailTrigger from the provided 'My Tools' menu. If you wish to terminate the timebased triggering you may do so by utilizing the delEmailTrigger command in the 'My Tools' menu. You may also run the script manually from the 'My Tools' menu.

You will have to fill in the DataFolderID and DefaultFileName in the saveFile function.

SendingEmail.gs:

function sendingMyEmails() 
{
    var ss=SpreadsheetApp.getActiveSpreadsheet();
    var sht=ss.getSheetByName('EmailSetup');
    var rng=sht.getDataRange();
    var rngA=rng.getValues();
    var s='\n' + 'Checking for Emails that need to be sent';
    for(var i=2;i<rngA.length;i++)
    {
      var dataA={};
      for(var j=0;j<rngA[1].length;j++)
      {
        dataA[rngA[1][j]]=rngA[i][j];
      }
      if(dataA.EntryDate && dataA.Name && dataA.Email && dataA.Subject && dataA.Body && dataA.SendIfYes=='Yes' && dataA.DateSent=='' && dataA.SendAs)
      {
        //MailApp.sendEmail(dataA.Email, dataA.Subject, dataA.Body, {replyTo:dataA.Sender});
        GmailApp.sendEmail(dataA.Email, dataA.Subject, dataA.Body, {replyTo: dataA.Sender,from: dataA.SendAs});
        s+='\n' + 'Email Sent: Row: ' + Number(i+1) + ' Name: ' + dataA.Name + ' Email: ' + dataA.Email + ' Subject: ' + dataA.Subject + ' EntryDate: ' + dataA.EntryDate;
        sht.getRange(i+1,8).setValue(Utilities.formatDate(new Date(), 'GMT-6', "M/dd/yyyy'\n'HH:mm:ss"));
        SpreadsheetApp.flush();
      }
      else
      {
        var row=Number(i+1);
        s+='\n' + 'Email Not Sent: Row: ' + Number(i+1) + ' Name: ' + dataA.Name + ' Email: ' + dataA.Email + ' Subject: ' + dataA.Subject + ' EntryDate: ' + dataA.EntryDate + ' SendIfYes: ' + dataA.SendIfYes + ' Date Sent: ' + dataA.DateSent;
      }
    }
    s+='\n';
    logEntry(s);
}

function setupEmailTrigger()
{
  nMinuteTrigger('sendingMyEmails');
  logEntry('\n10 Minute trigger setup for sendingMyEmails.');
}

function delEmailTrigger()
{
  delTriggers('sendingMyEmails');
  logEntry('\nDeleted All Project Triggers for sendingMyEmails.');
}

Code.gs:

function onOpen()
{
  SpreadsheetApp.getUi().createMenu('My Tools')
      .addItem('sendingMyEmails','sendingMyEmails')
      .addItem('setupEmailTrigger','setupEmailTrigger')
      .addItem('delEmailTrigger','delEmailTrigger')
      .addToUi();      
}

function nDayTrigger(funcName,n) 
{
  var n = (typeof(n) != 'undefined')?n:1;
  ScriptApp.newTrigger(funcName).timeBased().everyDays(n).create();
}

function nHourTrigger(funcName,n)
{  
  var n = (typeof(n) != 'undefined')?n:1;
  ScriptApp.newTrigger(funcName).timeBased().everyHours(n).create();
}

function nMinuteTrigger(funcName,n)
{
  var n = (typeof(n)!='undefined')?n:10;
  ScriptApp.newTrigger(funcName).timeBased().everyMinutes(n).create();
}

function delTriggers(funcName)
{
  if(funcName)
  {
    var triggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<triggers.length;i++)
    {
      if(triggers[i].getHandlerFunction()==funcName)
      {
        ScriptApp.deleteTrigger(triggers[i]);
      }
    }
  }
  else
  {
    SpreadsheetApp.getUi().alert('Invalid Parameter', 'funcName is undefined or invalid in function delTriggers in Code.gs', SpreadsheetApp.getUi().ButtonSet.OK);
  }
}

function logEntry(entry,file)
{
  var file = (typeof(file) != 'undefined')?file:'eventlog.txt';
  var entry = (typeof(entry) != 'undefined')?entry:'No entry string provided.';
  if(entry)
  {
    var ts = Utilities.formatDate(new Date(), "GMT-6", "yyyy-MM-dd' 'hh:mm:ss a");
    var s = ts + ' - ' + entry + '\n';
    saveFile(s, file, true);
  }
}

function saveFile(datstr,filename,append)
{
  var append = (typeof(append) !== 'undefined')? append : false;
  var filename = (typeof(filename) !== 'undefined')? filename : DefaultFileName;
  var datstr = (typeof(datstr) !== 'undefined')? datstr : '';
  var folderID = (typeof(folderID) !== 'undefined')? folderID : DataFolderID;
  var fldr = DriveApp.getFolderById(folderID);
  var file = fldr.getFilesByName(filename);
  var targetFound = false;
  while(file.hasNext())
  {
    var fi = file.next();
    var target = fi.getName();
    if(target == filename)
    {
      if(append)
      {
        datstr = fi.getBlob().getDataAsString() + datstr;
      }
      targetFound = true;
      fi.setContent(datstr);
    }
  }
  if(!targetFound)
  {
    var create = fldr.createFile(filename, datstr);
    if(create)
    {
      targetFound = true;
    }
  }
  return targetFound;
}