CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetspdfurlquery-string
Andrew
Andrew

Reputation: 129

Margins parameters for Google spreadsheet export as PDF

I need the margins parameter to export my spreadsheet as a pdf.

I've searched for information about setting the margins parameter and can't find anything. The following parameters are working well.

var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=A4'                       // paper size legal / letter / A4
  + '&portrait=false'                    // orientation, false for landscape
  + '&fitw=true&source=labnol'           // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid=';                             // the sheet's Id

Upvotes: 13

Views: 15381

Answers (2)

MrtJa
MrtJa

Reputation: 585

Actually, it's possible to set the margins and create a PDF. You just need to know the names of the parameters and their possible values. I've have got it to work with these

Parameters:

&format=pdf                   //export format
&size=a4                      //A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
&portrait=false               //true= Potrait / false= Landscape
&scale=1                      //1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
&top_margin=0.00              //All four margins must be set!
&bottom_margin=0.00           //All four margins must be set!
&left_margin=0.00             //All four margins must be set!
&right_margin=0.00            //All four margins must be set!
&gridlines=false              //true/false
&printnotes=false             //true/false
&pageorder=2                  //1= Down, then over / 2= Over, then down
&horizontal_alignment=CENTER  //LEFT/CENTER/RIGHT
&vertical_alignment=TOP       //TOP/MIDDLE/BOTTOM
&printtitle=false             //true/false
&sheetnames=false             //true/false
&fzr=false                    //true/false
&fzc=false                    //true/false
&attachment=false             //true/false

As you can see there are more parameters for PDF formatting than currently known circulating around the Internet. Yes, it seems like they are not documented by Google.

If PDF export fails, it's probably an incorrect value. Some parameters do not have an effect on the Export. For example: your are using source=labnol, this is not a "real" parameter but it doesn't cause any trouble.

Make sure you have all four margin parameters inside your url like this:

&top_margin=0.00&bottom_margin=0.00&left_margin=0.00&right_margin=0.00

Otherwise it won't create the PDF.

Upvotes: 57

Jack
Jack

Reputation: 41

Just to add that it is possible to download a range as a PDF directly through URL options. You don't have to create a temporary sheet.

The URL options seem to be:

&r1 // first row to print: 0-indexed 
&r2 // last row to print: 1-indexed 
    // this seems inconsistent; maybe it is technically the first row that is 
    // not printed, 0-indexed

&c1 // first column to print: 0-indexed 
&c2 // last column to print: 1-indexed (see above)

These URL options are included in the code on this page

Upvotes: 2

Related Questions