google-apps-script - 在更改任何 Google 表格文件时调用 API 端点

Question

我希望实现的目标:

Whenever a cell is changed in any google sheet on my shared drive (by any user on the domain) I want to call an API endpoint and include information about which cell was edited.

我的方法:我相信 Google App Scripts Add-on 是我所需要的。为域中的所有用户安装。

我看到有“绑定(bind)”脚本和独立脚本。对于独立脚本，除了基​​于计时器和日历的触发器之外，我无法创建任何其他触发器。绑定(bind)的脚本似乎永久绑定(bind)到单个工作表，不会以任何方式影响其他工作表。

我错过了什么？

我在博客上找到了一些关于制作绑定(bind)脚本的端到端教程，但没有找到关于通用跨域内容的教程。

Answer 1

您可以通过独立脚本实现所有这些。创建独立脚本并按照以下步骤操作:

第 1 步:获取电子表格 ID

首先，您必须获取共享驱动器中不同电子表格的 id。如果您使用 Advanced Drive Service(请参阅下面的引用)，则可以在 Google Apps 脚本本身中执行此操作。要激活此服务，请在脚本编辑器中转到 Resources > Advanced Google services... 并启用 Drive API。

然后，编写一个函数，返回共享驱动器中电子表格 id 的数组。为此，您必须调用 Drive.Files.list。它可能是以下几行(请在相应行中写下您共享的 driveId):

function getFileIds() {
  var params = {
    corpora: "drive",
    driveId: "your-shared-drive-id", // Please change this accordingly
    includeItemsFromAllDrives: true,
    q: "mimeType = 'application/vnd.google-apps.spreadsheet'",
    supportsAllDrives: true
  }
  var files = Drive.Files.list(params)["items"];
  var ids = files.map(function(file) {
    return file["id"];
  })
  return ids;
}

第 2 步:为每个电子表格创建触发器

以编程方式为每个电子表格安装一个 onEdit 触发器(编辑触发器会在每次编辑相应的电子表格时触发一个函数，所以我假设这就是您想要的触发器)。为此，将使用在步骤 1 中检索到的 ids。它可能与此类似:

function createTriggers(ids) {
  ids.forEach(function(id) {
    var ss = SpreadsheetApp.openById(id);
    createTrigger(ss);
  })
}

function createTrigger(ss) {
  ScriptApp.newTrigger('sendDataOnEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

createTriggers 函数获取一个 ids 数组作为参数，并为每个 id 创建一个 onEdit 触发器:每次编辑这些电子表格中的任何一个时，函数 sendDataOnEdit 都会运行，这就是您要使用有关已编辑单元格的信息调用 API 端点的地方。

第 3 步:调用 API 端点

sendDataOnEdit 函数必须从已编辑的单元格中获取数据并将其发送到某处。

function sendDataOnEdit(e) {
  // Please fill this up accordingly
  var range = e.range;
  var value = range.getValue();
  UrlFetchApp.fetch(url, params) // Please fill this up accordingly
}

首先，它可以通过事件对象获取有关被编辑的单元格的信息，作为参数 e 传递给函数(您可以获取它的列、行、值、工作表以及它所在的电子表格等)。例如，要检索单元格的值，您可以执行 e.range.getValue()。检查我在引用中提供的链接以获取更多详细信息。

其次，当您正确检索到要发送的数据后，您可以使用 UrlFetchApp.fetch(url, params) 向您的 URL 发出请求。在我下面提供的链接中，您可以看到可以在此处指定的参数(例如，HTTP 方法、负载等)。

请记住，您可能需要授予一些授权才能访问 API 端点(如果这不是公开的)。检查我在下面附加的 OAuth 引用。

(你必须相应地编辑这个函数来检索和发送你想要的东西。我写的是一个例子)

总结一下:

为了创建触发器，您应该运行一次createTriggers(如果您运行多次，它将开始创建副本)。例如，运行这个函数，它首先通过 Drive API 获取文件 ids，然后创建相应的触发器:

function main() {
  var ids = getFileIds();
  createTriggers(ids);
}

此外，拥有一个删除所有触发器的函数会很有用。如果您想从头开始并确保没有重复项，请运行此命令:

function deleteTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function(trigger) {
    ScriptApp.deleteTrigger(trigger);
  })
}

引用:

• Advanced Drive Service
• Drive.Files.list
• onEdit trigger
• Install trigger programmatically
• onEdit event object
• UrlFetchApp.fetch(url, params)
• Connecting to external APIs
• OAuth2 for Apps Script
• ScriptApp.deleteTrigger(trigger)

希望对您有所帮助。