Google Apps Script to send email via Mailgun

gistfile1.txt

// Index of some column that is not used.
var SENT_COLUMN = 15;
// Place your Grid API Key here.
var MAILGUN_KEY = "YOUR_MAILGUN_KEY"
// The emails will be send from here.
var EMAIL_FROM = "Company Name <[email protected]>";
// Errors will be send here
var SUPPORT_EMAIL = "[email protected]";
// Subject of the email
var SUBJECT = "EMAIL SUBJECT";
// The product this email is for
var PRODUCT_NAME = "PRODUCT NAME";

var NAME_COLUMN_NAME = "Name";
var EMAIL_COLUMN_NAME = "Email";
 
/**
 * Creates a timebased trigger for the current spreadsheet.
 * Run this function to initialize the email sending script.
 */
function Initialize() {
  var triggers = ScriptApp.getProjectTriggers();
 
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  
  ScriptApp.newTrigger("processSpreadsheetForNewSubmissions")
  .timeBased()
  .everyMinutes(1)
  .create();  
}

/**
 * Main function that searches the spreadsheet for row that haven't been processed, and sends emails.
 */
function processSpreadsheetForNewSubmissions() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; // gets the first and only sheet
  var column = SENT_COLUMN;
  var lastRow = sheet.getLastRow();
  var columnValues = sheet.getRange(2, column, sheet.getLastRow() - 1).getValues(); // returns all values in "Confirmation Sent" column
  var searchResult = findIndex(columnValues); // returns rows that do not have a value of 'sent'
  if(searchResult.length > 0) {
    for (var i = 0; i <= searchResult.length - 1; i++) {
      var row = searchResult[i];
      processSubmission(row, sheet);
    }
  }
}

/**
 * Helper function to find rows, which haven't been processed.
 */
function findIndex(columnValues) {
  var rowNum = [];
  for (var i = 0; i < columnValues.length; i++) {
    if (columnValues[i] != 'sent' && columnValues[i] != 'error') {   
      rowNum.push(i + 2);
    }
  }
  return rowNum;
};
 
/**
 * Helper function for processing the submission in the given row
 */
function processSubmission(row, s) {  
  try {           
    var columns = s.getRange(1,1,1,s.getLastColumn()).getValues()[0];
    var values = s.getRange(row,1,1,s.getLastColumn()).getValues()[0];    
    
    // Compose the message
    var name = "";
    var emailaddress = "";
    
    var message = "Hello,\n\nThank you for showing interest in Symphony Products. Here is the " + PRODUCT_NAME + " Product Note. Feel free to contact us for any further questions.\n\nBest,\nMy Company.";    
    var htmlMessage = "Hello,<br/><br/>Thank you for showing interest in Symphony Products. Here is the " + PRODUCT_NAME + " Product Note. Feel free to contact us for any further questions.<br/><br/>Best,<br/>My Company.";

    // Only include form fields that are not blank
    for ( var keys in columns ) {
      var key = columns[keys];
      if (key && values[keys] && (values[keys] != "") ) {
        if (key == NAME_COLUMN_NAME) {
          name = values[keys];
        } 
        if (key == EMAIL_COLUMN_NAME) {
          emailaddress = values[keys];
        }
      }
    }

    var result = sendEmail(emailaddress, EMAIL_FROM, name, SUBJECT, message, htmlMessage);
    
    // Mark the row as processed
    s.getRange(row,SENT_COLUMN,1, 1).setValues([[result]]);
  } catch (e) {
    reportError(e.toString());
  }   
}

function sendEmail(toEmail, fromEmail, fromName, subject, message, htmlMessage) {
  var fileresponse = UrlFetchApp.fetch("http://symphonyfintech.files.wordpress.com/2014/07/fuseab-1.pdf");
  var fileBlob = fileresponse.getBlob();
  var url = "https://api.mailgun.net/v3/sandbox06997d18109746479b9f130895af8afe.mailgun.org/messages";  
  var params = {
    "from": fromEmail,
    "fromname": fromName,
    "to": toEmail,
    "subject": subject,
    "replyto": fromEmail,    
    "text": message,
    "html": htmlMessage,
    "attachment": fileBlob
  };
  
  var options = {
    'method': 'post',
    'payload': params,
    'headers': {
      'Authorization': 'Basic ' + Utilities.base64Encode("api:" + MAILGUN_KEY)
    }
  };

  try {
    var response = JSON.parse(UrlFetchApp.fetch(url, options));
    if (response && response["id"]) {
      return 'sent';
    } else {
      reportError("Invalid response: " + JSON.stringify(response));
      return 'error';
    }    
  } catch (e) {
    reportError(e.toString());
    return 'error';
  }  
}

/**
 * Helper function to report error;
 */
function reportError(message) {
  MailApp.sendEmail(SUPPORT_EMAIL, "Error Occured - " + SUBJECT, message);
}