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("");
}
}
}
This can be done with an arrayformula as follows:
=arrayformula(importrange("URL", "A1:A3") & " " & importrange("URL", "B1:B3"))
The inner part describes what should be done within one row; the arrayformula then says "do it for every row".
Best Answer
There are several ways that you could adapt your script to sort a different column for each sheet.
For example, you might create a series of IF statements that combine the sort column and the sheet name. Another option, and the one used in this script, is to use the Javascript
switchstatement (docs).Though an
IFstatement is still required, the value ofcolumnToSortByis modified according to the sheetname (var sheetname =); the sheet name drives the range viagetSheetbyName().Note the minor change in lines 1 and 2;
var ss =, and its impact onvar editedCell =.