Google Sheets Add On Executing custom functions when sheet is shared

Question

I have built and published a Google Sheets Add-on. The Official Similarweb For Google SheetsTM

The custom functions in the Add-on refer to an API key that is stored in user properties and then call the relevant API using that API key and displays the results on the sheet. The user has the ability to save the API key using the sidebar interface. When a user installs the Add-on, saves the API key and then shares the google sheet with another user,

1. The other use is also able to use the custom function in the shared sheet even if he did not install the Add-on. The Api key of the first user seems to be used.

2. If he installs the Add-on, and the sidebar when opened through menu, is not able to find an Api key in the user properties.

How can I make sure that when the sheet is shared with some one

1. The custom functions should not execute since Add-on is not installed.
2. If Add-on is installed, the custom functions will refer to the user properties of the second user?

Answer 1

Custom functions run in a restricted context where the identity of the user at the keyboard is not available. One side-effect is that the Properties service is limited in what it can get. From the documentation:

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

Also see issue 207386155.

The question also describes how server-side code that is run through a sidebar behaves differently to custom functions. That is as expected, because in that context, the identity of the user at the keyboard is readily available.