Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- // Simple function to add a menu option to the spreadsheet "Export", for saving a PDF of the spreadsheet directly to Google Drive.
- // The exported file will be named: SheetName and saved in the same folder as the spreadsheet.
- // To change the filename, just set pdfName inside generatePdf() to something else.
- // Running this, sends the currently open sheet, as a PDF attachment
- function onOpen() {
- var submenu = [{name:"Save PDF", functionName:"generatePdf"}];
- SpreadsheetApp.getActiveSpreadsheet().addMenu('Export', submenu);
- }
- function generatePdf() {
- // Get active spreadsheet.
- var sourceSpreadsheet = SpreadsheetApp.getActive();
- // Get active sheet.
- var sheets = sourceSpreadsheet.getSheets();
- var sheetName = sourceSpreadsheet.getActiveSheet().getName();
- var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
- // Set the output filename as SheetName.
- var pdfName = sheetName;
- // Get folder containing spreadsheet to save pdf in.
- var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
- if (parents.hasNext()) {
- var folder = parents.next();
- }
- else {
- folder = DriveApp.getRootFolder();
- }
- // Copy whole spreadsheet.
- var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
- // Delete redundant sheets.
- var sheets = destSpreadsheet.getSheets();
- for (i = 0; i < sheets.length; i++) {
- if (sheets[i].getSheetName() != sheetName){
- destSpreadsheet.deleteSheet(sheets[i]);
- }
- }
- var destSheet = destSpreadsheet.getSheets()[0];
- // Repace cell values with text (to avoid broken references).
- var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
- var sourcevalues = sourceRange.getDisplayValues();
- var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
- destRange.setValues(sourcevalues);
- // Save to pdf.
- var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
- var newFile = folder.createFile(theBlob);
- // Delete the temporary sheet.
- DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
- }
Add Comment
Please, Sign In to add comment