Google Scripts for Sheet Export to PDF

export_visible_sheets.gs

// This exports the sheet called Printout
// Hides all other sheets before export

function exportSheet() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Printout';
  var main = ss.getSheetByName(sheetName);
  var sheets = ss.getSheets();
  var folderID = '1234' // update with correct google drive ID
  
  // Hide all sheets other than the Print Sheet
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet();
    }
  }
  
  //Hide All Empty Rows in the Print Sheet
  var maxRows = main.getMaxRows(); 
  var lastRow = main.getLastRow();
  if (maxRows-lastRow != 0){
    main.hideRows(lastRow+1, maxRows-lastRow);
  }
  
  // Save pdf version
  var folder = 'productPDF';
  var parentFolder = DriveApp.getFolderById(folderID); 
  var folder, folders = DriveApp.getFoldersByName(folder);
   if (folders.hasNext()) {
     folder = folders.next();
   } else {
     folder = parentFolder.createFolder(folder);
   }
  var name = main.getRange("B8").getValue(); 
  folder.createFile(ss.getBlob().setName(name));
  
  // Unhide the rows again
  var fullSheetRange = main.getRange(1,1,main.getMaxRows(), main.getMaxColumns());
  main.unhideRow(fullSheetRange); 
  
  // Unhide the sheets
  for (i = 0; i < sheets.length; i++) {
    sheets[i].showSheet();
}

exportLoopedSheets.gs

// This loops through a list of products, updating a cell that triggers a query refresh on main
// Then each product query is exported to pdf

function exportLoopedSheet(firstRow, lastRow) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Printout'; // update for print sheet name
  var productSheetName = 'Product_List'; // update for final product list
  var folderName = 'productPDFs';
  var main = ss.getSheetByName(sheetName);
  var sheets = ss.getSheets();
  var productList = ss.getSheetByName(productSheetName);
  var lastProductRow = lastRow;
  var firstProductRow = firstRow;   
  
  // Hide all sheets other than the Print Sheet
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() !== sheetName) {
      sheets[i].hideSheet();
    }
  }
  
  for (var prodNo = firstProductRow; prodNo < lastProductRow + 1; prodNo ++) {
    var currentProduct = productList.getRange('A'+ prodNo).getValue();
    main.getRange('B9').setValue(currentProduct);
    
    // Ensure all changes are updated
    SpreadsheetApp.flush();
    
    // call the export sheet function
    // see exportSheet.gs
    exportSheet();
  }
  
  // Unhide the sheets
  for (i = 0; i < sheets.length; i++) {
    sheets[i].showSheet();
  }
}

exportSheet.gs

// This exports the sheet called Printout
// It assumes only one sheet visible otherwise you need to hide the other sheets first

function exportSheet() {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Printout';
  var main = ss.getSheetByName(sheetName);
  var sheets = ss.getSheets();
  var folderID = '1234' // update with correct google drive ID
  
  //Hide All Empty Rows in the Print Sheet
  var maxRows = main.getMaxRows(); 
  var lastRow = main.getLastRow();
  if (maxRows-lastRow != 0){
    main.hideRows(lastRow+1, maxRows-lastRow);
  }
  
  // Save pdf version
  var folder = 'productPDF';
  var parentFolder = DriveApp.getFolderById(folderID); 
  var folder, folders = DriveApp.getFoldersByName(folder);
   if (folders.hasNext()) {
     folder = folders.next();
   } else {
     folder = parentFolder.createFolder(folder);
   }
  var name = main.getRange("B8").getValue(); 
  folder.createFile(ss.getBlob().setName(name));
  
  // Unhide the rows again
  var fullSheetRange = main.getRange(1,1,main.getMaxRows(), main.getMaxColumns());
  main.unhideRow(fullSheetRange); 
  
}

exportSingleSheetToPDF_API.gs

// This is a function to export a single google sheet as pdf in Google drive
// Uses Google API
// Does not require all the other tabs to be hidden
// Much of the code taken from here: https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70
// I haven't been able to combine this with my loop as the repeated API calls lead to a 429 error (even with a sleep function)
// *******************************************************************************

function singleSheetExport(){

  // Get active spreadsheet URL
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = 'Printout';
  var main = ss.getSheetByName(sheetName)
  var folderID = '1234' // Google Drive Folder ID

  // 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=true'                                // 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();
  
  // Hide All Empty Rows in the Print Sheet
  var maxRows = main.getMaxRows(); 
  var lastRow = main.getLastRow();
  if (maxRows-lastRow != 0){
    main.hideRows(lastRow+1, maxRows-lastRow);
  }

  // Convert your specific sheet to blob
  var response = UrlFetchApp.fetch(url + url_ext + main.getSheetId(), {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  
  // Save pdf version
  var folder = 'productPDF';
  var parentFolder = DriveApp.getFolderById(folderID);
  var folder, folders = DriveApp.getFoldersByName(folder);
   if (folders.hasNext()) {
     folder = folders.next();
   } else {
     folder = parentFolder.createFolder(folder);
   }
  var name = main.getRange("B8").getValue(); 
  var cleanName = name.replace(/([^a-zA-Z0-9() -])/g, "_");
  folder.createFile(response.getBlob().setName(cleanName));
  
  // Unhide the rows again
  var fullSheetRange = main.getRange(1,1,main.getMaxRows(), main.getMaxColumns());
  main.unhideRow(fullSheetRange); 
   
}

Good work for "exportSingleSheetToPDF_API.gs" !
what about if I wanna send email with this attachment?