Dynamic Page Breaks in Google Sheets

Question

I have mild general programming knowledge but know basically nothing about google apps scripts specifically.

I am trying to create dynamic page breaks in google sheets, or find another way to keep certain rows grouped together when printing.

On my data sheet I have 100s of rows of information and within each row the data can vary significantly in length (from a single number to many paragraphs of text). I have created a second sheet that both filters the information that I want and displays it in a visually-appealing way, taking the original data from each row and parsing it into 8 total rows (7 with information, and one blank to visually separate one block of info from the next) per one original. The problem is that the varying length of the data means I have to manually move the page breaks every time I change the filter.

Here is a blank section of the second sheet for reference.

I want to be able to print with as many 8-row groupings on a page as I can, but not split up a group onto the next page.

I'm honestly not sure how to get started, though I presume that I can use the blank row to trigger the page breaks somehow. Any help would be greatly appreciated!

Updated

I have been able to write some rudimentary code to (mostly) accomplish what I wanted. However the best that I can tell is that getRowHeight() is not working with my wrapped text, as it properly formats when I have any empty data set, but not otherwise.

Can someone confirm, and tell me what I'm missing?

function dynamicPageBreaks() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var curRow = 2; //start on Row 2 (1 contains the filter selection)
var curTotPix = ss.getRowHeight(curRow);
var pgPix = 1030;

//loop until end of sheet
var endRow = ss.getLastRow();
do {

//find row that goes past page break
do {
  curRow++;
  curTotPix = curTotPix + ss.getRowHeight(curRow);
} while (curTotPix <= pgPix);

//get value of cell in column B of that row
var curCell = sheet.getRange(curRow,2).getValue();

//back up until we find an empty row
if (curCell == "") {
  break;
} else do {
  curTotPix = curTotPix - ss.getRowHeight(curRow);
  curRow = curRow - 1;
  curCell = sheet.getRange(curRow,2).getValue();
} while (curCell != "");

//expand empty row to match necessary pixels
var addHeight = pgPix - curTotPix;
ss.setRowHeight(curRow - 1, ss.getRowHeight(curRow) + addHeight);

//reset for next iteration
curTotPix = ss.getRowHeight(curRow);
} while (curRow < endRow);
}

Answer 1

I would recommend you get started by following an Apps script quickstart like the one about extending sheets here[1]

Then you can define your logic using methods from the Spreadsheet service[2].

You have the method getRowHeight()[3] which could help you in determining the length of the range you are looking to print. And that also depends on the paper size you choose.

[1]https://developers.google.com/apps-script/guides/sheets#get_started [2]https://developers.google.com/apps-script/reference/spreadsheet [3]https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrowheightrowposition