How to Print sheet/range using .gs script in Google Sheets?

by

You can use the following script:

var PRINT_OPTIONS = {
  'size': 7,               // paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B
  'fzr': false,            // repeat row headers
  'portrait': true,        // false=landscape
  'fitw': true,            // fit window or actual size
  'gridlines': false,      // show gridlines
  'printtitle': false,
  'sheetnames': false,
  'pagenum': 'UNDEFINED',  // CENTER = show page numbers / UNDEFINED = do not show
  'attachment': false
}

var PDF_OPTS = objectToQueryString(PRINT_OPTIONS);

function onOpen(e) {
  SpreadsheetApp.getUi().createMenu('Print...').addItem('Print selected range', 'printSelectedRange').addToUi();
}

function printSelectedRange() {
  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getActiveRange();

  var gid = sheet.getSheetId();
  var printRange = objectToQueryString({
    'c1': range.getColumn() - 1,
    'r1': range.getRow() - 1,
    'c2': range.getColumn() + range.getWidth() - 1,
    'r2': range.getRow() + range.getHeight() - 1
  });
  var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + PDF_OPTS + printRange + "&gid=" + gid;

  var htmlTemplate = HtmlService.createTemplateFromFile('js');
  htmlTemplate.url = url;
  SpreadsheetApp.getUi().showModalDialog(htmlTemplate.evaluate().setHeight(10).setWidth(100), 'Print range');
}

function objectToQueryString(obj) {
  return Object.keys(obj).map(function(key) {
    return Utilities.formatString('&%s=%s', key, obj[key]);
  }).join('');
}

You will also need to create an html file in your project (File>New>HTML File) with the name js, and paste in the following code:

<script>
  window.open('<?=url?>', '_blank', 'width=800, height=600');
  google.script.host.close();
</script>

This will create a button in your Sheets menu that will open a PDF with the selected range. You can modify some settings such as the print orientation, its size, or whether to show the gridlines or not on top of the script. If you still want to automatically print the ranges without having to manually go through the print dialog, you can either:

More Related Contents:

  1. Google Sheet API v4
  2. How to create a runing total formula that expands automatically when new rows are added to my spreadsheet [duplicate]
  3. Conditional formatting based on another cell’s value
  4. Google Sheets importXML Returns Empty Value
  5. Get the last non-empty cell in a column in Google Sheets
  6. How to SUM duration in Google Sheets?
  7. Conditional Formatting from another sheet
  8. Google Sheets API returns “The caller does not have permission” when using server key
  9. Google Sheet API V4(Java) append Date in cells
  10. How do I access (read, write) Google Sheets spreadsheets with Python?
  11. Copying a spreadsheet copies all linked files as well
  12. New Google Spreadsheets publish limitation
  13. ArrayFormula and “AND” Formula in Google Sheets
  14. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  15. GAS “On edit” trigger not running when edited from API
  16. I get Formula parse error with all formulas
  17. Generate all possible combinations for Columns(cross join or Cartesian product)
  18. Download link for Google Spreadsheets CSV export – with Multiple Sheets
  19. IMPORTXML and right XPath from Bloomberg price [duplicate]
  20. What factors determine the memory used in lambda functions?
  21. How to list all permutations without repetition?
  22. How to use arrayformula with formulas that do not seem to support arrayformulas?
  23. email notification if cell is changed
  24. Repeat each row N times in Google Sheets
  25. How to create all possible pair combinations without duplicates in Google Sheets?
  26. Col BY causing parse error in Google sheets query
  27. Google Apps Script for Multiple Find and Replace in Google Sheets
  28. How to make a range repeat n-times in Google SpreadSheet
  29. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  30. Google Sheets QUERY Function: Select Columns by Name

Leave a Comment