时间戳

首页 > 解决方案 > 过滤重复数据集的应用脚本，但保留每个数据集的新信息

mysql - 过滤重复数据集的应用脚本，但保留每个数据集的新信息

问题描述

您好我目前有这个应用程序脚本代码，

    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
 var sheet = ss.getSheetByName("DataBase");
 var stmt = conn.createStatement();
 var temp_date_start = SpreadsheetApp.openById('1L4UrgCzwH2mZ7LBi7utiyzVqKkGKt32g3Akfs99OhHk').getSheetByName('_0_tbl_BankTracker_Rollup_Cross').getRange('J1').getValue();
 var startDate = new Date(temp_date_start);
 //calledstoredpro = "{?=call SP_Generate_BankTracker_WF_StagesReport()}";
  stmt.setQueryTimeout(0);
 stmt.setMaxRows(5000);
var results = stmt.executeQuery('call SP_Generate_BankTracker_WF_StagesReport');

var numCols = results.getMetaData().getColumnCount();
 
  
  var values = [];
  while (results.next()) {
    var temp = [];
      for (var col = 0; col < numCols; col++) {
      temp.push(results.getString(col + 1))
    }
    values.push(temp);
  }
  Logger.log(values)
 
  results.close();
  stmt.close();
    
  sheet.getRange('A1:X400').clearContent();
  
  sheet.getRange(5, 1, values.length, values[0].length).setValues(values);
  results.close
}

它正在创建重复的数据集 EX：下面

example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    1   0.15
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    2   0.02
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    3   0.12
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    5   0.07
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    6   0.06
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    7   0.08
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 testAvenue    NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    8   0.03
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 testAvenue    NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    9   0.03
example 1020083 3   22  Hillside Park Units 3 & 4   315016  15  16  12612 test Avenue   NewYork, NY House   2021-04-27  2021-05-11      144956.14   38854   106102.14   69623.62    75332.52    11  0.03
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    1   0.15
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    2   0.02
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    3   0.12
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    5   0.07
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 testAvenue    NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    6   0.06
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    7   0.08
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    8   0.03
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    9   0.03
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    10  0.03
example 1020083 3   22  Hillside Park Units 3 & 4   315018  15  18  12620 test Avenue   NewYork, NY House   2021-04-12  2021-04-30      149005.13   38854   110151.13   78508.41    70496.72    11  0.03

我尝试寻找一种方法来过滤数组并将新信息添加到匹配数据的末尾，但无法获得有效的方法。

标签： mysqlgoogle-apps-script

解决方案

什么是“匹配数据”？每条线都有一些条件？另一个具有相同长度的数组？在哪里添加？在每一行的末尾？

例如：

var a = [
    [1,2,3],
    [4,5,6],
    [7,8,9]
]
console.log(a) // original array

var b = a.map(row => row.concat(['b']))
console.log(b) // added 'b' to every row

var c = ['c1', 'c2', 'c3']
var d = b.map((row,i) => row.concat([c[i]]));
console.log(d) // added c[row] to every row

var e = d.map(row => (row[0]>1) ? row.concat(['yes']) : row.concat(['no']));
console.log(e) // added 'yes' or 'no' to every row based on first cell value

推荐阅读