从多张Google表格中获取值 [英] Fetch values from multiple sheets Google sheets

问题描述

我正在尝试从跨越多张纸的出席者中找到一个员工ID，并将时间戳记放到一张纸中.

I'm trying to find an Employee ID from the attendance which spans multiple sheets and pull the timestamp into a single sheet.

Google工作表中包含多个工作表.每个工作日都有单独的工作表:

The Google sheet has multiple sheets in it. There are individual sheets for every working day:

每个出勤表都有两列.为了了解员工登录的所有时间，我想从所有工作表中提取所有出现的员工ID，并将其时间戳记入合并工作表:.

Each attendance sheet has two columns. In order to know all the times of the employee's login I want to pull in all the occurrences of the employee ID from all the sheets and along with its timestamp to the Consolidation sheet: .

我已经在Excel中完成了类似的操作，并且可以使用Google Apps脚本在Google工作表中进行猜测.

I've done a similar thing in Excel and guess can be done in Google sheet using Google Apps script.

如果有人可以引导我使用Google表格中的内置功能或自定义功能，这将很有帮助.

It would be helpful if someone can guide me to a built-in or custom function in google sheets.

推荐答案

我会为您提供基本的概述，一些建议和一些资源来帮助您，但不要指望我会写完整的东西.

I'll help you with a basic outline, some advice, and some resources to help, but don't expect me to write the whole thing.

>

---

第1步-创建自定义菜单选项

您将希望能够从电子表格中访问脚本.您可以通过创建自定义菜单选项来完成此操作.这是Google在自定义菜单上发布的文章.

You'll want to be able to access you script from the spreadsheet. You can accomplish this by creating a custom menu option. Here's an article by google on custom menus.

尽管google使用简单的触发器 onOpen ，但我发现

Although google uses the simple trigger onOpen, I've found installable triggers to be more reliable.

第2步-获取用户输入

最好提示员工输入ID，并使脚本正常运行.这是 Google在对话框和侧边栏上的文章，其中讨论了如何获取用户输入一个脚本.

It would be nice to be prompted for the id of the employee and have the script work it's magic. Here is an article by google on dialogs and sidebars that discusses how to get user input for a script.

第3步-从电子表格中读取和写入数据

您可以使用 SpreadsheetApp > .从自定义菜单调用脚本时，可以使用 SpreadsheetApp.getActiveSpreadsheet ；但是，如果要在脚本编辑器中测试代码，则没有活动电子表格"，因此请使用 SpreadsheetApp.openById .

You can access spreadsheeet data with the SpreadsheetApp. When calling your script from a custom menu, you can use SpreadsheetApp.getActiveSpreadsheet; however, if you want to test your code in the script editor, there is no "active spreadsheet", so use SpreadsheetApp.openById instead.

具有访问电子表格的权限，您可以致电:

With access to the spreadsheet, you can call:

spreadsheet.getSheetByName("name").getRange(1, 1, 2, 2).getValues();
spreadsheet.getSheetByName("name").getRange(1, 1, 2, 2).setValues([[1, 2], [3, 4]]);

请注意， getValues 和 setValues 可用于二维数组

Note that getValues and setValues work with 2-dimensional arrays

警告-与往常一样，I/O占用大量处理时间，因此请避免不必要地调用 ="https://developers.google.com/apps-script/best_practices"rel =" nofollow>有关应用程序最佳做法的Google文章进行了更详细的介绍.另外，如果您有很多考勤表，那么您可能需要考虑将它们压缩成一张.

WARNING - As usual, I/O takes a lot of processing time so avoid superfluously calling getRange().XetValues, this google article about appscript best practices goes into more detail. Also, if you have a LOT of attendance sheets, then you may want to consider condensing them into one.

第4步-获取适当的工作表

您将需要某种方式来区分电子表格中的哪些表是出勤表:

You'll need someway to distinguish which sheets in the spreadsheet are attendance sheets:

function isAttendanceSheet(sheet) {
  return /Magical regex/.test(sheet.getName);
}

var sheets = spreadsheet.getSheets().filter(isAttendanceSheet);

使用适合您的神奇正则表达式，它将对其进行过滤.

Come up with the magical regex that works for you, and this will filter them.

希望这会有所帮助，祝你好运！

Hope this helps and good luck!

这篇关于从多张Google表格中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！