Google sheets apps script, how to format a pdf saved to google drive?

Question

im using this script in order to save the curren tab as a pdf to specific folder

function SaveAsPDF() {

  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSpreadsheet = SpreadsheetApp.getActive(); // Get active spreadsheet.
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var pdfName = sheetName + ".pdf"; // Set the output filename as SheetName.

  folder = DriveApp.getFolderById('ID');

  var theBlob = createblobpdf(sheetName, pdfName);



  var existing = folder.getFilesByName(pdfName); //returns file iterator;
  var hasFile = existing.hasNext(); //check if iterator isn't empty;
  if (hasFile) {
    var duplicate = existing.next(); //access file;

    //delete file;
    var durl = 'https://www.googleapis.com/drive/v3/files/' + duplicate.getId();
    var token = ScriptApp.getOAuthToken();
    var dres = UrlFetchApp.fetch(durl, {
      method: 'delete',
      muteHttpExceptions: true,
      headers: { 'Authorization': 'Bearer ' + token }
    });
    if (dres.getResponseCode() >= 400) {
      //handle errors;
    }
  }

  var newFile = folder.createFile(theBlob);
  sourceSpreadsheet.toast("Saved ", "Success");

}

function createblobpdf(sheetName, pdfName) {
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId()
    + '/export'
    + '?format=pdf'
    + '&size=letter' // paper size legal / letter / A4
    + '&portrait=true' // orientation, false for landscape
    + '&scale=4'  // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
    + '&fitw=true'                   // fit to width, false for actual size
    + '&top_margin=1.00'              // All four margins must be set!
    + '&bottom_margin=0.00'           // All four margins must be set!
    + '&left_margin=2.00'             // All four margins must be set!
    + '&right_margin=0.00'            // All four margins must be set!
    + '&sheetnames=true&printtitle=false' // hide optional headers and footers
    + '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
    + '&fzr=false' // do not repeat row headers (frozen rows) on each page
    + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
    + '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
    + '&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);
  return theBlob;
};

my problem is that im not being able to format the pdf, I have been trying different ways but its like not responding to my changes (size, scale, margins fzr=false, etc) am I doing something wrong ? the main thing I need is to fit to height in a letter size

any help please ?

https://stackoverflow.com/questions/39690232/using-google-apps-script-to-save-a-single-sheet-from-a-spreadsheet-as-pdf-in-a-s — aNewb, Jan 07 '21 at 19:07

score 0 · Answer 1 · answered Jan 08 '21 at 08:43

Your code for formatting the pdf works correctly

The error must be elsewhere.

Mind that you have a loop that checks either the the file already exists before creating a new one. Most liekly you are not creating a new file because of the already existing file.

Try the following code:

function SaveAsPDF() {

  SpreadsheetApp.flush();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSpreadsheet = SpreadsheetApp.getActive(); // Get active spreadsheet.
  var sheetName = sourceSpreadsheet.getActiveSheet().getName();
  var pdfName =  "thisFileisNew.pdf"; // Set the output filename as SheetName.

//  folder = DriveApp.getFolderById('ID');

  var theBlob = createblobpdf(sheetName, pdfName);
  var newFile = DriveApp.createFile(theBlob);
  sourceSpreadsheet.toast("Saved ", "Success");

}

function createblobpdf(sheetName, pdfName) {
  var sourceSpreadsheet = SpreadsheetApp.getActive();
  var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
  var url = 'https://docs.google.com/spreadsheets/d/' + sourceSpreadsheet.getId()
    + '/export'
    + '?format=pdf'
    + '&size=A4' // paper size legal / letter / A4
    + '&portrait=false' // orientation, false for landscape
    + '&scale=4'  // 1= Normal 100% / 2= Fit to width / 3= Fit to height / 4= Fit to Page
    + '&fitw=true'                   // fit to width, false for actual size
    + '&top_margin=1.00'              // All four margins must be set!
    + '&bottom_margin=0.00'           // All four margins must be set!
    + '&left_margin=2.00'             // All four margins must be set!
    + '&right_margin=0.00'            // All four margins must be set!
    + '&sheetnames=true&printtitle=false' // hide optional headers and footers
    + '&pagenum=RIGHT&gridlines=false' // hide page numbers and gridlines
    + '&fzr=false' // do not repeat row headers (frozen rows) on each page
    + '&horizontal_alignment=CENTER' //LEFT/CENTER/RIGHT
    + '&vertical_alignment=TOP' //TOP/MIDDLE/BOTTOM
    + '&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);
  return theBlob;
};

It will create in the root directory of your Drive a file called thisFileisNew.pdf - I set the format to landscape, so you see the change in the formatting easier.

Google sheets apps script, how to format a pdf saved to google drive?

1 Answers1

Your code for formatting the pdf works correctly