How to Print Google Sheet to PDF Using Apps Script

Introduction

In today’s fast-paced, data-driven world, automating repetitive tasks is no longer just a convenience—it’s a necessity for improving productivity. To illustrate, one such common task is exporting Google Sheets to PDF format for sharing, archiving, or reporting purposes. Although Google Sheets provides a built-in option to download as a PDF (File > Download > PDF Document), unfortunately, this method often falls short, especially when it comes to flexibility and automation capabilities.

Fortunately, that’s where Google Apps Script steps in. With this powerful tool, you can not only automate the process of converting your Google Spreadsheet to a PDF file, but also customize it based on your needs. Whether it’s the entire document, individual sheets, selected ranges, or even predefined areas, Apps Script offers the versatility and control that manual exporting simply can't match. As a result, you can save time, reduce manual errors, and ensure consistency across your reports—making your workflow more efficient and scalable.
In this article, we'll walk you through how to use Apps Script to export a Google Sheet to PDF, along with practical use cases, code examples, and best practices.

What is Apps Script?

Google Apps Script is a powerful scripting language based on JavaScript that lets you customize and automate Google Workspace apps like Google Sheets, Docs, Forms, and more. It enables you to write scripts that perform custom tasks such as exporting sheets, sending emails, and interacting with Google Drive.

Why Use Apps Script to Export Sheets to PDF?

• Efficiency: Save time and reduce manual errors.
• Automation: Automatically generate PDFs daily, weekly, or on specific triggers.
• Customization: Print specific sheets, selected areas, or named ranges.

Use Cases of Google Sheets Script Print to PDF

• Daily reporting dashboards.
• Inventory snapshots.
• Invoice generation.
• Client-specific data sharing.

Before diving into the source code, let's take a look at demos (Demo Sheet). The script / add-on will save a PDF copy or multiple PDF copies in the root of your Google Drive depending on your printing choices.

Print all sheets to a PDF file

Print all sheets as separate PDF files

The menu Add-ons > PDF Assistant > Export all sheets as separate files iterate your spreadsheet and save each sheet as a PDF file in your Drive root folder.

Print all sheets as separate PDF files

Print current sheet as a PDF file

1. Switch to the sheet to print
2. Click Add-ons > PDF Assistant > Export current sheet.
3. The PDF file will be generated in your Drive root folder, in the name of the sheet.

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 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.

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.

/**
 * @license MIT
 * 
 * © 2020 xfanatical.com. All Rights Reserved.
 * 
 * @since 1.3.4 Add retrying for printing many sheets
 * @since 1.3.3 Add an option to save PDF files in the same folder
 * @since 1.3.2 Fix time changed issue
 * @since 1.3.1 Fix a landscape problem
 * @since 1.3.0 Support printing the entire spreadsheet as separate pdf files
 * @since 1.2.3 Add formatting for a single predefined area
 * @since 1.2.2 Add formatting for a single selected area
 * @since 1.2.1 Fix a reference issue of printing current sheet
 * @since 1.2.0 Support printing current sheet as pdf
 * @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
 */

// By default, PDFs are saved in your Drive Root folder
// To save in the same folder as the spreadsheet, change the value to 'false' without the single quote pair
// You must have EDIT permission to the same folder
var saveToRootFolder = true

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Export all sheets', 'exportAsPDF')
    .addItem('Export all sheets as separate files', 'exportAllSheetsAsSeparatePDFs')
    .addItem('Export current sheet', 'exportCurrentSheetAsPDF')
    .addItem('Export selected area', 'exportPartAsPDF')
    .addItem('Export predefined area', 'exportNamedRangesAsPDF')
    .addToUi()
}

function _exportBlob(blob, fileName, spreadsheet) {
  blob = blob.setName(fileName)
  var folder = saveToRootFolder ? DriveApp : DriveApp.getFileById(spreadsheet.getId()).getParents().next()
  var pdfFile = folder.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">' + fileName + '</a></p>')
    .setWidth(300)
    .setHeight(80)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}

function exportAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var blob = _getAsBlob(spreadsheet.getUrl())
  _exportBlob(blob, spreadsheet.getName(), spreadsheet)
}

function _getAsBlob(url, sheet, range) {
  var rangeParam = ''
  var sheetParam = ''
  if (range) {
    rangeParam =
      '&r1=' + (range.getRow() - 1)
      + '&r2=' + range.getLastRow()
      + '&c1=' + (range.getColumn() - 1)
      + '&c2=' + range.getLastColumn()
  }
  if (sheet) {
    sheetParam = '&gid=' + sheet.getSheetId()
  }
  // A credit to https://gist.github.com/Spencer-Easton/78f9867a691e549c9c70
  // these parameters are reverse-engineered (not officially documented by Google)
  // they may break overtime.
  var exportUrl = url.replace(/\/edit.*$/, '')
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=0.75'              
      + '&bottom_margin=0.75'          
      + '&left_margin=0.7'             
      + '&right_margin=0.7'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=UNDEFINED' // change it to CENTER to print page numbers
      + '&gridlines=true'
      + '&fzr=FALSE'      
      + sheetParam
      + rangeParam
      
  Logger.log('exportUrl=' + exportUrl)
  var response
  var i = 0
  for (; i < 5; i += 1) {
    response = UrlFetchApp.fetch(exportUrl, {
      muteHttpExceptions: true,
      headers: { 
        Authorization: 'Bearer ' +  ScriptApp.getOAuthToken(),
      },
    })
    if (response.getResponseCode() === 429) {
      // printing too fast, retrying
      Utilities.sleep(3000)
    } else {
      break
    }
  }
  
  if (i === 5) {
    throw new Error('Printing failed. Too many sheets to print.')
  }
  
  return response.getBlob()
}

function exportAllSheetsAsSeparatePDFs() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var files = []
  var folder = saveToRootFolder ? DriveApp : DriveApp.getFileById(spreadsheet.getId()).getParents().next()
  spreadsheet.getSheets().forEach(function (sheet) {
    spreadsheet.setActiveSheet(sheet)
    
    var blob = _getAsBlob(spreadsheet.getUrl(), sheet)
    var fileName = sheet.getName()
    blob = blob.setName(fileName)
    var pdfFile = folder.createFile(blob)
    
    files.push({
      url: pdfFile.getUrl(),
      name: fileName,
    })
  })
  
  const htmlOutput = HtmlService
    .createHtmlOutput('<p>Click to open PDF files</p>'
      + '<ul>'
      + files.reduce(function (prev, file) {
        prev += '<li><a href="' + file.url + '" target="_blank">' + file.name + '</a></li>'
        return prev
      }, '')
      + '</ul>')
    .setWidth(300)
    .setHeight(150)
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Export Successful')
}

function exportCurrentSheetAsPDF() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var currentSheet = SpreadsheetApp.getActiveSheet()
  
  var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet)
  _exportBlob(blob, currentSheet.getName(), spreadsheet)
}

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'
  }
  
  if (selectedRanges.length === 1) {
    // special export with formatting
    var currentSheet = selectedRanges[0].getSheet()
    var blob = _getAsBlob(spreadsheet.getUrl(), currentSheet, selectedRanges[0])
    
    var fileName = spreadsheet.getName() + fileSuffix
    _exportBlob(blob, fileName, spreadsheet)
    return
  }
  
  var tempSpreadsheet = SpreadsheetApp.create(spreadsheet.getName() + fileSuffix)
  if (!saveToRootFolder) {
    DriveApp.getFileById(tempSpreadsheet.getId()).moveTo(DriveApp.getFileById(spreadsheet.getId()).getParents().next())
  }
  var tempSheets = tempSpreadsheet.getSheets()
  var sheet1 = tempSheets.length > 0 ? tempSheets[0] : undefined
  SpreadsheetApp.setActiveSpreadsheet(tempSpreadsheet)
  tempSpreadsheet.setSpreadsheetTimeZone(spreadsheet.getSpreadsheetTimeZone())
  tempSpreadsheet.setSpreadsheetLocale(spreadsheet.getSpreadsheetLocale())
  
  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)
  }
}