Ggtcf

I have a google spreadsheet that sends student information contained in a column that is queried and concatenated from another sheet. The queried information is separated by carriage returns. This column is then emailed to families triggered by a formula that calculates how many carriage returns should be in the cell.

That part I have taken care of in the sheet itself. I need assistance with emailing the column and maintaining the format of the cell. I've been able to do that with the code I have below, but it sends ALL of the data in column D in every email. I need it to only send what's located in the appropriate row for the email.

The main portion of the code that's giving me trouble, I think, is the following:

var OriginalString = SpreadsheetApp.getActive().getRange("D2:D").getValues();
var NewString = OriginalString.toString().replace(/n/g, '<br>');

Any help would be appreciated!

function SendEmail3() 
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
 var startrow = 2;
 var lastRow = ss.getLastRow();
 var lastCol = 10;
 var range = ss.getRange(2, 1, lastRow, lastCol);
 var data = range.getValues();
 for (var i = 0; i < data.length; i++) 
 var row = data[i];
 var AccountName = row[0];
 var email = row[1];
 var Parent = row[2];
 var Consent = row[4];
 var StudentData = row[3];
 var CarriageReturns = row[6];
 var Trigger = row[7];
 var emailSent = row[8];
 var subject = "Your Application";
 var OriginalString = SpreadsheetApp.getActive().getRange("D2:D").getValues();
 var NewString = OriginalString.toString().replace(/n/g, '<br>');

 var message = "<HTML><BODY><font size=4>" +
 "<P>" + "Hello " + Parent + ','
 +"<BR>
 +"<BR>" + NewString + "<BR>"
 +"</HTML></BODY>";
 var recipientsTo = email;
 if (AccountName.length > 0 && Trigger >= CarriageReturns && emailSent != 'EMAIL_SENT') 
 MailApp.sendEmail(
 subject: subject,
 to: recipientsTo,
 htmlBody: message
 );
 ss.getRange(startrow + i, 9).setValue('EMAIL_SENT');
 
 

Is this what your looking for?

I set it up so that you can see the message on a modeless dialog rather than sending emails.

function SendEmail3() 
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
 var startrow = 2;
 var lastRow = ss.getLastRow();
 var lastCol = 10;
 var range = ss.getRange(2, 1, lastRow, lastCol);
 var data = range.getValues();
 var html='';//Remove
 for (var i = 0; i < data.length; i++) 
 var row = data[i];
 var AccountName = row[0];
 var email = row[1];
 var Parent = row[2];
 var Consent = row[4];
 var StudentData = row[3];
 var CarriageReturns = row[6];
 var Trigger = row[7];
 var emailSent = row[8];
 var subject = "Your Application";
 var NewString = StudentData.split('n').join('<br />');
 //var message = "<HTML><BODY><font size=4>" + "<P>" + "Hello " + Parent + ',' + "<BR>" +"<BR>" + NewString + "<BR>" +"</HTML></BODY>";
 var message=Utilities.formatString('<HTML><BODY><font size=4><P>Hello %s,<BR><BR>%s<BR></HTML></BODY>', Parent,NewString);
 var recipientsTo = email;
 if (AccountName.length > 0 && Trigger >= CarriageReturns && emailSent != 'EMAIL_SENT') 
 //MailApp.sendEmail(subject: subject,to: recipientsTo,htmlBody: message);
 html+=Utilities.formatString('%s- %s',i+1,message);//Remove
 //ss.getRange(startrow + i, 9).setValue('EMAIL_SENT');
 
 
 var userInterface=HtmlService.createHtmlOutput(html);//Remove
 SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Email Message');//Remove

• Utilities.formatString()