If you're a Gsuite/Google workspace customer, You can use Directory API as mentioned in this answer.
If not, You may be able to leverage the identity token provided by ScriptApp.
★ You need to get explicit permission from each of your editor to get their name. Without getting explicit permission, You will not be able to log their edits programmatically. The built in feature "edit history" would still log them though.
Flow:
- Get current script scopes from File > Project properties> Scopes
- Add explicit
openid, profile and email scopes to the above scopes and add them to the manifest. For eg, the following sample script requires the following scopes:
"oauthScopes":["openid","profile","email",
"https://www.googleapis.com/auth/script.scriptapp",
"https://www.googleapis.com/auth/spreadsheets.currentonly",
"https://www.googleapis.com/auth/script.container.ui"
],
Ask editors to sign up to add them to the edit log by clicking a menu button
Create a installed Edit trigger for them on clicking the menu button.
Use the installable edit trigger to get a identity token. Parse the token to get given name and family name.
Sample script:
function getNameOfCurrentEditor() {
const idToken = ScriptApp.getIdentityToken();
const body = idToken.split('.')[1];
const decoded = Utilities.newBlob(
Utilities.base64Decode(body)
).getDataAsString();
const { given_name: firstName, family_name: lastName } = JSON.parse(decoded);
return { firstName, lastName };
}
/**
* @param{GoogleAppsScript.Events.SheetsOnEdit} e
*/
function installedEditTrigger(e) {
const eUser = e.user.getEmail();
if (eUser === '') return; //no permission=> exit
const { firstName, lastName } = getNameOfCurrentEditor();
e.range.setNote(
`${e.range.getNote()}\n${e.value} added by ${firstName}_${lastName}`
);
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Edit Logger')
.addItem('Sign me up!', 'createEditTrigger')
.addToUi();
}
function createEditTrigger() {
ScriptApp.newTrigger('installedEditTrigger')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
Note: Multiple edit triggers for all editors, who signed up will run automatically, but only the editor who actually made the edit will be allowed to pass beyond this condition:if (eUser === ''). This works because each editor is unable to get email addresses of other editors. Only a empty string is returned in that case.
