Well, I've done a lot of investigations on this topic, so let share some results.
Main problem appears next: even installable open/edit triggers run as owner, not as an active user. So one-time authorization will not help for logging current user.
Let mention, speaking here about free google accounts users.
When searching, how to identify an Active user with on-edit trigger, found 2 ways.
First is to ask every user press a button and to install a separate installable trigger for this user. This way on every edit all triggers will run. On first check we had to skip all, except current. This can work with small number of users, but looks not very stable on long run. Here is a link to this solution: How to get active user email with installabled onEdit trigger?
Second way is to check an active user somehow from on-edit trigger.
The only(!) way I found is Session.getTemporaryActiveUserKey() (remember, it is auto-changing every month).
So all the code is like this:
- Installable triggers:
function onEditInst(e) {
let userAKey = userIdent(e, 'edit')
if (!userAKey) userGoPermisPage()
// other trigger code
}
function onEditInst(e) {
let userAKey = userIdent(e, 'edit')
if (!userAKey) userGoPermisPage()
// other trigger code
}
- Identification function:
function userIdent(e, eType) { // used by onOpen and onEdit to get session Active user
// ident of current user by open edit triggers is only possible by temporaryActiveUserKey, so storing it in docProperties (can also store somewhere on a sheet)
// helping consts
const docProps = PropertiesService.getDocumentProperties()
// start with reading temporaryActiveUserKey (user working)
let tempAUKey = Session.getTemporaryActiveUserKey()
// try to identify user by this key:
let userKey = docProps.getProperty(tempAUKey)
if (userKey) { // user ok
if (eType === 'open') { // report ok
SpreadsheetApp.getActiveSpreadsheet().toast('User key is valid',`User found (${userKey})`)
}
}
return userKey // if user ident, have it
}
- If user is not authorized, throw him on 'Permis' page. This page has description, he need to receive a userkey, once a month. And on the very first run he will be asked for a script permission.
function userGoPermis() { // just go to page where to authorize
let ss = SpreadsheetApp.getActiveSpreadsheet()
let ws = ss.getSheetByName(models.permis.sheetName) //using global variable var models = {permis:{sheetName:'Permis'}}
ss.setActiveSheet(ws)
SpreadsheetApp.getActiveSpreadsheet().toast("Need user confirm","User not recognized")
}
- Receive key - this code bound to button on a separate page. On the very first run it will also ask for a script permission. So put some description on a page.
function userPermisButton() {// ask permissions and save/renew user data
// debug stuff: console.log('Session.getActiveUser().getEmail()', Session.getActiveUser().getEmail());
console.log('tempo', Session.getTemporaryActiveUserKey());
try {
// Get multiple doc properties in one call, then log them all. -
const docProperties = PropertiesService.getDocumentProperties();
const data = docProperties.getProperties();
for (const key in data) {
console.log('Key: %s, Value: %s', key, data[key]);
}
} catch (err) {
// TODO (developer) - Handle exception
console.log('Failed with error %s', err.message);
}
// helping consts
const docProps = PropertiesService.getDocumentProperties()
// start with reading temporaryActiveUserKey (user working)
let tempAUKey = Session.getTemporaryActiveUserKey()
// try to identify user by this key:
let userKey = docProps.getProperty(tempAUKey)
if (userKey) { // user ok - case if just manual user check
SpreadsheetApp.getActiveSpreadsheet().toast('',`User is already identified (${userKey})`)
console.log('User already found');
} else { // unknown user, no such temp key
// in any case identifying user (button is pressed, so we know his email)
let email = Session.getActiveUser().getEmail()
if (email) {
let userKey = Object.keys(users).find(userKey => users[userKey].email === email) // userkey found by email. We use here global var={U1:'[email protected]', U2:'[email protected]', ...}
if (userKey) { // 2 possibilities here - temp Key was rotated, or temp Key was not ever recorded for this user.
// let search such user in stored
let allProps = docProps.getProperties()
let oldTmpKey = Object.keys(allProps).find(key => allProps[key] === userKey )
if (oldTmpKey) { // found such a tempKey for user in old records - and now it was changed
docProps.deleteProperty(oldTmpKey) // removing old key
}
docProps.setProperty(tempAUKey, userKey) // saved tmpKey user
userMarkIdent(userKey) // optional separate function to log somewhere last key date for user. (not given here, quite usual saving - any way)
SpreadsheetApp.getActiveSpreadsheet().toast(`Proceed. `,`USER SAVED(${userKey})`)
} else { // email not recognized
SpreadsheetApp.getActiveSpreadsheet().toast(`See the user, but he is not in users list. Call admin.`,`ERROR`)
return
}
} else { //neither user no email
SpreadsheetApp.getActiveSpreadsheet().toast(`Give permission and run once again for saving user.`,`PERMISSION REQUIRED FOR IDENTIFICATION SCRIPT RUNNING`)
}
}
return // no need to return, it is just visual check / save user if need and can
}
There is also an option to corresponds once a month tempKey with an user with login password (not googles, but from your project). Probably, this case any script authorization from user is not necessary at all. But login/password alert or window is.
