google-apps-script - 尝试在具有大型数据集的工作表中工作

Question

我在 Google Drive 的“我的仪表板”表中有一个自动仪表板。

仪表板的数据通过 API 来自 Google Analytics (GA)。我能够使用 Google Sheets GA 插件获取的大多数数据。

我的仪表板中其中一个表的源数据非常大 - 太大而无法放入工作表本身。

因此，借助一些有限的脚本技能以及本论坛和一些在线教程的帮助，我在 Google-Apps-Script 中创建了一个脚本，用于查询 GA api 并返回我需要的所有数据，然后将其放入 csv 文件中与主仪表板相同的目录。

所以现在，在 Drive 的“仪表板”文件夹中，我有 2 个文件:“我的仪表板” - 一个工作表和“我的数据”一个 csv 文件。如果我愿意，我可以将 api 调用的结果输出到我只是认为 csv 文件会更有效的工作表。

我在 Gsheet 中打开了“我的数据”csv 文件并将其称为“组合”。以下是数据的示例:

ga:year ga:month    ga:medium   ga:source   ga:campaign ga:goal1Completions
2013    5   (none)  (direct)    (not set)   116
2013    5   (not set)   Adperio silvercontact?dp    0
2013    5   (not set)   Conde*it    _medium=email   0
2013    5   (not set)   Hearst  (not set)   0
2013    5   (not set)   stackersocial   stackersocial   0
2013    5   12111   9591    201fHN000xRGGszT3aEAA11uIsaT000.    0
2013    5   12111   9591    201fHN00BrT.K.AY0Hvf3q1uIqgl000.    0
2013    5   12111   9591    201fHN00CK619oBE3OsBZp1uIqGX000.    0
2013    5   12111   9591    201fHN00DFXJxnUu1jx25M1uIzkC000.    0

有 ~ 65k 行数据。

现在，在我的仪表板中，我需要一个表格来对“组合”表中的数据进行分组和聚合(或者我可以以某种方式使用 csv？)。我首选的公式通常是例如
=sum(filter(ga:goal1Completions, ga:year="2015"... ))
事实证明，提取和查询这些数据很困难，我希望得到一些建议。

我不能简单地将数据导入我的仪表板，因为我收到有关超过 200k 单元格的工作表最大大小的警报。所以每次都要靠公式来导入数据和运行计算。这是我刚刚使用的示例:

=SUM(filter(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!F2:F"),
IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!A2:A")=year(G$17),
IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!B2:B")=month(G$17),
IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!C2:C")="(direct)",
IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit?usp=sharing","combined!D2:D")="(none)"))

因此，对于单元格中函数中的每个参数，我必须导入数据列并对其进行子集化。

我相信一定有更好的方法。现在这确实有效，但速度非常慢。

我正在考虑的大道:

SO上有人提到使用缓存服务here .但是这将如何工作，或者我如何将它与上面的 IMPORTRANGE() 函数集成？

拥有 csv 文件有什么好处，还是应该从一开始就将结果输出到 GSheet 中？

在我运行以获取数据的脚本中，在将其输出到 csv 文件(或 Gsheet，如果更好？)之前，我将数据作为数组转换为文件之前。我可以在这里做些什么，比如直接从工作表中查询这个数组？请记住，我的脚本技能非常基础。

大概我可以创建一个函数来为函数所在的每个单元格单独调用 GA api，返回每个单元格中的结果(因此在上面的示例中，一个函数来调用 ga api for where year=year(G17) &month=month(G17)&medium=[some_other_cell_reference)。所以这个选项会导致更多的 api 调用，但会导致处理的数据更小。不确定这是一个好主意还是完全走错了方向。

我希望我已经充分传达了我的问题。我需要找到一种更有效的方式来查询仪表板中的外部数据。

Answer 1

不是通过使用多个 IMPORTRANGE 和 FILTER 一次导入一列，而是使用一个 IMPORTRANGE 以单步方式提取您需要的所有列并使用 QUERY做你需要的操作。

与在 FILTER 中包含 IMPORTRANGE 的方式相同，您也可以将其包含在 QUERY 中

IE。为简单起见，我将只添加一个过滤条件

`=QUERY(IMPORTRANGE("key","range"),"select sum(Col6) where Col1 ='"&G$17&'")`