时间戳

首页 > 解决方案 > 如何在谷歌表格中编译数据表？

javascript - 如何在谷歌表格中编译数据表？

问题描述

我充其量是一个完整的脚本小猫，我正在尝试使用谷歌应用程序脚本来为我自动化一个过程。这里有一个像我正在使用的示例表（数据在谷歌表格电子表格中）：

种类 类别 彼得 考特尼
DP HBC 0.00 4.5
DP MNG 2.00 0
UB THN 7.00 0
471H THN 5.00 0
471H THN 0.00 5
生长激素 FST 4.00 0
生长激素 THN 8.00 0
生长激素 THN 0.00 8
生长激素 THN 0.00 8
HM HBC 6.50 0
HM HBC 0.00 6.5
HM MNG 2.00 0
HM MNG 0.00 2
CL HBC 8.50 0
CL HBC 7.00 0
埃因霍温 HBC 2.50 0
埃因霍温 HBC 7.00 0
埃因霍温 HBC 0.00 2.5

该表显示了按品种和类别细分的员工报告的工作时间。我的目标是编译这些数据，因此如果员工在同一类别和同一品种工作，我希望所有时间都在一行中表示。例如，如果您查看数据的最后两行，您会注意到 Variety 和 Category 列是匹配的，这意味着应该编译数据。所以而不是：

种类 类别 彼得 考特尼
埃因霍温 HBC 7.00 0
埃因霍温 HBC 0.00 2.5

我想像这样编译数据：

种类 类别 彼得 考特尼
埃因霍温 HBC 7.00 2.5

我正在尝试从下到上执行此操作，因为我听说从上到下可能会导致问题，但我愿意接受任何建议。我想我需要编写一个 for 循环，将活动行的品种框与上一行的品种框进行比较，并比较活动行的类别框是否等于上一行的类别框，如果两者都是true 添加“Peter”列和“Courtney”列的小时数。

我知道除此之外还有更多工作要做，但这个 for 循环是我真正需要帮助的地方，除非你能提出更好的方法？

谢谢你看我的作品，JP

Stackoverflow 成员 Ruben 要求我详细说明，这里是我所在的位置：

我找到了一个 stackoverflow 的答案，将我推向了如何使用 for 循环解析数据的方向。这是我正在使用的 for 循环骨架：

function project() {

    var ss = SpreadsheetApp.getActiveSheet();
    var sheet = ss.getSheetByName("gest");
    var data = sheet.getRange('A2:F19').getValues();
// Category and Variety columns
    var leadV = sheet.getRange('B18');
    var followV = sheet.getRange('B19');
    var leadC = sheet.getRange('C18');
    var followC = sheet.getRange('C19');
// Hour columns
    var leadH1 = sheet.getRange('E18');
    var followH1 = sheet.getRange('E19');
    var leadH2 = sheet.getRange('F18');
    var followH2 = sheet.getRange('F19');

    
    
    for (var i = 0 ;i < data.length ; i++)
    if leadV === followV AND leadC === followC, 
    // add both hours columns together
        leadH1.getValue()+followH1.getValue()
        leadH2.getValue()+followH2.getValue();
        //Delete active row(not made yet)
        else continue;
    sheet.getRange(18-i, 2).activate();

我的想法是有一个“领先”行和一个“跟随”行，它们会比较自己并通过 for 循环逐一处理数据。我意识到我需要使用 getActiveCell()，而不是 getRange()，但我不明白如何将相邻单元格与活动单元格进行比较，同时将其包装到 for 循环中。我只是有点不知所措，我想我可能把我的情况复杂化了。

标签： javascriptgoogle-apps-scriptgoogle-sheets

解决方案

如果您愿意使用公式而不是脚本，我可以提出一个解决方案。（在我自己几十年的实践中，我只在公式无法产生预期结果的时候保存脚本；这大大减少了潜在问题。）

此解决方案使用您帖子中脚本中的信息来确定源工作表的名称是“gest”，并且要包含在新报告中的数据范围是 B:C 和 E:F。

创建一个新工作表并将以下公式放在单元格 A1 中：

=ArrayFormula({gest!B1:C1, gest!E1:F1; QUERY({gest!B2:C, E2:F},"Select Col1, Col2, SUM(Col3), SUM(Col4) WHERE Col1 Is Not Null GROUP BY Col1, Col2 LABEL SUM(Col3) '', SUM(Col4) ''")})

这个单一公式将生成所有标题和结果，并在您在“gest”表中添加新数据行时“跟上”。

在大括号 { } 之间形成一个虚拟数组。这是一种以新方式将非连续数据“粘在一起”的方式。

标题由分号前面的两个引用抓取。

然后QUERY从目标列中的非标题信息形成a。

用简单的英语， “返回四列：确切地说是在第一个请求的列和第二个请求的列中Select的QUERY内容，然后是来自第三和第四个请求的列的分类总和，将这些总和分开（即“ GROUPing”）通过前两列中的每个唯一配对。” 该LABEL部分只是从创建的总和列中删除技术标题（否则会在每个总和列上方放置“总和”的股票标题）。

推荐阅读