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);
}

Considering you don't show what this custom order is, how can we possibly help? The only thing we can do is to say that there are methods which will sort a sheet range by its values. — tehhowch
– tehhowch, Commented Sep 12, 2018 at 12:54
@Stefan you are getting the values and pasting them back into the cells so you are loosing your formulas. Tottally normal. but please tell use what orderCustomis — JSmith
– JSmith, Commented Sep 12, 2018 at 12:58
@tehhowch: I don't understand why the customOrder function should be relevant. The sorting works ok. I only need to preserve my formulas. When I apply the custom sort on the range, it fails as it should, sort is an array function and therefore doesn't work with range. — Stefan
– Stefan, Commented Sep 12, 2018 at 13:03
If your comparator is value based, you can write a custom sort object that will work on the actual sheet range. If not, then you have to implement a script-only solution. And no, I did not suggest you sort the range with your orderCustom function as-is - I suggested that you review the format expected by the Range#sort class method, as it may resolve your issue simply. — tehhowch
– tehhowch, Commented Sep 12, 2018 at 13:06
getFormulas() and getValues() -- map them or link them togetheras a array. Run your custom sort and remap it back to formulas. Set back the formulas(where formula else set values) — TheMaster
– TheMaster, Commented Sep 12, 2018 at 13:07

JSmith · Accepted Answer · 2018-09-12 14:21:28Z

1

I suggest you try it this way:

get formulas with var formulas = myRng.getFormulas()
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
put in myDataall the formulas like if(formulas[row][column]) myData[row][column] = formulas[row][column]
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; }));

edited Sep 12, 2018 at 14:21

answered Sep 12, 2018 at 13:07

JSmith

4,8526 gold badges36 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stefan Over a year ago

I'-'I proposed something similar in the comments, it seems like the right way to go. However the formulas move when sorted and the rows to which they belong don't have special identificators. Don't know how to map that.

Stefan Over a year ago

When I grab the range with getFormulas() I don't get any values, the range is sorted by values only. I can not sort the formula range the sameway because I don't get the same input there.

JSmith Over a year ago

I know that why you need to sort by values but keep track of what index is moved to another. I'm sorry if you misunderstand. But for me it's easy. Sort by values and apply same sorting to the formula array then merge both

Stefan Over a year ago

I understand the solution and like it, but how can I sort the formula array if it doesn't contain the values by which the mydata array is sorted? How can I keep indexes the same in the formula array if they aren't there. getFormulas grabs only formulas everything else remains empty, no values are grabed.

JSmith Over a year ago

@Stefan use map developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/… or use a real custom sorting function not a custom compare function

Collectives™ on Stack Overflow

Google Sheets Script custom range sort without pasting result

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related