Would like the script to return the PDF letter via email with the sheet name not the spreadsheet name

Question

The below script has been working fine for multiple things but the letter returns as a PDF letter named "Site Utilities" this is the whole spreadsheet name.

I would like it to return tab/sheet name "Park Letter" instead. Even better would be a chosen name or a cell within the sheet.

function Email3() {
var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

//var email = Session.getUser().getEmail();

var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Park Letter").getRange("F20");

var emailAddress = emailRange.getValue();

var subject = "Coffee Morning";

var body = ("Attached is a letter about the up coming Coffee Morning. \n \nMany thanks, \n \nCoast and Country Parks");

var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
var shID = getSheetID("Park Letter") //Get Sheet ID of sheet name "Master"
var url = "https://docs.google.com/spreadsheets/d/"+ ssID + "/export?format=pdf&id="+ssID+"&gid="+shID;

var result = UrlFetchApp.fetch(url , requestData);
var contents = result.getContent();

MailApp.sendEmail (emailAddress, subject ,body, {attachments:[{fileName:sheetName+".pdf", content:contents,    mimeType:"application//pdf"}]});

};
function getSheetID(name){
var ss = SpreadsheetApp.getActive().getSheetByName(name)
var sheetID = ss.getSheetId().toString()
return sheetID
}

I have looked at other examples where it states get sheet name rather than spreadsheet but this didn't seem to work.

I also saw an option for just changing the name "Get New Name". This made sense but I don't think I am putting the code within the right section.

Answer 1

About I would like it to return tab/sheet name "Park Letter" instead.

When I saw your showing script, var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName(); is used as the filename of PDF file at MailApp.sendEmail (emailAddress, subject ,body, {attachments:[{fileName:sheetName+".pdf", content:contents, mimeType:"application//pdf"}]});. If you want to use the value of Park Letter as the filename of a PDF file, how about the following modification?

By the way, I think that application//pdf should be application/pdf.

From:

MailApp.sendEmail (emailAddress, subject ,body, {attachments:[{fileName:sheetName+".pdf", content:contents,    mimeType:"application//pdf"}]});

To:

MailApp.sendEmail(emailAddress, subject, body, { attachments: [{ fileName: "Park Letter" + ".pdf", content: contents, mimeType: "application/pdf" }] });

or

MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName("Park Letter.pdf")] });

By this modification, the filename of the PDF file is Park Letter.pdf.

About Even better would be a chosen name or a cell within the sheet.

If you want to use the filename from the cell value, how about the following modification? In this case, please replace ### to your sheet name. And, the value of cell "A1" is used as the filename. Please modify it to your actual situation.

From:

MailApp.sendEmail (emailAddress, subject ,body, {attachments:[{fileName:sheetName+".pdf", content:contents,    mimeType:"application//pdf"}]});

To:

var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###").getRange("A1").getDisplayValue();
MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)] });

or

var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###").getRange("A1").getDisplayValue();
MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)] });

By this modification, the cell value of "A1" of the sheet "###" is used as the filename of the PDF file.

Added:

About your following new question.

Is there a way I can send this same letter to multiple people but as a Bcc from a range? I cannot have anyone seeing another persons email address. The example I am thinking is the same as above but instead of var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Park Letter").getRange("F20"). I was thinking var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("vicki working sheet").getRange("F20:F76") although this would need to be a Bcc. Is such a thing possibe? Thank you so much in advance.

If you want to use the values of var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("vicki working sheet").getRange("F20:F76") as bcc, how about the following modification?

From:

MailApp.sendEmail (emailAddress, subject ,body, {attachments:[{fileName:sheetName+".pdf", content:contents,    mimeType:"application//pdf"}]});

To:

var bcc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("vicki working sheet").getRange("F20:F76").getDisplayValues().flat().join(",");
var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("###").getRange("A1").getDisplayValue();
MailApp.sendEmail(emailAddress, subject, body, { attachments: [result.getBlob().setName(`${filename}.pdf`)], bcc });