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.
-
When the owner has edited a cell,
e
ofinstalledOnEdit(e)
has the property of"user":{"email":"### owner's email ###","nickname":"### owner name ###"}
.Session.getActiveUser().getEmail()
andSession.getEffectiveUser().getEmail()
return the owner’s email.
-
When the special permitted user has edited a cell,
e
ofinstalledOnEdit(e)
has the property of"user":{"email":"","nickname":""}
. In this case, no email address and no name are returned.Session.getActiveUser().getEmail()
returns empty, andSession.getEffectiveUser().getEmail()
returns the owner’s email.
-
When the anonymous user has edited a cell,
e
ofinstalledOnEdit(e)
has the property of"user":{"email":"### owner's email ###","nickname":"### owner name ###"}
.Session.getActiveUser().getEmail()
andSession.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 thatonSelectionChange
will be better rather thanonEdit
.
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.
-
Edit a cell by the owner.
- By this,
Operation by owner.
is put to the edited cell.
- By this,
-
Edit a cell by the special permitted user.
- By this,
Operation by the permitted user.
is put to the edited cell.
- By this,
-
Edit a cell by the anonymous user.
- By this,
Operation by an anonymous user.
is put to the edited cell.
- By this,
If the above result is not obtained, please confirm the above setting again.
Reference
- Installable Triggers
- Simple Triggers
- getActiveUser()
- getEffectiveUser()
- This sample script was used at this thread of Stackoverflow.