I'm trying to sort columns from left to right based on dates, here is an example of the issue that I'm facing:

https://docs.google.com/spreadsheets/d/1CuDW-VRZxrwXXjyBj4BeUleMFqL8DUQrW3sku6WjMh0/edit?usp=sharing

I'm sorting from column E to N based on the dates in row 6. The script that I'm currently using works okay as far as cell E6 has a date and there is no empty columns in between the full ones, otherwise the script won't work.

Here's the script that I'm using:

function sortLToR() {
  
  //Defining the spreadsheet variables and setting ranges
  var sheet = SpreadsheetApp.getActive().getSheetByName("Sort");
  var range3 = sheet.getRange(5, 5, 88,sheet.getLastColumn()-4)
  var range = sheet.getRange(5, 5, 88,sheet.getLastColumn()-4).getValues();
  Logger.log(sheet.getLastColumn())
  //Defining a blank array that can hold the result
  var trans = [];
  //transpose the data stored in range variable
  for(var column = 0; column < range[0].length; column  ){
    trans[column] = [];
    for(var row = 0; row < range.length; row  ){
      trans[column][row] = range[row][column];
    }
  }
    function sortByDate(a, b) {
    return new Date(b[1]).getTime() - new Date(a[1]).getTime();
}

var range2 = trans.sort(sortByDate);
var trans2 = [];
  //transpose the data stored in range variable
  for(var column = 0; column < range2[0].length; column  ){
    trans2[column] = [];
    for(var row = 0; row < range2.length; row  ){
      trans2[column][row] = range2[row][column];
    }
  }
  range3.setValues(trans2); 
}

Any ideas how to fix this?

Thanks

CodePudding user response：

I'm not sure if this is a viable solution, but you could just add another sheet or use a range in columns to the right, I think you could accomplish what you want.

=transpose(sort(TRANSPOSE(filter(F:N,F:F<>"")),6,true))

See example here of before and after.

Updated to account for potential breaks in data:

=transpose(sort(TRANSPOSE(filter(Before!E:N,Row(Before!E:E)<=max(arrayformula(--NOt(ISBLANK(Before!E:N))*row(Before!E:N))))),6,true))

One could then leverage this formula in an appscript to overwrite the data with something like this:

function makeRange(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const yourSheetName = "Before"; //sheet name with data
  const zFormula = "=transpose(sort(TRANSPOSE(filter('zzzzz'!E:N,Row('zzzzz'!E:E)<=max(arrayformula(--NOt(ISBLANK('zzzzz'!E:N))*row('zzzzz'!E:N))))),6,true))";//

  var newSheet = ss.insertSheet();
  var newFormula = zFormula.replace(/zzzzz/g,yourSheetName)
  newSheet.getRange("E1").setFormula(newFormula);

  var newValues = newSheet.getRange("E:N").getValues();
  
  ss.getSheetByName(yourSheetName).getRange("E:N").setValues(newValues);

  ss.deleteSheet(newSheet);
}

CodePudding user response：

Try this:

function sortLToR() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const rg = sh.getRange(5, 5, sh.getLastRow() -4, sh.getLastColumn() - 4)
  const vs = rg.getValues();
  let b = transpose(vs);
  b.sort((a,b) => {
    return new Date(a[1]).valueOf() - new Date(b[1]).valueOf();
  });
  let c = transpose(b)
  rg.setValues(c);
}

function transpose(a) {
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}