Send Email - Keep Cell Formatting2019 Community Moderator ElectionSend email when cell is edited using email in parallel cellRename worksheet to cell value keeping formatNeed to send Email when a particular cell is updatedConditional formatting based on another cell's valueGoogle-apps-script will not send email inside an onEdit functionSend an email on edit if a cell contains specfic valueGoogle Sheets to send email when cell is not blankGoogle Sheets to send email with text of cell that is enteredChange Google Sheet trigger frequency based on cell contentsFetch rows with column matching a criteria and send them by email (Google Sheets)Send email on matching cell value in a Sheet
One circle's diameter is different from others within a series of circles
Difference between `nmap local-IP-address` and `nmap localhost`
What is better: yes / no radio, or simple checkbox?
Are all players supposed to be able to see each others' character sheets?
I am the person who abides by rules, but breaks the rules. Who am I?
What sort of fish is this
How to copy the rest of lines of a file to another file
What should I do when a paper is published similar to my PhD thesis without citation?
ESPP--any reason not to go all in?
Short scifi story where reproductive organs are converted to produce "materials", pregnant protagonist is "found fit" to be a mother
What does the Digital Threat scope actually do?
What is Tony Stark injecting into himself in Iron Man 3?
Trocar background-image com delay via jQuery
If sound is a longitudinal wave, why can we hear it if our ears aren't aligned with the propagation direction?
Idiom for feeling after taking risk and someone else being rewarded
Smooth vector fields on a surface modulo diffeomorphisms
How exactly does an Ethernet collision happen in the cable, since nodes use different circuits for Tx and Rx?
Too soon for a plot twist?
Does the US political system, in principle, allow for a no-party system?
Gomel chasadim tovim - are there bad chasadim?
Use Mercury as quenching liquid for swords?
Leveling the sagging side of the home
Do Paladin Auras of Differing Oaths Stack?
Why do phishing e-mails use faked e-mail addresses instead of the real one?
Send Email - Keep Cell Formatting
2019 Community Moderator ElectionSend email when cell is edited using email in parallel cellRename worksheet to cell value keeping formatNeed to send Email when a particular cell is updatedConditional formatting based on another cell's valueGoogle-apps-script will not send email inside an onEdit functionSend an email on edit if a cell contains specfic valueGoogle Sheets to send email when cell is not blankGoogle Sheets to send email with text of cell that is enteredChange Google Sheet trigger frequency based on cell contentsFetch rows with column matching a criteria and send them by email (Google Sheets)Send email on matching cell value in a Sheet
1
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');
google-apps-script google-sheets getstring
edited Mar 6 at 23:38
Lucky Saini
2,6511536
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
1
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');
google-apps-script google-sheets getstring
edited Mar 6 at 23:38
Lucky Saini
2,6511536
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
1
1
1
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');
google-apps-script google-sheets getstring
edited Mar 6 at 23:38
Lucky Saini
2,6511536
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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');
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');
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');
google-apps-script google-sheets getstring
google-apps-script google-sheets getstring
edited Mar 6 at 23:38
Lucky Saini
2,6511536
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 6 at 23:38
Lucky Saini
2,6511536
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Mar 6 at 23:38
Lucky Saini
2,6511536
edited Mar 6 at 23:38
Lucky Saini
2,6511536
edited Mar 6 at 23:38
Lucky Saini
2,6511536
2,6511536
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
asked Mar 6 at 22:55
Michael BrushMichael Brush
82
82
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Michael Brush is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
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()
answered Mar 7 at 0:29
CooperCooper
7,6342729
1
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
1
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
1
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
1
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Michael Brush is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55033516%2fsend-email-keep-cell-formatting%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
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()
answered Mar 7 at 0:29
CooperCooper
7,6342729
1
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
1
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
1
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
1
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
add a comment |
1
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()
answered Mar 7 at 0:29
CooperCooper
7,6342729
1
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
1
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
1
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
1
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
add a comment |
1
1
1
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()
answered Mar 7 at 0:29
CooperCooper
7,6342729
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()
answered Mar 7 at 0:29
CooperCooper
7,6342729
edited Mar 7 at 1:39
answered Mar 7 at 0:29
CooperCooper
7,6342729
answered Mar 7 at 0:29
CooperCooper
7,6342729
answered Mar 7 at 0:29
CooperCooper
7,6342729
7,6342729
1
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
1
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
1
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
1
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
add a comment |
1
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
1
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
1
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
1
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
1
1
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
That returns all of the data in column D in the email and removes the formatting, unfortunately.
– Michael Brush
Mar 7 at 0:41
1
1
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
Can you share your spreadsheet for some data?
– Cooper
Mar 7 at 0:45
1
1
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
Sure thing! docs.google.com/spreadsheets/d/…
– Michael Brush
Mar 7 at 0:52
1
1
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
Now I can see what you were trying to accomplish. Thanks for the data.
– Cooper
Mar 7 at 1:23
add a comment |
Michael Brush is a new contributor. Be nice, and check out our Code of Conduct.
draft saved
draft discarded
Michael Brush is a new contributor. Be nice, and check out our Code of Conduct.
Michael Brush is a new contributor. Be nice, and check out our Code of Conduct.
Michael Brush is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55033516%2fsend-email-keep-cell-formatting%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
