How to adjust as custom size margins and paper size in script to save google spreadsheet in PDF?

Question

I have a script that saves my spreadsheet in pdf, but I can't edit the margins and paper size I want.

function Testando() {
  var spreadsheet = SpreadsheetApp.getActive();

SpreadsheetApp.flush();
      //make pdf
      var theurl = 'https://docs.google.com/a/mydomain.org/spreadsheets/d/'
      + 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX'  //the file ID
      + '/export?exportFormat=pdf&format=pdf'
      + '&size=LETTER'
      + '&portrait=true'
      + '&fitw=true'       
      + '&top_margin=0.50'              
      + '&bottom_margin=0.50'          
      + '&left_margin=0.50'             
      + '&right_margin=0.50'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=false'
      + '&gridlines=false'
      + '&fzr=FALSE'      
      + '&gid='
      + 'XXXXXXXXXXXXXXXXXXXXXX';       //the sheet's Id

      var token = ScriptApp.getOAuthToken();

      var docurl = UrlFetchApp.fetch(theurl, { headers: { 'Authorization': 'Bearer ' +  token } });
      var fileid = DriveApp.createFile(docurl.getBlob()).setName('Teste.pdf').getId();

      var pdf = docurl.getBlob().setName('Teste.pdf');
    //  var pdf = docurl.getBlob().getAs('application/pdf').setName('testss.pdf'); 
     var filetodel = DriveApp.getFileById(fileid);
     DriveApp.getRootFolder().createFolder("Teste");  //comment if folder exists
     // if folder exists use next 
  if (DriveApp.getFoldersByName("Teste").hasNext()){
    var folder = DriveApp.getFoldersByName("Teste").next();
    filetodel.makeCopy(folder);
   }
   DriveApp.removeFile(filetodel);
}

I need the PDF to have the custom paper size for → Height: 38 centimeters and Width: 40 centimeters

Page orientation → Landscape

Scale → Fit Width

Margins → Custom Numbers: 0 Left, 0 Right, 0 Start and 0 End.

Could someone help me please?

Answer 1

This is in response to your request:

By the way, would you be able to help me edit the script so that the name of the file saved in PDF equals the value found in Cell "B5" of the "WORK" page of my spreadsheet?

You can fetch the name of the file form the sheet using this line

  // Get filename from sheet "Work", cell "B5"
  var fileName = spreadsheet.getSheetByName("WORK").getRange("B5").getValue();

And then set the name of the new file by using

.setName(fileName); 

There were a few other lines where the code could be optimised. I have added comments to explain. Hope this helps:

function Testando() {
  var spreadsheet = SpreadsheetApp.getActive();
  SpreadsheetApp.flush();

  //make pdf
  var theurl = 'https://docs.google.com/a/mydomain.org/spreadsheets/d/' + // Best to place the line break after '+'
    'XXXXXXXXXXXXXXXXXXXXXXXXXXXX' +  //the file ID
      '/export?exportFormat=pdf&format=pdf' +
        '&size=LETTER' +
          '&portrait=true' +
            '&fitw=true' + 
              '&top_margin=0.50' +            
                '&bottom_margin=0.50' +         
                  '&left_margin=0.50' +        
                    '&right_margin=0.50' +     
                      '&sheetnames=false&printtitle=false' +
                        '&pagenum=false' +
                          '&gridlines=false' +
                            '&fzr=FALSE' +
                              '&gid=' +
                                'XXXXXXXXXXXXXXXXXXXXXX'; //the sheet's Id

  var token = ScriptApp.getOAuthToken();
  var docurl = UrlFetchApp.fetch(theurl, { headers: { 'Authorization': 'Bearer ' +  token } });
  var pdfBlob = docurl.getBlob();

  // Get filename from sheet "Work", cell "B5"
  var fileName = spreadsheet.getSheetByName("WORK").getRange("B5").getValue();

  // Create file from blob and name it
  // The newFile is placed in the root folder by default
  var newFile = DriveApp.createFile(pdfBlob).setName(fileName);  

  // if folder exists use next 
  if (DriveApp.getFoldersByName("Teste").hasNext()){
    var folder = DriveApp.getFoldersByName("Teste").next();

  // if folder does not exist
  } else {
    var folder = DriveApp.createFolder("Teste");// new folder created in the root folder by default
  }

  folder.addFile(newFile); // add new file to folder
  DriveApp.removeFile(newFile); // remove file from root folder
}

Answer 2

'&size=AxB' //A = Width you gonna use (inch); B = Height you gonna use (inch) //Don't use mayus and space. Put both together with the "x" Example: '&size=2.8363636363x5.9055118110' equivalent to 7.2cm and 15cm

Answer 3

here are some examples in how to select the correct margins and paper size.

 //FORMATS WITH NO ADDITIONAL OPTIONS
  //format=xlsx       //excel
  //format=ods        //Open Document Spreadsheet
  //format=zip        //html zipped          

  //CSV,TSV OPTIONS***********
  //format=csv        // comma seperated values
  //             tsv        // tab seperated values
  //gid=sheetId             // the sheetID you want to export, The first sheet will be 0. others will have a uniqe ID

  // PDF OPTIONS****************
  //format=pdf     
  //size=0,1,2..10             paper size. 0=letter, 1=tabloid, 2=Legal, 3=statement, 4=executive, 5=folio, 6=A3, 7=A4, 8=A5, 9=B4, 10=B5  
  //fzr=true/false             repeat row headers
  //portrait=true/false        false =  landscape
  //fitw=true/false            fit window or actual size
  //gridlines=true/false
  //printtitle=true/false
  //pagenum=CENTER/UNDEFINED      CENTER = show page numbers / UNDEFINED = do not show
  //attachment = true/false      dunno? Leave this as true
  //gid=sheetId                 Sheet Id if you want a specific sheet. The first sheet will be 0. others will have a uniqe ID. 
                               // Leave this off for all sheets. 
  // EXPORT RANGE OPTIONS FOR PDF
  //need all the below to export a range
  //gid=sheetId                must be included. The first sheet will be 0. others will have a uniqe ID
  //ir=false                   seems to be always false
  //ic=false                   same as ir
  //r1=Start Row number - 1        row 1 would be 0 , row 15 wold be 14
  //c1=Start Column number - 1     column 1 would be 0, column 8 would be 7   
  //r2=End Row number
  //c2=End Column number 

This being said your var should be something like:

 var theurl = 'https://docs.google.com/a/mydomain.org/spreadsheets/d/'
      + 'XXXXXXXXXXXXXXXXXXXXXXXXXXXX'  //the file ID
      + '/export?format=pdf'
      + '&size=0'
      + '&portrait=false'
      + '&fitw=true'       
      + '&top_margin=0'              
      + '&bottom_margin=0'          
      + '&left_margin=0'             
      + '&right_margin=0'           
      + '&sheetnames=false&printtitle=false'
      + '&pagenum=false'
      + '&gridlines=false'
      + '&fzr=FALSE'      
      + '&gid='
      + 'XXXXXXXXXXXXXXXXXXXXXX';  

Now the PDF size I don't think that can be changed to a specific size since there are predefined sizes already. I investigated but found nothing helpful, I hope this helps you.