I found this code to send by email all tabs of a Google Spreadsheet as a PDF file (one merged file).
function sendReport() {
var message = {
to: "contact@sexample.com",
subject: "Sales Reports",
body: "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s",
name: "Sales",
attachments:[SpreadsheetApp.getActiveSpreadsheet().getAs(MimeType.PDF).setName("sales_report_2021")]
}
MailApp.sendEmail(message);
So, I'd like to merge into the final document only the tabs that "A1" cell is equal "1" and send them by email as attachment.
Thank you very much!
I believe your goal is as follows.
1.In this case, how about the following modification?
function sendReport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ranges = ss.createTextFinder("1").matchEntireCell(true).findAll();
var showSheets = ranges.reduce((ar, e) => {
if (e.getA1Notation() == "A1") ar.push(e.getSheet().getSheetName());
return ar;
}, []);
var sheets = ss.getSheets();
sheets.forEach(s => {
if (!showSheets.includes(s.getSheetName())) s.hideSheet();
});
SpreadsheetApp.flush();
var pdf = ss.getBlob().setName("sales_report_2021");
var message = {
to: "contact@sexample.com",
subject: "Sales Reports",
body: "Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s",
name: "Sales",
attachments: [pdf]
}
MailApp.sendEmail(message);
sheets.forEach(s => s.showSheet());
}
1 are retrieved from all sheets using TextFinder. And, the sheets that the cell "A1" has 1 are retrieved. And, the sheets that the cell "A1" has no value of 1 are hidden. And then, the Spreadsheet is converted to a PDF data, and used with MailApp.sendEmail. At last, all sheets are shown. By this flow, your goal is achieved.