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.

Answer 2

Custom functions can't access PropertiesServices.userProperties() (maybe the exception is the spreadsheet owner).

According to https://developers.google.com/apps-script/guides/sheets/functions

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data, specifically the following:

While Properties is included on the list and there isn't any note for this service, since the custom function doesn't ask users to authorize to access personal data it should not return PropertiesServices.userProperties() as it potentially store user personal data.