Google apps script getAs(‘application/pdf’) layout

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("https://stackoverflow.com/",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;
}

Leave a Comment