CODEWITHSUNDEEP
javascriptsortinggoogle-apps-script
Andrew M
Andrew M

Reputation: 3

Google Apps Script: Why Does this Sorting Not Work?

new to google apps script and trying to find the second highest value in a range. When I input a range like [45, 101, 100, 40] the value returned is 101. Clearly the sort() isn't working as it is just returning the second value in the range. What am I missing?

function second_max(range) {
  if (range) {
    var arr = [];
    for (var i = 0; i < range.length; i++) {
      arr.push(range[i]);
    }
    arr.sort();
    Logger.log(arr);
    return arr[1];
  }

}

Upvotes: 0

Views: 753

Answers (2)

c-an
c-an

Reputation: 4080

change arr.sort() to arr.sort(function(a, b){return a-b}).

It doesn't sort because it thinks it's a string. For example, [1, 5, 2, 100, 500, 250, 60, 55, 100000] will be sorted to [1, 100, 100000, 2, 250, 5, 500, 55, 60].

So, you'd better to implement subtraction callback function inside of sort() so that you can get the right result.

check this out: https://www.w3schools.com/jsref/jsref_sort.asp

Upvotes: 1

TheMaster
TheMaster

Reputation: 50382

Issue:

  • range is a two dimensional array - array of arrays(eg:[[45], [101], [100], [40]]). arr.sort can sort only numbers.

Solution(s):

You can try any of the following:

  • You can easily use inbuilt formulas like SORTN to do the same:

      =INDEX(SORTN(A1:A20,2),2)

  • Pass a compareFunction to parse the array and sort.

  • Flatten the array and use Math.max

Snippet:

// Will only work for vertical rows as horizontal ranges like A1:C1 looks like [[1,2,3]]; Modify it accordingly.
function second_max(arr) {
  return arr.sort(([a],[b])=>b-a)[1][0];
}

/* Works with any 2D array*/
function second_max2(arr) {
  let flatArr = arr.flat();
  let max1 = Math.max(...flatArr);
  return Math.max(...flatArr.filter(num=>num !== max1))
}

Upvotes: 0

Related Questions