更改任何Google表格文件后调用API端点 [英] Calling an API endpoint upon changes to any Google Sheets files

问题描述

我希望实现的目标:

每当我共享驱动器上的任何Google表格中的单元格发生更改时(通过 域中的任何用户)我想调用API端点并包括 有关哪个单元格被编辑的信息.

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脚本插件是我所需要的.为域中的所有用户安装.

My approach: I believe Google App Scripts Add-on is what I need. Installed for all users on the domain.

我看到有绑定"脚本和独立脚本.对于独立脚本，除了基​​于计时器和日历的触发器之外，我无法创建任何其他触发器.绑定脚本似乎永久绑定到一张纸上，不会以任何方式影响其他纸页.

I see there are "bound" scripts and standalone scripts. For standalone scripts I am not able to create any other triggers than timer and calender based triggers. Bound scripts seem to be permanently bound to a single sheet and won't impact other sheets in any way.

我想念什么?

我在博客上找到了一些制作绑定脚本的端到端教程，但对于通用的跨域内容却一无所获.

I find a few end-to-end tutorials on blogs for making bound scripts, but nothing for generic cross-domain stuff.

推荐答案

您可以通过独立脚本来实现所有这些功能.创建一个独立的脚本，然后按照以下步骤操作:

You can achieve all this through a standalone script. Create a standalone script and follow these steps:

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

First you would have to get the id of the different Spreadsheets in your shared drive. You can do it in Google Apps Script itself if you use the Advanced Drive Service (see Reference below). To activate this service, go to Resources > Advanced Google services... in your script editor and enable Drive API.

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

Then, write a function that will return an array of the spreadsheet ids in the shared drive. You will have to call Drive.Files.list for that. It could be something along the following lines (please write your shared driveId in the corresponding line):

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.可能与此类似:

Step 2: Create triggers for each spreadsheet

Install an onEdit trigger programmatically for each of the spreadsheets (an edit trigger fires a function every time the corresponding spreadsheet is edited, so I assume this is the trigger you want). For this, the ids retrieved in step 1 will be used. It could be something similar to this:

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端点的地方.

The function createTriggers gets an array of ids as a parameter and, for each id, creates an onEdit trigger: everytime any of these spreadsheets is edited, the function sendDataOnEdit will run, and that's where you will want to call your API endpoint with information about the edited cell.

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

The function sendDataOnEdit has to get data from the edited cell and send it somewhere.

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().检查我提供的链接以获取更多详细信息.

First, it can get information about the cell that was edited via the event object, passed to the function as the parameter e (you can get its column, its row, its value, the sheet and the spreadsheet where it is located, etc.). For example, to retrieve the value of the cell you can do e.range.getValue(). Check the link I provide in reference to get more details on this.

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

Second, when you have correctly retrieved the data you want to send, you can use UrlFetchApp.fetch(url, params) to make a request to your URL. In the link I provide below, you can see the parameters you can specify here (e.g., HTTP method, payload, etc.).

请记住，如果这不是公共的，则可能需要授予一些访问API端点的授权.检查我在下面附加的OAuth参考.

Please bear in mind that you might need to grant some authorization to access the API endpoint, if this is not public. Check the OAuth reference I attach below.

(您必须相应地编辑此功能，才能准确地检索并发送您想要的内容.我写的是一个示例).

(You have to edit this function accordingly to retrieve and send exactly what you want. What I wrote is an example).

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

In order to create the triggers you should run createTriggers once (if you run it more times, it will start creating duplicates). Run for example, this function, that first gets the file ids via Drive API and then creates the corresponding triggers:

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

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

Also, it would be useful to have a function that will delete all the triggers. Run this in case you want to start from fresh and make sure you don't have duplicates:

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

参考:

• 高级驱动器服务
• Drive.Files.list
• onEdit触发器
• 以编程方式安装触发器
• onEdit事件对象
• UrlFetchApp.fetch(url ，参数)
• 连接到外部API
• 用于Apps脚本的OAuth2
• ScriptApp.deleteTrigger(trigger)

Reference:

• 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)

我希望这会有所帮助.

这篇关于更改任何Google表格文件后调用API端点的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！