How to sort Google Sheets columns by row height

Question

I'm working on a sheet that involves multiple columns corresponding to various data. Each column has a varying number of cells of varying heights with wrapped text. What I want to do is make the sheet as compact as possible, such that a cell in column A that is 50px high is in the same row as other cells with that height (or as close to that height as possible). Essentially, I want to make the output of Logger.log(sheet.getRowHeight()) result in each column sorting by height, ascending. I've been doing this by hand so far—when a cell in a column has a height of, say, 100px I move it to the row that has cells in other columns close to that height (with the smallest cells at the top).

I don't know Google sheets syntax really at all; I know basically what I want to do, but I have no idea how to implement it with the commands available. If there is a way to get the cell with the most height in each column to be moved to row 2 (I have a header row), second-most height to row 3, etc. that would be great. Even better would be if I could somehow sort a column with only three cells whose tallest cell is like 200px such that that cell is moved down to a row of similar height even if that results in several empty cells above it.

Answer 1

I don't think rowHeight can help you here, because it refers to the entire row, not to any particular cell in it. Sorting each column by RowHeight would just rearrange the rows without making your spreadsheet more compact.

Here is the script that sorts each column by the length of cell content, ascending. The first row (headers) is left in place; empty cells are ignored.

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var width = range.getWidth();
   var height = range.getHeight();
   var column, i, j, content;
   for (j = 1; j <= width; j++) {
     column = [];
     for (i = 2; i <= height; i++) {
       content = range.getCell(i,j).getValue();
       if (content) {
         column.push(content);
       }
     }
     column.sort(function (a,b) {return a.length - b.length;});
     for (i = 0; i < column.length; i++) {
       range.getCell(i+2,j).setValue(column[i]);
     }
     for (i = column.length; i < height-1; i++) {
       range.getCell(i+2,j).setValue("");
     }
  }
}

But in practice, this particular sort does not help all that much, because (as you noticed) the longer texts can still match up against shorter texts. Here is another sort, somewhat along the lines of what you mentioned: after sorting, each column is dropped down so that all the longest entries are in the same row. This will typically result in some empty cells at the top.

function myFunction2() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var width = range.getWidth();
   var height = range.getHeight();
   var column, i, j, content;
   for (j = 1; j <= width; j++) {
     column = [];
     for (i = 2; i <= height; i++) {
       content = range.getCell(i,j).getValue();
       if (content) {
         column.push(content);
       }
     }
     column.sort(function (a,b) {return a.length - b.length;});
     for (i = 0; i < column.length; i++) {
       range.getCell(height-column.length+i+1,j).setValue(column[i]);
     }
     for (i = 2; i <= height-column.length; i++) {
       range.getCell(i,j).setValue("");
     }
  }
}