13

I'm wondering how to add custom headers or footers to a PDF that is exported using Google Apps Script from a Google Sheet. I would like to add a footer that says "My Company Proprietary and Confidential" in the center of the page.

This is functionality that is available in normal PDF export from Sheets (i.e. File » Download as » PDF » Headers & footers » EDIT CUSTOM FIELDS), but I don't know how to replicate it with URL parameters. I've also tried setting '&sheetnames=true&printtitle=true', but this puts the sheet name and spreadsheet name in the header instead of the footer.

Are there any additional URL parameters I can use to control these custom fields?

  var url = 'https://docs.google.com/spreadsheets/d/'+sourceSpreadSheet.getId()
  +'/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
  + '&size=letter'                           // paper size legal / letter / A4
  + '&portrait=false'                     // orientation, false for landscape
  + '&fitw=true'                        // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenum=CENTER&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid='+sourceSheet.getSheetId()    // the sheet's Id
  + '&top_margin=0.45&left_margin=0.25&right_margin=0.25&bottom_margin=0.5'; // margins

Target Footer:

footer screenshot

Oleg Valter is with Ukraine
  • 8,117
  • 7
  • 30
  • 49
Colin Fennern
  • 131
  • 1
  • 5
  • 3
    I've not seen that option: these are the ones I've found so far: https://gist.github.com/andrewroberts/c37d45619d5661cab078be2a3f2fd2bb – Andrew Roberts Apr 22 '20 at 10:52

1 Answers1

1

I think the workaround would be:

  1. create a new document
  2. Insert your header
  3. Copy the contents of your spreadsheet
  4. Insert your footer
  5. Export the new file as PDF
  6. Trash the new file
Dmitry Kostyuk
  • 1,261
  • 1
  • 4
  • 19