Create script to apply a custom sort range to a Google Sheet

Question

I have a Google Sheet containing several hundred events. I regularly change the sort as I work, but the "final" sort order is by Country > Region > City > Start Date (defined by column headers of the same names).

I can apply this via the Data > Sort Range command, but I wonder if it's possible to create a script or "button" that can run this functionality for me?

Answer 1

You can "record" yourself doing that using the menu Item Tools>Macros>Record a Macro.

Then it can be saved so you can run it whenever you like.

Answer 2

function sort() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet Name');
  const headerrow = 1;
  const hA = sh.getRange(headerrow, 1, 1, sh.getLastColumn()).getValues()[0];
  const startrow = 2;
  let col = {};
  hA.forEach((h, i) => { col[h] = i + 1 });
  sh.getRange(startrow, 1, sh.getLastRow(), sh.getLastColumn()).sort([{ column: col['Country'], ascending: true }, { column: col['Region'], ascending: true }, { column: col['City'], ascending: true }, { column: col['Start Date'], ascending: true }]);
}

Answer 3

Apart from the solutions that have already been mentioned, you can also:

Create your own "button" and assign a script to it:

1. Go to Insert > Drawing and design your button

2. Assign a script to it and type in the name of the function which ends up sorting your data

3. You now have a button which sorts your data!

Create a custom menu using an onOpen trigger

You can make use of Apps Script's onOpen trigger in order to create a custom menu in your spreadsheet. This basically means that every time the spreadsheet is being opened, the new custom menu will get created and it will look something similar to this:

As for the code for this, it will end up following this structure more or less:

function onOpen(e) {
  SpreadsheetApp.getUi() 
      .createMenu('Sorting Menu')
      .addItem('Sorting A-Z', 'sortAZ')
      .addToUi();
}

function sortAZ() {
  //function to sort your data
}

Reference

• Apps Script Simple Triggers.