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("");
}
}
}
Short answer
The desired result could be obtained without scripting and this is recommended for cases where number of cells is small, especially if the OP don't know about scripting. To do so,
- Add auxiliary columns for each sorting criteria
- Then use the SORT() or QUERY() functions.
Extended answer
Google Sheets has some additional functions regarding those available in other spreadsheet applications. Two of them are SORT() and QUERY() which allow to get the desired result without scripting.
As QUERY() return a blank cell for those containing ?
as value, the SORT() function will be used.
Original data
Assume that the original data is in A2:D13
.
Auxiliary columns
Auxiliary columns will be used for sorting. Only one aggregate function is presented in order to keep the answer short.
Add the following formulas and fill down until the last row of the source data.
D2: =Row(C2)-Row($C$2)+1
E2: =ISERROR(LOOKUP("?",OFFSET($A$2,MATCH(A2,$A$2:$A$13,0)-1,2,COUNTIF($A$2:$A$13,A2))))
F2: =AVERAGEIF($A$2:$A$13,A2,$C$2:$C$13)
If you find helpful having column headers, add them to the row 1.
Key Formula
G2: =ARRAY_CONSTRAIN(SORT(A2:F13,5,TRUE(),6,TRUE(),1,TRUE()),12,3)
ARRAY_CONSTRAIN
is included to limit the result to the columns of the source data.
Best Answer
If you don't have many possibilities for the
Section
column, this can be a viable option :Example here
If there are many many many sections, you can use this formula who'll create the array you need :
Then copy and paste inside a cell (the content, not the formula)
This is the kind of example it can create :