Sorting in Google apps scripts by a named column

Question

I am interested in writing a function that would sort some rows with respect to values in a named column. For example, suppose I have named the range A3:C8 as Data, and column A (i.e. A:A) as Surname, and I want to order it with respect to surname. Something like the following code does this

function sortBySurname() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();   
    var range = ss.getRangeByName("Data");

    range.sort(1); 
}

However, I would like to achieve the same without explicitly using the fact that column Surname is the first one, i.e. what's the right way to write range.sort("Surname") instead of range.sort(1)?

Anton Dementiev · Accepted Answer · 2018-02-04 13:14:39Z

2

Here's the function that takes in the header name as an argument and sorts the range under that header. Hope that helps.

Update

I updated the answer to account for your comment below

function sortByRangeName(rangeName){

  var ss = SpreadsheetApp.getActive();
  var namedRange = ss.getRangeByName(rangeName);
  var startCol = namedRange.getColumn();
  var lastCol = namedRange.getLastColumn();

  var dataRange = ss.getRangeByName("Data");
  var columnForSorting = (startCol <= dataRange.getLastColumn()) ? startCol : null;


  if (namedRange && (startCol == lastCol) && columnForSorting) {

    dataRange.sort({column: columnForSorting, ascending: false});

  } else {

    throw new Error(Utilities.formatString("Range name: %s, startCol: %s, lastCol: %s, columnForSorting: %s", header, startCol, lastCol, columnForSorting));

  }


}

edited Feb 4, 2018 at 13:14

answered Feb 4, 2018 at 1:03

Anton Dementiev

5,7344 gold badges23 silver badges36 bronze badges

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

3 Comments

Frando Mariacci Over a year ago

Thanks for this. However, this doesn't completely answer my question as my table doesn't have a header which would indicate which column is Surname. Instead, the column in question is a named range, as in Data -> Named ranges...

Anton Dementiev Over a year ago

Your named range is two-dimensional (contains multiple rows and columns). You should either assign a named range for each column or create a configuration object linking column number to it string value

Anton Dementiev Over a year ago

Updated my original answer

Collectives™ on Stack Overflow

Sorting in Google apps scripts by a named column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related