Is it possible to add page breaks into Google Spreadsheet using Apps Script?

Question

I would like to programmatically set page breaks in my Google Spreadsheet before exporting to PDF, using Apps Script

It should be possible as you can manually set the page breaks when you print the Spreadsheet (https://support.google.com/docs/answer/7663148?hl=en)

I found that it's possible in Google Docs (https://developers.google.com/apps-script/reference/document/page-break) but they don't mention it on the sheet.

Is there a way to do it, even if it's a "hack"?

Answer 1

I use a work around. I adjust the page size by altering the row height to fit the paper size I want (A4). When exporting to pdf google changes sizes to fit the width. I add up the size of the columns and then set the row heights accordingly. Numbers were chosen by trial and error.

var width = 0;
for(var z = 0; z < s4.getLastColumn(); z++){
  width += s4.getColumnWidth(z+1);
}
var a4PageHeightPixels = 1050 * width / 800;

Because I wanted the rows all the same height I set the row height dividing my page height by the number of rows. Having ensured the last row was blank, I adjusted the last row to take up the rounding error.

  rowHeight= Math.floor(a4PageHeightPixels/(numDataRows ));
  lastRowHeight = a4PageHeightPixels - (numDataRows -1) * rowHeight;
  s4.setRowHeights(pageFirstRow,numDataRows-1,rowHeight);
  s4.setRowHeight(pageFirstRow+numDataRows-1,lastRowHeight);

(s4 is the sheet I am using)However, I would expect most people would simply want to insert a blank line at the bottom of each page and adjust its size to fit the pdf paper size.

Answer 2

Talking about "hacks", you may try to capture HTTP request sent from the Spreadsheet to Google when you are trying to save a sheet as PDF by going to the developer tools - Network.

From this link you can get formatting parameter pc, which in my case looks like this:

[null,null,null,null,null,null,null,null,null,0,
[["1990607563"]],
10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
43866.56179325232,
null,null,
[0,null,1,0,0,0,1,1,1,1,2,1,null,null,2,1],
["A4",0,6,1,[0.75,0.75,0.7,0.7]],
null,0,
[["1990607563",[[45,92],[139,139]],[[0,15]]]],0]

where:

[["1990607563",[[45,92],[139,139]],[[0,15]]]],0]  // page breaks parameters
  

Note though that I used custom page breaks and landscape orientation, which are reflected in the response above.

Putting it all together, the following code does the trick:

function exportPDFtoGDrive (ssID, filename, source){
  var source = "1990607563"
  var dt = new Date();
  var d = encodeDate(dt.getFullYear(),dt.getMonth(),dt.getDate(),dt.getHours(),dt.getMinutes(),dt.getSeconds());
  var pc = [null,null,null,null,null,null,null,null,null,0,
            [[source]],
            10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
            d,
            null,null,
            [0,null,1,0,0,0,1,1,1,1,2,1,null,null,2,1],
            ["A4",0,6,1,[0.75,0.75,0.7,0.7]],
            null,0,
            [[source,[[45,92],[139,139]],[[0,15]]]],0];
           
  
 var folder = DriveApp.getFoldersByName("FolderNameGoesHere").next(); 
  
  var options = {
    'method': 'post',
    'payload': "a=true&pc="+JSON.stringify(pc)+"&gf=[]",
    'headers': {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
      'muteHttpExceptions': true
};

const esid = (Math.round(Math.random()*10000000));
const theBlob = 
UrlFetchApp.fetch("https://docs.google.com/spreadsheets/d/"+ssID+"/pdf?id="+ssID+"&esid="+esid, options).getBlob();
folder.createFile(theBlob).setName(filename+".pdf");
}

function myExportPDFtoGDrive(){
  
  var ss = SpreadsheetApp.openById('yourSpreadSheetID');
  var sheet = ss.getSheetByName("NameGoesHere");

  var filename = ss.getName()+" ["+sheet.getName()+"]";
  exportPDFtoGDrive (ss.getId(),filename);
}

A more detailed explanation of the hack is available here Export Google Sheets to PDF though in Russian only.