LockService:如何在脚本中锁定Google工作表? [英] LockService: How do I lock a Google sheet from within a script?

问题描述

我正在使用Google电子表格存储从网站收集的联系信息(符合GDPR的要求，请放心)，并使用后台运行的自定义App脚本将此工作表与Mailchimp同步.

I'm using a Google spreadsheet to store contact info I collect from a website (GDPR compliant, don't worry) and I synchronize this sheet with Mailchimp using a custom App script, which runs in the background.

更详细地说，我使用一个临时表作为缓冲区，其中填充了用户通过网站表单发送的数据(我使用的是Contact Form 7 Google Sheets Connector).每当在缓冲区"工作表中触发INSERT_ROW事件时，我的脚本就会运行.它分析数据，将其存储在最终工作表中，最后清空缓冲区".
现在，意识到潜在的并发问题，我以以下形式编写了代码:

More in detail, I'm using a temporary sheet as a buffer which is filled with the data users send via the site's forms (I'm using Contact Form 7 Google Sheets Connector). My script runs each time the event INSERT_ROW is triggered in the "buffer" sheet. It analyzes the data, stores them in the final sheet and, eventually, it emptys the "buffer".
Now, aware of potential concurrency issues I've written the code in the following form:

function onChange(e) {
  // Script initializaztion

  if (!formNames.includes(formName) || e.changeType != "INSERT_ROW") return;

  let lock = LockService.getScriptLock();

  try {
    lock.waitLock(60000);

    // Get new stuff from Form sheet
    .
    .
    .

    // Get existing data and compare with new
    compareData();

    // New entries
    insertNewDataIntoFinalSheet();

    // Updates
    updatesExistingDataInFinalSheet();

    // Clean up the buffer sheet
    deleteRowsFromSiteForms();

    console.log(formName);

  } catch(e) {
    console.log(e);
  }

  lock.releaseLock();
}

但是我不确定Lock是否正在执行我期望的操作.而且Google文档对其用途还不太清楚.
如果我不理解Google文档有误，则锁定"为

But I'm not sure the Lock is doing what I'm expecting it to do. And Google Docs are not so clear about its use.
If I'm not understanding Google docs wrong, any Lock is

防止代码段同时运行.该服务允许脚本阻止并发访问代码段.当您有多个用户或进程正在修改共享资源并且想要防止冲突(Google文档)时，这很有用.

preventing sections of code from running concurrently. This service allows scripts to prevents concurrent access to sections of code. This can be useful when you have multiple users or processes modifying a shared resource and want to prevent collisions (Google docs).

现在，想象以下情形:浏览器中的用户A填写表格.同时(几乎，是)用户B在世界其他地方的另一个浏览器中执行相同的操作.用户A的浏览器的CF7连接器将数据发送到我的缓冲区"工作表，并获得了锁定，以便我的脚本开始工作.一纳秒后，用户B的表单将其他数据插入缓冲区"，从而触发了脚本执行.这个新的脚本实例尝试获取该锁，但被用户A的锁阻止.

Now Imagine the following scenario: User A in his browser fills in the form. At the very same time (well, almost) User B is doing the same in another browser somewhere else in the world. The CF7 connector of User A's browser sends data to my "buffer" sheet and it gets the lock so that my script begins its work. A nanosecond later, users B's form inserts other data into the "buffer" triggering thus a script execution. This new script instance tries to acquire the lock but is blocked by user A's lock.

与此同时，用户A的脚本实例正在完成其工作并清理所有内容，还删除了用户B输入的数据！当用户A的脚本实例释放该锁时，用户B的脚本会启动，但会发现其数据被用户A的脚本实例清除了.

In the mean time, User A's script instance is finishing its job and cleans up everything, deleting also the data input by User B! When user A's script instance releases the lock, user B's script kicks in, but it finds its data swept away by user A's script instance.

理想情况下，我需要的是一种冻结"对缓冲区"工作表的任何修改(包括插入新行)的方法，直到每个脚本实例完成其工作为止.

Ideally, what I need is a way to "freeze" any modification to the "buffer" sheet (including insertion of new rows) until each script instance has finished its job.

但是据我了解，并且从我在日志中遇到的一些奇怪错误来判断，这不是getScriptlock()所做的(和getUserlock()或getDocumentlock()都没有).

But as far as I understand, and also judging by some strange errors I have in my logs, this is not what getScriptlock() is doing (and getUserlock() or getDocumentlock() neither).

有人可以帮助我了解我应该如何使用Google LockService吗?

Can anyone help me understanding how I should use Google LockService for that?

提前谢谢

那些评论我的帖子(尤其是@TheMaster)的人已经抓住了问题的核心，因此，让我补充一些进一步的信息/想法.这是我有时(在我的Stackdriver Log中)得到的错误

Those who have commented my post (@TheMaster in particular) have caught the very core of the problem, so let me add some further information/thought. This is the the error I'm getting (sometimes) in my Stackdriver Log

很显然，我的脚本试图在不应该访问的数据上进行访问.我怀疑这是我描述的场景:脚本的第二个实例找到了第一个实例清空的缓冲区".

Apparently, my script is trying to access data when it is not supposed to. I suspect it is the scenario I've depicted: the second instance of the script finds the "buffer" emptied by the first instance.

就像@TheMaster所说的那样，问题在于，如果通过某些外部资源(例如Zapier)或Wordpress连接器(例如我)访问文档，则无法真正锁定"文档任何API请求(Google开发人员，您是否不应该解决此问题?).

As @TheMaster says, the problem is that there's no way to really "lock" a document if the document is accessed via some external resource, like Zapier, for example, or, as in my case, a Wordpress connector, or any API request (Google developers, shouldn't you address this issue?).

推荐答案

Google开发人员，您不应该解决此问题

Google developers, shouldn't you address this issue

我会报告的，但是，您知道，可安装触发器的限制很明确:

I would've reported it, but, you see, the restrictions of a installable trigger are clear:

脚本执行和 API请求不会导致触发器运行.

Script executions and API requests do not cause triggers to run.

因此，我不希望他们通过完全删除此功能来修复"它.然后，您必须定期轮询，这很麻烦.

So, I didn't want them to "fix" it by removing this functionality altogether. Then you'd have to poll at regular intervals, which would be a pain.

如果说两个api完成了两个INSERT_ROW，则

If two INSERT_ROWs are done by the api at say,

• 第21至25行和
• 第26至30行.

即使外部api按顺序插入行，有时，后者也会首先触发(例如23:00)，而前者最后触发(23:01).前者的 sheet.getLastRow()可能仍为30(即使它是为插入行21至25而触发的，因为它触发得较晚).一种解决方案是让所有内容异步运行:在更改事件触发功能内，活动范围指的是api当前添加的范围.可以通过以下方式获取"INSERT_ROW"的尺寸:

Even though the external api inserts the rows in order, sometimes, the latter is triggered first(say at 23:00) and the former is triggered last(at 23:01). The former's sheet.getLastRow() may still be 30(even though it was triggered for row insertion 21 to 25, because it was triggered late). One solution would be to let everything run async: Inside the change event trigger function, The active range refers to the range currently added by the api. The dimensions of the "INSERT_ROW" can be obtained by:

  const rng = ss.getActiveRange(); //Currently Active Range
  const row = rng.getRow(); //Active Row number
  const col = rng.getColumn(); //Active Column number
  const lrow = rng.getLastRow(); //Last Row in Active range

请注意，最后一行是由 rng.getLastRow()而不是 sheet.getLastRow()推断的.因此，即使后者先触发，我们也只会在该脚本执行实例中获得当前添加的行(即行26至30).您也许可以利用它来获得真正的异步工作流程.

Notice that the last row is inferred by rng.getLastRow() and not sheet.getLastRow(). So, even if the latter is triggered first, We only get the currently added rows in that instance of script execution(i.e., rows 26 to 30). You maybe able to leverage that to get a true async workflow.

这篇关于LockService:如何在脚本中锁定Google工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！