gistfile1.txt

/**
 * Retrieves all the rows in the active spreadsheet that contain data and logs the
 * values for each row.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    Logger.log(row);
  }
};

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Read Data",
    functionName : "readRows"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

function PreviewEmails() {
  showURL("mailto:[email protected]?subject=test")
}

function showURL(href){
  var app = UiApp.createApplication().setHeight(50).setWidth(200);
  app.setTitle("Preview Email");
  var link = app.createAnchor('open ', href).setId("link");
  app.add(link);  
  var doc = SpreadsheetApp.getActive();
  doc.show(app);
}

function previewEmail() { 
  return getFinishedEmail();
}

function showURL(href){
  var app = UiApp.createApplication().setHeight(50).setWidth(200);
  app.setTitle("Show URL");
  var link = app.createAnchor('open ', href).setId("link");
  app.add(link);  
  var doc = SpreadsheetApp.getActive();
  doc.show(app);
}

function getFinishedEmail()
{
  var email = substituteText(getEmailText());
  var subject = substituteText(getSubject());
  
  return "subject=" + encodeURIComponent(subject) + "&body=" + encodeURIComponent(email);
}

function substituteText(email, row) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var values = rows.getValues();  
  
  var headersStartCol = 2; 
  var headersRow = 0; 
  
  var headers = new Array();
  
  for (ndx = headersStartCol; ndx < rows.getNumColumns(); ndx++) {
    var header = values[headersRow][ndx];
    
    if (header != "")
    {
      email = email.replace(new RegExp('\\[' + header + '\\]', 'gi'), values[row][ndx]);
    }
  }
  
  return email;
}

function getEmailsColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var values = rows.getValues();  
  
  var headersStartCol = 2; 
  var headersRow = 0; 
  
  var headers = new Array();
  
  for (ndx = headersStartCol; ndx < rows.getNumColumns(); ndx++) {
    var header = values[headersRow][ndx];
    
    if (header.toLowerCase() == "email")
    {
      return ndx;
    }
  }
  
  return 0;
}

function getEmails()
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();  
  
  var contactsRowStart = 1;
  
  var app = UiApp.createApplication().setHeight(400).setWidth(500);
  app.setTitle("Open Emails");
  
  var panel = app.createVerticalPanel();
  
  // iterate over email fields, skipping the subject and looking for email content
  for (var ndx = contactsRowStart; ndx < numRows; ndx++) {
    var email = values[ndx][getEmailsColumn()];
    
    if (email != "")
    {
      var href = "mailto:" + email + "?subject=" + encodeURIComponent(substituteText(getSubject(), ndx)) + "&body=" + encodeURIComponent(substituteText(getEmailText(), ndx));
      var link = app.createAnchor(email, href).setId("email" + ndx);
      panel.add(link);  
    }
  }
 
  app.add(panel);

  var doc = SpreadsheetApp.getActive();
  doc.show(app);  
}

function getEmailText() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();  
  
  var emailFieldsColumn = 0;
  var email = "";
  
  // iterate over email fields, skipping the subject and looking for email content
  for (n=0;n<values.length;++n) {
    var emailField = values[n][emailFieldsColumn];
    var emailValue = values[n][emailFieldsColumn + 1];
    
    if (emailField.toLowerCase() != "subject" && emailField != "") {
      email += emailValue + "\r\n\r\n";
    }
  }
  
  return email;
}

function getSubject() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();  
  
  var emailFieldsColumn = 0;
  var subject = "";
  
  // iterate over email fields, looking for email content
  for (n=0;n<values.length;++n) {
    var emailField = values[n][emailFieldsColumn];
    var emailValue = values[n][emailFieldsColumn + 1];
    
    if (emailField.toLowerCase() == "subject") {
      return emailValue;
    }
  }
}