getRowHeight() returns default row height instead of actual height, when text wrap is enabled

Question

I want to be able to format and adjust the size of the rows programmatically when creating a report in Google Sheets so that I don't have the header stuck on one page and then content on the next with a multi page report. It looks bad.

So if I could determine the height of each row, then I could determine whether the header will be stuck on one page and the content on another.

It appears that the getRowHeight() function only returns the height of the row if a height has been specified for the row.

The function will return the default height of the row, even if the row height has been adjusted/increased because of using wrap text. With wrap text the row height has been increased because of the text wrapping to the next line in the cell.

Is there another solution for determining row height in order to programmatically determine what rows will print on which page?

function test2(){
  var defSpreadsheet = SpreadsheetApp.getActive()
  var sheet6 = defSpreadsheet.getSheetByName("Sheet6")
  sheet6.activate();
  sheet6.showSheet();
  var startingrow = 1;
  for(var i= 2;i<4;i++){
    sheet6.getRange(i,5).setValue(sheet6.getRowHeight(i));
  }
}

Lemur

Ruben thats a different problem, for setRowHeight.......my issue is with getRowHeight. Though the answer suggests that this is potentially the way its supposed to work or an issue with it.......though I'm not sure why. What kind of workaround is available for getting cell height? Use DOM with the google sheets html? – Lemur

Related: Is there a way to set row height to match wrapped text height? – Couperin

@Trevor: AFAIK it's not possible to use DOM to get the row height and any other Google Sheets element property. The only answer to the above linked Q suggest to use calculate the height indirectly by using the font size, value length, etc. – Couperin

DOM? Probably need tampermonkey type scripts. appsscript can't access DOM. – Stopwatch

Ruben, I am essentially doing that, calculating chars per line for a given font and font size, then calculating pixel height, and setting the row height based on that. Its ugly and way more code then it should be.......but I think it should work most of the time. – Lemur

Hello Trevor, can you share your code. I am also trying to estimate the row height from its content. – Honkytonk

Answer 1

Easier way is to freeze the rows you want in each page, and then print. ( Row & column headers Go to View > Freeze to select which rows/columns to repeat on all pages)

When you print from goole sheet, you will get the option at the last in "Header and footer section" --> "Row and column headers" will be highlited, and you can edit if needed

Thank you

Merridie

Answer 2

Here is a code sample for determining the height of the cell with Word wrap turned on.

-assume default font "Arial"

-Font size 10

-default cell or column width

-you would need to determine mycharspercol if you change the column width

function test3(){
  var defSpreadsheet = SpreadsheetApp.getActive()
  var sheet6 = defSpreadsheet.getSheetByName("Sheet6")
  sheet6.activate();
  sheet6.showSheet();
  var mycharspercol = 18;
  var startingrow = 1;
  for(var i= 2;i<12;i++){
    sheet6.getRange(i,6).setValue(getMyRowHeight(sheet6.getRange(i,1).getValue(),mycharspercol));
  }
}

function getMyRowHeight(myCell, CharsPerCol){
  var mylength = myCell.length;
  var numofrows = mylength/CharsPerCol;
  var rowheight =0;
  if (numofrows<1){
    if(mylength==0){
      rowheight=-1;
    }
    else{        
      rowheight = 21;
    }
  }
  else{
    rowheight = 5 + (parseInt(Math.ceil(numofrows))*16); 
  }
  return rowheight;
}

Lemur