Export a Google sheet as PDF setting parameters such as margin

Question

I am trying to export a specific sheet in my spreadsheet to a PDF file, the file will be set a name based on cell values and I want to set export settings so that all margins are set as 0

I got the below code online and have modified it to suit my needs but the export doesn't bring through the file name as I would like and it doesn't set the export margins to 0 and fit to page:

Has anyone got any ideas of how I can do this through apps script (if someone has a better script i'm open to trying that :) )

function exportPDF() { 
  var sourceSpreadsheet = SpreadsheetApp.getActive();

  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName("Agreement");

  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Agreement");
  var sheet = ss.getSheetByName("Input");
  var name = sheet.getRange("C3").getValue();
  var company = sheet.getRange("C4").getValue();
  var order = sheet.getRange("C11").getValue();
  var gAcc = sheet.getRange("C12").getValue();
  var pdfName = "Agreement_" & name;

  if(company != "")
  {
    pdfName = pdfName + "_" & company & "_" & order & "_" & gAcc;
  }
  else
  {
    pdfName = pdfName + "_" & order & "_" & gAcc;
  }

  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  if (parents.hasNext()) {
    var folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))

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

  var destSheet = destSpreadsheet.getSheets()[0];

  var sourceRange = sourceSheet.getRange("A1:I57");
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
  destRange.setValues(sourcevalues);

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

  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

Answer 1

The filename is achieved by rearranging your code, and properly concatenating some strings.

I have modified your script in several places, as follows:

function exportPDF() { 

  var sourceSpreadsheet = SpreadsheetApp.getActive();

  var sheets = sourceSpreadsheet.getSheets();
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var sourceSheet = sourceSpreadsheet.getSheetByName("Agreement");

  var name = sourceSheet.getRange("C3").getValue();
  var company = sourceSheet.getRange("C4").getValue();
  var order = sourceSheet.getRange("C11").getValue();
  var gAcc = sourceSheet.getRange("C12").getValue();
  var pdfName = "Agreement_" + name;

  if(company != "")
  {
    pdfName = pdfName + "_" + company + "_" + order + "_" + gAcc;
  }
  else
  {
    pdfName = pdfName + "_" + order + "_" + gAcc;
  }

  var parents = DriveApp.getFileById(sourceSpreadsheet.getId()).getParents();
  var folder;
  if (parents.hasNext()) {
     folder = parents.next();
  }
  else {
    folder = DriveApp.getRootFolder();
  }

  var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy(pdfName, folder))

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

  var destSheet = destSpreadsheet.getSheets()[0];

  var sourceRange = sourceSheet.getDataRange();
  var sourcevalues = sourceRange.getValues();
  var destRange = destSheet.setActiveSelection(sourceRange.getA1Notation());
  destRange.setValues(sourcevalues);

  var theBlob = getBlob();
  var newFile = folder.createFile(theBlob).setName(pdfName);

  DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);
}

Up to here you have created a pdf with the data that you want, and the name you want. But the margins are still not what you want.

---

Setting the pdf margins:

Get the blob using UrlFetchApp

function getBlob(){
  var url = 'https://docs.google.com/spreadsheets/d/';
  var id = '<YOUR-FILE-ID>';
  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=1'                      //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=0';
  // console.log(url+id+url_ext);
  var blob = UrlFetchApp.fetch(url+id+url_ext).getBlob().getAs('application/pdf');
  return blob;
}

---

There you have it. A pdf without margins exported from a sheet.

Note: To avoid authenticating when using UrlFetchApp you can make your sheet public

Answer 2

I have been looking for a long time on how to get the scale factor aka zoom (in 5th scale mode, i.e. custom in the GUI).

spct (scale percent, e.g. 1.3 for 130%) which must be used in conjunction with scale=5 parameter.

Looking like ...&scale=5&spct=1.3&...