0

Right now I have this Google Spreadsheet code I pieced together, which does 2 types of sorting:

  1. By character count, then alphabetically.
  2. By syllable count, then alphabetically.

Here is the code:

const VOWEL_PATTERN = /[ieaou]/i;

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Sort")
    .addItem("Sort by length then alphabetically", "sortByLength")
    .addItem("Sort by syllables then alphabetically", "sortBySyllable")
    .addToUi();
}

function sortBySyllable() {
  const range = SpreadsheetApp.getActive().getDataRange();
  const array = range.getValues();
  const sortedArray = [array[0]].concat(
    array.slice(1).sort((a, b) => {
      const xp = a[0];
      const yp = b[0];
      return (
        xp.split(VOWEL_PATTERN).length - yp.split(VOWEL_PATTERN).length ||
        xp.length - yp.length ||
        xp.localeCompare(yp)
      );
    })
  );
  range.setValues(sortedArray);
}

function sortByLength() {
  const range = SpreadsheetApp.getActive().getDataRange();
  const array = range.getValues();
  const sortedArray = [array[0]].concat(
    array.slice(1).sort((a, b) => {
      const xp = a[0];
      const yp = b[0];
      return xp.length - yp.length || xp.localeCompare(yp);
    })
  );
  range.setValues(sortedArray);
}

That works fine, given that it sorts according to the standard unicode sorting algorithm (I guess?).

However, I am working on a fantasy language, and in my spreadsheet I want to sort the letters in a particular order. Let's say this is the order I want to sort them in:

const ALPHABETICAL_ORDER = 'ieaoumnqgdbptkhsfvzjxcCwylr'

How do I then somewhat efficiently sort the string by this custom alphabetical order?

2

1 Answer 1

0

You could replace the characters by another alphabe which reflects the order of the characters and sort by the replacements.

const
    ALPHABETICAL_ORDER = 'ieaoumnqgdbptkhsfvzjxcCwylr',
    by = order => (a, b) => {
        const
            values = Object.fromEntries(Array.from(order, (c, i) => [c, String.fromCharCode(i + 33)])),
            convert = s => Array.from(s, c => values[c] || c).join(''),
            aa = convert(a),
            bb = convert(b);
            
        return (aa > bb) - (aa < bb);
    },
    data = ['a', 'an', 'be', 'in', 'out', 'from', 'go', 'can', 'CAL', 'cC', 'CC', 'Cc', 'cc'];

console.log(...data);
data.sort(by(ALPHABETICAL_ORDER));

console.log(...data);

Another approach by repecting other characters.

unsorted: a an be in out from go can CAL cC CC Cc cc
sorted:   in a an out go be from can cc cC Cc CC CAL

const
    ALPHABETICAL_ORDER = 'ieaoumnqgdbptkhsfvzjxcCwylr',
    data = ['a', 'an', 'be', 'in', 'out', 'from', 'go', 'can', 'CAL', 'cC', 'CC', 'Cc', 'cc'],
    values = Object.fromEntries(Array.from(ALPHABETICAL_ORDER, (c, i) => [c, String.fromCharCode(i + 65)])),
    result = data
        .map((s, i) => ({ i, v: Array.from(s, c => c in values ? ' ' + values[c] : c + ' ').join('') }))
        .sort((a, b) => a.v.localeCompare(b.v))
        .map(({ i }) => data[i]);

console.log(result);
.as-console-wrapper { max-height: 100% !important; top: 0; }

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

2 Comments

Note, I have a c and C in there, but doing this I think will result in those being together, rather than separated. How can you solve for that?
@lance, please have a look to the answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.