G.A.S code to limit a pdf generated from a google sheet only to the rows that have data

Question

I use the following Google Script Code to generate a pdf of a google sheet and send it through email. The code works fine except that the generated pdf includes too many blank pages. How can I limit the generated pdf to only the sheet rows that contain data?

function emailReport() {
  readyForExport();
  var spreadsheet = SpreadsheetApp.getActive();
  var subject = spreadsheet.getRange("U1:U1").getValues();
  var emailTo = spreadsheet.getRange("V1:V1").getValues();
  var message = spreadsheet.getRange("W1:W1").getValues();
  var fileName = spreadsheet.getRange("X1:X1").getValues();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName("Students"));
  var pdf = DriveApp.getFileById(spreadsheet.getId()).getAs('application/pdf').getBytes();
  var attach = {fileName:fileName[0][0],content:pdf, mimeType:'application/pdf'};
  MailApp.sendEmail(emailTo, subject, message, {attachments:[attach]});
};

Thanks in advance.

Answer 1

I finally managed to write a code that can successfully remove the empty pages of my pdf attachment by first removing the empty rows from the sheet. :)

I intentionally wrote the code in a way to leave just a single empty row at the end to make it clear to the user that no other data exists in the succeeding rows.

I had to solve a number of bugs because the code ran into errors depending on the number of empty rows that already existed on the page. Finally I managed to deal with all the bugs and come up with a code than runs sleekly and considers all the exceptional cases. :)))

Here is the code:

  var firstDelRow = spreadsheet.getSheetByName('Students').getLastRow()+2;
  var lastDelRow = spreadsheet.getSheetByName('Students').getMaxRows();
  var rowDif = lastDelRow-firstDelRow+1;

  if (rowDif == -1) {
  spreadsheet.getSheetByName('Students').insertRowAfter(firstDelRow-2);
  };

  if (rowDif >= 1) {

  spreadsheet.getSheetByName('Students').deleteRows(firstDelRow, rowDif);
  };