Google Sheets Script - Send to email in multiple cells on sheet

Question

I am trying to send an email using Google scripts to multiple emails listed in the cells. I would like to send one email and the other emails would be cc. Emails are listed in cells D10:D12, M5:M6, and AL10:AL12

Sheet Link = https://docs.google.com/spreadsheets/d/1xVjEQv6FOnKk-qZgcPV4b7vdeEP3h53on2CA-jJa7iU/edit?usp=sharing

The current script is working great to send an email to me. Just can't figure out how to do the multiple emails and CC's.

function sendEmail() {

var email = sheet.getRange(D10:D12).getValues();

var ss = SpreadsheetApp.getActiveSpreadsheet();

var actualSheet=SpreadsheetApp.getActiveSheet()

var sheet = ss.getActiveSheet() // Enter the name of the sheet here

var subject = "PDF from Water Polo Scoresheet - " + actualSheet.getName();

var body = "\n Attached is a PDF copy of the sheet " + sheet.getName() + " in the " + ss.getName() + " spreadsheet. For the most up to date scoresheet make sure and check www.waterpolodrills.com";

// Base URL
var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
 */

var url_ext = 'exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
     + '&size=letter' // paper size legal / letter / A4
     + '&portrait=false' // orientation, false for landscape
     + '&fitw=true&source=labnol' // fit to page width, false for actual size
     + '&sheetnames=false&printtitle=false' // hide optional headers and footers
     + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
     + '&fzr=false' // do not repeat row headers (frozen rows) on each page
     + '&gid='; // the sheet's Id

var token = ScriptApp.getOAuthToken();

var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
        headers : {
            'Authorization' : 'Bearer ' + token
        }
    }).getBlob().setName(sheet.getName() + ".pdf");

// Uncomment the line below to save the PDF to the root of your drive. 
//  var newFile = DriveApp.createFile(response).setName(sheet.getName() + ".pdf")

if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {
        htmlBody : body,
        attachments : [response],
      cc : ccEmail.join(",")
    });

}

Liora Haydont Liora Haydont · Accepted Answer · 2018-04-27T20:11:10

As described in the doc of sendEmail, you can add options as the 4th parameter (https://developers.google.com/apps-script/reference/mail/mail-app#sendEmail(String,String,String,Object))

In the possible options parameter, there is cc with description "a comma-separated list of email addresses to CC"

So I guess you could use it like this:

ccEmails = //get your ccs here
if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {
        htmlBody : body,
        attachments : [response],
        cc : ccEmails.join(",")
    });

Google Sheets Script - Send to email in multiple cells on sheet

1 Answers