Google sheet App script | Save PDF with page break

172 Views Asked by At

I have a script below which saves a sheet as a PDF. Everything works fine. But I need to insert a command into the script that would set a page break on the sheet being saved to PDF on a specific row, for example, row 25. That is, everything after row 25 would be on a new page in the resulting PDF file. Is this possible? Thanks for your help.

`function printPDFsheet10() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcesheet = ss.getSheetByName("Doklad-PKBOZP");
  var originalSheet = ss.getActiveSheet(); // uložení ID listu, ze kterého se funkce spouští

  // filtruj sloupec L a odstraň řádky s mezerami
  sourcesheet.getRange('L27').activate();
  var criteria = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues([''])
    .build();
  sourcesheet.getFilter().setColumnFilterCriteria(12, criteria); 
  
  // export do PDF
  var id = ss.getId();
  var shtId = sourcesheet.getSheetId();
  var url = 'https://docs.google.com/spreadsheets/d/' + id + '/export' +
    '?format=pdf&landscape=false&portrait=true&size=A4&scale=2&fitw=true&fith=true&top_margin=0.60&bottom_margin=0.60&left_margin=0.60&right_margin=0.60&gid=' + 
    shtId;
  
  var sheetName = sourcesheet.getName();
  var timestamp = Utilities.formatDate(new Date(), "GMT+1", "yyyyMMdd-HHmmss");
  var val = sheetName + "_" + timestamp + ".pdf";

  var res = UrlFetchApp.fetch(url, {
    headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
  });
  
  // vloží novou stránku před řádek 25
  var range = sourcesheet.getRange('A25');
  var anchor = range.getCell(1, 1);
  var drawing = anchor.createDrawing();
  var image = drawing.getImage();
  sourcesheet.insertPageBreak(25);
  drawing.remove();
  
  // zobrazení dialogového okna pro stažení PDF souboru
  SpreadsheetApp.getUi().showModelessDialog(
    HtmlService.createHtmlOutput(
      '<a target ="_blank" download="' +
      val +
      '" href = "data:application/pdf;base64,' +
      Utilities.base64Encode(res.getContent()) +
      '">Klikněte zde</a> pro stažení PDF souboru, pokud stahování nezačalo automaticky.' +
      '<script> \
        var a = document.querySelector("a"); \
        a.addEventListener("click",()=>{setTimeout(google.script.host.close,10)}); \
        a.click(); \
      </script>'
    ).setHeight(150),
    'Stahování vašeho protokolu v PDF..'
  );

  ss.setActiveSheet(originalSheet); // nastavení původního listu jako aktivní
}`
0

There are 0 best solutions below