How do I set the pdf size as A4 in the following script? Also, is it possible to fit the sheet into a single pdf page?

function sendSheet() {
  var message = {
    to: "EMAIL",
    subject: "SUBJECT",
    body: "BODY",
    attachments: [SpreadsheetApp.openByUrl("URL").getAs(MimeType.PDF).setName("NAME.pdf")]
  }
  MailApp.sendEmail(message);

CodePudding user response：

You can resize the PDF in the sheets url parameters like this.

Script:

function sendSheet() {
  EMAIL = '<EMAIL>'
  URL = 'https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>'
        '/export?'
        'format=pdf'
        '&size=A4' // size, (letter, legal, A4)
        '&gid=0'; // sheetID

  // dummy call to get document access
  SpreadsheetApp.getActive();

  var response = UrlFetchApp.fetch(URL, {
    headers: {
      'Authorization': 'Bearer '   ScriptApp.getOAuthToken()
    }
  });

  var message = {
    to: EMAIL,
    subject: "SUBJECT",
    body: "BODY",
    attachments: [response.getBlob().setName('NAME.pdf')]
  }

  MailApp.sendEmail(message);
}

There seems to be an option where you can "fit" your data into just a single page, but it will scale down your data instead (including the width).

The page size retains, but it will scale down the data into 1 page if exceeds a single page. See sample pdf with expected 48 pages:

scale=1 parameter:

scale=3 (fit to height) or scale=4 (fit to page) parameter:

As you can see, it is unreadable when dealing with large number of pages and trying to fit into 1.

I assume what you want is a single long page in your PDF containing your data in a continuous matter which is technically impossible since you can't modify its page height (only the size in totality)

UPDATE:

I found this comprehensive list of parameters here, feel free to test them and see if you might need something from the list below.

//All requests must include id in the path and a format parameter
//https://docs.google.com/spreadsheets/d/{SpreadsheetId}/export

//FORMATS WITH NO ADDITIONAL OPTIONS
//format=xlsx //excel
//format=ods //Open Document Spreadsheet
//format=zip //html zipped

//CSV,TSV OPTIONS***********
//format=csv // comma seperated values
// tsv // tab seperated values
//gid=sheetId // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID

// PDF OPTIONS****************
//format=pdf
//size=0,1,2..10 paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5
//fzr=true/false repeat row headers
//fzc=true/false repeat column headers
//portrait=true/false false = landscape
//fitw=true/false fit window or actual size
//gridlines=true/false
//printtitle=true/false
//pagenum=CENTER/UNDEFINED CENTER = show page numbers / UNDEFINED = do not show
//attachment = true/false dunno? Leave this as true
//gid=sheetId Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. Leave this off for all sheets.
//printnotes=false Set to false if you don't want to export the notes embedded in a sheet
//top_margin=[number] Margins - you need to put all four in order fir it to works, and they have to be to
//left_margin=[number] 2DP. So 0.00 for zero margin.
//right_margin=[number]
//bottom_margin=[number]
//horizontal_alignment=CENTER Horizontal Alignment: LEFT/CENTER/RIGHT
//vertical_alignment=TOP Vertical Alignment: TOP/MIDDLE/BOTTOM
//scale=1/2/3/4 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
//pageorder=1/2 1= Down, then over / 2= Over, then down
//sheetnames=true/false
//range=[NamedRange] Named ranges supported - see below

// EXPORT RANGE OPTIONS FOR PDF
//need all the below to export a range
//gid=sheetId must be included. The first sheet will be 0. others will have a uniqe ID
//ir=false seems to be always false
//ic=false same as ir
//r1=Start Row number - 1 row 1 would be 0 , row 15 wold be 14
//c1=Start Column number - 1 column 1 would be 0, column 8 would be 7
//r2=End Row number
//c2=End Column number