This article shares with you a snippet of Apps script that could print the entire google sheets or selected area to a pdf file. You may ask “why not use the default feature File > Download as > PDF document”. Well, it’s an alternative to automate things, for example, you recorded a macro on an inventory spreadsheet to refresh stocks and make a snapshot everyday as a pdf file. This apps script may be helpful for your automation.

Before diving into the code, let’s take a look at the demo. It does two things. The demo file including the source code can be copied here. The script / add-on will save a PDF copy in the root of your Google Drive.

Print all Sheets to a PDF file

Print all sheets as a PDF file using apps script
Print all sheets as a PDF file using apps script

Print selected area (part of sheets) to a PDF file

This feature is complementary to Google Sheets’ default export feature. It gives you some flexibility to print only the area of interest for your client. Here is what you do.

  1. Select multiple ranges with the CTRL button pressed.
  2. Click the menu Add-ons > PDF Assistant > Export selected area.
  3. You will be given the link of PDF, which is saved in the root folder of your Drive.
Print selected area in Google Sheets as a PDF file
Print selected area in Google Sheets as a PDF file

Source code

Do you know how to use this piece of source code? If not, check out my another tutorial learning how to Add An Apps Script To Your Google Apps.

Feel free to leave comments below if you have questions, feature requests and bugs. Play fun!

/**
 * @license
 * The source code is subject to the terms of service in
 * https://xfanatical.com/terms-of-service/
 * 
 * © 2019 xfanatical.com. All Rights Reserved
 */

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Export all sheets', 'exportAsPDF')
    .addItem('Export selected area', 'exportPartAsPDF')
    .addToUi()
}

function exportAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var blob = spreadsheet.getAs('application/pdf').setName(spreadsheet.getName())
  var pdfFile = DriveApp.createFile(blob)
  
  // Display a modal dialog box with custom HtmlService content.
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open <a href="' + pdfFile.getUrl() + '" target="_blank">' + spreadsheet.getName() + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}

function exportPartAsPDF() {
  var ui = SpreadsheetApp.getUi()
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var activeRangeList = spreadsheet.getActiveRangeList()
  if (!activeRangeList) {
    ui.alert('Please select at least one range to export')
    return
  }
  
  var selectedRanges = activeRangeList.getRanges()
  
  var tempSpreadsheet = SpreadsheetApp.create(spreadsheet.getName() + '-selected')
  SpreadsheetApp.setActiveSpreadsheet(tempSpreadsheet)
  
  for (var i = 0; i < selectedRanges.length; i++) {
    var selectedRange = selectedRanges[i]
    var originalSheet = selectedRange.getSheet()
    var originalSheetName = originalSheet.getName()
    
    var destSheet = tempSpreadsheet.getSheetByName(originalSheetName)
    if (!destSheet) {
      destSheet = tempSpreadsheet.insertSheet(originalSheetName)
    }
    
    Logger.log('a1notation=' + selectedRange.getA1Notation())
    var destRange = destSheet.getRange(selectedRange.getA1Notation())
    destRange.setValues(selectedRange.getValues())
    destRange.setTextStyles(selectedRange.getTextStyles())
    destRange.setBackgrounds(selectedRange.getBackgrounds())
    destRange.setFontColors(selectedRange.getFontColors())
    destRange.setFontFamilies(selectedRange.getFontFamilies())
    destRange.setFontLines(selectedRange.getFontLines())
    destRange.setFontStyles(selectedRange.getFontStyles())
    destRange.setFontWeights(selectedRange.getFontWeights())
    destRange.setHorizontalAlignments(selectedRange.getHorizontalAlignments())
    destRange.setNumberFormats(selectedRange.getNumberFormats())
    destRange.setTextDirections(selectedRange.getTextDirections())
    destRange.setTextRotations(selectedRange.getTextRotations())
    destRange.setVerticalAlignments(selectedRange.getVerticalAlignments())
    destRange.setWrapStrategies(selectedRange.getWrapStrategies()) 
  }
  
  // remove empty Sheet1
  var sheet1 = tempSpreadsheet.getSheetByName('Sheet1')
  Logger.log('lastcol = ' + sheet1.getLastColumn() + ',lastrow=' + sheet1.getLastRow())
  if (sheet1 && sheet1.getLastColumn() === 0 && sheet1.getLastRow() === 0) {
    tempSpreadsheet.deleteSheet(sheet1)
  }
  
  exportAsPDF()
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet)
  DriveApp.getFileById(tempSpreadsheet.getId()).setTrashed(true)
}