在同一Google电子表格上确定活跃用户 [英] Determining Active Users on the same Google Spreadsheet

问题描述

我有一个共享的电子表格，其中包含许多表格，并带有一个OnOpen脚本，该脚本可以隐藏除仪表板以外的所有打开的表格.有一个自定义菜单，用户可以从中显示和处理工作表.

I have a shared spreadsheet with many sheets, with an OnOpen script that hides all the opened sheets, except a dashboard sheet. There is a custom menu from which users can display and work on worksheets.

问题在于，当User1在Sheet1上工作时，User2打开电子表格，而OnOpen脚本为 User1和User2都隐藏Sheet1.

The problem is that while User1 is working on Sheet1, User2 opens the spreadsheet and the OnOpen script hides Sheet1 for both User1 and User2.

是否可以确定其他用户是否当前正在处理电子表格? (以控制OnOpen工作表隐藏操作).

Is there a way to determine if another user is currently working on the spreadsheet? (to control the OnOpen sheet hide actions).

实现此目标的另一种方法?

Another way to achieve this?

推荐答案

我认为，确定是否有活动用户的最接近方法是使用捕获编辑内容的简单触发器在工作表上不断缓存活动:

I think the closest you can come to determining if there are active users is to constantly cache activity on the worksheet with a simple trigger that captures edits:

var EDIT_CACHESTRING = "last write";
// Bind the simple edit trigger (no authorizations needed)
function onEdit(eventObject) {
  CacheService.getDocumentCache()
      .put(EDIT_CACHESTRING, (new Date().getTime()).toString());
}

function onOpen(eventObject) {
  // or function myNonSimpleTriggerOnOpen(e), if you do stuff that needs authorization.
  const cache = CacheService.getDocumentCache();

  // If nothing was cached, there have been no recent edits.
  // (default cache duration is 10 minutes).
  var lastWrite = cache.get(EDIT_CACHESTRING);
  var showOnlyDashboard = !lastWrite;

  // If something was cached, then determine how long it has been since the edit.
  if (lastWrite) {
    const now = new Date().getTime();
    lastWrite = parseInt(lastWrite, 10);
    showOnlyDashboard = (now - lastWrite) > someNumberOfSeconds * 1000;
  }
  if (showOnlyDashboard) {
    methodThatHidesSheets();
  }
  /**
   * other stuff you do in your on open function
   */
}

您可能会觉得更奇特，并缓存一个包含已编辑工作表名称/id(将从编辑触发器的事件对象获取的对象)以及编辑时间的对象，以便隐藏非活动工作表，即使其他情况下也是如此正在进行编辑.

You could get fancier, and cache an object that contains the edited sheet name / id (which you would obtain from the edit trigger's event object) along with the time of the edit, to allow hiding non-active sheets even if other edits are being made.

另一种方法(也更复杂)是使用与当前文档一起使用Drive Activity API .您将在选择的时间范围内检查某种活动，并在该时间范围内缺少该活动时将呼叫锁定到methodThatHidesSheets().

An alternate method (more complicated, too), would be to use the Drive Activity API with the current document. You'd check for some sort of activity within a timeframe of your choosing, and gate the call to methodThatHidesSheets() on the lack of that activity within that timeframe.

这两种方法均无法确定工作表是否存在非活动的查看器.您已经注意到，无法按用户控制同一文档中图纸的可见性-文档的任何当前查看者都可以看到与所有其他当前查看者相同的图纸.

Both of these methods are not able to determine if there is an inactive viewer of a worksheet. As you've already noticed, there is no way to control the visibility of sheets within the same document on a per-user basis - any current viewer of a document sees the same sheets as all other current viewers.

必读:

• CacheService
• 触发器指南
• 在编辑"事件对象上

• CacheService
• Guide to Triggers
• On Edit event object

这篇关于在同一Google电子表格上确定活跃用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！