3

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,

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Frank
  • 159
  • 3
  • 14
  • What I want to do is: in custom function it will visit some external data sources, but only the permitted users can access the private data. Suppose this is very common usage, does anyone know how to achieve this goal ? Thanks, – Frank Jun 12 '19 at 09:25

3 Answers3

5

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.
Frank
  • 159
  • 3
  • 14
  • 1
    Hi Frank. Can you please explain more how you solved this? I have the same problem. Thanks, Henrik – Henrik Dec 11 '20 at 13:52
1

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • 2
    Thanks, Rubén. I've read the https://developers.google.com/apps-script/guides/sheets/functions Please see the chart in the middle of the document, it list the supported services. The property service is supported, and I've tested using different accounts, it works fine------ different users will get their own user property data. The only problem is, if the user A shared the spreadsheet to user B, then the user B will execute the spreadsheet as if the user A is working on it(even though user B doesn't installed the add-on). I want to know if there is a solution to solve this problem. – Frank Jun 16 '19 at 06:13
  • 1
    I think this problem is related on how the Google spreadsheet shared among different accounts...... Others working on the spreadsheet just act as the role of the creator. How to tell who is currently working on it , if we can't tell from the user property ? Seems the custom function does not support Session service . – Frank Jun 16 '19 at 06:18
0

How about using the root folder Id to distinguish between users? DriveApp.getRootFolder().getId(); This should be the id of My Drive which is different for each user. You could use this as part of a key into a property store. This might be a security breach as anyone with edit access to the code could add a function to retrieve all the ids of the users' "My Drive" as well as the individual's spreadsheet data.

Peter Boughton
  • 1
  • 1
  • Exception: You do not have permission to call DriveApp.getRootFolder. Required permissions: (https://www.googleapis.com/auth/drive.readonly || https://www.googleapis.com/auth/drive) – Max Makhrov Aug 29 '23 at 10:49