Using script to export individual sheets inside a google sheet AND set page height

Question

I have a google sheet with 25 sheets/tabs inside it. I need to be able to export each one individually as a pdf and ideally rename them with the name of their individual sheet.

The following code works great except that my doc is just a few lines too long for a single page.

I have tried using the scale function and setting page margins and neither option changes my final exported file.

Is there a way to print to the height of my page or scale the exported pdf?

function exportSheetsAsPDFs() {
SpreadsheetApp.getActiveSpreadsheet().toast('Writing the reports.','Printing Reports');
 
var email = Session.getActiveUser().getEmail();
var TodayDate = new Date();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
 
// Show one sheet at a time, and print each one out as a PDF
for (var i = 0; i < sheets.length; i++) {
sheets[i].showSheet();
 
// Hide all sheets except for our currently active one.
for (var j = 0; j < sheets.length; j++) {
if (j != i) {
sheets[j].hideSheet();
}
}
 
// Ensure all hide / show changes are applied
SpreadsheetApp.flush();
   
// Export PDF
DriveApp.createFile(ss.getBlob().getAs('application/pdf').setName("My File Name - " + sheets[i].getName() + '.pdf'));
}
 
// Re-show all sheets
for (var i = 0; i < sheets.length; i++) {
sheets[i].showSheet();
}
SpreadsheetApp.getActiveSpreadsheet().toast('Reports all written.','Printing Complete');
}

NightEye · Accepted Answer · 2021-06-23T21:04:38.720

I recommend you export your sheet data using UrlFetchApp.fetch and passing some url parameters as it enables you to modify your pdf before creating it in the drive.

Just fiddle with the url parameters below and see what you need. There should be ample description on what every parameter does. Depending on your chosen parameters, you can do the following to address your issue:

Change pdf size (change to a longer size, a3, a4, etc)
Adjust margins on your pdf (you can reduce both top and bottom margins if it solves your issue)
Adjust the scale of your data in the pdf (scale to height, width, page or normal, depending on your needs)

Code:

function exportSheetsAsPDFs() {
  SpreadsheetApp.getActiveSpreadsheet().toast('Writing the reports.', 'Printing Reports');

  var email = Session.getActiveUser().getEmail();
  var TodayDate = new Date();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();

  // Show one sheet at a time, and print each one out as a PDF
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet();

    // Hide all sheets except for our currently active one.
    for (var j = 0; j < sheets.length; j++) {
      if (j != i) {
        sheets[j].hideSheet();
      }
    }

    // Ensure all hide / show changes are applied
    SpreadsheetApp.flush();

    // Exporting PDF (using getBlob function below)
    var theBlob = getBlob(sheets[i]);
    DriveApp.createFile(theBlob).setName("My File Name - " + sheets[i].getName() + ".pdf");
  }

  // Re-show all sheets
  for (var i = 0; i < sheets.length; i++) {
    sheets[i].showSheet();
  }
  SpreadsheetApp.getActiveSpreadsheet().toast('Reports all written.', 'Printing Complete');
}

function getBlob(sheet) {
  var url = 'https://docs.google.com/spreadsheets/d/';
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var id = spreadsheet.getId();
  var gid = sheet.getSheetId();
  var url_ext = '/export?'
    + 'format=pdf'
    + '&size=a4'                      //A3/A4/A5/B4/B5/letter/tabloid/legal/statement/executive/folio
    + '&portrait=true'                //true= Potrait / false= Landscape
    + '&scale=3'                      //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=true'               //true/false
    + '&printnotes=false'             //true/false
    + '&pageorder=2'                  //1= Down, then over / 2= Over, then down
    + '&horizontal_alignment=LEFT'    //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'
    + '&gid='
    + gid;

  var token = ScriptApp.getOAuthToken();
  var params = {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  };

  var blob = UrlFetchApp.fetch(url + id + url_ext, params).getBlob().getAs('application/pdf');
  return blob;
}

Fiddling with scale parameter

Scale = 1 (Normal 100%):

Normal scale resulted into overflow

Scale = 3 (Fit to Height):

Note:

Due to the structure of my sample data, Fit to Height (3) and Fit to Page (4) yields the same format.
Notice the difference of width? That's due to fitting it to height.
Fiddle with the parameters to see and find what you need.