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

我在Google云端硬盘的“我的仪表板”工作表中有一个自动仪表板。

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

我在仪表板中的表格之一的源数据非常大-太大而无法容纳工作表本身。

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

因此，现在，在云端硬盘的“仪表板”文件夹中，我有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)"))

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

我相信肯定有更好的方法。现在，它确实可以工作，但是速度非常慢。

我在考虑的大道:

有人提到使用缓存服务here。但是，该如何工作或如何将其与上面的IMPORTRANGE()函数集成在一起？

拥有一个csv文件是否有任何好处，还是我应该从一开始就将结果输出到GSheet中？

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

大概我可以创建一个函数，以针对该函数所在的每个单元格分别调用GA api，并返回每个单独单元格中的结果(因此，在上面的示例中，该函数针对year = year(G17 )＆month = month(G17)＆medium = [some_other_cell_reference)。因此，此选项将导致更多的api调用，但会导致处理的数据更少。不知道这是一个好主意还是完全走错了方向。

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

最佳答案

不必使用多个IMPORTRANGE和FILTER一次导入一列，而是使用一个IMPORTRANGE一步提取所需的所有列，并使用QUERY进行所需的操作。

就像将IMPORTRANGE包含在FILTER中一样，您也可以将其包含在QUERY中

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

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

关于google-apps-script - 尝试在具有大型数据集的图纸中工作，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30759200/