Google Sheets to PDF - How to programmatically trim blank lines at the bottom of the printout?

Question

I am using the following script, which works great to save a PDF copy of a particular Google Sheet to my Google Drive. It specifically saves overtop of an existing file on a schedule, but does not delete then replace the file in order to maintain the share links which have been given out.

function updatePDF2() {
  var folderId = 'google_drive_folder_id';
  var pdfName = 'filename.pdf';

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('PDF_AVAIL_TOOL');
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(pdfName);
  var existingFileId = files.hasNext() ? files.next().getId() : Drive.Files.create({ name: pdfName, mimeType: MimeType.PDF, parents: [folderId] }).id;
  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?exportFormat=pdf&format=pdf';
  var options = '&size=letter' +
    '&portrait=true' +
    '&scale=2' +
    '&top_margin=0.25' + '&bottom_margin=0.25' + '&left_margin=0.25' + '&right_margin=0.25' +
    '&sheetnames=false&printtitle=false&pagenumbers=true&gridlines=false&fzr=true' +
    '&gid=' + sheet.getSheetId(); // Using the specific Sheet ID for exporting
  var token = ScriptApp.getOAuthToken();
  var headers = {
    'Authorization': 'Bearer ' + token,
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch(url + options, { headers: headers });
  if (response.getResponseCode() == 200) {
    var blob = response.getBlob().setName(pdfName);
    Drive.Files.update({}, existingFileId, blob);
    DriveApp.getFileById(existingFileId).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    Logger.log('PDF updated successfully and permissions are set.');
  } else {
    Logger.log('Failed to fetch the PDF: ' + response.getContentText());
  }
}

My problem is that there are many blank lines at the end of this sheet which I do not want/need I the resulting PDF file. The length of this list changes frequently, so I need to adjust for this dynamically. If I put a filter on the sheet itself to remove blanks, the print will be perfect at the time the filter was applied, but if the list gets shorter there will be blanks at the bottom, and worse if the list gets longer those additional lines won't be shown on the printout.

How can I adapt the script so that it will apply a filter to only include non blank values in Column B of the sheet prior to exporting the PDF?

Answer 1

About How can I adapt the script so that it will apply a filter to only include non blank values in Column B of the sheet prior to exporting the PDF?, when your showing script is modified, how about the following modification?

Modified script:

function updatePDF2() {
  var folderId = 'google_drive_folder_id';
  var pdfName = 'filename.pdf';

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('PDF_AVAIL_TOOL');

  // --- I added the below script.
  var maxRow = sheet.getMaxRows();
  sheet.showRows(1, maxRow);
  var values = sheet.getRange("B1:B" + maxRow).getDisplayValues();
  values.forEach(([b], i) => {
    if (!b) {
      sheet.hideRows(i + 1);
    }
  });
  SpreadsheetApp.flush();
  // ---

  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFilesByName(pdfName);
  var existingFileId = files.hasNext() ? files.next().getId() : Drive.Files.create({ name: pdfName, mimeType: MimeType.PDF, parents: [folderId] }).id;
  var url = 'https://docs.google.com/spreadsheets/d/' + spreadsheet.getId() + '/export?exportFormat=pdf&format=pdf';
  var options = '&size=letter' +
    '&portrait=true' +
    '&scale=2' +
    '&top_margin=0.25' + '&bottom_margin=0.25' + '&left_margin=0.25' + '&right_margin=0.25' +
    '&sheetnames=false&printtitle=false&pagenumbers=true&gridlines=false&fzr=true' +
    '&gid=' + sheet.getSheetId(); // Using the specific Sheet ID for exporting
  var token = ScriptApp.getOAuthToken();
  var headers = {
    'Authorization': 'Bearer ' + token,
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch(url + options, { headers: headers });
  if (response.getResponseCode() == 200) {
    var blob = response.getBlob().setName(pdfName);
    Drive.Files.update({}, existingFileId, blob);
    DriveApp.getFileById(existingFileId).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
    Logger.log('PDF updated successfully and permissions are set.');
  } else {
    Logger.log('Failed to fetch the PDF: ' + response.getContentText());
  }

  sheet.showRows(1, maxRow); // Added
}

• By this modification, only the rows that column "B" has a value are shown in the exported PDF file.