Google spreadsheet script export active sheet only to PDF

Question

I created loop script for send email with PDF attachment. Loop function works correctly without any error. But email sent with attached PDF file shows all data of all sheets. I want to create PDF file only of ACTIVE sheet and not others.

function SendInvoiceNew4() {
  var sheet = SpreadsheetApp.getActiveSheet();

// Loop from CELL Number Value to CELL Number Value EQUAL
  for(i=sheet.getRange("H11").getValue();i<=sheet.getRange("I11").getValue();i++) {// *************** Enter Start Invoice Serial No Cell Reference & Last Serial No Cell is Auto
    sheet.getRange("H11").setValue(i); //Auto Enter Next Loop Serail Number

    var InvDate = Utilities.formatDate(new sheet.getRange("H13").getValue(), "GMT+1", "MMM-yyyy") //Set invoice Date Format = MONTH & YEAR
    var emailTo = sheet.getRange("B12").getValue(); //Get Email Address from Data
    var message = 'Dear' + "\n\n" + 'See attached your attached invoice in PDF format.' + "\n\n" + 'Thanking you' + "\n" + 'www.xyz.in' + "\n" + '[DO NOT REPLY to this Email.]'; //Enter Custom Messagen ************************************************** Message Body
    var subject = 'Invoice for Month ' + InvDate;  // ************* Enter Cell Reference for Date of Invoice for Subject

    // Convert Invoice Sheet to PDF
    var originalSpreadsheet = SpreadsheetApp.getActive(); // Set original invoice sheet
    var pdf = DriveApp.getFileById(originalSpreadsheet.getId()).getAs('application/pdf').getBytes(); // Convert PDF file
    var attach = {fileName:'Invoice',content:pdf, mimeType:'application/pdf'}; //Set File Name

    // Send Email with attached PDF file   
    MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
    //MailApp.sendEmail(emailTo, subject, message);
    SpreadsheetApp.flush(); // Make sure the cell is updated right away in case the script is interrupted
  }
}

Unknown Unknown · Accepted Answer · 2020-04-20T08:53:35

You will have to adapt your script to send the PDF attachment using @James D function.

Solution

// ... 

// Send Email with attached PDF file   
// FROM:
// MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
// TO:
emailSpreadsheetAsPDF(emailTo, subject, message, sheetName);

// ...

I changed the signature of James' function to use your loop parameters.

function emailSpreadsheetAsPDF(email, subject, body, sheetName) {

    var email = email; // Enter the required email address here

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName(sheetName); // Enter the name of the sheet here

    var subject = subject;

    var body = body;

    // 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]
        });
}

Google spreadsheet script export active sheet only to PDF

1 Answers

Solution