按2个变量排序 [英] Sorting by 2 variables

问题描述

这是对我先前回答的问题的跟进:

代码:

 函数filternsort(){var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet3');//获取sheet1数据var val = sheet.getDataRange().getValues();var complete = [];var notcompleted = [];//从1开始排除标题for(var i = 1; i< val.length; i ++){if(val [i] [5] .toString().match(/Completed.+/)){//如果与到期日期"列中的已完成"一词匹配，则将其添加到已完成"数组中complete.push(val [i]);}别的{//如果不匹配，则添加到未完成的数组.notcompleted.push(val [i]);}}//将标头插入数组的开头complete.unshift(val [0]);//设置过滤后的值sheet2.getRange(1，1，completed.length，completed [0] .length).setValues(completed);//按人排序sheet2.getRange(2，1，completed.length，completed [0] .length).sort(1);//将标头插入数组的开头notcompleted.unshift(val [0]);//设置过滤后的值sheet3.getRange(1，1，notcompleted.length，notcompleted [0] .length).setValues(notcompleted);//按人排序sheet3.getRange(2，1，notcompleted.length，notcompleted [0] .length).sort(1);} 

第2张:

第3张:

参考文献:

• Array.prototype.unshift()
• Range.sort()
• String.prototype.match()

This is a follow up to my previous question that was answered: How to Not Transfer Blank Column to Master Sheet?

2 new questions.

1. How do I sort by 2 items? I need to sort by Person and their Status (ie Completed). In the status cell is also a date, I just want it to find the completed part.
2. How to skip if the status is completed? Both of these are for the page that has already been combined. Hope this makes sense.

Sample Sheet: 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);
}

解决方案

Note: In my example, I removed the Other Data columns from your example to make the sheet cleaner. I also added Person column.

Try this:

Sheet1:

Code:

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);  
}

Sheet2:

Sheet3:

References:

• Array.prototype.unshift()
• Range.sort()
• String.prototype.match()

这篇关于按2个变量排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！