Sorted 2D array sorting again on check

Question

I'm using Google Sheets and trying to confirm, using Google Apps Script, that a range is sorted by the last name (second column).
However, there are duplicates in the second column and, when performing the check, the data is re-sorting before the check, flipping the position of rows where the last name matches, at which point the lists don't match.
For instance, the Sheets range is as follows:

First Name	Last Name
Frida	Acosta
Autumn	Acosta
Edgar	Andersen
Kayla	Andersen
Raphael	Andrade
Johnathon	Andrews
Danielle	Archer

As you can see, rows {1,2} and {3,4} have the same last name. But I've sorted the data on Last Name from Sheets, so this should be correct. However, when I run the code it resorts and flips them, so that it sorts to:

First Name	Last Name
Autumn	Acosta
Frida	Acosta
Kayla	Andersen
Edgar	Andersen
Raphael	Andrade
Johnathon	Andrews
Danielle	Archer

and now, obviously, they do not match.

How can I check to see if a 2d array is sorted on a single column without having this happen where it attempts to re-sort the array?

function myFunction() {
  const ss = SpreadsheetApp.getActive().getActiveSheet();
  let a1 = ss.getRange(4,1,502,5).getValues();
  let a2 = JSON.stringify(a1);
  a1.sort((a, b) => a[1] > b[1] ? 1 : -1);
  Logger.log(JSON.stringify(a1) == a2);
}

Test Sheet

@marikamitsos why did you remove the "javascript" tag? GAS is Javascript. Also, why didn't my table format correctly? — Spencer
– Spencer, Commented Mar 13, 2021 at 16:41
If you read the tag descriptions about javascript and google-apps-script, you will find that the javascript tag is actually in excess. That is why it is not usually applied when google-apps-script is in use. Please have [a look]() — marikamitsos
– marikamitsos, Commented Mar 14, 2021 at 7:08
@marikamitsos I just read the full description and don't see anything that obviously tells me it shouldn't be used with GAS, but if that's how it's supposed to be, that's fine. I was kinda just going for the "widest net" approach, since GAS knowledge isn't really necessary for the question. — Spencer
– Spencer, Commented Mar 14, 2021 at 7:46
I never said that "it shouldn't be used with GAS". Just that "it is not usually applied...". In any case it is not really that important. Call it my mistake :) — marikamitsos
– marikamitsos, Commented Mar 14, 2021 at 7:55
As for "why didn't my table format correctly?". You need to leave an empty line (create a new paragraph) before applying the code syntax. — marikamitsos
– marikamitsos, Commented Mar 14, 2021 at 7:57

Marios · Accepted Answer · 2021-03-13 10:47:05Z

1

Solutions:

Basically your approach but without ? 1 : -1:

function myFunction() {
  const ss = SpreadsheetApp.getActive().getActiveSheet();
  let a1 = ss.getRange(4,1,502,5).getValues();
  let a2 = JSON.stringify(a1);
  a1.sort((a, b) => a[1] > b[1] );
  console.log(a1);
}

or:

function myFunction() {
  const ss = SpreadsheetApp.getActive().getActiveSheet();
  let a1 = ss.getRange(4,1,502,5).getValues();
  let a2 = JSON.stringify(a1);
  a1.sort((a, b) => a[1].localeCompare(b[1]));
  console.log(a1);
}

References:

String.prototype.localeCompare()

answered Mar 13, 2021 at 10:47

Marios

27.5k9 gold badges40 silver badges59 bronze badges

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

1 Comment

Spencer Over a year ago

The second one works. The first one I had before and it wasn't doing anything, so I added the ? 1 : -1

Collectives™ on Stack Overflow

Sorted 2D array sorting again on check

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related