AppScript UserProperties within cell vs from menu

Question

I'm developing an AppScript, and I've noticed some strange behavior regarding UserProperties. I have the followng function that is triggered by a menu button:

function inputApiKey() {
  var ui = SpreadsheetApp.getUi();
  var apiKeyValue;
  if (!!PropertiesService.getUserProperties().getProperty(API_KEY)) apiKeyValue = ui.prompt('Your current API Key is:\n' + PropertiesService.getUserProperties().getProperty(API_KEY) + '\n\nEnter your new API Key below:\n', ui.ButtonSet.OK);
  else apiKeyValue = ui.prompt('Add your API Key here:\n', ui.ButtonSet.OK);
  if (apiKeyValue.getResponseText()) PropertiesService.getUserProperties().setProperty(API_KEY, apiKeyValue.getResponseText());
}

Now let's say the spreadsheet was created by User 1, and User 1 uses the above function to input an API key. Then suppose User 1 shares the sheet with User 2, and User 2 inputs a separate API key.

Now suppose I have a second function:

function MyApiKey() {
  return PropertiesService.getUserProperties().getProperty(API_KEY);
}

If User 1 calls this function from a menu button, it returns User 1's API key. If User 1 calls this function from within a cell, it returns User 1's API key.

If User 2 calls this function from a menu button, it returns User 2's API key. But if User 2 calls this function from within a cell, it returns User 1's API key.

Can someone help me understand why this is happening and how to resolve it?

EDIT:

I now understand why this happens. How can one store information specific to each user (like an API key) such that each user’s data is invisible to others accessing the sheet?

Answer 1

As written in the Custom function docs,

getUserProperties() only gets the properties of the spreadsheet owner. Spreadsheet editors can't set user properties in a custom function.

Answer 2

Unfortunately the scenario you are experiencing is an expected behaviour when using custom function

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

That is why when PropertiesService.getUserProperties() in a custom function it only returns the data from the owner.

It was also stated by the custom function documentation

getUserProperties() only gets the properties of the spreadsheet owner. Spreadsheet editors can't set user properties in a custom function.

The possible workaround also which you've already done as stated on documentation is to

create a custom menu that runs an Apps Script function instead of writing a custom function. A function that is triggered from a menu will ask the user for authorization if necessary and can consequently use all Apps Script services.

Reference:

Custom Functions