使用Google脚本将多个Google表格查询到一个视图 [英] Using Google Scripts to Query Multiple Google Sheets to A View
问题描述
我有一个Google Spreadsheet，用于存储我的工作场所的员工可用性信息.每个工作表都具有以下示例中显示的格式，日期字段根据我要收集其可用性的星期而变化.员工每周通过在后端提交与这些表格链接的Google表单来提交可用性.我的工作场所领导已指示我限制每个工作表最多包含一周的数据.
I have a Google Spreadsheet that stores employee availability information for my workplace. Every Sheet has the format shown in the example below, with the date fields changing based on the week for which I am collecting availability. Employees submit availability every week by submitting a Google Form that is linked to these Sheets on the backend. My workplace leadership has directed that I limit each Sheet to contain at most one week of data.
7月9日至7月13日回复
Timestamp | Email Address | What is your name? | [Mon Jul 2018] | [Tue Jul 10 2018] | ... | [Fri Jul 13 2018] | Comments
7/9/2018 16:30:48 | johndoe@aol.com | John Doe | Available | Not Available | ... | Available | Must leave by 3:30 p.m.
7/9/2018 16:32:51 | janedoe@aol.com | Jane Doe | Not Available | Available | ... | Available | None
我遇到的问题是我的老板希望我生成一个涵盖任意日期范围的员工可用性的清晰视图.换句话说，他希望可以选择一个日期范围，并查看该日期范围内每天可用的所有员工.例如，这对于生成每月可用性视图是有问题的，因为它涉及解析多个工作表，每个工作表覆盖一个星期.
The problem I am encountering is that my boss wants me to generate a clean view of employee availability covering an arbitrary date span. In other words, he wants to have the option of selecting a date span and seeing all the employees who are available on each day in that date span. This is problematic for generating, say, a monthly view of availability because it involves parsing multiple sheets each covering a single week.
根据我的直觉，我需要创建一个Google Web App，允许用户使用两个字段查询我的电子表格: StartDate 和 EndDate .我需要一些代码，然后遍历每个电子表格中的每个记录，以确定在该日期范围内每天都有哪些人可用，将这些记录以对象格式推送到列表中，然后使用这些对象在Web上生成HTML输出应用页面.这将允许多个用户生成他们想要的任何可用性显示.
My intuition says I need to create a Google Web App that allows a user to query my Spreadsheet with two fields: StartDate and EndDate. I'd need some code that then iterates over every record in every spreadsheet to determine who's available on every day in that date span, push those records in object format to a list, and then use those objects to generate an HTML output on the Web App page. This would allow multiple users to generate whatever availability display they want.
允许用户输入日期范围并使用Google脚本输出在该日期范围内可用的所有员工的最佳方法是什么?
What is the best way to allow a user to enter a date span and output all the employees who are available in that date span using Google Scripts?
推荐答案
从查询的角度来看，您当前的设置中跨越多个工作表的记录效率低下，随着数据规模的扩大，将难以维护.
Your current setup where you have records spanning multiple sheets is inefficient from a query standpoint and is going to be difficult to maintain as the data scales up.
我会将所有数据移动到一个真实的数据库中( Google云具有许多存储选项)，然后利用Apps脚本使用日期范围查询所述数据库，并用结果填充工作表.
I'd move all that data into a true database (google cloud has a number of storage options) and then leverage Apps Script to query said database using a date range and populate a sheet with the results.
Google表格是一种可行的存储解决方案，适用于相对较小的非关系数据集.但是随着您扩大规模并想执行更复杂的查询，其局限性变得更加明显.它们仍然可以用作显示查询结果的平台，但是对于需要扩展的存储，我建议将其卸载到适当的数据库解决方案中.
Google Sheets are a viable storage solution for relatively small non-relational data sets. But as you scale up and you want to execute more complex queries its limitations become more evident. They can still serve as a platform to display the results of a query, but with respect to storage that needs to scale I'd recommend offloading that to a proper database solution.
这篇关于使用Google脚本将多个Google表格查询到一个视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
