Created
January 18, 2022 13:54
-
-
Save amattu2/5e786b9335a1a01f0b4eeae14f37eba7 to your computer and use it in GitHub Desktop.
A very simple Google Apps Script implementation to add pre-configured "Print Regions" to Google Sheets
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
| const PRINT_HEADER = "Generating export..."; | |
| const PRINT_OPTS = { | |
| 'size': 0, | |
| 'fzr': false, | |
| 'portrait': false, | |
| 'fitw': true, | |
| 'gridlines': false, | |
| 'printtitle': false, | |
| 'sheetnames': false, | |
| 'pagenum': 'CENTER', | |
| 'attachment': false | |
| }; | |
| const PDF_OPTS = objectToQueryString(PRINT_OPTS); | |
| /** | |
| * Run Main Setup | |
| */ | |
| function onOpen(e) { | |
| SpreadsheetApp.getUi().createMenu('Print') | |
| .addItem('Selected Range', 'printSelectedRange') | |
| .addItem('Sheet 1', 'printExample') | |
| .addToUi(); | |
| } | |
| /** | |
| * Print from selection range | |
| */ | |
| function printSelectedRange() { | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getActiveSheet(); | |
| var range = sheet.getActiveRange(); | |
| printBySheetRange(sheet.getName(), range.getA1Notation()); | |
| } | |
| /** | |
| * Print example Sheet 1 range A1:D45 | |
| */ | |
| function printExample() { | |
| printBySheetRange("Sheet 1", "A1:D45"); | |
| } | |
| /** | |
| * Print a sheet by sheetname and range | |
| * | |
| * @string Sheet Name | |
| * @string Print Range (A1 notation) | |
| * | |
| * @See https://stackoverflow.com/questions/58627501/how-to-print-sheet-range-using-gs-script-in-google-sheets | |
| */ | |
| function printBySheetRange(sheetName, sheetRange) { | |
| // Run pending operations | |
| SpreadsheetApp.flush(); | |
| // Find target sheet | |
| const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName); | |
| const sheetID = sheet.getSheetId(); | |
| // Find target range | |
| const range = sheet.getRange(sheetRange); | |
| var printRange = objectToQueryString({ | |
| 'c1': range.getColumn() - 1, | |
| 'r1': range.getRow() - 1, | |
| 'c2': range.getColumn() + range.getWidth() - 1, | |
| 'r2': range.getRow() + range.getHeight() - 1 | |
| }); | |
| // Export PDF | |
| const url = SpreadsheetApp.getActiveSpreadsheet().getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + sheetID; | |
| var htmlTemplate = HtmlService.createTemplateFromFile('print'); | |
| htmlTemplate.url = url; | |
| SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(25).setWidth(100), PRINT_HEADER); | |
| } | |
| /** | |
| * Turn a JSON-Object into a query string | |
| */ | |
| function objectToQueryString(obj) { | |
| return Object.keys(obj).map(function(key) { | |
| return Utilities.formatString('&%s=%s', key, obj[key]); | |
| }).join(''); | |
| } |
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
| <!DOCTYPE html> | |
| <html> | |
| <head> | |
| <base target="_top"> | |
| </head> | |
| <body> | |
| <script> | |
| window.open('<?=url?>', '_blank', 'width=950, height=750'); | |
| google.script.host.close(); | |
| </script> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment