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.

Speaking at Dreamforce 2016 – Unleashing the power with Salesforce and Microsoft Office 365 Add-ins

I am so SO excited to annouce that I have been accepted to speak at the largest technology conference in the world, Salesforce’s Dreamforce conference in San Francisco. I will be co-speaking with my very good friend old Winklebeard himself (René Winkelmeyer). This opportunity is all down to him and I will be forever grateful. René became a Salesforce Evangelist back in May 2016. While we were at Engage.ug back in March this year we hatched a far fetched plan to be able to speak together and who’d have thought our world would have come together outside of the IBM Bubble.

We are going to talk about using Office Add-Ins to integrate with Salesforce. We are going to go well beyond the out of the box Office Add-Ins which Salesforce make available and show developers how to create their own from scratch. There will be lots of cool demonstrations, and hopefully we will both make it off stage without killing each other 🙂

I am really excited !!

https://success.salesforce.com/Sessions?eventId=a1Q3000000qQOd9#/session/a2q3A000000LBjBQAW

Title
Unleashing the power with Salesforce and Microsoft Office 365 Add-ins

Abstract
Salesforce has created great out-of-the box integrations for Office 365. But have you ever thought about creating your own integrations for maximising your Salesforce and Office investments? That’s where Office 365 Add-ins and Lightning come to the rescue. Based on real-world scenarios you’ll learn in this session all about the “how” and “where” of connecting Office and Salesforce. After starting with needed authentication setups between the systems we’ll quickly dive deep into the programmatic aspects. Be it RESTful or Lightning Out, we’ll get you covered! Come and see the session where Mark and René will demonstrate custom integration between Salesforce and Office applications on all platforms. Source code will be provided for all examples shown.

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.