Send email to multiple users with only their data

[Steve Denbow]

Hello All

I'm fairly new to scripts, and have tried my hand at writing a script that would allow me to send an email to each user with only their data. Here is a link to a sample spreadsheet

https://docs.google.com/spreadsheets/d/1jO-MnKnlHoiiZSkQjtT_fDOOBV_S2aAmvUb8YpzaouU/edit?usp=sharing

For a test, I entered multiple users, but only 1 email address. The email that was sent contained the data columns for all the users on the list. The intended output would be a email to each user with only their data. Also, the % values send as a decimal (0.98 instead of 98%). How 

Here is the script I have currently. Any pointers would be appreciated! 

function CustomEmail() {
  var body,i,name,row;
  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(2,2,200,1);
  var emailAddress = emailRange.getValues();
 
 
  body = "";
 
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = SpreadsheetApp.getActiveSheet()
  var range = sheet.getRange(1,0,sheet.getMaxRows()-1,sheet.getMaxColumns());
  var data = range.getValues();
 
  for (i in data) {
    row = data[i];
    name = row[0];
    data1 = row[2];
    data2 = row[3];
    data3 = row[4];  
   
   
    {
      body = body + (name + "\n" + "Data1" + data1 + "\n" + "Data2" + data2 + "\n" + "Data3" + data3);
    }
                     }
    var subject = "Your Weekly Numbers";
                     
  MailApp.sendEmail(emailAddress, subject, body);
    }

[Martin Hawksey]

Hi Steve,

When you are iterating over the email data you are adding the result to the same variable e.g. body = body + 

body = body + (name + "\n" + "Data1" + data1 + "\n" + "Data2" + data2 + "\n" + "Data3" + data3)

If you change this to the following it should solve that problem:

body = (name + "\n" + "Data1" + data1 + "\n" + "Data2" + data2 + "\n" + "Data3" + data3)

If you are emailing each row you'll also probably need to move your MailApp into the loop.

Easiest way to get the percentage value is to format the value. This might do what you are looking for:

function CustomEmail() {

  var body,i,name,row;

  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(2,2,200,1);

  var emailAddress = emailRange.getValues();

  

  var ss = SpreadsheetApp.getActiveSpreadsheet()

  var sheet = SpreadsheetApp.getActiveSheet()

  var range = sheet.getDataRange(); // gets data range

  var data = range.getValues(); // gets the values

  data.shift(); // removes the header row

  var subject = "Your Weekly Numbers";

  for (i in data) {

    row = data[i];

    name = row[0];

    var data1 = row[2];

    var data2 = row[3]*100+"%"; // convert value to percentage See https://stackoverflow.com/q/19819086 for alternative solution

    var data3 = row[4]; 

    

    body = (name + "\n" + "Data1: " + data1 + "\n" + "Data2: " + data2 + "\n" + "Data3: " + data3 + "\n");

    Logger.log(body);

    MailApp.sendEmail(emailAddress, subject, body);

  }

}

Going forward you might want to look at this mail merge tutorial for some coding tips/ideas https://developers.google.com/apps-script/articles/mail_merge

[Raja Anirudh Reddy Sarikonda]

Hi Steve Denbow,

Could you please check if the following code works?

function customEmail(){
  var data = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('A2:E').getValues();
  for (i in data){
    var rowdata = data[i];
    if (!rowdata[0] || rowdata[0] == ''){break;}
    MailApp.sendEmail(rowdata[0], "Your Weekly Numbers", rowdata[0] + "\nData1: " + rowdata[2] + "\nData2: " + (rowdata[3]*100) + "%" + "\nData3: " + rowdata[4]);
  }
}

[Steve Denbow]

Raja

That works perfectly, and I think I see what you did and where mine failed. I appreciate your response!!

[Raja Anirudh Reddy Sarikonda]

Glad I could help :)