Determining Active Users on the same Google Spreadsheet

Question

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.

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.

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?

In your situation, how about using Lock Service? https://developers.google.com/apps-script/reference/lock/ — Tanaike, Jul 14 '18 at 07:29
The user can still use the View menu to unhide sheets - which would not trigger the lock service - which I presume has to be triggered from the custom menu user clicks. Unfortunately, I cannot trust the users to behave ... — Riyaz Mansoor, Jul 14 '18 at 07:49
Related question: https://stackoverflow.com/questions/39602639/getting-a-list-of-active-file-viewers-with-apps-script I say related, because your definition of "active" is those that are editing the sheet, not just viewing. — tehhowch, Jul 14 '18 at 20:03

score 1 · Answer 1 · answered Jul 14 '18 at 19:59

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
   */
}

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.

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.

Required reading:

Determining Active Users on the same Google Spreadsheet

1 Answers1

Linked