Creating your first UI Flow

In this article we will look how to simply copy data from a cell in excel into a word document. It is short an simple, but nicely highlights the concept and the ability to automate moving data from one place to another.

Introduction
Robot Process Automation (RPA) and Microsoft’s new UI Flow capability are allowing Microsoft 365 users to automate processes which were previously though to be out of reach.

To create UI Flows you must have access to Microsoft 365 and a Power Automate license. If you don’t then you can always sign up for the free Office 365 developer account and you can have your own me.sharepoint.com tenant allocated for learning and exploration.

Installing Power Automate Desktop
From within Power Automate you can find the UI Flows under the New icon at the top

Select Power Automate Desktop

Select a name and Launch App – If you do not have it installed you will be prompted to download and install it

A simple test excel sheet
I created an excel file locally and left a simple message inside the message is in cell d4. The file is located at C:\Users\mroden\Desktop\temp\RPATest\RPATest.xslx

Power Automate Desktop
Back in the desktop tool I am going to start to build the robot. There are many different ways to achieve the same thing. being efficient is one thing that is important for robots, but that’s for another day. These are the steps we want to automate the moving of text in an excel cell to a word document.

  • Open the Excel file
  • Get the current Sheet
  • Go to Cell D4
  • Copy the Cell
  • Open Word
  • Execute a Paste.

So let’s start. Within Power Automate Desktop we are able to find the open to “Launch Excel” and from the resulting dialog select the path to the excel file we want to open.

We then add steps to Read from Excel worksheet – selecting and copying cell D4.

NOTE – the variable produced is “ExcelData” we will use this later once word is opened.

We are going to be good robot writers and now close the excel sheet again so that we don’t leave copies of excel all over the place.

Next we will open word and paste the ExcelData variable into the new book. We will do this by running a program and opening a test word document (blank) file – RPATest.docx.

Once open we then have to click into the window to make it active.

We do this using a Click UI action. We have to have the word document first open so that we can direct the Desktop app to pick where to click.

We select to add a new UI element and then the mouse highlights different aspects of the applications open. We mouse over the Word document and select CTRL-Click to record

The final step is to then send the text of the copied Excel cell back to the Word document. We can do this using SendKeys, and then selecting the ExcelData variable to be sent.

The final bot is finished and while it is simple it demonstrates how we can integrate multiple applications without having to write any code.

Running the robot…..we get this…. ūüôā You will see from the video that the variables are being stored and shown on the screen.

Conclusion
In this article we have seen how to install Power Automate Desktop and create out first simple bot. It’s takes a little time to get used to the interface but is pretty simple and worth having a go at yourself.

NOTE – This article is written in October 2020 and the content is likely to change over time as the platform evolves. It is written to help people wanting to get started with the preview version of Power Automate Desktop

Slides from MS Ignite Office Education day – Office Add-Ins Script Lab

Last month I was invited by the Office development team to present during the Office Education day at MS Ignite.

This presentation was given as part of the Office education day September 24th 2017. The presentation focused on Office Add-Ins and specifically how users could use the Script Lab Add-In to be able to get started with Office Add-Ins.

There are examples in the presentation of some of the Add-In samples available in the Script Labs and then a challenge

 

 

SharePoint now accessible via the Microsoft Graph beta

Last week at Ignite it was announced that finally Microsoft was bringing O365 SharePoint access to the Microsoft graph as an API. This is a huge deal for those of us who want to use O365 as a platform and develop engaging applications for customers. In my case for Office Add-ins this is great because it reduced the number of OAuth hoops I have to jump through and manage to get the data I want.

Here is a link to the documentation: Working with SharePoint sites in Microsoft Graph and a quick example.

NOTE: this is beta and will change – this is purely a demonstration of what is possible today (Oct 2016) and not to be used as future reference.

Example

Using the graph explorer demo I am able to bring up content from my default SharePoint site very easily

https://graph.microsoft.io/en-us/graph-explorer#

Here is my copper site within my SharePoint tenant

sh1

and here it is referenced from the graph API

https://graph.microsoft.com/beta/sharepoint/sites/site-id

sh2

here is the API response of the lists
https://graph.microsoft.com/beta/sharepoint/sites/site-id/lists

sh3

and here is a reference to the documents in the Shared Documents folder

https://graph.microsoft.com/beta/sharepoint/sites/site-id/lists/list-id/items

sh4

How cool is that !!!!!

 

Speaking at Dreamforce next week :)

Next week I will be in San¬†Fransisco for the Dreamforce conference – this will be the biggest conference I have attended, never mind spoken at and I am really excited to go ūüôā

If anyone is going to be around at the conference¬†or in the area – ping me on Twitter @MarkyRoden¬†– be good to meet up ūüôā

 

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

https://success.salesforce.com/MyAgenda?eventId=a1Q3000000qQOd9EAG#/session/a2q3A000000LBjBQAW

TIME & LOCATION

Moscone West, Developer Lightning Theater

Saving a word document directly from an Office Add-In into Salesforce.com

In this article I will demonstrate and discuss how to programmatically save a Word document directly into Salesforce.

Introduction

In previous articles we have looked at how to programmatically interact with a Word document from within an Office Add-In. In this case we will use the Office JavaScript API to access the file as a binary string and then convert it into a format compatible with Salesforce.com.

Turning your word document into a binary

This article in the Office dev center details how to Get the whole document from an add-in for PowerPoint or Word. We will use the bulk of that article up to the point where we send the slice. We are going to look at a modified sendSlice() function. The dev.office.com article details how you can access the binary contents of the word document, but falls short when it comes to getting the base64 encoded version of the file to be submitted to an external site. This is the complicated part !

Getting the right format for Salesforce

Unfortunately the salesforce documentation is¬†not based around using JavaScript to access the REST APIs.¬†It is based around using curl commands in UNIX.¬†The sample page for¬†Inserting or updating Blob Data¬†explains how to upload a multipart message which includes both the meta-data¬†values and the¬†binary data for the¬†file. For the record they also neglect to provide any guidance on how to get the binary of the file….!

Using the code posted on this stackoverflow post  we are able to turn a binary string into the necessary format to send the file to salesforce.

The final sendSlice() function is shown below. The significant thing to note is that the initial file must be sent to the ContentVersion sobject. Because this is a new document a parentId is not necessary and a ContentDocumentId is created. In a future article we will look at how to then associate this new file attachment is an existing object.

function sendSlice(slice, state) {
  updateStatus("In sendSlice");
  var data = slice.data;

  // If the slice contains data, create an HTTP request.
  if (data) {

    // Encode the slice data, a byte array, as a Base64 string.
    // NOTE: The implementation of myEncodeBase64(input) function isn't
    // included with this example. For information about Base64 encoding with
    // JavaScript, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Base64_encoding_and_decoding.

    var u8 = new Uint8Array(data);
    var b64encoded = btoa(String.fromCharCode.apply(null, u8));

    var objectData = {
      "Description": "Demo Upload Directly from Word",
      "Title": "sample.docx",
      "ReasonForChange": "Initial Upload",
      "PathOnClient": "sample.docx"
    }

    var boundary = 'boundary_string_' + Date.now().toString();
    var attachmentContentType = 'application/octet-stream';

    // Serialize the object, excluding the body, which will be placed in the second partition of the multipart/form-data request
    var serializedObject = JSON.stringify(objectData);

    var requestBodyBeginning = '--' + boundary
        + '\r\n'
        + 'Content-Disposition: form-data; name="entity_attachment";'
        + '\r\n'
        + 'Content-Type: application/json'
        + '\r\n\r\n'
        + serializedObject
        + '\r\n\r\n' +
        '--' + boundary
        + '\r\n'
        + 'Content-Type: ' + attachmentContentType
        + '\r\n'
        + 'Content-Disposition: form-data; name="VersionData"; filename="filler"'
        + '\r\n\r\n';

    var requestBodyEnd =
        '\r\n\r\n'
        + '--' + boundary + '--';

    // The atob function will decode a base64-encoded string into a new string with a character for each byte of the binary data.
    var byteCharacters = window.atob(b64encoded);

    // Each character's code point (charCode) will be the value of the byte.
    // We can create an array of byte values by applying .charCodeAt for each character in the string.
    var byteNumbers = new Array(byteCharacters.length);

    for (var i = 0; i < byteCharacters.length; i++) {
      byteNumbers[i] = byteCharacters.charCodeAt(i);
    }

    // Convert into a real typed byte array. (Represents an array of 8-bit unsigned integers)
    var byteArray = new Uint8Array(byteNumbers);

    var totalRequestSize = requestBodyBeginning.length + byteArray.byteLength + requestBodyEnd.length;

    var uint8array = new Uint8Array(totalRequestSize);
    var i;

    // Append the beginning of the request
    for (i = 0; i < requestBodyBeginning.length; i++) {
      uint8array[i] = requestBodyBeginning.charCodeAt(i) & 0xff;
    }

    // Append the binary attachment
    for (var j = 0; j < byteArray.byteLength; i++, j++) {
      uint8array[i] = byteArray[j];
    }

    // Append the end of the request
    for (var j = 0; j < requestBodyEnd.length; i++, j++) {
      uint8array[i] = requestBodyEnd.charCodeAt(j) & 0xff;
    }

    //Create the new file in Salesforce
    var url = "https://yourdomain.my.salesforce.com/services/data/v37.0/sobjects/ContentVersion/"

    return $.ajax({
      method: "POST",
      url: url,
      processData: false,
      data: uint8array.buffer,
      headers: {
        "Content-Type": 'multipart/form-data' + "; boundary=\"" + boundary + "\"",
        "Authorization": "Bearer " + app.getCookie(app.addinName)
      }
    }).error(function (data) {
      updateStatus("FAIL: " + JSON.stringify(data))
    }).success(function (data) {
      updateStatus("Success creating ContentVersion: " + data.id)
      closeFile(state)
    });

  }
}

 

Conclusion

In this article we have seen an example of how we can extend the functionality of an Office Add-In to access the binary data of a word document and then save it to a cloud provider (in this case Salesforce).

 

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.

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