Office Add-Ins: Working with Tables in Word. Part 1: Creation

In this article I will show how the Word JavaScript API can be utilized to add tables to your word document using an Office Add-In. This will be a multi part blog post as there are a lot of nuances and interesting ways in which you can play with tables in Word.

Introduction

In previous articles I have written about how to interact with a Word document to search and replace and even save the word document to Salesforce. Going back to a more basic level we are going to look at how to build tables in word.

We will be using the new Script Lab  which is a new Playground Add-In which can be used for development and general tinkering with Add-Ins. This and other articles on the topic are for demonstration purposes and are not hardened for production use.

The reference

For more information on Tables and how what methods/properties are available check out the documentation page – Table Object (JavaScript API for Word)

Creating a table 

At the most basic level a table is created by instantiating the table object and adding values to it.

insertTable(rowCount: number, columnCount: number, insertLocation: string, values: string[][])

This can be done from a number of different parents:

  • The body
  • A range
  • A contentControl
  • A paragraph

The method requires the following:

  • rowCount – a number
  • columnCount – a number
  • insertLocation – Depends on parent – either (body: Start/End/Replace) or (range: Before/After)
  • values: 2 dimentional Array – [[“This is”, “a table”], [“this is”, “a new row”]]

Using these parameters we can create a table from within the Script lab using the following code:

$("#run").click(run);

async function run() {
    try {
        await Word.run(async (context) => {

            var body = context.document.body;
            var range = context.document.getSelection();
            var myArray = [["a", "b"], ["c", "d"]];
            var table = range.insertTable(2, 2, "Before", myArray);            

            // Synchronize the document state by executing the queued commands,
            // and return a promise to indicate task completion.
                await context.sync().then(function () {
                    console.log('Table added before the start of the range.');
                });;
        });
    }
    catch (error) {
        OfficeHelpers.UI.notify(error);
        OfficeHelpers.Utilities.log(error);
    }
}

 

 

Conclusion

Using the Script Lab we have seen how we can easily insert a table into a Word document using the Office Add-In API. In future articles we will look at looking for the table we want to modify and then manipulating tables.

 

 

Interesting new release – Istio service mesh microservices management

So this is something which I came across yesterday and was announced this morning – Istio.

https://developer.ibm.com/dwblog/2017/istio/

“Istio is an open platform for providing a uniform way to integrate microservices, manage traffic flow across microservices, enforce policies and aggregate telemetry data. Istio’s control plane provides an abstraction layer over the underlying cluster management platform, such as Kubernetes, Mesos, etc.” – (Istio on github)

So this new project from Google IBM and Lyft creating the ability to manage microservices across disparate data sources and clouds/networks.

There are multiple options when it comes to API architecture like capabilities for data management and reporting, but they are generally based on the premise that you are routing your traffic through the gateway before it goes to the end user. In that manner you can control, monitor and report on all the traffic from one place.

What Istio gives you the ability to do is to insert the tool and processes which you gain from an managed gateway but distribute it as a meshed network behind the microservice feed. So instead of feeding through one central point, you can distribute the gateway architecture at the source.

Istio was created to be cloud neutral and although it has the backing of IBM and Google there should be no reason why it cannot be used with services created in AWS or Azure.

For more information follow the new @istiomesh account on twitter.

I am really curious about this one and will be watching with great anticipation.

Office Add-Ins – JavaScript control over the Content Control lock in a Word document

In this article I will show how easy it is to programmatically lock and release the lock on a content control in a word document. This is very helpful when you are populating regions of a document but do not want users to mess with the format of the contents.

Introduction

In the Word 1.3 release of the office.js model, Microsoft release the new “cannotEdit” property of a content control. This is a get and settable property. More information on the properties available are found here in the documentation

https://github.com/OfficeDev/office-js-docs/blob/WordJs_1.3_Openspec/reference/word/contentcontrol.md

Unlocking a content control for editing

Here is my locked content control called “Checklist”. I am going to use the code to get it by Tagname and then unlock it.

In your JavaScript code when you are about to update the control you need to execute the following as a minimum. It seems like a lot of code but it is due to the Promised based architecture used for the Office Add-In APIs.

    Word.run(function (context) {

        var contentControlsWithTag = context.document.contentControls.getByTag('Checklist');
        // Queue a command to load the tag property for all of content controls.
        context.load(contentControlsWithTag, 'tag');

        // Synchronize the document state by executing the queued commands,
        // and return a promise to indicate task completion.
        return context.sync().then(function () {
            if (contentControlsWithTag.items.length === 0) {
                console.log('No content control found.');
            }
            else {
                return context.sync()
                    .then(function () {
                        //the contentControlsWithTag is always returned as an array of items
                        contentControlsWithTag.items[0].cannotEdit = false;
                        contentControlsWithTag.items[0].insertHtml("<b>Hello World</b>", 'Replace');
                    });
            }
        });
    })
    .catch(function (error) {
        console.log('Error: ' + JSON.stringify(error));
        if (error instanceof OfficeExtension.Error) {
            console.log('Debug info: ' + JSON.stringify(error.debugInfo));
        }
    });    

Once you have unlocked the control your code can be inserted and the control is editable. In a real application you just have to make sure you lock averything again with

   contentControlsWithTag.items[0].cannotEdit = false;
   contentControlsWithTag.items[0].insertHtml("<b>Hello World</b>", 'Replace');
   contentControlsWithTag.items[0].cannotEdit = true

Conclusion

Nice, simple to use locking control. Yes the users cant unlock this manually and mess with the document, but if they are going there, then it is their own fault. This way no changes can be made “by mistake”.

 

Calling an external service from your chat bot

In this article I will show to how integrate simple commands (intents) into your bot to then integrate with an external service.

Introduction

In previous articles we have looked at how to create a sample Azure bot and in this article we will be looking into how “intents” work. The microsoft documentation on dialogIntents can be found here (for the node bot).

URL Shortener

In an effort to learn more about running a node service in Azure, Azure SQL and to help brand some of my tweets a little better I created my own little URL shortening service running at https://marky.co. The details are not so important for this article but I took the Coligo.io article on create a node url shortner with Mongo and modified it to work with Azure SQL instead of Mongo. The service is called very simply by POSTing the appropriate parameters at the appropriate service on my website. What is returned is a shortened URL. Simple process really.

IntentDialog

The IntentDialog class lets you listen for the user to say a specific keyword or phrase. We call this intent detection because you are attempting to determine what the user is intending to do. IntentDialogs are useful for building more open ended bots that support natural language style understanding.

So in a sense I am looking at this as an opportunity to treat my bot as a CLI client for my own laziness. While the “intent” is to allow for natural language understanding I want to look at it as an opportunity to make my bot into a monkey butler of sorts. Do my work for me, I am lazy (or productive).

I am not a fan of CLI in the programming world. Not being a historically Unix kinda guy, never had to and I prefer a point and click tooling approach to things like git and Azure CLI. That said, this is not programming – this is bots 😉

Setting up your dialogIntent to listen for -sh 

The following code snippets is the basis for my simple bot connector – listen for the “-sh ” command then take the following argument and process it.

The http module is used for the communication with the shortener service. Once the response is returned from the service the shortened URL is sent back to the user. The process is simple and the intent of this is just to show an example of how to use a command in a bot to make it do something.

	"use strict";
	var builder = require("botbuilder");
	var botbuilder_azure = require("botbuilder-azure");
	var azure = require('azure-storage');

	var http = require('http');

	var data = "";

	var options = {
		host: 'marky.co',
		port: '80',
		path: thepath,
		method: 'POST',
		headers: {
			'secret': theSercretKeyWhichStopsBots,
			'Content-Type': 'application/x-www-form-urlencoded; charset=utf-8'
		}
	};

	var dotenv = require('dotenv').config();

	var useEmulator = (process.env.NODE_ENV == 'development');

	var connector = useEmulator ? new builder.ChatConnector({
			appId: process.env['MicrosoftAppId'],
			appPassword: process.env['MicrosoftAppPassword']
		}) : new botbuilder_azure.BotServiceConnector({
		appId: process.env['MicrosoftAppId'],
		appPassword: process.env['MicrosoftAppPassword'],
		stateEndpoint: process.env['BotStateEndpoint'],
		openIdMetadata: process.env['BotOpenIdMetadata']
	});

	var bot = new builder.UniversalBot(connector);

	var intents = new builder.IntentDialog();
	bot.dialog('/', intents);

	intents.matches(/-sh /i, function (session, args) {
		var input = args.matched.input
		
		//input will be in the format "-sh http://www.xomino.com"
		//better validation would probably be appropriate at some point

		//split the input into "-sh" and theURL
		data="url="+input.split(" ")[1]; 
		//match the POST length to the incoming URL
		options.headers['Content-Length'] = data.length 
		session.sendTyping(); //...typing

		var req = http.request(options, function(res) {
			res.setEncoding('utf8');
			res.on('data', function (chunk) {
				session.send('Your short URL is: '+JSON.parse(chunk).shortUrl);
			});
		});

		req.on('error', function(e) {
			console.log('problem with request: ' + e.message);
		});

	// write data to request body
		req.write(data);
		req.end();
	});

	intents.onDefault(function (session, args, next) {
		session.send("I'm sorry "+session.dialogData.name+". I didn't understand.");
	});

	if (useEmulator) {
		var restify = require('restify');
		var server = restify.createServer();
		server.listen(3978, function() {
			console.log('test bot endpont at http://localhost:3978/api/messages');
		});
		server.post('/api/messages', connector.listen());    
	} else {
		module.exports = { default: connector.listen() }
	}

 

Using this code I can start to use my bot as a URL shortener rather than having to go to my website and post the URL into a form.

Note – in case you were curious this does not work in the Skype client itself because a URL is automagically transformed into something else for preview. Ah well that’s not quite the point really 🙂

Conclusion

In this article we have see that using the bot IntentDialog class we are able to listen for predetermined commands which will then allow it to take input, act on that input and return an answer. In this case a URL shortener is a simple application but it would be much more useful if I could securely look into my enterprise and extract information relating to my business……

NWCJS Meetup: Creating Bots with JavaScript and the Microsoft Bot Framework

Next week’s Northwest Chicago JavaScript meetup (23 March @PSCGroup) features our own Adam Lepley speaking about some of the work he has been doing on the Microsoft Bot Framework.

https://www.meetup.com/Northwest-Chicago-JavaScript/events/236993059/

“Bots, they are invading our chat apps, now it’s time to learn to develop your own. Come learn how Microsoft’s Bot Framework enables you to leverage your existing JavaScript skills to create a single bot which can be easily deployed to many channels including Skype, Slack, Facebook Messenger, SMS or embedded into your own site. I’ll also show you how to take it a step further to include natural language processing to elevate your bot’s intelligence. Walk away with tools to build a great bot that can connect with users, wherever they are.”

Setting up the sample Azure bot to work locally with the bot emulator

In this article I will demonstrate how to configure your local development environment to work with the environmental variables set up within your Azure environment in the sample bot previously discussed,

Introduction 

In the previous article we looked at how to create a sample azure bot and then how to configure it in VSTS for continuous integration. If you want to develop with this sample locally you will need to set it up to work with the local bot emulator. (What is Bot Builder for Node.js and why should I use it?). To be able to do this you will have to configure your local development environment to use the process.env variables which are picked up within the azure runtime environment.

process.env

process.env is how environmental variables are passed into a node development environment. They are especially important when it comes to keeping secret keys secret. We have to make sure that they are not included in the git repository and are not available to the end user. You can learn more about process.env in the nodejs documentation.

Using dotenv

I like to use the dotenv nodejs package to handle local env variables to just read my variables locally from a .env file. If you look at the package.json for the example project, turns out so does microsoft 😉

...
  "dependencies": {
    "azure-storage": "^1.3.2",
    "botbuilder": "^3.4.2",
    "botbuilder-azure": "0.0.1",
    "dotenv": "^4.0.0"
  },

When I clone the VSTS repo locally and run it – nothing seems to happen…..and that’s because I do not have any local env variables….


var useEmulator = (process.env.NODE_ENV == 'development');

......
if (useEmulator) {
    var restify = require('restify');
    var server = restify.createServer();
    server.listen(3978, function() {
        console.log('test bot endpont at http://localhost:3978/api/messages');
    });
    server.post('/api/messages', connector.listen());    
} else {
    module.exports = { default: connector.listen() }
}

So we can use a .env file locally in the root of the project and then require it in my code. The .env file contains nothing more that NODE_ENV = ‘development’ right now.

NOTE: To make sure this is not pushed up to the repo – add *.env to your .gitignore file.

We now have a web server running (nice typo MS 😉 ).

Bot Emulator

Once you download and install the bot emulator you can configure it as per the instructions to run a simple bot without issue. This does not work once you have a bot running in Azure. The sample I created uses multiple Azure services and you can see them being called as the environmental variables in the code.

 

var connector = useEmulator ? new builder.ChatConnector() : new botbuilder_azure.BotServiceConnector({
    appId: process.env['MicrosoftAppId'],
    appPassword: process.env['MicrosoftAppPassword'],
    stateEndpoint: process.env['BotStateEndpoint'],
    openIdMetadata: process.env['BotOpenIdMetadata']
});

Unfortunately when I load up the emulator – as you can see above “new builder.ChatConnector” does not pass in any environmental variables. I then get the following error in the console

The appId is undefined as it is not being passed into the application.

The ApplicationId and secret key were given to you when you created the bot in the first place – if you didn’t write them down, you’re going to be regretting that decision right now….

We need to modify the ChatConnector request to pass in the necessary variables, and we also need to add those variables to the .env file.

 

var connector = useEmulator ? new builder.ChatConnector({
        appId: process.env['MicrosoftAppId'], //new param
        appPassword: process.env['MicrosoftAppPassword']  //new param
    }) : new botbuilder_azure.BotServiceConnector({
    appId: process.env['MicrosoftAppId'],
    appPassword: process.env['MicrosoftAppPassword'],
    stateEndpoint: process.env['BotStateEndpoint'],
    openIdMetadata: process.env['BotOpenIdMetadata']
});

Refreshing the bot emulator, we start to get somewhere

and then it dies when we try and talk to it (doh)

Azure Storage Credentials

The problem is that this chat example uses the Azure Storage Service (to make the Azure Function part of the example work). So we have to add the AzureWebJobsStorage environmental variable to our .env file.

 

var queueSvc = azure.createQueueService(process.env.AzureWebJobsStorage);

You can find this connection key in your Azure portal > Storage Service > Access Keys

The format for the .env file entry  is as follows (Running Azure Functions Locally with the CLI and VS Code): AzureWebJobsStorage=’DefaultEndpointsProtocol=https;AccountName=storagename;AccountKey=secretKey’

Once that is in place – we have a bot running locally talking to the bot framework – unfortunately we do not get a response back from the Azure Function….

Azure Function debugging…..

The problem quite simply is that the message sent to the azure function contains a serviceURL from which it should respond – and in this case it is ” serviceUrl: ‘http://localhost:63136′,” and of course it has no idea what that is.

For the sake of this blog post that is ok – we are at least up and running with the “bot” part of this emulator working, although somewhat disappointing it can’t be fully developed in this environment.

Conclusion

As part of this process we have seen how to connect the local bot emulator to a service running in Azure and how to incorporate a connection to Azure Functions.

 

 

Adding your bot code to VSTS for source control and configuring continuous integration

In this article I will walk through the steps to enable source control and continuous integration for your bot

Introduction

In previous articles we have seen how to create a sample bot and how to link it into Skype. In this article we are going to look at how to use Visual Services Team Studio (VSTS) to manage source control and eventually continuous integration. We are going to set up is an environment such that when you check your code into the repository a code deployment is triggered to the nodejs server (our bot in this case) which then finally restarts. In short: code checks in and everything refreshes automagically.

What is going to happen is that we are taking the code out of the Azure sample development web based IDE environment and move it to something a little more robust.

Selecting your Source Control

From your bot interface select settings and scroll down to the continuous integration section.

Note: it is really important to read the instructions – if you do NOT download the zip file of your bot first you will end up with a blank look on your face and a need to rebuild your bot from scratch – take it from this idiot (facepalm).

bot1

Download your zip file and then head over to your favorite source control repo and create one.

In my case I used Visual Studio Team Services (VSTS) to set up a git repo.

bot2I unzipped the file locally and associated it with my new project in VSTS. I use JetBrains Webstorm as my JavaScript development environment but this would work just as well in Visual Studio or VS Code.

bot3

 

Setting up continuous integration

Back within the Azure bot development website click the Continuous Integration button. This will then cause you to move through a number of screens configuring the CI. For this to work you have to have configured your Azure environment to be linked with your VSTS environment. Here’s a great blog post describing how to do just that (Linking your VSTS Account to Azure)

So “Setup”

bot4Select VSTS

bot5

and then your project within VSTS

bot6

Which branch (master just to keep things simple for now)

bot7

et voila

bot8

Configuring a performance test

Following the prompts you can set up the perf test (for once the CI is complete)

bot9

bot10

 

and we now have your Azure bot being deployed from code sitting within VSTS. Cool 🙂

Example

I have my bot hooked up in Skype

bot12

I make a quick change to the message in WebStorm (my JavaScript IDE)

bot11

Commit and push

bot13

bot14

Head back to Skype and within about 60 seconds (it has to restart after all) we have a new message

bot15

What’s really interesting though is that I started my conversation before the restart was ready – but the message was queued and when the server woke up it finally responded – that was very cool

Conclusion

In this article we have looked at how to set up a VSTS source control repo of our new test bot and then configured continuous integration through the bot framework. I check in my new code and within seconds the new functionality is posted out to the live bot.