I made a script that gets several ranges from an Google Sheets file and exports each ranges on a single page in a PDF file.
It works fine so far but I have encountered a big problem: When a row higher than the page size (A4) the content outside of the page will not be shown. Is there a way, so that an automatic page break happens and the overlapping text gets printed on a new page?
async function PDFAngebot() {
var sheetId = "1949460457"; // Sheet ID – letzte Zahlen von Link des Sheets.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var sheet = ss.getSheetByName('AN_Vorlage');
var pageAmmount = sheet.getRange('V13').getValue();
pageAmmount -= 1;
var rangeVariable =3 + pageAmmount;
var rangeInput = 'X3' + ':' + 'X' + rangeVariable;
var ranges = sheet.getRange(rangeInput).getValues(); // Ranges die exportiert werden.
ss.toast('pageAmmount: ' + pageAmmount + 'rangeVariable: ' + rangeVariable + 'ranges: ' + ranges);
var token = ScriptApp.getOAuthToken();
var data = ranges.map(r => {
var url = `https://docs.google.com/spreadsheets/d/${ssId}/export?format=pdf&exportFormat=pdf&size=A4&portrait=true&scale=2&top_margin=0.15&left_margin=0&right_margin=0&bottom_margin=0.15&range=${r}&gid=${sheetId}`;
return new Uint8Array(UrlFetchApp.fetch(url, { headers: { Authorization: "Bearer " + token } }).getContent());
});
// Load pdf-lib.js
var cdnUrl = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
eval(UrlFetchApp.fetch(cdnUrl).getContentText().replace(/setTimeout\(.*?,.*?(\d*?)\)/g, "Utilities.sleep($1);return t();"));
// Merge PDF data.
var pdfDoc = await PDFLib.PDFDocument.create();
for (let i = 0; i < data.length; i++) {
var pdfData = await PDFLib.PDFDocument.load(data[i]);
var pages = await pdfDoc.copyPages(pdfData, [...Array(pdfData.getPageCount())].map((_, i) => i));
pages.forEach(page => pdfDoc.addPage(page));
}
var bytes = await pdfDoc.save();
var contents = Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, "sample.pdf");
// PDF blob is created as a file. This is from your showing script.
var inputs = sheet.getRange('H9:H10').getValues();
var folder = DriveApp.getFolderById(inputs[1][0]);
DriveApp.createFile(contents).setName(inputs[0][0] + ".pdf").moveTo(folder);
}