在Google Sheets中按姓名与ID整理数据：排序及列复制

核心需求 #

• 从Google Sheets的「Log表」中，按指定日期范围筛选指定列数据并复制到目标表
• 按姓名+ID分组排序
• 每组顶部添加汇总行，包含：ID、日期范围、姓名、总工时、总薪资
• 每组末尾添加分隔空行
• 额外需求：能否在下拉菜单中加入复选框（如附件GIF所示）

已尝试的方法 #

公式 #

=QUERY(Log!B:S, "select J,L,O,P,S where B = "&A2&" and J >= date '"&TEXT(L2,"yyyy-mm-dd")&"' and J <= date '"&TEXT(V2,"yyyy-mm-dd")&"'",0)
=Filter({Log!J:J,Log!L:L,Log!T:T,Log!AB:AB,Log!P:P,Log!X:X,Log!AF:AF,Log!O:O,Log!S:S},Log!B:B=A1,Log!J:J>=L1,Log!J:J<=V1)

Apps Script脚本 #

function groupingdata() {
var groupwiseSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("groupwise");
var logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log");
var empCode = groupwiseSheet.getRange("A1").getValue();
var nameRange = groupwiseSheet.getRange("B1:E1");
var fromDateRange = groupwiseSheet.getRange("L1");
var toDateRange = groupwiseSheet.getRange("V1");


// Get the merged value for the name range
var name = nameRange.getMergedRanges()[0].getValue();

// Get the value for the from date range and convert to Date object
var fromDate = new Date(fromDateRange.getValue());

// Get the value for the to date range and convert to Date object
var toDate = new Date(toDateRange.getValue());

var logData = logSheet.getRange("A1:BM").getValues();
var groupwiseData = [];

for (var i = 1; i < logData.length; i++) {
    if (logData[i][1] >= fromDate && logData[i][1] <= toDate && logData[i][2] == empCode && logData[i][3] == name) {

    groupwiseData.push([
    logData[i][0], // A to A (LogID)
    logData[i][1], // B to B (DATE)
    '=IMAGE("' + logData[i][5] + '", 4, 100, 100)', // C
    '=IMAGE("' + logData[i][13] + '", 4, 100, 100)', // D
      null, // E (skip)
      null, // F (skip)
    logData[i][6],  // G to G  / 1st ClockIn 
    logData[i][7],  // H to H / 1st In Location / H to H
    logData[i][8],  // I to I / 1-Activity to I
    logData[i][10], // K to J / 1-Project  K to J
    logData[i][12], // M to K  / 1-Note
    logData[i][22], // W to L / 2-IN-TIME
    logData[i][23], // X to M / 2-IN-Loc
    logData[i][24], // Y to N /2-Activity
    logData[i][26], // AA to O  / 2-Project
    logData[i][28], // AC to P  / 2-Note  
    logData[i][37], // AL to Q  / 3-IN-TIME   
    logData[i][39], // AN to R  /3-IN-Loc
    logData[i][40], // AO to S /3-Activity
    logData[i][42], // AQ to T /3-Project
    logData[i][44], // AS to U /3-Note
    logData[i][14], // O to V /1-OUT-TIME 
    logData[i][15], // P to W /1-OUT-Loc
    logData[i][16], // Q to X /1-OUT-Activity
    logData[i][20], // U to Y /1-OUT-Note
    logData[i][30], // AE to Z /2-OUT-TIME
    logData[i][31], // AF to AA /2-OUT-Loc
    logData[i][32], // AG to AB /2-OUT-Activity
    logData[i][36], // AK to AC /2-OUT-Note
    logData[i][46], // AU to AD /3-OUT-TIME
    logData[i][47], // AV to AE /3-OUT-Loc
    logData[i][48], // AW to AF /3-OUT-Activity
    logData[i][52], // BA to AG /3-OUT-Note
     
   
    //logData[i][55], // BC to AH + Symbol  
    //logData[i][56], // BD to AI Formula Add Hrs
    //logData[i][57], // BE to AJ Formula SiteMark
    //logData[i][58], // BF to AK Empty empty
    //logData[i][59], // BG to AL Formula Recorded Hrs
    //logData[i][60], // BH to AM Formula Auto LunchHr Adjust
    //logData[i][61], // BI to AN Formula Manual Hrs Adjust        

  ]);
}

  }}

当前问题 #

使用上述公式和脚本，无法实现按姓名分组、添加分隔行及汇总行的效果，恳请指导实现。

期望输出效果 #

按姓名分组，每组顶部显示ID、日期范围、姓名、总工时、总薪资，下方为对应明细行，每组末尾添加分隔空行（如参考表格所示）。

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