Sort grouped ranges of rows - Google Sheets

Question

Example Sheet: Link

I have a fairly small data set that I sort frequently and to avoid sorting manually every time, I have created a basic sort in apps-script that works but is far from ideal. I was hoping there is a more straight forward sort method without needing any input from google formulas.

The data is set out like as (left) and once sorted as (right):

The apps-script I have so far looks like:

function sortSheet() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var data = ss.getSheetByName('Data');

  var lA = data.getRange('B2').getValue(); //Retuns the row for the first occurance of this value. Via.(=MATCH($A2,'Sheet1'!$A:$A,0)) Where Match(Value,Range to check) on the 'data' sheet.
  var nA = data.getRange('C2').getValue(); //Returns the total number of these values. Via. (=COUNTIF('Sheet1'!$A:$A,$A2)) Where CountIF(Range to check,Value) on the 'data' sheet.
  var sortA = sheet.getRange('A'+lA+':Y'+(lA+nA)); //Selects the range that contains the values to be sorted. Via. A through Y within the specified rows.
  sortA.sort({column: 2, ascending: true}) //Sorts range by column 2.

  var lB = data.getRange('B3').getValue();
  var nB = data.getRange('C3').getValue();
  var sortB = sheet.getRange('A'+lB+':Y'+(lB+nB));
  sortB.sort({column: 2, ascending: true})

  var lC = data.getRange('B4').getValue();
  var nC = data.getRange('C4').getValue();
  var sortC = sheet.getRange('A'+lC+':Y'+(lC+nC));
  sortC.sort({column: 2, ascending: true})

}

This script is reliant on the information given from the sheet "Data". Within this sheet it takes the first row that the given key is found in column B and the count of that key in column C.

The script then gets the required range to sort and runs through them one at a time. Anyone be able to assist with simplifying this operation or going about it in an easier way?

Aerials Aerials · Accepted Answer · 2020-02-05T13:53:21

You can try collecting your data in a sheet for each ID.

Your sheets would be something like sheets = ["Sheet1","ID A","ID B","ID C",...]

And then:

Set up the ID sheets to be sorted ascending by applying sort.
Make the cells of your choosing in Sheet1 to point = to each ID sheet.

From your comment you have some manual inputs to the sheet. So perhaps you could try using a Google Form for the manual inputs, and use Apps Script triggers to build your Sheet1 when changes are made to the sheet.

Also, the Sheets.getDataRange() method could be useful to you.

Read through the SpreadsheetApp service's Range class, there are methods like and getLastRow() or getNumRows() that might help.

Sort grouped ranges of rows - Google Sheets

2 Answers