I have implemented a function in Apps script, with the following goals:
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.
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.
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!")
}