I have a spreadsheet data containing Name, Start Contract, End Contract. I want to send email notifications to gmail if the End Contract ends within 1 month. I got the email but i can’t get the value of ValueA, ValueB, ValueC, and ValueH in email body.
function onChange(e) {
try {
if (!e || !e.source) {
throw new Error("Event object or source is undefined.");
}
var sheet = e.source.getActiveSheet();
var editedRange = e.range;
var editedRow = editedRange.getRow();
console.log("Sheet Name:", sheet.getName());
console.log("Edited Row:", editedRow);
if (sheet.getName() === '2023') {
var valueInColumnI = sheet.getRange(editedRow, 9).getValue();
console.log("Value in Column I:", valueInColumnI);
if (valueInColumnI === 'OVERDUE') {
var valueInColumnC = sheet.getRange(editedRow, 3).getValue();
var valueInColumnH = sheet.getRange(editedRow, 8).getValue();
var valueInColumnA = sheet.getRange(editedRow, 1).getValue();
var valueInColumnB = sheet.getRange(editedRow, 2).getValue();
console.log("Raw Value in Column C:", valueInColumnC, "Type:", typeof valueInColumnC);
console.log("Raw Value in Column H:", valueInColumnH, "Type:", typeof valueInColumnH);
console.log("Raw Value in Column A:", valueInColumnA, "Type:", typeof valueInColumnA);
console.log("Raw Value in Column B:", valueInColumnB, "Type:", typeof valueInColumnB);
var stringValueC = convertToString(valueInColumnC);
var stringValueH = convertToString(valueInColumnH);
var stringValueA = convertToString(valueInColumnA);
var stringValueB = convertToString(valueInColumnB);
console.log("Converted String Value in Column C:", stringValueC);
console.log("Converted String Value in Column H:", stringValueH);
console.log("Converted String Value in Column A:", stringValueA);
console.log("Converted String Value in Column B:", stringValueB);
sendEmailNotification(stringValueA, stringValueB, stringValueC, stringValueH);
}
}
} catch (error) {
console.error("Error in onChange:", error.message);
}
}
function convertToString(value) {
if (value === null || value === undefined) {
return 'N/A';
}
if (Object.prototype.toString.call(value) === '[object Date]') {
return Utilities.formatDate(value, Session.getScriptTimeZone(), 'MM/dd/yyyy');
}
if (typeof value === 'object') {
return JSON.stringify(value); // Convert objects to JSON strings for better debugging
}
return String(value);
}
function sendEmailNotification(valueA, valueB, valueC, valueH) {
var email = "myemail";
var subject = "Contract Extend";
var message = `Hello,
Please be informed of the following details:
- Contract: ${valueC}
- Due Date: ${valueH}
- Additional Info A: ${valueA}
- Additional Info B: ${valueB}
Please ensure to extend the contract '${valueC}' before '${valueH}'.
Best regards,
Your Team`;
console.log("Email Message:", message);
MailApp.sendEmail(email, subject, message);
}
The output shows undefined as seen below. What should I do to fix this script?
Hello,
Please be informed of the following details:
- Contract: undefined
- Due Date: undefined
- Additional Info A: [object Object]
- Additional Info B: undefined
Please ensure to extend the contract ‘undefined’ before ‘undefined’.
Best regards,
Your Team
0
function onChange(e) { // ... var editedRange = e.range;
There is no range field in the “on change” event object.
Delete the “on change” trigger and run the function on an installable “on edit” installable trigger instead.
You can reduce complexity in the code by using Range.getDisplayValues(), like this:
function installableOnEdit(e) {
if (!e || !e.range) throw new Error('Event object or range is undefined.');
try {
const sheet = e.range.getSheet();
if (sheet.getName() !== '2023') return;
const values = sheet.getRange(e.range.rowStart, 1, 1, 9).getDisplayValues().flat();
if (values[8] !== 'OVERDUE') return; // column I
sendEmailNotification(values[0], values[1], values[2], values[7]);
} catch (error) {
e.source.toast(error.message);
throw error;
}
}
See Range.getDisplayValues().