How to get the user property for the "active user" in google apps script custom function

Question

I've published a Google Apps Script standalone spreadsheet add-on, while I met some problem using PropertiesService.getUserProperties() in a custom function.

If User A has installed the add-on and saved some data in the user property. Then User A shared the spreadsheet to User B. When user B run the custom function, he can get the saved data in user property for user A. This is not what I suppose user Property works. Both User A and B should get their own saved data.

So what is the active user for a shared spreadsheet? I don't want all the spreadsheet viewers to get the saved data for the spreadsheet creator. Is there anything I'm doing wrong in code or "Cloud Platform project" option?

In other words, how can I get different data saved in user properties for different viewers in a shared spreadsheet?

I tried to get the active user email in a custom function, but it does not work.

Please note, I can't use Session.getActiveUser in the custom function, because Google Apps Script Custom function does not support Session service. It will get an invalid permission exception.

https://developers.google.com/apps-script/guides/sheets/functions#Advanced

Thanks,

Answer 1

After writing tons of code to test different scenarios, I believe it has no way to solve the above problem.

So I will use a workaround to achieve the goal. In case someone also run into the trouble. I will put down my test results here.

If Google account A shared spreadsheet to Google account B.

1. In custom function, they all get A's user property.
2. From a webpage, call into server function via script.run, both A and B can get their own user property.