1

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);
     }
2

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 ... ');
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.