How do I send email to user by appending my domain name to the user name in Google Apps Script? - Stack Overflow

admin管理员组

文章数量:1314221

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?

• email
• google-sheets
• google-apps-script

Share Improve this question Follow edited Jan 30 at 13:08 Codedabbler asked Jan 30 at 12:59 CodedabblerCodedabbler 1,08211 gold badge44 silver badges1717 bronze badges 0

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

This 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.

本文标签：