Formatting help: sheets to gmail (cc, bold, checkbox, each line)

[Julia Sullivan]

Hello, 

I'm very new to any kind of coding and while I've managed to read up and work out how to get sheets to send an email, there are a few added extras I'm really struggling with. 

1. I'd like the email to cc email addresses found in cells ('D:D', 'E:E', 'F:F')

2. I would like to send 15 emails to different addresses with info from each row (rows 1 to 15)

3. I would like some text to be in bold ('J:J', 'K:K', 'Q:Q', 'R:R')

4. I would like the email to only send if the checkbox is ticked in column A

I've attached the sheet it will send from and the format of the email as I wish it to appear, as well as my script so far. If anyone could help with even one of these problems I'd be so grateful. 

So far, my script looks like this and sends only the first line to the one recipient. 

/** @OnlyCurrentDoc */

function sendUpdate() {

const sheet = SpreadsheetApp.getActiveSheet();

const recipient = sheet.getRange('B:B').getValue();

const body = sheet.getRangeList(['G:G', 'H:H', 'I:I', 'J:J','K:K','L:L','M:M','N:N','O:O','P:P','Q:Q','R:R','S:S','T:T','U:U','V:V','W:W','X:X'])

.getRanges()

.map(range => range.getDisplayValue())

.join('\n');

const subject = sheet.getRange(['C:C']).getValue();

GmailApp.sendEmail(recipient, subject, body);

}

My Sheet looks like this 

 

I'm hoping for it to send an email like this

Thanks in Advance, 

Julia 

[CBMServices Web]

Hi Julia,

I can help you with this. Out of curiosity, how do you want the email send to be triggered? When you tick the checkbox in column A or on a nightly basis?

George

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9917d729-8873-4fac-8437-0e406a0ff45bn%40googlegroups.com.

[Ellora Bhattacharya]

Can you send a solution to this regardless of trigger?

function sendEmails() {

var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();

var datasheet = spreadSheet.getSheetByName("Email Blast")

var startRow = 2;

var lr = datasheet.getLastRow();

var dataRange = datasheet.getRange(startRow, 1, lr-1, 13);

var data = dataRange.getValues();

for (var i = 0; i < data.length; i++) {

var row = data[i];

var name = "Sales Strategy";

var cc1 = row[4]+ "," +"ell...@browserstack.com";

var options = {

cc: cc1,

name: name

}

var emailAddress = row[1];

if (emailAddress.match('@') === null){

continue;

};

var subject = row[2];

var message = "Hi "+row[0]+",\n Happy to introduce to you, your personalised Performance Tracking Report."

+ "\n This report will help you and your manager engage in conversations regarding your performance with valueable data and key metrices."

+ "\n This report is divided into several sections that give key insights into your State Of Play."

+ "\n Please find a brief explanation of each section below."

+ "<b>\n Performance on Closed Won:</b>"+row[6]

+ "<b>\n Performance on Pipe:</b>"+row[7]

+ "<b>\n Performance on ST Renewals:</b>"+row[8]

+ "<b>\n Performance on Leading Metrics:</b>"+row[9]

+ "<b>\n Performance on Hygiene Metrics:</b>"+row[10]

+ "<b>\n Performance on Accounts:</b>"+row[11]

+ "\n Link to your Personalised Tracking Report : "+row[3]+

+ "\n Let us know if you have any questions and please feel free to send us any feedback & requests";

GmailApp.sendEmail(

emailAddress,

subject,

htmlBody: message,

options);

datasheet.getRange(i+2,6).setValue("Sent");

}

}

This gives me [output][Output] in my email

[cbmserv...@gmail.com]

Here you go:

 

Here is a spreadsheet with the updated script that you wanted. Make a copy of it and you should be able to edit the script in your copy.

 

https://docs.google.com/spreadsheets/d/1zFFBEH464hKl0rDdwZBrDCN40e0dUkLevirIKfkKGO0/edit?usp=sharing

 

 

I'm hoping for it to send an email like this

 

Thanks in Advance, 

Julia 

 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9917d729-8873-4fac-8437-0e406a0ff45bn%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5f7ff090-8bfa-4ca9-9554-062d452e893en%40googlegroups.com.