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("");
}
}
}
function dataValidation() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Tr = ss.getRange("'Body Armor'!C1:C20").getValues(); //Testrange
// Variables and sheetnames
var b = "'Body Armor'!";
var g = "GS256!" ;
var gC = "'GS256 Classified'!";
var h = "B10:B27";
var l = "B28:B36";
// Get DV ranges
var gh = ss.getRange(g+h) ;
var ghc = ss.getRange(gC+h);
var gl = ss.getRange(g+l);
var glc = ss.getRange(gC+l);
// Build DV rules
var ghr = SpreadsheetApp.newDataValidation().requireValueInRange(gh,true);
var ghcr = SpreadsheetApp.newDataValidation().requireValueInRange(ghc,true);
var glr = SpreadsheetApp.newDataValidation().requireValueInRange(gl,true);
var glcr= SpreadsheetApp.newDataValidation().requireValueInRange(glc,true);
// Loop through each cell
for(i=0;i<Tr.length;i++) {
var j = i+1;
var Hr = ss.getRange(b+"H"+j);
var Jr = ss.getRange(b+"J"+j);
var Lr = ss.getRange(b+"L"+j);
// Test the test range and apply corresponding DV
if (Tr[i][0] == true) {
Hr.setDataValidation(ghcr);
Jr.setDataValidation(ghcr);
Lr.setDataValidation(glcr);
}
else {
Hr.setDataValidation(ghr);
Jr.setDataValidation(ghr);
Lr.setDataValidation(glr);
}
}
}
Best Answer
If I understand this correctly a simple if function should work. Try this: =arrayformula(IF(A18=F2:F,H2:H,""))