I'm trying to run the script below sending emails from the template in Sheet[1] A1. Each time the script triggers it selects the data for fillInTheTemplate function from the range
const grg = sheet.getRange(2, 1, 6, 36);
I require the code to only select the range for the fillTemplateFunction from the row which has does not have "Email_Sent" in row 36
Thanks for any assistance
* Sends emails from spreadsheet rows.
*/
function sendEmails() {
const ss = SpreadsheetApp.getActive();
const dsh = ss.getSheets()[0];//repl with getshbyname
const drg = dsh.getRange(2, 1, dsh.getLastRow() - 2, 36);
const vs = drg.getValues();
const tsh = ss.getSheets()[1];//repl with getshbyname
const tmpl = tsh.getRange('A1').getValue();
var sheet = SpreadsheetApp.getActiveSheet();
const grg = sheet.getRange(2, 1, 6, 36);
objects = getRowsData(sheet, grg);
for (var i = 0; i < objects.length; i) {
var rowData = objects[i];}
vs.forEach((r,i) => {
let emailSent = r[35];
let status = r[10];
if (status == 'PRICE ONLY' && emailSent != 'EMAIL_SENT') {
MailApp.sendEmail(r[9], 'SUPERMIX QUOTATION',fillInTemplateFromObject(tmpl, rowData) );//if last paramenter is the options object then you are missing the null for the body. but since fillInTemplateFromObject is undefined I can not know that
dsh.getRange(2 i, 36).setValue('EMAIL_SENT');
}
});
}
/**
* Replaces markers in a template string with values define in a JavaScript data object.
* @param {string} template Contains markers, for instance ${"Column name"}
* @param {object} data values to that will replace markers.
* For instance data.columnName will replace marker ${"Column name"}
* @return {string} A string without markers. If no data is found to replace a marker,
* it is simply removed.
*/
function fillInTemplateFromObject(tmpl, grg) {
console.log('[START] fillInTemplateFromObject()');
var email = tmpl;
// Search for all the variables to be replaced, for instance ${"Column name"}
var templateVars = tmpl.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; templateVars && i < templateVars.length; i) {
// normalizeHeader ignores ${"} so we can call it directly here.
var variableData = grg[normalizeHeader(templateVars[i])];
email = email.replace(templateVars[i], variableData || '');
}
SpreadsheetApp.flush();
return email;
}
/**
* 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.
* @param {Sheet} sheet The sheet object that contains the data to be processed
* @param {Range} range The exact range of cells where the data is stored
* @param {number} columnHeadersRowIndex Specifies the row number where the column names are stored.
* This argument is optional and it defaults to the row immediately above range;
* @return {object[]} 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.
* @param {object} data JavaScript 2d array
* @param {object} keys Array of Strings that define the property names for the objects to create
* @return {object[]} A list of objects.
*/
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.
* @param {string[]} headers Array of strings to normalize
* @return {string[]} An array of normalized strings.
*/
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.
* @param {string} header The header to normalize.
* @return {string} The normalized header.
* @example "First Name" -> "firstName"
* @example "Market Cap (millions) -> "marketCapMillions
* @example "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.
* @param {string} cellData Cell data
* @return {boolean} True if the cell is empty.
*/
function isCellEmpty(cellData) {
return typeof(cellData) == 'string' && cellData == '';
}
/**
* Returns true if the character char is alphabetical, false otherwise.
* @param {string} char The character.
* @return {boolean} True if the char is a number.
*/
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.
* @param {string} char The character.
* @return {boolean} True if the char is a digit.
*/
function isDigit(char) {
return char >= '0' && char <= '9';
}```
CodePudding user response：
When I saw your script, it seems that your script sends the email by checking the column "K" (column number is 11) and the column of "AJ" (column number is 36) with if (status == 'PRICE ONLY' && emailSent != 'EMAIL_SENT') {}. And, when I saw your sample Spreadsheet, the row number which has no value of "EMAIL_SENT" at the columns "AJ" is 5. But, when I saw the column "K", no value is existing. By this, the status == 'PRICE ONLY' && emailSent != 'EMAIL_SENT' returns false. By this, the email is not sent. I thought that this might be the reason for your issue.
If you want to send the email when the column "AJ" has no value of "EMAIL_SENT", how about the following modification?
From:
if (status == 'PRICE ONLY' && emailSent != 'EMAIL_SENT') {
To:
if (emailSent != 'EMAIL_SENT') {
Added:
From the following replying,
The code is sending the email as I require and 'Email_Sent' is placed in column AJ as it should. The problem I have relates with the function fillInTemplateFromObject(tmpl, grg) function. The range I am using for this function is 'const grg = sheet.getRange(2, 1, 5, 36); ' This starts at row 2 rows and includes 5 rows. When I send the email The cell data in the range for the template data is from row 6 even though the row which the Email_Sent is row 4. The Template should take data from the same cells which the Email is being sent.
In this case, how about the following modification?
From:
for (var i = 0; i < objects.length; i) {
var rowData = objects[i];}
vs.forEach((r,i) => {
let emailSent = r[35];
let status = r[10];
if (status == 'PRICE ONLY' && emailSent != 'EMAIL_SENT') {
MailApp.sendEmail(r[9], 'SUPERMIX QUOTATION',fillInTemplateFromObject(tmpl, rowData) );//if last paramenter is the options object then you are missing the null for the body. but since fillInTemplateFromObject is undefined I can not know that
dsh.getRange(2 i, 36).setValue('EMAIL_SENT');
}
});
To:
vs.forEach((r, i) => {
let emailSent = r[35];
if (emailSent != 'EMAIL_SENT') {
MailAppaa.sendEmail(r[9], 'SUPERMIX QUOTATION', fillInTemplateFromObject(tmpl, objects[i]));
dsh.getRange(2 i, 36).setValue('EMAIL_SENT');
}
});