Totally Free Google Sheet Mail Merge

kxmailmerge.gs

/*
*  KX MailMerge
*  Developer: Kanakorn Horsiritham
*    Computer Center, Prince of Songkla University
*    Hat Yai, Songkhla, THAILAND
*  Create Date: 2016-11-28
*  Website: http://sysadmin.psu.ac.th/author/kanakorn-h/
*/


var scriptProperties = PropertiesService.getScriptProperties();

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('MailMerge')
      .addItem('Select Email Subject Range', 'selectemailsubject')
      .addItem('Select Email Template Range', 'selectemailtemplate')
      .addItem('Send Mail Merge', 'sendemailmerge')
      .addToUi();
}
function selectemailsubject() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selectRange = sheet.getActiveRange();
  var r = selectRange.getRow();
  var c = selectRange.getColumn();
  var h = selectRange.getHeight();
  var w = selectRange.getWidth();
  var values = selectRange.getValues();  
  
  var result="";
  
  for (var i = 0; i< h ; i++) {
    for (var j = 0 ; j < w ; j++){
      if (values[i][j] == "") { 
        result = result + "\t"; 
      } else {
        result = result + " " + values[i][j];
      }      
    }
  }
  scriptProperties.setProperty("subject", result);  
}

function selectemailtemplate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var selectRange = sheet.getActiveRange();
  var r = selectRange.getRow();
  var c = selectRange.getColumn();
  var h = selectRange.getHeight();
  var w = selectRange.getWidth();
  var values = selectRange.getValues();  
  
  var result="";
  
  for (var i = 0; i< h ; i++) {
    for (var j = 0 ; j < w ; j++){
      if (values[i][j] == "") { 
        result = result + "\t"; 
      } else {
        result = result + " " + values[i][j];
      }      
    }
    result = result + "\n";
  }
  scriptProperties.setProperty("template", result);  
}

function sendemailmerge() {
  var subject,template;
  subject= scriptProperties.getProperty("subject");
  template = scriptProperties.getProperty("template");
  
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var selectRange = sheet.getActiveRange();
  var r = selectRange.getRow();
  var c = selectRange.getColumn();
  var h = selectRange.getHeight();
  var w = selectRange.getWidth();
  var values = selectRange.getValues();  
  

  
  for (var i = 0; i< h ; i++) {
    var s,t;
      s=subject;
      t=template;
    for (var j = 0 ; j < w ; j++){
    
      
      s=s.replace("{F"+ (j+1) + "}" , values[i][j]);
      t=t.replace("{F"+ (j+1) + "}" , values[i][j]);
      
      Logger.log(i + " " + j + " " + values[i][j]);
    }
    try{
      MailApp.sendEmail(values[i][0], s, t);
      sheet.getRange(r+i, c + w ).setValue("Sent");
    } catch (e) {
      sheet.getRange(r+i, c + w ).setValue(e.message);
    }
  }  
  
}