Custom function to export a google sheet to PDF

Question

I have tried may "Export to PDF" functions and none of them are working. Below is my code:

code.gs

    function printPdf() {
    SpreadsheetApp.flush();
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();

    var gid = sheet.getSheetId();

    var pdfOpts = '&size=A4&fzr=false&portrait=false&fitw=true&gridlines=false&printtitle=false&sheetnames=false&pagenum=UNDEFINED&attachment=false&gid='+gid;


    var row2 = 29;
    var printRange = '&c1=0' + '&r1=0' + '&c2=7' + '&r2='+row2; // B2:APn
    var url = ss.getUrl().replace(/edit$/, '') + 'export?format=pdf' + pdfOpts + printRange;

    var app = UiApp.createApplication().setWidth(200).setHeight(50);
    app.setTitle('Print this sheet');

    var link = app.createAnchor('Download PDF', url).setTarget('_new');

    app.add(link);

    ss.show(app);
     }

Answer 1

I guess I've figured it out.

function buttonprtPDF() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sprsheetid = ss.getId();
  var sheetid = ss.getSheetId();

  var ssa = SpreadsheetApp.getActive();
  var sheet = ssa.getSheets()[0]; //Query Sheet
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();

  //All requests must include id in the path and a format parameter
  //"https://docs.google.com/spreadsheets/d/"+sprsheetid+"/export?"

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

  var printRange = '&c1=0' + '&r1=0' + '&c2='+lastColumn  + '&r2='+lastRow; // B2:APn

  var url= ss.getUrl().replace(/edit$/, '') + 
  "export?exportFormat=pdf&" +
  "format=pdf&"+
  "fmcmd=12&"+
  "size=0&"+
  "fzr=true&"+
  "gid="+sheetid+"&"+
  "sheetnames=true&"+
  "pagenum=CENTER&"+
  "gridlines=true&"+
  "portrait=false&"+
  "scale=2&"+
  "fitw=true&"+
  "horizontal_alignment=CENTER&"+
  "top_margin=0.10&"+
  "bottom_margin=0.10&"+
  "left_margin=0.20&"+
  "right_margin=0.20&"+
  "attachment=true&"+ printRange;


  var html = "<script>window.open('" + url + "');google.script.host.close();</script>";
  var userInterface = HtmlService.createHtmlOutput(html)
  .setHeight(10)
  .setWidth(100);
  SpreadsheetApp.getUi().showModalDialog(userInterface, 'Downloading PDF ... ');
}