联合数据在多个选项卡中 [英] union data in multiple tabs

问题描述

我使用Google Analytics附加表单。我想运行的特定查询返回70k行数据。由于Google Analytics只能返回最多10K的时间，因此我将查询分成了7个，其中起始索引为10,001,20,001等。现在我有7个选项卡每行有10k行数据。

我想把它们放在一张大桌子里。我查看了 = query（）函数，并且没有成功地弄弄它。我也看过 = join（）但也没有成功。

即使我成功选项我不确定它们是否是最好的。

感谢所以我对Google Apps脚本有一些有限的经验（但都是非常基本的）。我不确定我是否应该尝试弄清楚如何使用脚本执行此操作，或者是否有方便的内置函数。

我也意识到违反了我的单元格数限制为200k。

例如，I在7个选项卡中已经共享数据。看起来效率低下，因为它是重复的，所以它们完全合并为一个大表。

我的最终目标是使用例如 = sum（filter（））函数和/或 = query（）函数。我需要以多种方式切分这些数据，但它目前分布在7个表格中。

1. 如何加入一个大表


2. 这是一个好主意，如果不是有更好的方法吗？

您使用的是错误的工具 - 将所有工作表导入 Google Fusion表格，然后使用一个公用密钥字段（如果它尚未存在，则在创建之前先创建一个）将其加入到新的合并表格中。

它的执行速度会快得多，您将能够过滤结果&选择带有视图的列，然后将输出下载到新的电子表格中，您也可以上传为Google表格：）

I'm using the Google Analytics add on for sheets. The particular query I'd like to run returns 70k rows of data. Since Google Analytics only returns a max of 10k ata time I broke the queries out into 7 where the start index was at 10,001, 20,001 etc.

So now I have 7 tabs of data each with 10k rows.

I would like to join them all together in one big table. I've looked into the =query() function and fiddled with it with no success. I've also looked at =join() but also no success.

Even if I was successful with those options I'm not sure they are the "best" ones either.

Thanks to SO I have some limited experience with Google Apps script (but all together pretty basic). I'm not sure if I should try to figure out how to do this with a script or if there's a handy built in function.

I'm also conscious of breaching my sheets count of cells limit of 200k.

For example, I have the data already shared amongst the 7 tabs. Seems inefficient to bring them altogether into 1 big table since that's duplication.

My end goal is to query the data as a group using for example =sum(filter()) function and/or =query() functions. I need to slice n dice this data in several ways but it's currently spread across 7 tables.

1. How can I join into one big table
2. Is on a good idea and if not is there a better way?

解决方案

You are using the wrong tool for the job - import all the sheets into Google Fusion tables, then use a single common key field (create one before uploading if it doesn't already exist) to join them in a new Merged Table.

It will execute much faster, and you will be able to filter the results & select the columns with a View, then download the output into a new spreadsheet which you can also upload as a Google Sheet :)

这篇关于联合数据在多个选项卡中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！