I have the following script:
function addToSchedule() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var sheetName = ss.getSheetName();
var cell = sheet.getActiveCell();
var row = cell.getRow();
var rangeToCopy = sheet.getRange(row,4,1,14).getValues();
var destSheet = ss.getSheetByName("Scheduled_01");
var searchValue = sheet.getRange(row,15).getValue();
var lastRow = destSheet.getLastRow();
var lastCol = destSheet.getLastColumn();
var user = sheet.getRange(row,14).getValue();
var recipients = email + "@domainname";
I have a function set up to send an email based on user name in column N (row,14) in the row that contains the active cell.
Here is the line of code for sending the email:
try {
MailApp.sendEmail({
to: recipients,
subject: "Test Email",
htmlBody: "This is a test.",
noReply: true,
});
The error I get in the console is that the value for user (without the appended domain name) is not a valid email. I am sending email to recipients, which is user + "@domainname", but for some reason, "@domainname" is not getting added to user variable.
Can anyone point out where I am going wrong?
I have the following script:
function addToSchedule() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var sheetName = ss.getSheetName();
var cell = sheet.getActiveCell();
var row = cell.getRow();
var rangeToCopy = sheet.getRange(row,4,1,14).getValues();
var destSheet = ss.getSheetByName("Scheduled_01");
var searchValue = sheet.getRange(row,15).getValue();
var lastRow = destSheet.getLastRow();
var lastCol = destSheet.getLastColumn();
var user = sheet.getRange(row,14).getValue();
var recipients = email + "@domainname";
I have a function set up to send an email based on user name in column N (row,14) in the row that contains the active cell.
Here is the line of code for sending the email:
try {
MailApp.sendEmail({
to: recipients,
subject: "Test Email",
htmlBody: "This is a test.",
noReply: true,
});
The error I get in the console is that the value for user (without the appended domain name) is not a valid email. I am sending email to recipients, which is user + "@domainname", but for some reason, "@domainname" is not getting added to user variable.
Can anyone point out where I am going wrong?
Share Improve this question edited Jan 30 at 13:08 Codedabbler asked Jan 30 at 12:59 CodedabblerCodedabbler 1,0821 gold badge4 silver badges17 bronze badges 02 Answers
Reset to default 2This answer is for precaution only incase the user might contain whitespaces, or not formatted properly.
Note: I've included toString() and trim() to handle usernames with whitespace and special characters.
function addToSchedule() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var sheetName = ss.getSheetName();
var cell = sheet.getActiveCell();
var row = cell.getRow();
var rangeToCopy = sheet.getRange(row,4,1,14).getValues();
var destSheet = ss.getSheetByName("Scheduled_01");
var searchValue = sheet.getRange(row,15).getValue();
var lastRow = destSheet.getLastRow();
var lastCol = destSheet.getLastColumn();
var user = sheet.getRange(row, 14).getValue().toString().trim();
var recipients = user + "include your valid and registered domain here";
try {
MailApp.sendEmail({
to: recipients,
subject: "Test Email",
htmlBody: "This is a test.",
noReply: true,
});
} catch (e) {
Logger.log("Error sending email: " + e.message);
}
}
What I've changed in your code is:
Before:
var user = sheet.getRange(row,14).getValue();
After:
var user = sheet.getRange(row, 14).getValue().toString().trim();
The email variable is undefined. Try replacing email with user.
Add console.log(`${typeof user}: ${user}`); before the line that throws the error to see what's in column N.
Also see How to debug small programs.