Exporting Google Sheet as PDF with Custom Headers or Footers in Google Apps Script

13
votes

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

1
pdfgoogle-apps-scriptgoogle-sheetspdf-generation
I've not seen that option: these are the ones I've found so far: gist.github.com/andrewroberts/c37d45619d5661cab078be2a3f2fd2bb – Andrew Roberts

1 Answers

1
votes

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