sorting - 按 2 个变量排序

Question

这是对我之前回答的问题的跟进：如何不将空白列转移到主表？

2个新问题。

1. 如何按 2 个项目排序？我需要按人员及其状态（即已完成）进行排序。在状态单元格中也是一个日期，我只想让它找到已完成的部分。
2. 如果状态已完成如何跳过？这两个都是针对已经合并的页面。希望这是有道理的。

样本表：https ://docs.google.com/spreadsheets/d/1Y48N8W8CdaNlrxXopj5lnHTiNep33g_qA2-kTrPdt3A/edit#gid=130911536

function combineSheets() {
      const sApp = SpreadsheetApp.getActiveSpreadsheet();
      const months = ['January','February','March','April','May','June','July',
                     'August','September','October','November','December'];  
      const master = sApp.getSheetByName('Master');           
      const sourceData = [];
      months.forEach(m=>{
         let sh = sApp.getSheetByName(m);
         let vals = sh.getRange(1,2,sh.getLastRow(),27).getValues().filter(r=>r[0]!='');
           sourceData.push(...vals);
      });
      master.getRange(1,1,sourceData.length,sourceData[0].length).setValues(sourceData);
}

标签： sortinggoogle-apps-scriptgoogle-sheets

Answer 1

注意： 在我的示例中，我Other Data从您的示例中删除了列以使工作表更清洁。我还添加了Person专栏。

尝试这个：

表 1：

代码：

function filternsort() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');
  //get sheet1 data
  var val = sheet.getDataRange().getValues();
  var completed = [];
  var notcompleted = [];
  //start to 1 to exclude header
  for (var i = 1; i < val.length; i++) {
    if(val[i][5].toString().match(/Completed.+/)){
      //add to array completed if it matches the word Completed in the Date Due column
      completed.push(val[i]);
    }else{
      //add to array notcompleted if not match.
      notcompleted.push(val[i]);
    }
  }
  //insert header to the beginning of the array
  completed.unshift(val[0]);
  //set filtered values
  sheet2.getRange(1, 1, completed.length, completed[0].length).setValues(completed);
  //sort by Person
  sheet2.getRange(2, 1, completed.length, completed[0].length).sort(1);

  //insert header to the beginning of the array
  notcompleted.unshift(val[0]);
  //set filtered values
  sheet3.getRange(1, 1, notcompleted.length, notcompleted[0].length).setValues(notcompleted);
  //sort by Person
  sheet3.getRange(2, 1, notcompleted.length, notcompleted[0].length).sort(1);  
}

表2：

表 3：

参考：

• Array.prototype.unshift()
• 范围.sort()
• String.prototype.match()