Google Sheets行重复与分组功能实现需求及代码求助

原本以为在Google Sheets中实现行的重复与分组操作难度不大，但目前已陷入困境。我准备了示例图，展示功能的初始状态、期望实现的分组效果，以及可接受的替代方案效果。

先写出了初步的autoDup函数和未完成的autoGroup函数，后续在@tedinoz帮助下，更新了可实现替代方案的完整代码，希望能得到进一步的技术指导。

初始代码 #

function autoDup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  for(var n in data){
    newData.push(data[n]);
    if(!Number(data[n][3])){continue};// if column 3 is not a number then do nothing
    for(var c=0 ; c < Number(data[n][3]) ; c++){
      newData.push(data[n]);//store values
    }
  }
  sheet.getRange(1,1,newData.length,newData[0].length).setValues(newData);// write new data to sheet, overwriting old data
}

function autoGroup() {
}

更新后代码 #

function autoDup(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rowLen = sheet.getLastRow()-1;
  var dataRange = sheet.getRange(2,1,rowLen,sheet.getLastColumn());
  var data = dataRange.getValues().sort();

  dataRange.expandGroups().shiftRowGroupDepth(-1);

  var newData = [];

  data = removeDups(data, rowLen);

  for(var n in data){
    
    newData.push(data[n]);
    var qty = data[n][2];

    if(!Number(qty)){continue};
    if(qty !== 1){
      for(var c=0 ; c < qty ; c++){
        newData.push([data[n][0],data[n][1],1]);//store values
      }
    }
  }
  // write new data to sheet, overwriting old data
  sheet.getRange(2,1,newData.length,newData[0].length).clearContent().setValues(newData);

}

function removeDups(data, dataLen){

  var newData = []

  for(var i=0; i<dataLen; i++){

    var valLast = data[i-1];
    var valCurrent = data[i];
    var valNext = data[i+1];

    if(valCheck(valCurrent, valNext)){
      continue;
    }else if(valCheck(valCurrent, valLast)){
      continue;
    }else {
      newData.push(valCurrent)
    }
  }
  
  return newData;
}

function autoGroup(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var rowLen = sheet.getLastRow() - 1;
  var data = sheet.getRange(2,1,rowLen,sheet.getLastColumn()).getValues();

  for(var i=0, j=0, r=0; i<rowLen; i++){
    
    var valCurrent = data[i];
    var valNext = data[i+1];

    if(valCheck(valCurrent, valNext)){
      if(j === 0){
       j++;
       r = i + 2;
      }else j++;
    }else {
      if(j !== 0){
        sheet.getRange(r,1,j+1).activate().shiftRowGroupDepth(1);
        j=0;
      }
    }
  }
  sheet.collapseAllRowGroups();

}

function valCheck(value1,value2){
  if(value1 != null && value2 != null){
    if(value1.join() === value2.join()){
      return true;
    }else return false;
  }else return false;
}

内容的提问来源于stack exchange，提问作者Brad Miller