Store user credentials on Excel add-in

Question

I have an Excel add-in published that allows customers to retrieve/send data from a Spreadsheet to my application. The first add-in screen asks the users to provide valid credentials (of my app) before proceeding. These credentials are a user name and an API Key. Some customers are complaining they need to enter the 40-digit long API key every time they want to use the add-in. My question is: is there a way to safely store these credentials within the add-in? I can't store them on the spreadsheet, since the users just use a temporary one to retrieve/edit the data - and just close Excel after doing it.

Is the API key the same for all users? How do the users know what API key to use? — Rick Kirkham, Sep 01 '22 at 21:05
It's not, unfortunately. It is auto-generated and each user has one - they can always create a new one if they want (for security reasons). — Bruno, Sep 01 '22 at 21:29

score 2 · Accepted Answer · answered Sep 02 '22 at 01:31

2

It's not really unusual to require a passcode of some kind every time a user starts another session with an app. Facebook and most other online services work this way. Is it the sheer length of the key that bothers your users?

At any rate, if the workbook isn't being preserved, then you can't store it in the document and the add-in has no way to store it locally because web apps don't have access to the file system (except for cookies).

You could store the key in a cookie. Another possibility is LocalStorage.

answered Sep 02 '22 at 01:31

Rick Kirkham

9,038
1
14
32

Using cookies seems to be the best solution for me. Thanks for the suggestion! I found [this article](https://learn.microsoft.com/en-us/office/dev/add-ins/develop/persisting-add-in-state-and-settings) explaining how to do it and [this other](https://learn.microsoft.com/en-us/office/dev/add-ins/develop/itp-and-third-party-cookies) about a possible extra configuration that needs to be done. – Bruno Sep 02 '22 at 16:40

score 0 · Answer 2 · answered Sep 01 '22 at 21:41

I've built something like this and used the PERSONAL.XLSB to address this. Important to note that this is not very secure and anyone who had access to person's laptop/account could probably extract it once saved.

Const namedReference = "userAPI"
Sub storeOnLocalMachine()
   Dim theAPIKEY As String, wkbk As Workbook
      theAPIKEY = "sample123key" 'maybe have them enter once as inputbox
   
   For Each wkbk In Application.Workbooks
      If wkbk.Name = "PERSONAL.XLSB" Then Exit For
   Next wkbk

   
   If wkbk Is Nothing Then
      'figure out how to open silentely open for user
   End If
   
   
   wkbk.Names.Add Name:=namedReference, RefersToLocal:="=""" & theAPIKEY & """", Visible:=False
   wkbk.Save
    
End Sub


Function retrieveTheAPI() As String
Dim wkbk As Workbook

   For Each wkbk In Application.Workbooks
      If wkbk.Name = "PERSONAL.XLSB" Then Exit For
   Next wkbk

   retrieveTheAPI = Evaluate(wkbk.Names(namedReference).RefersTo)

End Function

Store user credentials on Excel add-in

2 Answers2