Google Sheets Script custom range sort without pasting result

Question

I have a range in Google Sheets that contains a combination of values and formulas scattered in various rows and columns. The sort needs to be custom and I wrote a custom sort function that extracts data and sorts the array. However when pasting the sorted result back I loose the formulas.

Is there a way to create a custom sort directly for the range without the set/get Values?

function orderMyRange() {
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var mySheet = sh.getSheetByName("MySheet");
  var myRng = mySheet.getRange("A4:L500");
  var myData = myRng.getValues();

  myData.sort(orderCustom);
  myRng.setValues(myData);
}

Answer 1

I suggest you try it this way:

1. get formulas with var formulas = myRng.getFormulas()
2. find how to sort formulas and myData the same way. in a way that if myData[12] moves to 9 (10th row) then formulas will do the same in var temp = formulas[9]; formulas[9] = formulas[12]. sort by values but apply the same sorting to formulas
3. put in myDataall the formulas like if(formulas[row][column]) myData[row][column] = formulas[row][column]
4. use myRng.setValues(myData) or return the array if it's a custom function used as a formula in google sheet.

for a select sort algorithm this will do something like this:

var myData = [[1], [223], [3], [2], [345]];
var formulas = [["=ROW(A1)"], ["=ROW(A223)"], ["=ROW(A3)"], ["=ROW(A2)"], ["=ROW(A345)"]];

function Selection_Sort(arr, parr, compare_Function) {
  //arr is the value array and parr is the formula array
  function compare(a, b) {
   return a - b;
   }
  var min = 0;
  var minFormula
  var index = 0;
  var temp = 0;
  var tempFormula = "";
 //{Function} compare_Function Compare function
  compare_Function = compare_Function || compare;

  for (var i = 0; i < arr.length; i += 1) {
    index = i;
    min = arr[i][0];
    minFormula = parr[i][0];
    for (var j = i + 1; j < arr.length; j += 1) {
      if (compare_Function(min, arr[j]) > 0) {
        min = arr[j][0];
        minFormula = parr[j][0];
        index = j;
      }
    }

    temp = arr[i][0];
    tempFormula = parr[i][0];
    arr[i][0] = min;
    parr[i][0] = minFormula;
    arr[index][0] = temp;
    parr[index][0] = tempFormula;
  }

  //return sorted arr
  return [arr, parr];
}

console.log(Selection_Sort(myData, formulas, function(a, b) { return a - b; }));