Convert a multi-sheet Google Spreadsheet into a PDF, with option to email it.

convertSpreadsheetToPdf.gs

/*
 * Save spreadsheet as a PDF
 *
 * Based on Dr.Queso's answer in http://stackoverflow.com/questions/30367547/convert-all-sheets-to-pdf-with-google-apps-script/30492812#30492812
 *
 * @param {String} email Where to send the PDF [OPTIONAL] 
 * @param {String} spreadsheetId Or the active spreadsheet[OPTIONAL]
 * @param {String} sheetName The tab to output [OPTIONAL]
 * @param {String} PdfName [OPTIONAL]
 */

function convertSpreadsheetToPdf(email, spreadsheetId, sheetName, pdfName) {

  var spreadsheet = spreadsheetId ? SpreadsheetApp.openById(spreadsheetId) : SpreadsheetApp.getActiveSpreadsheet();
  spreadsheetId = spreadsheetId ? spreadsheetId : spreadsheet.getId()  
  var sheetId = sheetName ? spreadsheet.getSheetByName(sheetName).getSheetId() : null;  
  var pdfName = pdfName ? pdfName : spreadsheet.getName();
  var parents = DriveApp.getFileById(spreadsheetId).getParents();
  var folder = parents.hasNext() ? parents.next() : DriveApp.getRootFolder();
  var url_base = "https://docs.google.com/spreadsheets/d/" + spreadsheet.getId() + "/"

  var url_ext = 'export?exportFormat=pdf&format=pdf'   //export as pdf

      // Print either the entire Spreadsheet or the specified sheet if optSheetId is provided
      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=letter'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=false&printtitle=false&pagenumbers=false'  //hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }
  
  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  folder.createFile(blob);
  
  if (email) {

    var mailOptions = {
      attachments:blob
    }

    MailApp.sendEmail(
      email, 
      "Here is a file named " + pdfName, 
      "Please let me know if you have any questions or comments.", 
      mailOptions);
  }
  
} // convertSpreadsheetToPdf()

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Create PDF').addItem('Create PDF', 'testCreateOnePdfOneSheet').addToUi()
}

var TEST_EMAIL = '' // !!!! Complete this if you want the PDF to be emails !!!!!!!!!!!
var GSHEET_ID = '1BhHmQ9QCEcV9ZNCuOo4-IZ1o-_b9_TpF5vowo0mkFBU' // "Convert multi-sheet Google Sheet to PDF"
var TAB_NAME = 'Sheet2'
var PDF_NAME = 'PDF 3'

function testVarious() {
  
  // Create a PDF containing all the tabs in the active spreadsheet, name it
  // after the spreadsheet, and email it
  convertSpreadsheetToPdf(TEST_EMAIL)
  
  // Create a PDF containing all the tabs in the spreadsheet specified, name it
  // after the spreadsheet, and email it
  convertSpreadsheetToPdf(TEST_EMAIL, GSHEET_ID)

  // Create a PDF just containing the tab 'Sheet2' in the active spreadsheet, specify a name, and email it
  convertSpreadsheetToPdf(TEST_EMAIL, null, 'Sheet2', 'PDF 3')
}

function testCreateOnePdfOneSheet() {
  // Create a PDF just containing the tab 'Sheet2' in the active spreadsheet, specify a name, and email it
  convertSpreadsheetToPdf(TEST_EMAIL, null, TAB_NAME, PDF_NAME)  
}

Hi all,

I really new in apps script.
I have a apps script below that convert from Google sheet to PDF. This is for only one sheet/tab.

My question is , how to convert multiple tabs/sheets of google sheet?

   var changedFlag = false;
var TEMPLATESHEET='M-Email Me';

function emailSpreadsheetAsPDF() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();

  // This is the link to my spreadsheet with the Form responses and the Invoice Template sheets
  // Add the link to your spreadsheet here 
  // or you can just replace the text in the link between "d/" and "/edit"
  // In my case is the text: 17I8-QDce0Nug7amrZeYTB3IYbGCGxvUj-XMt8uUUyvI
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1Zgs1jzjIeaBpd5Ms7emQgxhVJBMtlEOlDNDfxlhSRiY/edit");

  // We are going to get the email address from the cell "B7" from the "Invoice" sheet
  // Change the reference of the cell or the name of the sheet if it is different
  const value = ss.getSheetByName("M-Generate").getRange("F3").getValue();
  const email = value.toString();

  // Subject of the email message
  const subject = ss.getSheetByName("M-Generate").getRange("B3").getValue();

   // Email Text. You can add HTML code here - see ctrlq.org/html-mail
  const body = "Automated Quotation - Sent via Auto Generate from Glideapps";

  // Again, the URL to your spreadsheet but now with "/export" at the end
  // Change it to the link of your spreadsheet, but leave the "/export"
  const url = 'https://docs.google.com/spreadsheets/d/1Zgs1jzjIeaBpd5Ms7emQgxhVJBMtlEOlDNDfxlhSRiY/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf
    '&size=A4' + // paper size letter / You can use A4 or legal
    '&portrait=true' + // orientation portal, use false for landscape
    '&fitw=true' + // fit to page width false, to get the actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=true' + // do not repeat row headers (frozen rows) on each page
    '&gid=101637384'; // the sheet's Id. Change it to your sheet ID.


  const exportOptionss =
    'exportFormat=xlsx' +
    '&gid=557878560';// export as pdf
  
  // You can find the sheet ID in the link bar. 
  // Select the sheet that you want to print and check the link,
  // the gid number of the sheet is on the end of your link.
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
  
  // Generate the PDF file
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  var responsee = UrlFetchApp.fetch(url+exportOptionss, params).getBlob();
  
  // Send the PDF file as an attachement 
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [{
            fileName: ss.getSheetByName("M-Generate").getRange("B3").getValue().toString() +".pdf",
            content: response.getBytes(),
            mimeType: "application/pdf"
        },{
            fileName: ss.getSheetByName("M-Generate").getRange("B3").getValue().toString() +".xlsx",
            content: responsee.getBytes(),
            mimeType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        }]
    });

  // Save the PDF to Drive. (in the folder) The name of the PDF is going to be the name of the Company (cell B5)
  const nameFile = ss.getSheetByName("M-Generate").getRange("B3").getValue().toString() +".pdf"
  const folderID = "1SHKAXCyXmNMwv1QKPYW0QCwey-yzpJih";
  DriveApp.getFolderById(folderID).createFile(response).setName(nameFile);
}


function on_sheet_change(event) {
  var sheetname = event.source.getActiveSheet().getName();
  var sheet = event.source.getActiveSheet();
 
  if (sheetname == 'M-Email Me') {
    emailSpreadsheetAsPDF() ;
  } else return;
}