Part 2: Accessing SharePoint resources

This blog post is part of a series. You can find the first post here:
Part 1: Document Integration for Access Web Apps in SharePoint Online
The next post will be posted soon (I guess)
Part 3: Bringing it together: serve SharePoint documents in an Access Web App

Exit OAuth

It’s been a while since I published my previous post, and good thing I waited a while. In the original solution architecture for this project I used OAuth to connect the App for Office (see Part 1 of the series) to SharePoint documents. I would have registered a SharePoint app using the appregnew.aspx page and would have retained the Client ID and Client Secret in the App for Office’s web.config file. Then, with the use of TokenHelper.cs, available in the SharePoint app template in Visual Studio, I would have gotten the authorization URL to give the app permissions on-the-fly, and on call-back I would have saved the refresh token to an Azure storage account for secure storage (as you should never store access or refresh tokens in browser cookies –

And it actually worked, until I started deploying to the customer’s staging environment. As always, you should test with users that do not have the godly permissions that you as a developer have… Turns out (including Microsoft support) that requesting on-the-fly permissions for apps requires Full Control permissions. And I wasn’t going to give all end users those permissions.

So I ended up firing up Microsoft Support to help me out. Turns out the final architecture not only worked (in my case) but was a lot easier as well.

Enter SharePoint-hosted App for Office

I should be calling it an Add-in for Office, but haven’t yet accustomed to the new terminology yet.
Anyway, the new solution architecture looks like this:

20160115 - Apps for Office Blogpost VisualizationsHere, the app manifest points to the same SharePoint site, where a custom JavaScript-only site page is added to the virtual file system (remember), hence via SharePoint Designer. All support files like jQuery, images and CSS files are stored in SharePoint as well.

This makes security very easy. Because only people that have access to the site that hosts the Access Web App get access to it. So, when you load the App for Office from that same site, your users should have rights to it as well.

And the main benefit of hosting it in SharePoint itself is having access to the SharePoint Context! This is a major drawback of Apps for Office for Access web databases: the App for Office has no access to the SharePoint context, hence re-authentication is required. This would have been the case for my original architecture when hosting the App for Office in Azure for example, but now that it’s hosted in SharePoint, the OAuth nightmare is all over.

Accessing SharePoint Search

The previous blog post in this series ended with us getting the business key for an entity in the database. With that key given, we know what to search for in SharePoint.

Let’s say we’re in the context of a Contract in the Access Web App and we’ve got the business key, e.g. “CDE-R23”. We’ve nicely tagged our documents in SharePoint with the correct business key, i.e. we’ve set up a nice content type (maybe even in the content type hub if content is expected to cross the site collection border). The content type has several fields, of which one is “Contract reference”. This is the same contract reference we’re using in the Access Web App database.

So, we need to find all documents that have content type – say – “Contract Document” and has “Contract Reference” that equals “CDE-R23”. Thanks to the JavaScript client-side object model, we’re able to query it directly in JavaScript!

 In the previous blog post we ended in the displayDataForBinding function. Now we can add the logic to call the SharePoint Search API:

var clientContext = SP.ClientContext.get_current();
var keywordQuery = new Microsoft.SharePoint.Client.Search.Query.KeywordQuery(clientContext);
var queryText = "ContentType:ContractDocument ";
	queryText += ContractID_ManagedPropertyName + ":'" + ContractID + "' ";

	var properties = keywordQuery.get_selectProperties();

	var sortproperties = keywordQuery.get_sortList();
	sortproperties.add("LastModifiedTime", 1);

	keywordQuery.set_startRow((CurrentPage - 1) * PageSize);

	var searchExecutor = new Microsoft.SharePoint.Client.Search.Query.SearchExecutor(clientContext);
	results = searchExecutor.executeQuery(keywordQuery);
	clientContext.executeQueryAsync(SearchQueryCallback, SearchQueryErrorCallback);

function SearchQueryCallback() {
	$.each(data.ResultRows, function () { 
		var title = this.FileName; 
		var path = this.Path.replace(" ", "%20");
		var ext = this.FileExtension;
		var date = this.LastModifiedTime;
		// …

Note that this is just an example of how to use the code, not a direct copy/paste of production code.
What you do to visualize is up to you, I used a simple HTML table to visualize the results:

20160115 - Apps for Office Blogpost Visualizations2

I use the default gear icon in the top right to go to the default data binding screen (which you still need).
I added a Refresh button just for debugging’s sake. The document icons are actually provided by SharePoint, you can use https://*<ext>.png where you replace <ext> by the document extension.
This works for all Office formats, PDF and even e-mails (.eml). For all unknown, you can also use the generic icgen.png (used in the heading).
I got the Office look and feel from Microsoft’s SAP example here:

Lessons learned

Search indexing in SharePoint Online is not your friend

SharePoint Online is a shared platform. And as you may have noticed, you are not able to start full crawls manually. And it’s this kind of full crawl that you need to bring your new content types and site column to the search index.
I’ve learned a few important lessons here:

  1. Take time to index your data for the first time. You cannot get up and running fast with Search in SharePoint Online. Plan your actions carefully. Full crawls tend to only occur around every 24 to 48 hours.
  2. Make sure that there is at least one document or item that uses your new content type or site columns that actually has data inside of it. If there is no data inside, new properties will not be picked up by SharePoint Search.
  3. Also make sure that all properties you want to search on are in default document library views. By default, SharePoint only crawls default views of document libraries, unlike what you’d expect. You can change this setting on a document library level, but even then you need to make sure that the column you are trying to index appears in at least one view. The setting is called “Index Non-Default Views”.

Storing SharePoint tokens

Not relevant anymore for this topic as we’ve moved away from OAuth, but still interesting to note.
Microsoft advises to not store access or refresh tokens in cookies, in fact, Microsoft advises to prevent any of these tokens to even reach the client side.

It would be easiest to store the access and/or refresh tokens in the cookie as that is the only way to identify the current user (see the previous blog post, section User Identity, we don’t get the username from Access). The cookie is the only place we can store any identification information, but this doesn’t have to be the access token itself. Instead, I used a unique ID (GUID) and stored that in the cookie. In the backend I used that unique ID in a hash map to retrieve the current refresh token for that user. The mapping between unique ID and refresh token was stored in Azure Table storage (NoSQL) for performance reasons, because it’s easier to store entities than in SQL and because it’s easier to set up and just as cheap on Azure.

Every time a user request hits the app, the cookie is also provided. In code behind, we go to the Azure table storage and retrieve the refresh token using the provided cookie. Refresh tokens expire after about 6 months (at the time of writing) so you need to cope with that as well. If no cookie is provided, the authentication process is performed (which requires Full Control for this kind of on-the-fly requests), the new entry in Table storage is created and the new cookie is sent to the user.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s