google-apps-script - 使用自定义比较器功能对一系列单元格中的背景颜色进行排序

Question

我使用脚本对列中特定项目的工作表进行排序。此列中的数据是一个以字母开头的数字，因此or类的常规sort方法无法正常工作。rangesheet

这是脚本，实际问题如下。

function sortIDs(){ // test on column 5
  sortOnNumbersInCol(5);
}

function sortOnNumbersInCol(col){ // numeric sort except for 2 first rows on numeric value in column col
  var sh = SpreadsheetApp.getActive().getSheetByName('copie de travail');
  var data = sh.getDataRange().getValues();
  //Logger.log('length départ='+data.length);
  var codes = data.shift();
  var headers = data.shift();
  var dataOut=[];
  for(var n=0 ; n<data.length ; n++){
    if(data[n][col] != ''){
      dataOut.push(data[n]);
      //Logger.log('data['+n+']['+col+']='+data[n][col].substring(7)+'|');
    }
  }  
  dataOut.sort(function(a,b){
    var aE,bE;
    aE=a[col].substring(7); // the numeric part comes at the 7 th position (and following)
    bE=b[col].substring(7);
    return aE - bE
  })
  dataOut.unshift(headers);
  dataOut.unshift(codes);
  sh.getRange(1,1,dataOut.length,dataOut[0].length).setValues(dataOut);
}

这非常适合数据排序，但它显然不关心单元格颜色......我的一些同事使用颜色来指定此表中的项目，当我对范围进行排序时，颜色不遵循。

所以我的问题是：如何使用我的特定标准对这张表进行排序并保持与单元格颜色的相关性？

下面是工作表数据的屏幕截图

如果我在这个范围内运行我的脚本，颜色就不会移动......那是我的问题；）

标签： google-apps-scriptgoogle-sheets

Answer 1

更新：

在没有空间复杂性的情况下执行此操作的另一种方法是对键/索引进行排序，而不是对实际数组进行排序。

function sortOnNumbersInCol(col = 5) {
  const sh = SpreadsheetApp.getActive().getSheetByName('copie de travail');
  const dataRg = sh.getDataRange();
  const noOfHeadrs = 2;
  const rg = dataRg.offset(
    //Remove  headers before getting values
    noOfHeadrs,
    0,
    dataRg.getNumRows() - noOfHeadrs,
    dataRg.getNumColumns()
  );
  let data = rg.getValues();
  let colors = rg.getBackgrounds();

  /* Filter Empty rows if needed 
  [data, colors] = [data, colors].map(arr =>
    arr.filter((_, i) => data[i][col] !== '')
  );*/

  const dataKeys = [...data.keys()];
  dataKeys.sort(function(a, b) {
    return data[a][col].substring(7) - data[b][col].substring(7);
  });
  const [dataOut, colorsOut] = [data, colors].map(arr =>
    dataKeys.map(i => arr[i])
  );
  const outRg = sh.getRange(
    noOfHeadrs + 1,
    1,
    dataOut.length,
    dataOut[0].length
  );
  outRg.setValues(dataOut);
  outRg.setBackgrounds(colorsOut);
}

---

创建{data:colors}的地图：

  const rg = sh.getDataRange();
  const data = rg.getValues();
  const colors = rg.getBackgrounds();
  const dcMap = data.reduce((mp, row, i) => mp.set(row[col], colors[i]),new Map)

然后在排序后，您可以使用排序后的值作为键并创建一个新的排序颜色数组：

  dataOut.sort(function(a,b){
    var aE,bE;
    aE=a[col].substring(7); // the numeric part comes at the 7 th position (and following)
    bE=b[col].substring(7);
    return aE - bE
  })
  dataOut.unshift(headers);
  dataOut.unshift(codes);

  const sortedColors = dataOut.map(row=>dcMap.get(row[col]));
  const outRg = sh.getRange(1,1,dataOut.length,dataOut[0].length);
  outRg.setValues(dataOut);
  outRg.setBackgrounds(sortedColors);
}