pdf - 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

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.