Sort by Named Range in Dropdown List

0
votes

I am trying to create a sort function using Google Apps Script. I have about 20 columns in a Google sheet and want users to have the ability to sort the sheet by the click of a button rather than using the filter view because they keep on messing it up.

Rather than having 20 buttons for each column, I want one button with script which links to a dropdown list of Named Ranges being the same as the column headers.

Not sure if this is possible but this is a sample of my sheet:

Sample Sheet

I am struggling to get this script to work:

    function sortByRangeName(rangeName){
      var ss = SpreadsheetApp.getActive();
      var sheet = ss.getSheetByName('Sheet 1');
      var namedRange = ss.getRangeByName();
      var startCol = namedRange.getColumn();
      var lastCol = namedRange.getLastColumn();
      var range = sheet.getRange('E1');
      var value = range.getValue();
      var rangeName = ss.getRangeByName(value);
      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));
      }
    }

This is a link to my spreadsheet:

Sample Spreadsheet

2
google-apps-scriptgoogle-sheets

2 Answers

2
votes

Try an Installable onEdit() with this function:

function sortByColumn(e) {
  const sh=e.range.getSheet();
  if(sh.getName()=='Sheet 1' && e.range.columnStart==5 && e.range.rowStart==1 && e.value) {
    const hA=sh.getRange(2,1,1,sh.getLastColumn()).getValues()[0];
    const col={};
    hA.forEach(function(h,i){col[h]=i+1;});
    const rg=sh.getRange(3,1,sh.getLastRow()-2,sh.getLastColumn());
    rg.sort({column:col[e.value],ascending:true});
  }
}
1
votes

I believe your goal as follows.

  • You want to sort the range of "A3:E" with each column using the named ranges when the dropdown list at the cell "E1" on "Sheet 1" is selected.

Modification points:

  • In your script,

    • At var namedRange = ss.getRangeByName(), the argument is not used.
    • dataRange is not declared.
    • When above issues are resolved, the script works. But in this case, unfortunately, your goal cannot be achieve.

  • In order to achieve above goal, in this answer, the OnEdit event trigger is used.

  • In your dropdown list, it seems that there is the names including the space. Please be careful this. For this, I used trim().

Modified script:

Please copy and paste the following script to the script editor on the Google Spreadsheet. And, please select the dropdown list. By this, the values are sorted using the named ranges with the column selected by the dropdown list.

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != "Sheet 1" || range.getA1Notation() != "E1") return;
  sheet.getDataRange().offset(2, 0).sort({
    column: e.source.getRangeByName(e.value.trim()).getColumn(),
    ascending: false
  });
}

Note:

  • When the issues are removed from your script, it becomes as follows. I thought that knowing the modification points in your script might be help to study the script. So I also added this.

      function sortByRangeName(rangeName){
    rangeName = "Branch";  // This is a sample value

    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName('Sheet 1');
    var namedRange = ss.getRangeByName(rangeName.trim());  // Modified
    var startCol = namedRange.getColumn();
    var lastCol = namedRange.getLastColumn();
    var range = sheet.getRange('E1');
    var value = range.getValue();
    var rangeName = ss.getRangeByName(value);
    var dataRange = sheet.getDataRange().offset(2, 0); //  Added
    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));
    }
  }

References: