daily pastebin goal
59%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 53 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // Simple function to add a menu option to the spreadsheet "Export", for saving a PDF of the spreadsheet directly to Google Drive.
  2. // The exported file will be named: SheetName and saved in the same folder as the spreadsheet.
  3. // To change the filename, just set pdfName inside generatePdf() to something else.
  4.  
  5. // Running this, sends the currently open sheet, as a PDF attachment
  6. function onOpen() {
  7.   var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
  8.   SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);  
  9. }
  10.  
  11. function generatePdf() {
  12.   // Get active spreadsheet.
  13.   var sourceSpreadsheet = SpreadsheetApp.getActive();
  14.  
  15.   // Get active sheet.
  16.   var sheets = sourceSpreadsheet.getSheets();
  17.   var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  18.   var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  19.  
  20.   // Set the output filename as SheetName.
  21.   var pdfName = sheetName;
  22.  
  23.   // Get folder containing spreadsheet to save pdf in.
  24.   var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  25.   if (parents.hasNext()) {
  26.     var folder = parents.next();
  27.   }
  28.   else {
  29.     folder = DriveApp.getRootFolder();
  30.   }
  31.  
  32.   // Copy whole spreadsheet.
  33.   var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
  34.  
  35.   // Delete redundant sheets.
  36.   var sheets = destSpreadsheet.getSheets();
  37.   for (i = 0; i < sheets.length; i++) {
  38.     if (sheets[i].getSheetName() != sheetName){
  39.       destSpreadsheet.deleteSheet(sheets[i]);
  40.     }
  41.   }
  42.  
  43.   var destSheet = destSpreadsheet.getSheets()[0];
  44.  
  45.   // Repace cell values with text (to avoid broken references).
  46.   var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
  47.   var sourcevalues = sourceRange.getDisplayValues();
  48.   var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  49.   destRange.setValues(sourcevalues);
  50.  
  51.   // Save to pdf.
  52.   var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  53.   var newFile = folder.createFile(theBlob);
  54.  
  55.   // Delete the temporary sheet.
  56.   DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  57. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top