Google表格：使用Apps腳本根據下拉值自動對資料行進行排序

我有這個腳本可以根據下拉串列中選擇的專案對資料行進行自動排序，但它不起作用。誰能弄清楚為什么？

    function onEdit(e) {
      
      const as = e.source.getActiveSheet();

      var ordering = {
      "Approved":1, //move to the top
      "Accept": 2,
      "Hold": 3,
      "Decline": 4, //move to the bottom
      };
    
      var compareIndex = 13;
      var range = as.getRange(4,1,as.getLastRow(),as[0].length);
      var values = range.getValues();
    
      values.sort(function(a,b){
        return ordering[[a][compareIndex]] - ordering[[b][compareIndex]];
      });
      range.setValues(values);
    }

這是作業表的樣子：

目標是根據從下拉串列（M 列）中選擇的狀態將資料行移動到適當的位置。“拒絕”將移至底部，而“已批準”將移至頂部 - “接受”和“保留”將在它們之間，按照腳本中顯示的順序。

To add another layer - I would ideally also want any dropdowns which have nothing selected to stay at the top of the sheet (with any row with a selected value from the dropdown to be sorted below) and I would like each category (i.e., Approved) to be sorted by date (Column A) with the most recent date at the top of each Status group.

Can anyone help with this?

Thanks!

uj5u.com熱心網友回復：

修改點：

• 當我看到您更新的腳本時，似乎as是const as = e.source.getActiveSheet();. 在這種情況下，我認為在as[0].lengthof處發生錯誤var range = as.getRange(4,1,as.getLastRow(),as[0].length);。
• 如果valuesofvar values = range.getValues();是您的資料范圍的正確值，我認為ordering[[a][compareIndex]] - ordering[[b][compareIndex]]應該是ordering[a[compareIndex - 1]] - ordering[b[compareIndex - 1]]. 但是，從您的示例輸出情況來看，如果要將空行放在資料的上方，則應該是(ordering[a[compareIndex - 1]] || 0) - (ordering[b[compareIndex - 1]] || 0).

當這些點反映在你的腳本中時，它變成如下。

修改后的腳本：

function onEdit(e) {
  const sheet = e.range.getSheet();
  var compareIndex = 13;
  // if (sheet.getSheetName() != "Sheet1" || e.range.columnStart != compareIndex || e.range.rowStart < 4) return; // If you want to restrict the edited cells, please use this. At that time, please set the sheet name.
  var ordering = {
    "Approved": 1, //move to the top
    "Accept": 2,
    "Hold": 3,
    "Decline": 4, //move to the bottom
  };
  var range = sheet.getRange(4, 1, sheet.getLastRow() - 3, sheet.getLastColumn());
  var values = range.getValues();
  values.sort(function (a, b) {
    return (ordering[a[compareIndex - 1]] || 0) - (ordering[b[compareIndex - 1]] || 0);
  });
  range.setValues(values);
}

• 如果要將空行放到資料的下方，請修改return (ordering[a[compareIndex - 1]] || 0) - (ordering[b[compareIndex - 1]] || 0);為return (ordering[a[compareIndex - 1]] || 5) - (ordering[b[compareIndex - 1]] || 5);.

uj5u.com熱心網友回復：

排序行

function onEdit(e) {
  //e.source.toast("Entry")
  const sh = e.range.getSheet();
  if (sh.getName() == 'SheetName' && e.range.columnStart == 13 && e.range.rowStart > 3) {
    //e.source.toast('Sorting')
    const oA = ["Approve", "Accept", "Hold", "Decline",""];
    vs = sh.getRange(4,1,sh.getLastRow() - 3, sh.getLastColumn()).getValues();
    vs.sort((a,b) => {
      let vA = oA.indexOf(a[12]);
      let vB = oA.indexOf(b[12]);
      return vA - vB;
    });
  }
  sh.getRange(4,1,vs.length,vs[0].length).setValues(vs);
  //e.source.toast('Done');
}

• 種類

標籤：google-apps-script google-sheets

上一篇：回圈無法正常作業。第一天嘗試在應用腳本中寫東西

下一篇：如何禁用pythonGTK主題或GUI