Google表格中的自定义排序顺序 [英] Custom sort order in Google Sheets

问题描述

我正在尝试根据单个列中的值并使用自定义排序顺序对Google表格电子表格中的一系列数据进行排序。我能够对数据进行排序，但我无法弄清楚如何设置排序的优先级（按字母顺序排列或按字母顺序反向排序）。我想根据下面的顺序排序，但显然我现在只是按字母顺序排序。非常感谢！

I am trying to sort a range of data in a Google Sheets spreadsheet based on the values in a single column AND using a custom sort order. I am able to sort the data, but I can't figure out how to set the priority for sorting (other than alphabetically or reverse alphabetically). I would like to sort based on the order indicated below, but obviously what I have now just does it alphabetically. Help much appreciated!

  function onEdit() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveCell();

  //sort based on priority
    var columnToSortBy = 4;
    var tableRange = "A2:Q1000";
    var SortOrder = [
      {name: "Urgent", value:1},
      {name: "High Priority", value:2},
      {name: "Moderate Priority", value:3},
      {name: "No deadline", value:4},
      {name: "Complete", value:5}
    ];

    if(editedCell.getColumn() == columnToSortBy){   
      var range = sheet.getRange(tableRange);
      range.sort( { column : columnToSortBy } );  //this needs to be sorted based on priority level, not 
    }
  }

推荐答案

GAS文档似乎没有引用自定义排序顺序。您可以从范围中获取值，通过将自定义比较器函数传递给Array.prototype的'sort'方法对它们进行排序，最后用排序数组覆盖范围。

GAS documentation doesn't seem to reference custom sort order. You can grab the values from the range, sort them by passing the custom comparator function to the 'sort' method of Array.prototype, and, finally, overwrite the range with sorted array.

下面的例子假设第一张表中有一列需要根据'sortBy'对象设置的优先级进行排序。

The example below assumes there's a single column in the first sheet that needs to be sorted in accordance with priorities set by the 'sortBy' object.

var sheet = SpreadsheetApp.getActive().getSheets()[0];

  var range = sheet.getRange(1, 1, sheet.getLastRow(), 1);
  var values = range.getValues();

  var sortBy = {  
    "a": 2,
    "b": 0,
    "c": 4,
    "d": 1,
    "e": 3 
  };

 values.sort(function(a, b){   
   return sortBy[[a][0]] - sortBy[[b][0]];
  });


 range.setValues(values);

这是按升序排序 - 反转顺序，更改比较器功能以从下一个中减去当前元素的优先级一个。

This sorts ascending - to reverse the order, change the comparator function to subtract current element's priority from the next one.

 return sortBy[[b][0]] - sortBy[[a][0]];

这篇关于Google表格中的自定义排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！