/exporting-google-sheets-pdfs

An example of how to export a PDF from a Google Sheet with Google App Script. Including various print parameters in the URL string.

Primary LanguageJavaScript

Exporting PDFs with Google App Script

Introduction

The Code.gs file shows some examples of how to set some print parameters for exporting PDFs from a Google Sheet using Google App Script.

PDF Parameters

The exportPDF function in this example demonstrates one method of constructing URL string parameters for PDF export. The parameters are simpler to understand if you compare them with the GUI when exporting a PDF from Google Sheets. Here's some extra guidance...

  • gid - Omit this to export all visible sheets. Add the sheet ID to export a single sheet.
  • format - Filetype. PDF in our case.
  • size - Paper size:
    • 0 (Letter)
    • 1 (Tabloid)
    • 2 (Legal)
    • 3 (Statement)
    • 4 (Executive)
    • 5 (Folio)
    • 6 (A3)
    • 7 (A4)
    • 8 (A5)
    • 9 (B4)
    • 10 (B5)
  • portrait - Page orientation (portrait true/false, don't use landscape)
  • scale - Scale data to fit. (Integer value 1-4. 1 = Normal (100%), 2 = Fit to Width, 3 = Fit to Height, 4 = Fit to Page. I haven't identified how to custom scale.)
  • top_margin - Set margin in inches. Same of course for bottom_, left_ and right_. Only seems to work if all four margins are present.
  • gridlines - Formatting > Show grid lines. (true/false)
  • printnotes - Formatting > Show notes. (true/false)
  • If anyone can add the parameters for Page order, that would be really helpful.
  • horizontal_alignment - Formatting > Alignment > Horizontal. (LEFT/CENTER/RIGHT)
  • vertical_alignment - Formatting > Alignment > Vertical. (TOP/MIDDLE/BOTTOM)
  • pagenum - Headers & Footers > Page Numbers. Use UNDEFINED to hide page numbers. (LEFT/CENTER/RIGHT/UNDEFINED)
  • printtitle - Headers & Footers > Workbook title. (true/false)
  • sheetnames - Headers & Footers > Sheet name. (true/false)
  • fzr / fzc - Repeat frozen rows/columns on new pages. (true/false)
  • If anyone can add the parameters for Current date, Current time or even Custom fields, that would be really helpful.
  • r1, c1 - Cell range to print, start row/column
  • r2, c2 - Cell range to print, end row/column

Emailing and saving a PDF

I've included an example of how to email the PDF files and how to save them to a Google Drive. You will need to include https://www.googleapis.com/auth/spreadsheets in your script scopes.

Hope it helps!

Multiple Sheets

If you do not specify a sheet ID, each visible sheet will be included in the PDF starting a new page.

Subset of Sheets as Pages

You can leverage the fact that only visible sheets will be exported to select a specific subset of sheets for export in your script. Hide the sheets you don't want in the export, then show them again after export.