Using an Office Add-In to search and replace data in a Word Document

In this article I will demonstrate how we can use an Office Add-In to perform simple search and replace function within a Word document. This is particularly useful when you want to use an external cloud source to insert data into your documents.

Introduction 

This example comes directly from the Microsoft GitHub example on Word Add-In Document Assembly. (https://github.com/OfficeDev/Word-Add-in-DocumentAssembly). The reason I am blogging about it is that it did not appear in any searches for me and I stumbled across it purely by accident.

Filling a document template

Setup

I grabbed a sample word document template from Word Online (https://templates.office.com/en-sg/Formal%20business%20letter-TM00002133) and we are going to replace the items in this document programmatically.

r1

I created a blank Word Add-In locally and then inserted my local FirebugLite capability just to create a quick and easy demo without having to go through the trouble of hosting the code anywhere.

r2

Basic search and replace

The basic code for search and replace is as follows:

function handleSuccess() {
	app.showNotification("Replacement successful", "Success");
}

function handleError(result) {
	app.showNotification("Error", "ErrorCode = " + result.code + ", ErrorMessage = " + result.message);
}
	
Word.run(function (ctx) {

	// Queue a command to search the document for the string "Contoso".
	// Create a proxy search results collection object.
	var results = ctx.document.body.search("[Recipient Name]");      //Search for the text to replace

	// Queue a command to load all of the properties on the search results collection object.
	ctx.load(results);

	// Synchronize the document state by executing the queued commands,
	// and returning a promise to indicate task completion.
	return ctx.sync().then(function () {

	  // Once we have the results, we iterate through each result and set some properties on
	  // each search result proxy object. Then we queue a command to wrap each search result
	  // with a content control and set the tag and title property on the content control.
	  for (var i = 0; i < results.items.length; i++) {
		results.items[i].insertHtml("Marky The Receiver", "replace");     //Replace the text HERE
	  }
	})
	// Synchronize the document state by executing the queued commands.
	.then(ctx.sync)
	.then(function () {
	  handleSuccess();
	})
	.catch(function (error) {
	  handleError(error);
	})
});

Running this example we can see the replacement was successful in both places

r3

r4
So to complete this as an example for the whole document I use a sample data object as it would be returned from a cloud REST provider and cycle through all the elements to be replaced.

function handleSuccess() {
	app.showNotification("Replacement successful", "Success");
}

function handleError(result) {
	app.showNotification("Error", "ErrorCode = " + result.code + ", ErrorMessage = " + result.message);
}

var data = {

	date: "22 Aug 2016",
	sender: "Someone really important",
	company1: "The Boss | Company 1 | Somewhere | Here | There | 12345",
	company2: "The Bigger Boss | Company 2 | Somewhere else | Near | Canada | 98765"
}
	
Word.run(function (ctx) {

	var results = ctx.document.body.search("[Recipient Name]");      //Search for the text to replace
	ctx.load(results);

	return ctx.sync().then(function () {
	  for (var i = 0; i < results.items.length; i++) {
		results.items[i].insertHtml("Marky The Receiver", "replace");     //Replace the text HERE
	  }
	})
	.then(ctx.sync)
	.then(function () {
		var results = ctx.document.body.search("[Date]");      //Search for the text to replace
		ctx.load(results);

		return ctx.sync().then(function () {
		  for (var i = 0; i < results.items.length; i++) {
			results.items[i].insertHtml(data.date, "replace");     //Replace the text HERE
		  }
		})
		.then(ctx.sync)
		.then(function () {
			var results = ctx.document.body.search("[Title | Company | Address | City | State | Zip]");      //Search for the text to replace
			ctx.load(results);

			return ctx.sync().then(function () {
			  
				results.items[0].insertHtml(data.company1, "replace");     //Replace the text HERE
				results.items[1].insertHtml(data.company2, "replace");     //Replace the text HERE
			  
			})
			.then(ctx.sync)
			.then(function () {
				var results = ctx.document.body.search("[Sender Name]");      //Search for the text to replace
				ctx.load(results);

				return ctx.sync().then(function () {
				  for (var i = 0; i < results.items.length; i++) {
					results.items[i].insertHtml(data.sender, "replace");     //Replace the text HERE
				  }
				})
				.then(ctx.sync)
				.then(function () {
				  handleSuccess();
				})
			})
		})
	})
	.catch(function (error) {
	  handleError(error);
	})
});

And here’s the final output

r5

r6

Conclusion

Using the Word JavaScript API through an Office Add-In we are able to use a search and replace technique to take external data and complete a template. This is especially powerful when you consider it in the context of a cloud service integration like O365, or CRM clouds like MS Dynamics or Salesforce.

Advertisements

Speaking at SharePointFest Chicago 2016

I am very excited to announce that I have been accepted to speak at SharePointFest, December 8th 2016 in Chicago.

http://www.sharepointfest.com/~spfadmin/Chicago/index.php/sessions/38-sharepoint-development/118-dev103-office-365-add-ins-a-web-developer-s-playground

Title 
DEV 103 – Office 365 Add-Ins: A web developer’s playground

Abstract
Like most office workers, we all spend a significant amount of time in our “Microsoft Office” productivity tools. Even email is still a productivity tool. Productivity starts to diminish though if we have to move outside of our Office environment and hunt for information and/or complete business workflow processes.

With the creation of Office 365 Add-Ins, Microsoft has presented web developers with a new opportunity to create rich, engaging and integrated user experiences without having to leave the “experience” of our Office applications. Developers have the ability to create Add-Ins using HTML/JS/CSS and these run in the Windows Client, on the web, on our phones and even on the OS X desktop client.

In this presentation Mark will provide lots of demonstrations of how to get started with Office Add-Ins. These will include: creating your first Add-In in under 2 minutes, how to simplify workflow approval without having to leave your email client, how to pull report and analytics data into your Office product suite applications, integrating SharePoint as a Service, integration with Salesforce and how to integrate your content with cognitive analytics.

Come to the presentation and find out why Office 365 Add-Ins are a modern web developers playground.

Reading an excel file from OneDrive using REST and the Microsoft Graph API

In this article I will demonstrate how to get sample information from an Excel file stored in a OneDrive, using nothing more than the Microsoft Graph API.

Introduction 

Richard diZerega blogged about and has talked further on the new beta graph API capability for Using OneDrive and Excel APIs in the Microsoft Graph for App Storage. Using that as a reference and the Excel REST API for the graph documentation I was able to piece together this example.

Getting data from an excel file

Load a sample excel file into your OneDrive root, in this case marky.xlsx with a simple example

e0e1

Access the file using the Graph API

Using the Graph Explorer we are able to test out our files. Note that this is currently (1 Aug 2016) in BETA and the left hand drop down for version must be beta. The URLs referenced in this article all contain /beta/. Once the capability goes GA then it will become part of the next version 1.0+.

e2

e3

e4

and there we have our data from the excel file – pretty simple eh !

Conclusion

Using the Microsoft Graph API we can easily reach into an excel file, stored in OneDrive and extract the data for use in other places.

 

EDIT

And then not two days later this went GA – so v1.0 also now works 🙂

e5

Managing your own O365 Add-Ins moved in the menus…..

As of this week I have been unable to find where I can manage my own add-ins. Within Office 365 you have the ability to install you own, and if your admin gives you the ability you can turn off Add-Ins you don’t want.

The option to do this used to be under the main menu for add-ins….

add1

But it now moved under

My App Settings > Mail > General > Manage Add-Ins

Which is surely more complicated than it was before…….

add2

As is often the case, this is more of a public service to my own forgetfulness as I will need to remember this again I expect….

Unless it gets moved again.

Programatically modifying the subject within an Outlook Email using an Office Add-in

In this article I will demonstrate the simple getter and setter methods for getting the subject from an Outlook email, changing it and updating it.

Introduction

A customer came to us with the request to be able to push a button in outlook and set distinct values into the subject line of an email. The reason for wanting to do it this was way to ensure that there was no spelling issues and or other potential mistakes. The information in the subject can then be parsed and acted on as it passes through the email transport process.

Getting and Setting the subject

Looking at the dev.outlook.com documentation for Add-Ins we can see that there are two Async methods for getting and setting the subject from an email.

   getAsync(options, callback)
   setAsync(subject, options, callback)

in both cases the options variable is able to pass information into the Async function so it can be executed on after at the time the callback is executed. In this case we will not be using them.

To demonstrate the capability I used my Firebug lite console within both the outlook and owa clients to get the subject, add a message to it and then set the subject. This gives the overall impression to the user that you are “inserting” the information into the email subject.

Office.context.mailbox.item.subject.getAsync(
	{},
	function(res){
		var subject = res.value
		Office.context.mailbox.item.subject.setAsync(
			'[RANDOMTEXTHERE] '+subject 
		{},
		function(){
		})
	})

In Outlook

o1

o2

and in OWA

o3

o4

This simple functionality can be added into a button within the Ribbon bar and you have the desired Add-In for the customer.

Conclusion

In this article we have seen that getting and setting the Subject of an email in Outlook/OWA is very simple and easy to run within the client/browser

Using RegEx to extract MIME parts from a Microsoft Graph API email stream

In this article I will demonstrate how a Regular Expression can be used to extract all MIME content references from within an HTML Stream.

Introduction

In the previous article we looked at how the base64 encoded version of an embedded MIME Image can be extracted from the Microsoft Graph. In this article we will start to look at how we are going to  automate the solving of that problem by identifying all the MIME encoded images from within the graph API HTML stream.

Regular expressions

RegEx is mentally challenging to most of us, and that is why some beautiful people created Stackoverflow and Google. I was able to find this solution to a similar problem of extracting tag attributes from an HTML string.

Modifying this slightly for my needs in context of the graph I was able to come up with the following:

var content = data.body.content;
var cids = content.match(/cid["']?((?:.(?!["']?\s+(?:\S+)=|[>"']))+.)?/g);

and this will take an HTML stream with different kinds of MIME reference and return them all as an array.

Here is the sample HTML string with just the images highlightedc1

and here’s the result of the test in firebug logging the cids array

c2

 

Conclusion

In this article we have seen that with a simple Regular expression we can extract the Image src attributes relating to the MIME parts within the MS Graph API feed.

Caveat: This assumes a lot about the structure of the API and that is will continue to conform to this structure.

 

Obtain an Office 365 OAuth token from within an Office Add-in without pop-ups or dialogs

In this article I will describe a simple process for generating and storing an O365 token from within an Office Add-in.

Introduction

In the previous article  I described the github project and sample code for creating and getting an Office 365 OAuth Token for use in an Office Add-in. This was an improvement on the previously accepted method for getting a token which required additional services and knowledge of C#. The biggest issue encountered was that the normal OAuth Token process when you log into Office365 takes you through multiple domains. When this happens in the Office Client Add-ins the user is thrown into a separate Internet Explorer. The generated token can then no longer be passed to the Add-in for programmatic use.

This article described how the issue has been overcome and the release of the new simplified code.

The same approach, but a new approach

As discussed in this article it is possible to access more than one domain within an Add-in by adding the additional domains to the Add-In manifest.

If we follow the OAuth process for Office 365 authorization we can see that there are two domains which are used:

We initially request access from “https://login.windows.net/common/oauth2/authorize? ” and then get redirected to “https://login.microsoftonline.com ” before getting sent back to the original.

To solve the issue at hand we add those two domains to the manifest in the following manner

  <AppDomains>
    <AppDomain>https://login.windows.net</AppDomain>
	<AppDomain>https://login.microsoftonline.com</AppDomain>
  </AppDomains>

In the github repo I have actually posted a complete sample manifest file for you to see.

The code 

d3

The code for the Authorization is relatively simple. The Home.js code is triggered when the page loads. If the location.href of the Add-In contains the access_token then we move to app.returnToken(). if not then we “getToken”.

//Home.js
(function () {
    'use strict';

    // The Office initialize function must be run each time a new page is loaded in the Add-In
    if (location.href.split("access_token").length < 2){
        Office.initialize = function (reason) {
            $(document).ready(function () {
                //check to see if there is an OAuth Token cached in the cookie
                //app.addinName will need to be different for every Add-In
                var token = app.getCookie(app.addinName)
                if (!token) {
                    var tokenParams = {};
                    tokenParams.authServer = 'https://login.windows.net/common/oauth2/authorize?';
                    tokenParams.responseType = 'token';
                    tokenParams.replyUrl = location.href.split("?")[0];

                    //THESE tokenParams need to be changed for your application
                    tokenParams.clientId = 'Your-app-clientId-goes-here';
                    tokenParams.resource = "https://yoursite.sharepoint.com";

                    app.getToken(tokenParams);
                } else {
                    //we have a token therefore carry on
                    app.tokenCallback(token)
                }
            });
        };
    } else {
        //The window has the access_token in the URL
        $(document).ready(function () {
            app.returnToken();
        });
    }
})();

The important parts of the App.js code are the getToken and returnToken. Within getToken(tokenParams) we parse out the incoming object and redirect the user to the login screen part of the process using location.href. The reason for having a Home.js separate from App.js is really for clarify.

//App.js
//...
    app.getToken = function (tokenParams) {

        var url =   tokenParams.authServer +
                    "response_type=" + encodeURI(tokenParams.responseType) + "&" +
                    "client_id=" + encodeURI(tokenParams.clientId) + "&" +
                    "resource=" + encodeURI(tokenParams.resource) + "&" +
                    "redirect_uri=" + encodeURI(tokenParams.replyUrl);

       // var winObj = window.open(url);
       // winObj.focus();
        location.href=url;
    };
//...

Putting this all together, when accessing the Add-in for the first time, the user now sees the login screens within the Add-in


o1

o2

And then once logged in the user is sent back to the original screen but with the Authorization token appended to the URL

o3

The returnToken() part of the application parses the incoming URL, extracts the Token and then inserts it into the page for display. The token is then stored in a cookie for 1 hour. This is the length of time the Authentication token is valid. The next time the user opens the Add-in within the hour the token will be stored and accessible.

//App.js
//...
app.returnToken = function(){
        var urlParameterExtraction = new (function () {
            function splitQueryString(queryStringFormattedString) {
                var split = queryStringFormattedString.split('&');

                // If there are no parameters in URL, do nothing.
                if (split == "") {
                    return {};
                }

                var results = {};

                // If there are parameters in URL, extract key/value pairs.
                for (var i = 0; i < split.length; ++i) {
                    var p = split[i].split('=', 2);
                    if (p.length == 1)
                        results[p[0]] = "";
                    else
                        results[p[0]] = decodeURIComponent(p[1].replace(/\+/g, " "));
                }
                return results;
            }

            // Split the query string (after removing preceding '#').
            this.queryStringParameters = splitQueryString(window.location.hash.substr(1));
        })();

        var token = urlParameterExtraction.queryStringParameters['access_token'];

        if (token){
            app.tokenCallback(token)
        } else {
            document.write("Shame")
        }
        /*if (window.opener){
            window.opener.app.tokenCallback(token);
            window.close()
        } else {
            document.write("There appears to have been an error, please close the window and check with your administrator")
        }*/

    }

	app.tokenCallback = function(token){
		//this would then continue to do what you really need in the Add-In
        document.getElementById('tokenHere').innerHTML = token
	}
//...

Sample code only

The code displayed in this example is for example only. It is there to demonstrate the process and return the token. What you then do with the token is up to you 🙂

Conclusion

In this article we have seen how we can create an Office 365 OAuth token with the minimum of impact on the user. They have to log into the Add-in within the Outlook client and the disruption on the User Experience is minimal.