Authentication for custom functions without a shared runtime

If your Excel custom function doesn't use a shared runtime, it runs in a JavaScript-only runtime. In that runtime, authentication usually requires a dialog flow and token sharing with your task pane.

Use OfficeRuntime.displayWebDialog to sign in and OfficeRuntime.storage to cache and share the token between runtimes.

Authentication workflow

The following workflow is typical for custom functions that don't use a shared runtime.

1. A user runs a custom function in an Excel cell.
2. The custom function calls OfficeRuntime.displayWebDialog to open a sign-in page in a dialog, where the user enters their credentials.
3. The sign-in page then returns an access token to the dialog.
4. The dialog calls the Office.ui.messageParent function to send the access token to the custom function. For more information about this function, see Send information from the dialog box to the host page.
5. The custom function then sets this access token to an item in the OfficeRuntime.storage.
6. The add-in's task pane accesses the token from OfficeRuntime.storage.

Try it with a sample

Use the Using OfficeRuntime.storage in custom functions sample to test token storage and retrieval between custom functions and a task pane.

Dialog API

If a token doesn't exist, you should use OfficeRuntime.displayWebDialog to ask the user to sign in. After a user enters their credentials, the resulting access token can be stored as an item in OfficeRuntime.storage.

Dialog box API example

In the following code sample, the function getTokenViaDialog uses the OfficeRuntime.displayWebDialog function to display a dialog box. This sample is provided to show the capabilities of the method, not demonstrate how to authenticate.

/**
 * Function retrieves a cached token or opens a dialog box if there is no saved token. Note that this isn't a sufficient example of authentication but is intended to show the capabilities of the displayWebDialog method.
 * @param {string} url URL for a stored token.
 */
function getTokenViaDialog(url) {
  return new Promise (function (resolve, reject) {
    if (_dialogOpen) {
      // Can only have one dialog box open at once. Wait for previous dialog box's token.
      let timeout = 5;
      let count = 0;
      const intervalId = setInterval(function () {
        count++;
        if(_cachedToken) {
          resolve(_cachedToken);
          clearInterval(intervalId);
        }
        if(count >= timeout) {
          reject("Timeout while waiting for token");
          clearInterval(intervalId);
        }
      }, 1000);
    } else {
      _dialogOpen = true;
      OfficeRuntime.displayWebDialog(url, {
        height: '50%',
        width: '50%',
        onMessage: function (message, dialog) {
          _cachedToken = message;
          resolve(message);
          dialog.close();
          return;
        },
        onRuntimeError: function(error, dialog) {
          reject(error);
        },
      }).catch(function (e) {
        reject(e);
      });
    }
  });
}

OfficeRuntime.storage object

The JavaScript-only runtime doesn't have a localStorage object available on the global window, where you typically store data. Instead, your code should share data between custom functions and task panes by using OfficeRuntime.storage to set and get data.

Suggested usage

When you need to authenticate from a custom function add-in that doesn't use a shared runtime, your code should check OfficeRuntime.storage to see if the access token was already acquired. If not, use OfficeRuntime.displayWebDialog to authenticate the user, retrieve the access token, and then store the token in OfficeRuntime.storage for future use.

Storing the token

The following examples show how to store and retrieve tokens by using OfficeRuntime.storage.

If the custom function authenticates, then it receives the access token and will need to store it in OfficeRuntime.storage. The following code sample shows how to call the storage.setItem method to store a value. The storeValue function is a custom function that stores a value from the user. You can modify this to store any token value you need.

/**
 * Stores a key-value pair into OfficeRuntime.storage.
 * @customfunction
 * @param {string} key Key of item to put into storage.
 * @param {*} value Value of item to put into storage.
 */
function storeValue(key, value) {
  return OfficeRuntime.storage.setItem(key, value).then(function (result) {
      return "Success: Item with key '" + key + "' saved to storage.";
  }, function (error) {
      return "Error: Unable to save item with key '" + key + "' to storage. " + error;
  });
}

When the task pane needs the access token, it can retrieve the token from the OfficeRuntime.storage item. The following code sample shows how to use the storage.getItem method to retrieve the token.

/**
 * Read a token from storage.
 * @customfunction GETTOKEN
 */
function receiveTokenFromCustomFunction() {
  const key = "token";
  const tokenSendStatus = document.getElementById('tokenSendStatus');
  OfficeRuntime.storage.getItem(key).then(function (result) {
     tokenSendStatus.value = "Success: Item with key '" + key + "' read from storage.";
     document.getElementById('tokenTextBox2').value = result;
  }, function (error) {
     tokenSendStatus.value = "Error: Unable to read item with key '" + key + "' from storage. " + error;
  });
}

General guidance

Office Add-ins are web-based, so you can use any web authentication technique. There isn't one required authentication pattern for custom functions. Start with Authorize to external services in Office Add-ins for design patterns and tradeoffs.

Avoid using the following locations to store data when developing custom functions:

• localStorage: custom functions that don't use a shared runtime don't have access to the global window object and therefore have no access to data stored in localStorage.
• Office.context.document.settings: This location isn't secure, and anyone using the add-in can extract this information.

Next steps

Learn how to debug custom functions.

See also

• JavaScript-only runtime for custom functions
• Excel custom functions tutorial
• JavaScript-only runtime