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. The file name comes with a ‘-selected’ suffix.
Print selected area in Google Sheets as a PDF file
Print selected area in Google Sheets as a PDF file

Print predefined areas to a PDF file

We also want to automate as much as possible. In version 1.1.0, the script supports exporting predefined areas to a PDF file. Follow these steps.

  1. Click the menu Data > Named ranges. A side panel will open.
  2. In the side panel, add all named ranges you want to print in a name pattern of print_area_{n}, where {n} is a number.
  3. Now click the menu Add-ons > PDF Assistant > Export predefined area. Like the result above, you will be prompted a link to the generated PDF file. The file name will come with a ‘-predefined’ suffix.
Print a predefined area or areas in Google Sheets as a PDF file
Print a predefined area or areas 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
 * 
 * @since 1.1.1 Fix an error for multi-language
 * @since 1.1.0 Support printing predefined areas as a pdf
 * @since 1.0.0 Support printing the entire spreadsheet as a pdf
 *              Support printing the selected areas as a pdf
 */

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Export all sheets', 'exportAsPDF')
    .addItem('Export selected area', 'exportPartAsPDF')
    .addItem('Export predefined area', 'exportNamedRangesAsPDF')
    .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(predefinedRanges) {
  var ui = SpreadsheetApp.getUi()
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  
  var selectedRanges
  var fileSuffix
  if (predefinedRanges) {
    selectedRanges = predefinedRanges
    fileSuffix = '-predefined'
  } else {
    var activeRangeList = spreadsheet.getActiveRangeList()
    if (!activeRangeList) {
      ui.alert('Please select at least one range to export')
      return
    }
    selectedRanges = activeRangeList.getRanges()
    fileSuffix = '-selected'
  }
  
  var tempSpreadsheet = SpreadsheetApp.create(spreadsheet.getName() + fileSuffix)
  var tempSheets = tempSpreadsheet.getSheets()
  var sheet1 = tempSheets.length > 0 ? tempSheets[0] : undefined
  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
  if (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)
}

function exportNamedRangesAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var allNamedRanges = spreadsheet.getNamedRanges()
  var toPrintNamedRanges = []
  for (var i = 0; i < allNamedRanges.length; i++) {
    var namedRange = allNamedRanges[i]
    if (/^print_area_.*$/.test(namedRange.getName())) {
      Logger.log('found named range ' + namedRange.getName())
      toPrintNamedRanges.push(namedRange.getRange())
    }
  }
  if (toPrintNamedRanges.length === 0) {
    SpreadsheetApp.getUi().alert('No print areas found. Please add at least one \'print_area_1\' named range in the menu Data > Named ranges.')
    return
  } else {
    toPrintNamedRanges.sort(function (a, b) {
      return a.getSheet().getIndex() - b.getSheet().getIndex()
    })
    exportPartAsPDF(toPrintNamedRanges)
  }
}