Google apps script getAs('application/pdf') layout

Question

I am generating a PDF file from a spreadsheet using an app script I found here. This one uses the good old Method getAs('application/pdf') and works great.

The problem is that the PDF document generated in this way has unwanted asymmetric margins (larger on the right, narrow on the left). I just wanted the page to be centered. The weird thing is that when I print from the Google menu File -> Print (or Ctrl + P) the document appears centered correctly.

My code looks like this:

function CreaPDF() {
  //The function prints an invoice to PDF. First it copies spreadsheet to a new document.
  //Deletes all sheet except the one to print. Saves it to PDF. 
  //It overwrites any existing doc with same name.

  var sourceSpreadsheet = SpreadsheetApp.getActive();          
  var sheetName = "Factura";
  var folderID = getParentFolder(); // Folder id to save in a folder.
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID); 
  var numf = sourceSpreadsheet.getRangeByName("NumeroFactura").getValue();
  var anof = numf.split("/",2);   // Seeks number and year -> filename 

  var pdfName = anof[1] +"_Factura_" + anof[0]+ "_Dra_Salazar"; // Nombre del documento;

  //Copy whole spreadsheet 2 temporary sheet
  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

  //delete redundant sheets
  var sheets = destSpreadsheet.getSheets();
  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetName() != sheetName){
    destSpreadsheet.deleteSheet(sheets[i]);
    }
  }

  //Deletes pdf if already exists
  var files = DriveApp.getFilesByName(pdfName);
  while (files.hasNext()) {
   files.next().setTrashed(true);
   }
  var destSheet = destSpreadsheet.getSheets()[0];
  //repace cell values with text (to avoid broken references)
  var sourceRange = sourceSheet.getRange(1, 1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());

  var sourcevalues = sourceRange.getDisplayValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

  SpreadsheetApp.getActiveSpreadsheet().toast('Creando PDF');

  //save to pdf
  var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
  var newFile = folder.createFile(theBlob);

  //Delete the temporary sheet
  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
  return true;
}

My settings when I print are:

• Paper size: "A4"
• Scale: "Normal" (I also tried: "fit to width" and "fit on page")
• Orientation: "Portrait"
• Margins: "Normal"

As seen in the Google Help Forums it seems like an old issue with the way google prints. In summary, it seems that print settings are not saved and there is no way to pass any parameter to the getAs('application/pdf') method either. So i assume that the method (and menu print options) use default parameters that can not be modified. Any solution for this? The "Print or change page setup" help page does not help too much.

Thank you very much

Answer 1

Try this solution based on https://ctrlq.org/code/19869-email-google-spreadsheets-pdf

Using export url parameters you can set needed options for result pdf. Also you can set specific id of sheet to export, so you don't need to make duplicate of your whole spreadsheet anymore.

function CreaPDF() {
  //The function prints an invoice to PDF. First it copies spreadsheet to a new document.
  //Deletes all sheet except the one to print. Saves it to PDF. 
  //It overwrites any existing doc with same name.

  var sourceSpreadsheet = SpreadsheetApp.getActive();          
  var sheetName = "Factura";
  var folderID = getParentFolder(); // Folder id to save in a folder.
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var folder = DriveApp.getFolderById(folderID); 
  var numf = sourceSpreadsheet.getRangeByName("NumeroFactura").getValue();
  var anof = numf.split("/",2);   // Seeks number and year -> filename 

  var pdfName = anof[1] +"_Factura_" + anof[0]+ "_Dra_Salazar"; // Nombre del documento;

  SpreadsheetApp.getActiveSpreadsheet().toast('Creando PDF');

  // export url
  var url = 'https://docs.google.com/spreadsheets/d/'+sourceSpreadsheet.getId()+'/export?exportFormat=pdf&format=pdf' // export as pdf / csv / xls / xlsx
  + '&size=A4'                           // paper size legal / letter / A4
  + '&portrait=true'                     // orientation, false for landscape
  + '&fitw=false'                        // 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='+sourceSheet.getSheetId();    // the sheet's Id

  var token = ScriptApp.getOAuthToken();

  // request export url
  var response = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var theBlob = response.getBlob().setName(pdfName+'.pdf');

  // delete pdf if already exists
  var files = folder.getFilesByName(pdfName);
  while (files.hasNext())
  {
    files.next().setTrashed(true);
  }

  // create pdf
  var newFile = folder.createFile(theBlob);

  return true;
}

Answer 2

Motivated by Kos' soultion.

I tracked request that created by google spreadsheet Download as GUI with chrome developer Tools. When you click next in Print settings, chrome sents post request and gets pdf file.

Let's cut to the chase. Below is my code.

var sourceSpreadsheet = SpreadsheetApp.getActive();
var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId() + '/pdf?id=' + sourceSpreadsheet.getId()
var formData = {
    'a': 'false',
    'pc': '[null,null,null, ... SOME VERY LONG LIST OF NULLS AND NUMBERS ... 166]],[[0,5]]]],0]',
    'gf': '[]'
};
var token = ScriptApp.getOAuthToken(); // doesn't needed when sheet is public
var options = {
        'method': 'post',
        'payload': formData,
        'muteHttpExceptions': true,
        headers: {
            'Authorization': 'Bearer ' + token
        }
    };
var response = UrlFetchApp.fetch(url, options)

var theBlob = response.getBlob().setName('YourPDFName.pdf');

You can fill formData['pc'] by following:
1. Record requests at Network tab.
2. Click pdf?id=blahblah pic for step 2
3. Scroll down to find Form Data pic for step 3
4. Copy form data and fill your code.

Form data looks like:
[null,null,null,null,null,null,null,null,null,0,[["$Sheet_id"]],10000000,null,null,null,null,null,null,null,null,null,null,null,null,null,null,$day_after_30-12-1899,null,null,[$note,null,1,$note,0,0,0,0,$fixed_row,1,1,1,$headers,$footers,1,1],["A4",1,2,1,[0.75,0.75,0.7,0.7]],null,0,[["$Sheet_id",[[26,52]....],[[0,5]]]],0]
There are many more parameters but I can't find them all. Also parameters can be wrong as I found their value by checking changed value.

When you change $day_after_30-12-1899, date printed in pdf changes.

You can customize headers/footers. They are UTF16 encoded and has several control characters.

| Name in `Download as` GUI | character | 
|---------------------------|-----------|
| page number A             | \uee12    |
| page number B             | \uee15    |
| page number C             | \uee16    |
| Workbook title            | \uee10    |
| sheet name                | \uee11    |


|   meaning in date & time  | character | 
|---------------------------|-----------|
| date formatstr start      | \uee13    |
| year                      | yyyy      |
| month                     | M         |
| day                       | d         |
| date formatstr end        | \uee14    |
|                           |           |
| time formatstr start      | \uee17    |
| am or pm                  | am/pm     |
| hour in 12hour clock      | h         |
| minute                    | mm        |

you can use time and date like this YourText\uee13yy. M. d\uee14Yourtext

I'd recommend create formData['pc'] with GUI and modify $day_after_30-12-1899.

*Any corrections are welcome. While I'm not a native English speaker, there are many awkward/incorrect expressions.