I'm using the Google simple mail merge apps script and I want to update the "status" of each person in the spreadsheet to "confirmed" once the email has been sent to them.
The scripts uses the getRowsData function to give you all of the data from a row ...
// Create one JavaScript object per row of data.
var objects = getRowsData(dataSheet, dataRange);
I'm not sure how to use this to set the value of the "status" column, which is the 7th column on my sheet. On their Sending Emails From A Spreadsheet tutorial code they use a startRow variable and getRange ...
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
...
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();
... but I can't see how to adapt that to use the with the "objects" variable in the Mail Merge example's code.
It doesn't make sense to use the "startRow" variable when I've already got the all the row info in the "objects" variable.
I tried this ...
dataSheet.getRange(rowData + i, 7).setValue(EMAIL_SENT);
... but just got the error "Cannot convert [object Object]0 to (class)." during debugging.
I know I'm doing something really stupid / missing something obvious. My Javascript is pretty rusty!
If someone can point me in the right direction, that would be great! :)
PS - Here's the standard simple mail merge script ....
function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheets()[0];
var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, 6);
var templateSheet = ss.getSheets()[1];
var emailTemplate = templateSheet.getRange("A1").getValue();
var emailSubject = templateSheet.getRange("A2").getValue();
var emailFrom = templateSheet.getRange("A3").getValue();
var emailReplyTo = templateSheet.getRange("A4").getValue();
var imageb64 = templateSheet.getRange("A5").getValue();
var imageb64h = templateSheet.getRange("A6").getValue();
// Create one JavaScript object per row of data.
var objects = getRowsData(dataSheet, dataRange);
// For every row object, create a personalized email from a template and send
// it to the appropriate person.
for (var i = 0; i < objects.length; ++i) {
// Get a row object
var rowData = objects[i];
// Generate a personalized email.
// Given a template string, replace markers (for instance ${"First Name"}) with
// the corresponding value in a row object (for instance rowData.firstName).
var emailText = fillInTemplateFromObject(emailTemplate, rowData);
if (emailFrom == null || emailFrom == ""){
MailApp.sendEmail(rowData.emailAddress, emailSubject, emailText);
}else{
var inlineImages = {};
var imgblob;
var imgType;
if (imageb64 != null && imageb64 != ""){
imageType = imageb64.substring(5, imageb64.indexOf(";"))
imageb64 = imageb64.substring(imageb64.indexOf(",") + 1)
imgblob = Utilities.newBlob(Utilities.base64Decode(imageb64), imageType, "signature"); // decode and blob
inlineImages["signature"] = imgblob
}
if (imageb64h != null && imageb64h != ""){
imageType = imageb64h.substring(5, imageb64h.indexOf(";"))
imageb64h = imageb64h.substring(imageb64h.indexOf(",") + 1)
imgblob = Utilities.newBlob(Utilities.base64Decode(imageb64h), imageType, "header"); // decode and blob
inlineImages["header"] = imgblob
}
MailApp.sendEmail(rowData.emailAddress, emailSubject, "", {cc: rowData.copy, name: emailFrom, replyTo: emailReplyTo, htmlBody: emailText, inlineImages: inlineImages});
}
}
}
function onOpen() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Send Emails",
functionName : "sendEmails"
}
];
spreadsheet.addMenu("Mail Actions", entries);
};
// Replaces markers in a template string with values define in a JavaScript data object.
// Arguments:
// - template: string containing markers, for instance ${"Column name"}
// - data: JavaScript object with values to that will replace markers. For instance
// data.columnName will replace marker ${"Column name"}
// Returns a string without markers. If no data is found to replace a marker, it is
// simply removed.
function fillInTemplateFromObject(template, data) {
var email = template;
// Search for all the variables to be replaced, for instance ${"Column name"}
var templateVars = template.match(/\$\{\"[^\"]+\"\}/g);
// Replace variables from the template with the actual values from the data object.
// If no value is available, replace with the empty string.
for (var i = 0; i < templateVars.length; ++i) {
// normalizeHeader ignores ${"} so we can call it directly here.
var variableData = data[normalizeHeader(templateVars[i])];
email = email.replace(templateVars[i], variableData || "");
}
return email;
}
//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
//////////////////////////////////////////////////////////////////////////////////////////
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
var numColumns = range.getEndColumn() - range.getColumn() + 1;
var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
var headers = headersRange.getValues()[0];
return getObjects(range.getValues(), normalizeHeaders(headers));
}
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
if (isCellEmpty(cellData)) {
continue;
}
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}
// Returns an Array of normalized Strings.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
var key = normalizeHeader(headers[i]);
if (key.length > 0) {
keys.push(key);
}
}
return keys;
}
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
var key = "";
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == " " && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) {
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
isDigit(char);
}
// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
return char >= '0' && char <= '9';
}
