我一直在为Google表中的项目资源管理建立解决方案。
这个想法是，团队经理将每周在一行中以每周的水平输入每个项目的资源需求。对于最终用户来说，这是一个非常用户友好且简单的解决方案。有关输入表的说明，请参见下图。
input_sheet
由于我的组织有多个团队，我们希望为每个团队提供一个单独的工作表。为了使报告仍然集中，我将每个纸连接到BigQuery，在那里我将来自不同表格的数据组合在一起，并最终在Power BI中创建报告。
当前，我使用的是Google功能的组合，例如 Query，arrayformula，split，flatten 将数据传输到合适的格式中，该格式在下图中显示。为了在数据库中使用数据，我需要将数据从水平格式转换为垂直格式。
result_sheet
我当前的问题是大约有300行和100列，并且在这一点上a简单的Google功能变得太重而无法运行。我现在正在寻找一种使用应用程序脚本进行处理的解决方案，如果这将被证明是一个更有效的解决方案，并且可以允许我为运行脚本的频率添加一些逻辑。
亲爱的专家，您是否看到这样的事情在应用程序脚本中可以做到这一点？我有一些编码经验，但是我是应用程序脚本的新手。我尤其在生产笛卡尔产品方面挣扎，这将使我能够将日期与需求联系起来。
我在下面添加了一个示例表的链接。
链接到表：
I have been building a solution for project resource management in Google Sheets.
The idea is that the team manager would input the resource demand per project on a weekly level on a single row. This is a very user friendly and easy solution for the end user. See image below for a description of what the input sheet looks like.
Input_Sheet
Since my organization has multiple teams, we want to have a separate sheet for each team. In order to still keep the reporting centralized, I have connected each sheet into BigQuery where I am combining the data from different sheets and finally creating reports in Power BI.
Currently, I am using a mix of google functions such as QUERY, ARRAYFORMULA, SPLIT, FLATTEN to transpose the data into a database suitable format, which is presented in the image below. In order to have the data in a database, I need to transpose the data from a horizontal format into a vertical format.
Result_Sheet
My current issue is that there can be around 300 rows and 100 columns and at this point a simple google function is getting too heavy to run. I am now looking for a solution to do the processing using apps script, if that would prove to be a more efficient solution and would allow me to add some logic for how often the script is run.
Dear Experts, do you see that something like this would be possible to do in apps script and how should one do this? I have some coding experience, but I'm new to apps script. I am struggling especially with producing the Cartesian Product, which would allow me to link date with the demand.
I have added a link to my example sheet below.
Link to sheet: https://docs.google.com/spreadsheets/d/1XKyt3BAo5L2RsK2vYpqrlBuEZoehIztGJ_Nl2DN-h-8/edit?usp=sharing
发布评论
评论（1）
{array表达式＆nbsp;}
使用 =“ https://en.wikipedia.org/wiki/cartesian_product” rel =“ nofollow noreferrer”> cartesian产品。它更像是不分散。
请参阅新的
Use an { array expression }, like this:
This is not a Cartesian product. It is more like unpivot.
See the new Solution sheet in your sample spreadsheet.