Detecting Operations to Google Spreadsheet by Owner, Specific Users, and Anonymous Users using Google Apps Script

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a report for detecting the operations to Google Spreadsheet by the owner, the specific users, and the anonymous users using Google Apps Script. When the Spreadsheet is shared with the specific users and/or the anonymous users, when these users can be identified, it will be useful. In this report, I would like to introduce the method for identifying the users who are the owner, the specific users, and the anonymous users.

Sample situation

As a sample situation, it thinks that a Google Spreadsheet is shared with specific users, and also, it is publicly shared with the anonymous users who have the written permission.

Under this condition, when the Spreadsheet is edited by a user, it identifies the user as the owner, the special permitted user, and the anonymous user.

Principle

In order to identify the user who edits the Spreadsheet, it is considered that the installable trigger is used. If the simple trigger is used, even when the anonymous users edit the Spreadsheet, the trigger is not fired. In this situation, when you want to identify the edit of Spreadsheet by the anonymous users, it is required to use the installable trigger.

When the installable trigger is used and the user edits Spreadsheet, and the OnEdit installable trigger is used, the following result is obtained.

  1. When the owner has edited a cell,

    • e of installedOnEdit(e) has the property of "user":{"email":"### owner's email ###","nickname":"### owner name ###"}.
    • Session.getActiveUser().getEmail() and Session.getEffectiveUser().getEmail() return the owner’s email.

  2. When the special permitted user has edited a cell,

    • e of installedOnEdit(e) has the property of "user":{"email":"","nickname":""}. In this case, no email address and no name are returned.
    • Session.getActiveUser().getEmail() returns empty, and Session.getEffectiveUser().getEmail() returns the owner’s email.

  3. When the anonymous user has edited a cell,

    • e of installedOnEdit(e) has the property of "user":{"email":"### owner's email ###","nickname":"### owner name ###"}.
    • Session.getActiveUser().getEmail() and Session.getEffectiveUser().getEmail() return the owner’s email.
    • In this case, the situation is the same with the owner. But, in order to identify this, here, the simple trigger can be used. Because when the anonymous user is edited, the simple trigger is not run.

When this principle is reflected in a sample script, it becomes as follows.

Usage

1. Prepare sample Spreadsheet.

Please create a Google Spreadsheet. And, please share it with a specific user as a writer. And also, please publicly share as the written permission. This sample script identifies the owner, the specific user, and the anonymous user when the Spreadsheet is edited.

2. Prepare sample script.

Please copy and paste the following script to the script editor of Spreadsheet.

function onEdit(e) {
  CacheService.getScriptCache().put("simpleTrigger", JSON.stringify(e), 30);
}

// Or you can also use onSelectionChange.
// function onSelectionChange(e) {
//   CacheService.getScriptCache().put("simpleTrigger", JSON.stringify(e), 30);
// }

function installedOnEdit(e) {
  const lock = LockService.getDocumentLock();
  if (lock.tryLock(350000)) {
    try {
      Utilities.sleep(3000); // Please increase this wait time when the identification is not correct.
      const c = CacheService.getScriptCache();
      const simpleTrigger = c.get("simpleTrigger");
      const activeUser = Session.getActiveUser().getEmail();
      const effectiveUser = Session.getEffectiveUser().getEmail();
      if (activeUser && effectiveUser && simpleTrigger) {
        // Operation by owner.

        // do something.
        e.range.setValue("Operation by owner.");
      } else if (!activeUser && effectiveUser && simpleTrigger) {
        // Operation by permitted user.

        // do something.
        e.range.setValue("Operation by permitted user.");
      } else {
        // Operation by anonymous user.

        // do something.
        e.range.setValue("Operation by anonymous user.");
      }
      c.remove("simpleTrigger");
    } catch (e) {
      throw new Error(JSON.stringify(e));
    } finally {
      lock.releaseLock();
    }
  } else {
    throw new Error("timeout");
  }
}
  • In this sample, onEdit is used as the simple trigger for identifying the anonymous user. But, for example, onSelectionChange simple trigger can be also used. For example, when you check the user who inserted a new sheet, I think that onSelectionChange will be better rather than onEdit.

3. Install OnEdit trigger as an installable trigger.

Please install a trigger to the function installedOnEdit as the OnEdit installable trigger. Ref

4. Testing.

In order to test this script, please do the following flow.

  1. Edit a cell by the owner.

    • By this, Operation by owner. is put to the edited cell.

  2. Edit a cell by the special permitted user.

    • By this, Operation by the permitted user. is put to the edited cell.

  3. Edit a cell by the anonymous user.

    • By this, Operation by an anonymous user. is put to the edited cell.

If the above result is not obtained, please confirm the above setting again.

Reference

 Share!