Google Sheets Script – export PDF – How to get overlapping text on new page?

39 Views Asked by At

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);
}
0

There are 0 best solutions below