Google Script数组过滤异常：仅返回单条而非全部19条最新数据

问题描述 #

我在Google Script中构建了一个数组，结构如下：

• Col0：responseTimeStamp（响应时间戳）
• Col1：TargetDate（目标日期）
• Col2-Col5：业务数据
• Col6（DisplayValue）、Col7（converted）：测试用，值与responseTimeStamp一致

各列从Google Sheets单独读取后合并为数组，每个TargetDate对应19条带时间戳的响应数据。需求是筛选每个TargetDate下的全部最新条目，但当前代码能识别所有TargetDate及对应最新响应时间，却只返回该组的最后一行，而非全部19条。

当前实现代码 #

var numRows = sheetCollectionFeedbackCoLi1.getLastRow()-1;
var DataFeedbackCo1_Col0 = sheetCollectionFeedbackCoLi1.getRange(2,1,numRows,1).getValues();
var DataFeedbackCo1_Col0Time = new Array ();
DataFeedbackCo1_Col0.map(function(Dat){{DataFeedbackCo1_Col0Time.push([Dat[0]*1 ] ) } });
var DataFeedbackCo1_Col0Display = sheetCollectionFeedbackCoLi1.getRange(2,1,numRows,1).getDisplayValues();
var DataFeedbackCo1_Col1 = sheetCollectionFeedbackCoLi1.getRange(2,2,numRows,1).getDisplayValues();
var DataFeedbackCo1_Col2 = sheetCollectionFeedbackCoLi1.getRange(2,3,numRows,1).getDisplayValues();
var DataFeedbackCo1_Col3 = sheetCollectionFeedbackCoLi1.getRange(2,4,numRows,1).getDisplayValues();
var DataFeedbackCo1_Col4 = sheetCollectionFeedbackCoLi1.getRange(2,5,numRows,1).getValues();
var DataFeedbackCo1_Col5 = sheetCollectionFeedbackCoLi1.getRange(2,6,numRows,1).getDisplayValues();

sheetResultCheck.getRange(1,1, sheetResultCheck.getMaxRows(), sheetResultCheck.getMaxColumns()).clear({contentsOnly: true}).clearFormat();

var new_arr_all = DataFeedbackCo1_Col0.map((e,i) => {return [e,DataFeedbackCo1_Col1[i],DataFeedbackCo1_Col2[i],DataFeedbackCo1_Col3[i],DataFeedbackCo1_Col4[i],DataFeedbackCo1_Col5[i],DataFeedbackCo1_Col0Display[i],DataFeedbackCo1_Col0Time[i]];})

var DataCol1flat = DataFeedbackCo1_Col1.flat(); 

const Arr_flat2 = [...DataCol1flat];
const unique = (value,index,self) => {return self.indexOf(value) ===index;}  ;
var Unique_List=Arr_flat2.filter(unique); 


var Arr_all_results_for_Co1 = new Array();

for (let index = 0, len = Unique_List.length ; index < len; ++index){ 
    const TargetDate = Unique_List[index];
    var Arr_result_TargetDate = new Array ();
    new_arr_all.map(function(Dat){if (Dat[1]==TargetDate){Arr_result_TargetDate.push([Dat[0], Dat[1], Dat[2], Dat[3], Dat[4], Dat[5], Dat[6], Dat[7] ] ) } });

    var Arr_Target_Date_col =new Array();
    new_arr_all.map(function(Dat){if (Dat[1]==TargetDate){Arr_Target_Date_col.push([Dat[7] ] ) } });
    
    var Arr_result_targetDate_latestresponse = Arr_Target_Date_col.flat();

    var max = Arr_result_targetDate_latestresponse.reduce(function(a, b) {
                         return new Date(a) > new Date(b) ? a : b;
                                   })
    // various attempts to filter for the latest response
    let filtered_arr_per_TargetDate = Arr_result_TargetDate.filter (row => row[7]===max);
    var nextemptyrow = sheetResultCheck.getLastRow()+1;
    sheetResultCheck.getRange(nextemptyrow,1,1,1).setValue('filtered_arr_per_TargetDate');
    sheetResultCheck.getRange(nextemptyrow+1,1,filtered_arr_per_TargetDate.length,filtered_arr_per_TargetDate[0].length).setValues(filtered_arr_per_TargetDate);
    
    var filtered_arr_per_TargetDate2 =  new Array();
    Arr_result_TargetDate.map(function (Dat){  if (Dat[7]==max)   {filtered_arr_per_TargetDate2.push([Dat[0], Dat[1], Dat[2], Dat[3], Dat[4], Dat[5], Dat[6], Dat[7]   ])}});
    // Logger.log("filtered_arr_per_TargetDate2"+filtered_arr_per_TargetDate2);

    var nextemptyrow = sheetResultCheck.getLastRow()+1;
    sheetResultCheck.getRange(nextemptyrow,1,1,1).setValue('filtered_arr_per_TargetDate2');
    sheetResultCheck.getRange(nextemptyrow+1,1,filtered_arr_per_TargetDate2.length,filtered_arr_per_TargetDate2[0].length).setValues(filtered_arr_per_TargetDate2);


    var filtered_arr_per_TargetDate3 =  Arr_result_TargetDate.filter(function(item){return item[7] === max;})
    // Logger.log("filtered_arr_per_TargetDate3 :"+filtered_arr_per_TargetDate3);

    var nextemptyrow = sheetResultCheck.getLastRow()+1;
    sheetResultCheck.getRange(nextemptyrow,1,1,1).setValue('filtered_arr_per_TargetDate3');
    sheetResultCheck.getRange(nextemptyrow+1,1,filtered_arr_per_TargetDate3.length,8).setValues(filtered_arr_per_TargetDate3);

    let Arr_result_TargetDateKopie = Arr_result_TargetDate.slice();
    var filtered_arr_per_TargetDate4 = Arr_result_TargetDateKopie.filter(function(item){return item[7] === max;});
    var nextemptyrow = sheetResultCheck.getLastRow()+1;
    sheetResultCheck.getRange(nextemptyrow,1,1,1).setValue('filtered_arr_per_TargetDate4');
    sheetResultCheck.getRange(nextemptyrow+1,1,filtered_arr_per_TargetDate4.length,8).setValues(filtered_arr_per_TargetDate4);

    

    Arr_all_results_for_Co1.push(...filtered_arr_per_TargetDate); 
  }

Logger.log("Arr_all_results_for_Co1"+Arr_all_results_for_Co1);

问题根源与解决方案 #

核心问题：类型不匹配导致筛选失效 #

你在计算最大时间戳时，把数字类型的时间戳转成了Date对象，但后续筛选时用原始数字时间戳和Date对象做全等比较，两者类型不同，永远无法匹配，仅偶然返回单条数据。

修正代码 #

直接基于数字时间戳计算最大值，无需转换为Date对象：

// 替换原max计算的代码段
var max = Arr_result_targetDate_latestresponse.reduce(function(a, b) {
  return a > b ? a : b;
});

额外优化建议 #

1. 批量读取数据：减少API调用次数，提升性能

// 替换原多列单独读取的代码
var numRows = sheetCollectionFeedbackCoLi1.getLastRow() - 1;
var rawData = sheetCollectionFeedbackCoLi1.getRange(2, 1, numRows, 6).getValues();
var rawDisplayData = sheetCollectionFeedbackCoLi1.getRange(2, 1, numRows, 6).getDisplayValues();

var new_arr_all = rawData.map((row, i) => {
  const timeStampNum = row[0] * 1;
  return [
    row[0], 
    rawDisplayData[i][1], 
    rawDisplayData[i][2], 
    rawDisplayData[i][3], 
    row[4], 
    rawDisplayData[i][5], 
    rawDisplayData[i][0], 
    timeStampNum
  ];
});

2. 简化去重逻辑：用Set实现更高效的TargetDate去重

var Unique_List = [...new Set(DataFeedbackCo1_Col1.flat())];

3. 减少重复遍历：单次遍历完成数据和时间戳收集

for (let index = 0, len = Unique_List.length ; index < len; ++index){ 
  const TargetDate = Unique_List[index];
  const Arr_result_TargetDate = [];
  const timeStamps = [];
  
  new_arr_all.forEach(Dat => {
    if (Dat[1] === TargetDate) {
      Arr_result_TargetDate.push(Dat);
      timeStamps.push(Dat[7]);
    }
  });
  
  const max = timeStamps.reduce((a, b) => a > b ? a : b);
  const filtered_arr_per_TargetDate = Arr_result_TargetDate.filter(row => row[7] === max);
  
  // 后续写入Sheet的代码保持不变
  Arr_all_results_for_Co1.push(...filtered_arr_per_TargetDate); 
}

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