-
-
Save vkotek/9b7c9ae2c84931c2488315d2dc2ae7a8 to your computer and use it in GitHub Desktop.
Export a Google Drive spreadsheet to PDF in Google Drive in the same folder.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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)) | |
var destSheet = destSpreadsheet.getSheetByName(sheetName); | |
// Repace cell values with text (to avoid broken references). | |
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns()); | |
var sourcevalues = sourceRange.getValues(); | |
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns()); | |
destRange.setValues(sourcevalues); | |
// Delete redundant sheets. | |
var sheets = destSpreadsheet.getSheets(); | |
for (i = 0; i < sheets.length; i++) { | |
if (sheets[i].getSheetName() != sheetName){ | |
destSpreadsheet.deleteSheet(sheets[i]); | |
} | |
} | |
// 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); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
To use this script in Google Drive: | |
1. In your spreadsheet, click Tools->Script Editor. | |
2. Paste the contents of Code.gs into the editor. | |
3. Save. | |
4. Select "Allow" when Google asks about permissions. | |
5. Reload your spreadsheet. | |
You should now see a new file menu option, "Export". |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment