Conditional Google Spreadsheet App Script that automatically sends email based on the value of a cell

I'm just putting up here how I made a project which requires to make a Google App Script on a Google Spreadsheet to send email if a particular cell contains Yes or No or something like OK as a value of a cell or your column indicator for something.

It's like this, supposed you have a Google Form which has name, email, address, and other important details on it, and you also have there an input which asks the users who fill up the form if they want to receive an email or not? or something complex and customized that requires you to build a custom script on it and you can't find any add-ons available.

On the project I made, it required me to send an email if the value of a particular cell which is a computed value of some cells from another sheet is "Y" and also if "N", but the two has a different email body customized for the users based on their answer in the form. I broke the work down to pieces to learn about the scripting, first I studied how the email is sent using the script, so I found a tutorial from the Google Developers site (Yeah what's nice about Google is that it has a broad sources of documentation not just on their site but a also a lot from the developers community)

So go to your Google Drive and make a spreadsheet there with two columns 3 rows, the first row should be your header, and the 2nd and 3rd contains the data.

Like this:

Here's the simple code:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

So, while developing to complete the requirements, I have encountered some problems which needs to be addressed, not just one but a bunch of them:

1. Tag rows as sent to avoid resending the email again everytime the script runs.

2. How do we check a cell value to be used as a condition to implement a rule to on when to send the email.

3. If the spreadsheet contains more than 1 sheet and the sheet you're working with is not active, the script might encounter error, so we must do something about it on how we can make a particular sheet active.

4. The example above shows gives us a fix variable for rows, so how about if we want to get all the rows filled and loop with it?

5. Add or embed an image attachment in the email from a URL.

6. An extra problem encountered that I was able to solve was an addition of variables in app script, instead of adding the two values, the script is actually concatenating it, like variable x value is 2 and y is 0, the answer becomes 20? (Anyway let's just be careful in copy-pasting from tutorials because that's where the problem is in my experience)

That's pretty much the hurdles on our requirements and we can now build it perfectly. So far, the code above works just fine, we'll just be tweaking that a little bit more.

Here's the final code, I'll be explaining it in another article:

function sendEmails() {
  
  var LogoUrl = "https://yourwebsite.com/photo.jpg";
  
  var LogoBlob = UrlFetchApp
                          .fetch(LogoUrl)
                          .getBlob()
                          .setName("LogoBlobName");
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var startRow = 2;
  var numCol = 39;
  
  //get number of rows to process - 1 for header  
  var numRows = sheet.getLastRow();
  
  var EMAIL_SENTY = "Yes";
  
  var messageY = "This is the templated message number 1";
  var messageN = "This is the templated message number 2";
  
  var subject = "This is the email subject";
  var footer = "Something in the footer,<br>Signature";
  
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, numCol);
  
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  
  for (i=0;i<data.length;i++) {
    
    var row = data[i];
    var emailAddress = row[5];
    var highRisk = row[6];
    var emailSent = row[38];
    
    //check if email address is not blank to avoid error
    if(emailAddress!="" && emailSent!="Yes"){
      
      //high risk
      if(highRisk=="Y"){
        MailApp.sendEmail({
          to: emailAddress,
          subject: subject,
          htmlBody: messageY+"<br><br><h3><b>"+footer+"</b></h2><img src='cid:LogoBlobName'>",
          inlineImages:
          {
          Logo: LogoBlob
        }
                          });
        sheet.getRange(startRow + i, 39).setValue(EMAIL_SENTY);
      }
      
      //not high risk
      else{
        MailApp.sendEmail({
          to: emailAddress,
          subject: subject,
          htmlBody: messageN+"<h2><b>"+footer+"</b></h2><img src='cid:LogoBlobName'>",
          inlineImages:
          {
          Logo: LogoBlob
        }
                          });
        sheet.getRange(startRow + i, 39).setValue(EMAIL_SENTY);
      }
    }
    
  }
}

Jeff Man

Search This Blog

Conditional Google Spreadsheet App Script that automatically sends email based on the value of a cell

Labels

Comments

Post a Comment

Popular posts from this blog

PROVEN and TESTED: Step-by-step guide to Register Alumni Association with SEC Philippines

Reaction Paper for the movie The Count of Monte Cristo

Pluggle Review: 6 Reasons Why I doubt Pluggle and Why I'm Not Joining