CODEWITHSUNDEEP
javascriptarrayssortinggoogle-apps-scriptgoogle-sheets
Andrius Šmitas
Andrius Šmitas

Reputation: 11

How to sort elements of array in natural order with mixed (letters and numbers) elements

i am trying to create google-form which is used to register students agreements on practice. Every agreement is registered and got agreement number which format is Last to digits of current year-T-number of agreement at this year/M. For example for now it is 17-T-11/M. The number of agreement currently is written by person which is responsible for practice. Here is code of script below:

function onChange(e) 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1];
  var range = sheet.getDataRange();
  var values = range.getValues();
  var comboValues = ['16-T-105/M']; 

   // removing titles from 0 column and 1 line (titles)
  for (var i = 1; i <= values.length; i++) {
    var v = values[i] && values[i][0];
    v && comboValues.push(v)
  }

  // Sort the values
  comboValues.sort(
    function(a, b) {
      if (b.toLowerCase() < a.toLowerCase()) return -1;
      if (b.toLowerCase() > a.toLowerCase()) return 1;
      return 0;
    }
  );
  Logger.log(comboValues);

  // google-form modification
  var form = FormApp.openById('1SHgVIosoE34m9cny9EQySljvgnRpzffdFEZe-kzNOzA');

  var items = form.getItems();
  for (i = 4; i < items.length; i++) {
    Logger.log("ID: " + items[i].getId(), ': ' + items[i].getType());
  }

  form.getItemById('2087613006').asListItem().setChoiceValues(comboValues);

I got issue which is related with lexicographical order. Person which register agreement choose from list last registered agreement number: i tryed to do that last registered agreement number will always be at list top. As time when i started this everything was fine (it started with number 16-T-105/M), but new year come and soon after 17-T-10/M agreement was registered i got issue, that 17-T-10/M was not on list top. Soon i realised that this happens because script use lexicographical order and "thinks" that 2 is more than 10. So i understood that i somehow will have to change that order and do that 2 is less than 10, 11 is less than 101 and so on. My question is how to do that? I guess that i need to sort array elements in natural order - but i do not have idea how to do this. I tryed to google how to do it , but result was not satisfactory - maybe my knowledge of coding is pretty limited (i am PhD student of Psychology, not Informatics) :) Maybe someone will help how to solve that problem.

Updates: Link to spreadsheet: https://docs.google.com/spreadsheets/d/1FH5qYTrLUNI2SCrcaqlwgu8lzAylaTkZsiALg0zIpCM/edit#gid=1620956794

Link to google-form (Copy of actual form): https://docs.google.com/forms/d/e/1FAIpQLSerJfkv1dgHexUwxppXNyhb46twOZgvEMOIVXSOJoED3SLmyQ/viewform

Upvotes: 1

Views: 661

Answers (1)

user6655984
user6655984

Reputation:

You should adjust the sorting method to account of the peculiarities of the data. Here is one way to do this: the function splitConvert processes each string, splitting it by non-word characters and then converting what can be converted to integers (and lowercasing the rest). Then the comparison goes through this array one by one. 

comboValues.sort(
  function(a, b) {
    var as = splitConvert(a);
    var bs = splitConvert(b);
    for (var i = 0; i < as.length; i++) {
      if (bs[i] < as[i]) return -1;
      if (bs[i] > as[i]) return 1;
    }
    return 0;
  }
);

function splitConvert(str) {
  return str.split(/\W/).map(function(part) {
    var x = parseInt(part, 10);
    return isNaN(x) ? part.toLowerCase() : x;
  });
}

This is not the most performance-oriented solution: the split-parse function will be repeatedly called on the same strings as they are being sorted. If this becomes an issue (I don't really think so), one can optimize by having one run of conversion, creating an array of arrays, and then sorting that.

Upvotes: 1

Related Questions