OK, found some time, and now we hopefully have a solution, if I have understood your requirements correctly. The following will generate a single pdf, with multiple pages consisting of a header and footer and up to 25 records per page.
My tests were with a google spreadsheet that contained just one sheet, with a header (A1:F11) a data section (A12:F1011) for upto 1000 records, and a footer (A1012:F1017).
The google apps script web app will generate a new sheet for each 25 records, with a header and footer, then hide the original records sheets, and generate a single pdf from the remaining sheets. I have annotated the script as best I can to explain what each line of the code does.
The google apps script needs to be fed 5 parameters:
- The spreadsheet ID
- The sheet name
- The folder ID
- The records number (25)
- The pdf name
The google apps script will return the ID of the newly created pdf file, which can then be accessed by inserting it to this url:
https://drive.google.com/file/d/<fileId here>/view
SCRIPT
function doGet(e) {
var fileId;
var ss = SpreadsheetApp.openById(e.parameter.ssId); //get the spreadsheet
var sh = ss.getSheetByName(e.parameter.shName); //get the sheet with the records
var rngLength = sh.getRange("A12:A1011").getValues().filter(String).length; //get the number of records on the sheet
var num = Math.ceil(rngLength/e.parameter.recNo); //get the ceiling (round up) for the number of pages
for (var i=0;i<num;i++) {
sh.hideRows(12,1000); //hide all the individual record rows
sh.showRows(12+(25*i),25); // show the records based upon the recrod numbers
sh.copyTo(ss); //copy the original sheet to a new sheet
SpreadsheetApp.flush(); //set all operations on the spreadsheet
} //create a new sheet for each set of records, based on the records number,
sh.hideSheet(); //hide the sheet with the recrods in it
var folder = DriveApp.getFolderById(e.parameter.folderId); //get the folder to store the pdf
var url_base = 'https://docs.google.com/spreadsheets/d/' + e.parameter.ssId; //the base url
var url_ext = '/export?exportFormat=pdf&format=pdf'
+ '&id=' + e.parameter.ssId; // !important - sets the spreadsheet to be converted to a pdf (instead of just the first sheet)
+ '&size=7' // paper size A4 = 7
+ '&portrait=true' // orientation, false for landscape
+ '&fitw=true' // fit to width, false for actual size
+ '&sheetnames=false&printtitle=false&pagenumbers=false' //hide optional headers and footers
+ '&gridlines=true' // show gridlines
+ '&horizontal_alignment=CENTER' //centre range on page
+ '&fzr=false'; // do not repeat row headers (frozen rows) on each page
// all the options for the pdf, including the requirement to set the entire spreadsheet to the pdf
var options = { headers: { 'Authorization': 'Bearer ' + ScriptApp.getOAuthToken(), } } //authorisation
var response = UrlFetchApp.fetch(url_base + url_ext, options); //now fetch the spreadsheet
var blob = response.getBlob().setName(e.parameter.pdfName + '.pdf'); //create a blob of the spreadsheet with the filename
fileId = folder.createFile(blob).getId(); //create the pdf file and get the file ID
SpreadsheetApp.flush(); //set all operations on the spreadsheet
sh.showSheet(); //show the original records sheet
sh.showRows(12,1000); //show all the record rows
var sheets = ss.getSheets(); //get all the sheets for the spreadsheet
sheets.shift(); //remove the first sheet (the original records sheet)
for (var i = 0;i<sheets.length;i++) {
ss.deleteSheet(sheets[i]);
} //delete all the generated sheets
return ContentService.createTextOutput(fileId); //return the file ID as a string
}
BLOCKS example
Here also an example output pdf file, created from 165 records in the Records sheet, using 25 records per sheet, which generated 7 pages. This took @ 7 seconds to create.
testPDF.pdf (75.1 KB)
Credits should go to this SO Q&A,and the contributors there, which helped me put part of this together
I trust you can make use of this, or adapt it for your needs
