Connecting to SharePoint from an Office App

Artikel
01/27/2015

I'm frequently asked by developers how to connect to SharePoint from an App for Office. There are several ways to accomplish this, so I thought I'd document the patterns I've used. I'll detail five patterns:

Explicit Login
SharePoint-hosted Office App
App-only Permissions
Permissions "on the fly"
Office 365 APIs

Apps for Office typically lack user identity and contextual information that are important for connecting to SharePoint. You will see that most of the patterns will require the user to authenticate and/or provide the details of the site(s) to connect. These hardships may improve as Office evolves and new APIs/SDKs become available, but are a reality of the current app model.

[View:https://www.youtube.com/watch?v=1JafRmvGI6A]

The explicit login approach uses an app-hosted login form and CSOM to explicitly set credentials on the SharePoint Client Context. Although not elegant, an explicit login is a simple approach for connecting to SharePoint from an App for Office. CSOMs SharePointOnlineCredentials class can be used to provide credentials to SharePoint Online (similar approaches may be feasible on-premises). Store apps should never handle/prompt for user credentials. OAuth was introduced into the app model to prevent 3rd parties from handling user credentials. As such, this solution should only be considered for private apps (those published to a private app catalog).

Explicit Login with SharePointOnlineCredentials

protected void btnSignin_Click(object sender, EventArgs e){ //read lists using (ClientContext client = new ClientContext(txtSite.Text)) { //set credentials on the clientcontext client.Credentials = new SharePointOnlineCredentials(txtUsername.Text, SecurePwd); //get all lists for the site var lists = client.Web.Lists; client.Load(lists); client.ExecuteQuery();

//bind the lists to the lists dropdown List<ListDetail> listDetails = new List<ListDetail>(); foreach(var list in lists) { listDetails.Add(new ListDetail() { Id = list.Id, Title = list.Title }); } cboList.DataSource = listDetails; cboList.DataBind(); divLogin.Visible = false; divSelectList.Visible = true; lblHeader.Text = "Select List"; }}

SharePoint-hosted Office App ( Code )

[View:https://www.youtube.com/watch?v=BYW9V-6jB5w]

Apps for Office can be delivered through a SharePoint app, which gives it direct context to SharePoint. In this scenario, the app is delivered in an Office template and its web content is rendered from a SharePoint app web. The Office template (containing the Office App) can be hosted in a SharePoint module, or as a content type in a document library. Either way, the Office app is launched in a template from SharePoint and not from the insert app button in Office. This solution works well for template scenarios or scenarios where the Office app is part of a larger SharePoint solution/site. SharePoint-hosting the Office app will limit the developer platform to client-side technologies. If you wanted to work with managed libraries/SDKs for Office (ex: Open XML SDK) you would need to do this behind a service client-side script could consume. It is possible to publish an app to the Office Store using this approach. However, it would be published as a SharePoint app, not an Office app. The code below shows how the app for Office can be SharePoint "aware" by using the script window.location or Office.context.document.url to get the app web and host web URLs for REST calls.

Getting AppWebUrl and HostWebUrl in SharePoint-hosted Office App

$(document).ready(function () { //determine the appweb and hostweb URLs based on the window.location var basePath = window.location.toString(); basePath = basePath.substring(0, basePath.toLowerCase().indexOf("splistreader")); var appWebUrl = basePath + "splistreader"; var hostWebUrl = basePath.substring(0, basePath.indexOf('-')) + basePath.substring(basePath.indexOf('.'));

//get the lists from the host web $.ajax({ url: appWebUrl + "/_api/SP.AppContextSite(@target)/web/lists?@target='" + hostWebUrl + "'", headers: { "Accept": "application/json; odata=verbose" }, success: function (data) { $(data.d.results).each(function (i, e) { $("#cboList").append($("<option value='" + e.Id + "'>" + e.Title + "</option>")); }); $("#btnGetData").removeAttr('disabled'); }, error: function (e) { $('#message').html('<div class="alert alert-danger" role="alert">Error occurred!</div>'); $('#message').show(); } });

App-only Permissions ( Code )

[View:https://www.youtube.com/watch?v=g5wSHNBF9Yk]

Another approach I've used to connect to SharePoint from an Office app is through a provider-hosted SharePoint app with app-only permissions. App-only permissions enables SharePoint to be queried without the normal user context. The app for Office will instead perform operations against SharePoint as an app and not a user. However, lack of user context doesn't mean this will work with zero context. At minimum, the Office app needs the URLs for any site(s) it will communicate with. This could be hard-coded for specific in-house scenarios or captured and cached from a user prompt. Enabling connections to any SharePoint site would require tenant-scoped permission in the SharePoint app. Due to the tenant permission scope and the lack of user/tenant context, this approach is not recommended for multi-tenant apps. You should be extra careful how you expose app-only functionality as it does not adhere to user permissions and could expose sensitive information to users.

App-only ClientContext with CSOM

public ActionResult Index(string site){ List<SPList> list = new List<SPList>();

//get site Uri siteUri = new Uri(site);

//Get the realm for the URL string realm = TokenHelper.GetRealmFromTargetUrl(siteUri);

//Get the access token for the URL. Requires this app to be registered with the tenant string accessToken = TokenHelper.GetAppOnlyAccessToken(TokenHelper.SharePointPrincipal, siteUri.Authority, realm).AccessToken;

//Get client context with access token using (var clientContext = TokenHelper.GetClientContextWithAccessToken(siteUri.ToString(), accessToken)) { var lists = clientContext.Web.Lists; clientContext.Load(lists); clientContext.ExecuteQuery();

foreach (var l in lists) { list.Add(new SPList { Id = l.Id, SiteUrl = site.ToLower(), Title = l.Title }); } }

return View(list);}

Permissions "on the fly" ( Code )

[View:https://www.youtube.com/watch?v=tEoLO7PrnJU]

Permissions "on the fly" is a technique that allows an app to dynamically ask for permissions to SharePoint resources at runtime. The dynamic permission request presents SharePoint with an app ID and desired permission(s). This technique requires a SharePoint app to be registered through the seller dashboard (or appregnew.aspx) before the app can request permissions (SharePoint won't give permissions to an app without registration details it can validate). The permissions "on the fly" flow (also called Authentication Code OAuth flow) will provide the app with a short-lived authorization code that can be used to get an access token for SharePoint resources. The app will also get refresh token that can be safely cached to avoid going through the permission flow again in the future.

To initiate the flow, the app should load or redirect to /_layouts/15/OAuthAuthorize.aspx relative to the desired SharePoint site the app wants to access. This should include the following URL parameters:

Parameter	Description
client_id (required)	The app id of the SharePoint app as registered in the seller dashboard or appregnew.aspx
scope (required)	Space-separated list of permissions the app is requesting (ex: Web.Manage). A comprehensive list of scope options can be found HERE
response_type (required)	The authorization type you want back from the authorize flow. For permissions on the fly this will always be "code"
redirect_url (required)	Where we want the authorization response returned. This should match the redirect uri registered in the seller dashboard of appregnew.aspx
isdlg (optional)	Flag indicating if the Authentication Code OAuth flow is performed in a dialog or not
state (optional)	Optional parameter that can be used to pass values through the OAuth flow and critical for referencing data between disconnected windows

I recommend launching the OAuthAuthorize.aspx page in a dialog window since the page is not responsive to render nicely in most Office app shapes. The pictures below illustrates the user experience difference in a task pane app.

OAuthAuthorize.aspx without dialog	OAuthAuthorize.aspx with dialog

Using a dialog window delivers a better OAuthAuthorize.aspx page experience, but also introduces an issue as Office app isolation prevents the dialog from communicating back into the app. A solution is to have the app pass a reference (ex: GUID) through the state URL parameter. The redirect page (which receives the authorization code) can use this reference to talk back into the app (via SignalR or cached in a database the app can refresh and read). For the sample app I've provided, I simply prompt the users to refresh the page after the authorization process is complete (this can be seen in the video and images above).

"Permissions on the fly" call using OAuthAuthorize.aspx

//build a redirect URI var redirect = encodeURI("https://localhost:44367/Site/Add") + "&state=" + $("#hdnUserID").val() + "|" + encodeURI(url.toLowerCase()); url += "_layouts/15/OAuthAuthorize.aspx?IsDlg=1&client_id=b36fb934-b990-41a5-b9e7-1dddf66ded2e&scope=Web.Manage&response_type=code&redirect_uri="; url += redirect; window.open(url, "", "width=720, height=300, scrollbars=0, toolbar=0, menubar=0, resizable=0, status=0, titlebar=0");

$("#refreshModal").modal("show"); } }); });</script>

The OAuthAuthorize.aspx page returns an authorization code to the redirect URI. This authorization code can be used to get access and refresh tokens for SharePoint.

Controller action to handle authorization code response from OAuthAuthorize.aspx

public ActionResult Add(){ //check for error if (Request["error"] != null) { //Redirect to error return RedirectToAction("Error", "Home", new { error = Request["error"] }); } else if (Request["code"] != null) { //get state parameters string[] stateParams = Request["state"].ToLower().Split('|'); Guid userID = new Guid(stateParams[0]); Uri siteURI = new Uri(stateParams[1]); string siteURIString = stateParams[1]; //get realm and token for site string realm = TokenHelper.GetRealmFromTargetUrl(siteURI); var token = TokenHelper.GetAccessToken(Request["code"], TokenHelper.SharePointPrincipal, siteURI.Authority, realm, new Uri("https://localhost:44367/Site/Add"));

//use access token to establish clientContext using (var clientContext = TokenHelper.GetClientContextWithAccessToken(stateParams[1], token.AccessToken)) { clientContext.Load(clientContext.Web.CurrentUser); clientContext.ExecuteQuery();

//check if a user exists in the database...create new if needed using (ShptPermsOnFlyEntities entities = new ShptPermsOnFlyEntities()) { var user = entities.Users.FirstOrDefault(i => i.UserLogin == clientContext.Web.CurrentUser.LoginName.ToLower() && i.Id == userID); if (user == null) { //create the user user = new User() { Id = userID, UserLogin = clientContext.Web.CurrentUser.LoginName.ToLower() }; entities.Users.Add(user); entities.SaveChanges(); }

//add the site to the site listing if it doesn't already exist var site = entities.Sites.FirstOrDefault(i => i.UserId == user.Id && i.SiteURI == siteURIString); if (site == null) { //create the site listing site = new Site() { Id = Guid.NewGuid(), UserId = user.Id, SiteURI = stateParams[1], Token = token.RefreshToken }; entities.Sites.Add(site); entities.SaveChanges(); } else { //update the refresh token site.Token = token.RefreshToken; entities.SaveChanges(); } } } } return View();}

Although this approach is one of the most complex, it is one of the best option for multi-tenant apps that are targeting the Office Store and a pattern used by several popular apps.

Office 365 APIs ( Code )

[View:https://www.youtube.com/watch?v=bgWNQcmPfoo]

The Office 365 APIs have a huge advantage over the other scenarios with regard to SharePoint context. These APIs leverage a discovery service that provides contextual information about users and SharePoint. An App for Office can use this service for basic SharePoint details (root site and OneDrive URLs) and perform search queries to deliver a "site picker" for users (instead of having users type site URLs).

The Office 365 APIs also pose a challenge when combined with an App for Office. Apps for Office are hosted in a browser control/iframe that "frames" the Azure AD login process. This login process does not work well when displayed in a frame. In fact, clicking the "Sign in" button causes a new browser window to open for completing the OAuth flow. Unfortunately, the new browser window has no way to communicate back to the App for Office. Similar to "Permissions on the fly", we can pass a reference code into the OAuth flow. However, the Office 365 SDK for ASP.NET/MVC does not currently expose the ability to customize the authorization request parameters. Instead, we will perform a manual OAuth flow that has been detailed by Chaks and Matthias Leibmann. Here are the high-level steps for this flow and cross-window communication:

Check for a user cookie (which maps to a refresh token in a database)
If the user doesn't have a cookie…generate a new GUID and store as cookie
Launch the OAuth flow with Azure AD in a new window (passing the GUID as reference)
Use the authorization code returned from the OAuth flow to get access and refresh token
Store the refresh token in the database with the GUID user reference
Prompt the user to refresh the Office app (which can now lookup the refresh token by the GUID user reference that is stored in a cookie)
Use the refresh token in the app to get resource-specific access tokens for data retrieval

This script sample launches the OAuth flow for unknown users. Notice the user GUID reference we are passing on the redirect URI (stored in the hdnUserId hidden input).

Manual authorization request with Azure AD

@section Scripts {<script type="text/javascript"> $(document).ready(function () { var exists = @Model.UserExists.ToString().ToLower(); if (!exists) { var redirect = "https://login.windows.net/common/oauth2/authorize"; redirect += "?client_id=2a337874-4d83-407c-b178-8379f24aff29"; redirect += "&resource=Microsoft.SharePoint"; redirect += "&redirect_uri=" + encodeURI("https://localhost:44365/OAuth/AuthCode/" + $("#hdnUserID").val()); redirect += "&response_type=code"; window.open(redirect, "", "width=720, height=300, scrollbars=0, toolbar=0, menubar=0, resizable=0, status=0, titlebar=0");

$("#refreshModal").modal("show"); } });</script>}

Here is the AuthCode action on the OAuthController. This accepts the authorization code from the OAuth flow, gets a refresh token, and stores it in a database with the user reference.

OAuthController for managing the authorization code response from Azure AD

public async Task<ActionResult> AuthCode(Guid id){ if (Request["code"] == null) return RedirectToAction("Error", "Home", new { error = "Authorization code not passed from the authentication flow" }); else if (id == null) return RedirectToAction("Error", "Home", new { error = "User reference code not passed from the authentication flow" });

//get access token using the authorization code var token = await TokenHelper.GetAccessTokenWithCode(id, Request["code"], SettingsHelper.O365DiscoveryResourceId);

//make call into discovery service HttpClient client = new HttpClient(); client.DefaultRequestHeaders.Add("Authorization", "Bearer " + token.access_token); using (HttpResponseMessage response = await client.GetAsync("https://api.office.com/discovery/v1.0/me/services")) { if (response.IsSuccessStatusCode) { string json = await response.Content.ReadAsStringAsync(); JObject root = JObject.Parse(json); var resources = ((JArray)root["value"]).ToObject<List<DiscoveryResource>>(); var rootResource = resources.FirstOrDefault(i => i.capability == "RootSite");

//redirect if we have an error if (rootResource == null) return RedirectToAction("Error", "Home", new { error = "RootSite is not a valid service capability for this user" });

//get root files resource var rootToken = await TokenHelper.GetAccessTokenWithRefreshToken(token.refresh_token, rootResource.serviceResourceId); //save the details in the token store database and redirect to sites using (O365TokenStoreEntities entities = new O365TokenStoreEntities()) { UserToken uToken = new UserToken() { UserId = id, ServiceEndpointUri = rootResource.serviceEndpointUri, ServiceResourceId = rootResource.serviceResourceId, RefreshToken = rootToken.refresh_token }; entities.UserTokens.Add(uToken); entities.SaveChanges(); } } }

//get discoverInfo return View();}

Here is an example of making a REST call into SharePoint using the cached refresh token (which we convert into an access token placed on the request header).

Performing REST call against SharePoint with cached refresh token

public async static Task<List<SPList>> GetLists(UserToken userToken, string siteUrl){ List<SPList> lists = new List<SPList>(); HttpClient client = new HttpClient(); var fullToken = await TokenHelper.GetAccessTokenWithRefreshToken(userToken.RefreshToken, userToken.ServiceResourceId); client.DefaultRequestHeaders.Add("Authorization", "Bearer " + fullToken.access_token); client.DefaultRequestHeaders.Add("Accept", "application/json; odata=verbose"); using (HttpResponseMessage response = await client.GetAsync(siteUrl + "/_api/web/lists")) { if (response.IsSuccessStatusCode) { JObject root = JObject.Parse(await response.Content.ReadAsStringAsync()); var listResults = root.SelectToken("d.results").ToArray(); foreach (var list in listResults) { lists.Add(new SPList() { SiteUrl = siteUrl, Id = new Guid(list.SelectToken("Id").ToString()), Title = list.SelectToken("Title").ToString() }); } } } return lists;}

The Office 365 APIs have a similar complexity to "permissions on the fly", but is the most solid solution for multi-tenant apps (thanks to the discovery service).

Final Thoughts

So there you have it…five patterns for connecting to SharePoint from an app for Office. I'm sure new options will show up as Office and its APIs continue to evolve.

Comments

Anonymous
April 21, 2015
The comment has been removed
Anonymous
October 29, 2015
The comment has been removed
Anonymous
November 22, 2015
Is it possible to do Google Oauth in an Outlook 365 Addin?
Anonymous
December 02, 2015
How do I display task pane in office (MS Word)? Your excel file for approach#2 already has a task pane opened
Anonymous
February 04, 2016
Greate post, but when I download the first solution. I can't run it, I am getting the message from Excel: APP ERRO "Sorry, we can't load the app. Please make sure you have network and/or Internet connectivity. Click "Retry" once you're back online.
Anonymous
February 04, 2016
What should I use on App Domain and RedicrectUrl if I am running on localhost. Would it be www.localhost.com for the App Domain ?
Anonymous
February 17, 2016
The comment has been removed

Freigeben über

Connecting to SharePoint from an Office App

SharePoint-hosted Office App ( Code )

App-only Permissions ( Code )

Permissions "on the fly" ( Code )

Office 365 APIs ( Code )

Final Thoughts

Comments

Zusätzliche Ressourcen

Freigeben über

Connecting to SharePoint from an Office App

Explicit Login ( Code )

SharePoint-hosted Office App ( Code )

App-only Permissions ( Code )

Permissions "on the fly" ( Code )

Office 365 APIs ( Code )

Final Thoughts

Comments

Zusätzliche Ressourcen