How to export Checked row(s) only to PDF in Google Apps Script?

Question

I have implemented a function in Apps script, with the following goals:

• Identify rows that have a Checkbox value of True (checked) in Column A
• Export only the checked rows into One pdf file
• Only the specified Cell Range should added to the pdf file (C4:J803), excluding Column A.

Current issue: after clicking the button, multiple copies of the pdf file is saved to the user's Google Drive and All rows (Checked & Unchecked) are listed in the pdf file.

/*
@OnlyCurrentDoc
*/

function exportRangeToPDf(range) {

var ui = SpreadsheetApp.getUi();

var response=ui.alert("Export row(s) to PDF", "Export the Selected Row(s) to PDF?", ui.ButtonSet.YES_NO);

//checking the user response

            if(response==ui.Button.NO)
            {
            return; //exit from this function
            }

  var blob,exportUrl,options,pdfFile,response,sheetTabNameToGet,sheetTabId,ss,ssID,url_base;

  pdf_range = range ? range : "C4:J803";

  sheetTabNameToGet = "Bldg Condition";//Replace the name with the sheet tab name for your situation
  ss = SpreadsheetApp.getActiveSpreadsheet();//This assumes that the Apps Script project is bound to a G-Sheet
  ssID = ss.getId();
  sh = ss.getSheetByName(sheetTabNameToGet);
  sheetTabId = sh.getSheetId();
  url_base = ss.getUrl().replace(/edit$/,'');

var sheet = SpreadsheetApp.getActive();
var dataRange = sheet.getRange("A4:A805");
var values = dataRange.getValues();

for (var i = 0; i < values.length; i++) {
var row = values[i];

 var checkbox = row[0]; // Assuming checkbox is in column A (0-based index)

 if(sh.getName() == "Bldg Condition" && checkbox == true) {


  exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +

    '&gid=' + sheetTabId + '&id=' + ssID +
    '&range=' + pdf_range + 
    //'&range=NamedRange +
    '&size=A4' +     // paper size
    '&portrait=true' +   // orientation, false for landscape
    '&fitw=true' +       // fit to width, false for actual size
    '&sheetnames=true&printtitle=false&pagenumbers=true' + //hide optional headers and footers
    '&gridlines=false' + // hide gridlines
    '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  //Logger.log('exportUrl: ' + exportUrl)

  options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  options.muteHttpExceptions = true;//Make sure this is always set

  response = UrlFetchApp.fetch(exportUrl, options);

  //Logger.log(response.getResponseCode())

  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;

  }
  
  blob = response.getBlob();

  blob.setName('Bldg_Condition.pdf')

  pdfFile = DriveApp.createFile(blob);//Create the PDF file

  //Logger.log('pdfFile ID: ' +pdfFile.getId())
  }}
      ss.toast("PDF file has been added to your Google Drive folder!")
}

On the spreadsheet tab (Bldg Condition), the user has the ability to specify which rows are shown in a report by setting the Checkbox value to True. Once the Export to PDF button is clicked, 1 pdf file should be saved to their Google Drive.

I have searched for comparable questions, but I've only found OnEdit (simple trigger) examples and not Button initiated.

Dummy Spreadsheet

Answer 1

In your situation, how about hiding rows with the checked checkboxes in column "A", and exporting as PDF format? When this is reflected in your script, it becomes as follows.

Modified script:

function exportRangeToPDf(range) {
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert("Export row(s) to PDF", "Export the Selected Row(s) to PDF?", ui.ButtonSet.YES_NO);
  if (response == ui.Button.NO) {
    return;
  }
  var blob, exportUrl, options, response, sheetTabNameToGet, sheetTabId, ss, ssID, url_base;
  pdf_range = range ? range : "C4:J803";
  sheetTabNameToGet = "Bldg Condition";
  ss = SpreadsheetApp.getActiveSpreadsheet();
  ssID = ss.getId();
  sh = ss.getSheetByName(sheetTabNameToGet);
  sheetTabId = sh.getSheetId();
  url_base = ss.getUrl().replace(/edit$/, '');
  var sheet = SpreadsheetApp.getActive();
  var dataRange = sheet.getRange("A4:A805");
  var values = dataRange.getValues();


  // --- I modified the below script.
  sh.showRows(1, sh.getMaxRows());
  values.forEach(([a], i) => {
    if (a === true) {
      sh.hideRows(i + 4);
    }
  });
  SpreadsheetApp.flush();
  var exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +
    '&gid=' + sheetTabId +
    '&id=' + ssID +
    '&range=' + pdf_range +
    '&size=A4' +
    '&portrait=true' +
    '&fitw=true' +
    '&sheetnames=true&printtitle=false&pagenumbers=true' +
    '&gridlines=false' +
    '&fzr=false';
  var options = { headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken() }, muteHttpExceptions: true };
  var response = UrlFetchApp.fetch(exportUrl, options);
  sh.showRows(1, sh.getMaxRows());
  if (response.getResponseCode() !== 200) {
    console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
    return;
  }
  var blob = response.getBlob().setName('Bldg_Condition.pdf')
  var pdfFile = DriveApp.createFile(blob);
  // ---


  ss.toast("PDF file has been added to your Google Drive folder!")
}

• When this script is run, the rows without checking the checkboxes are included in the exported PDF data.