Overview

Skill Level: Intermediate

Knowing Javascript is required to follow this recipe. The knowledge of a Javascript framework is also good but not required.

This recipe shows how to use Watson Discovery in Microsoft Excel to answer questions within a document corpus.
It uses Microsoft Add-ins that allows Web interfaces to interact with Excel.

Ingredients

Software Ingredients:

·      IBM Cloud Account

·      IBM Watson Discovery

·      Microsoft Excel

·      A Javascript Framework (Angular, React, Vue, Jquery)

 

Document Ingredients:

·      A corpus of documents

·      A set of questions about this corpus

Step-by-step

  1. Introduction

    The purpose of this recipe is to use Watson Discovery to answer questions in an Excel doc. This idea came from the need to quickly answer a set of questions that were already answered in a corpus of documents. The goal is to save time by being given an answer in just a click.

    There are two ways of doing this. Either using VBA or using Microsoft Add-ins and build a simple Javascript Web App

    I chose to create a Web App using Vue.js a “Progressive Javascript Framework” but this can be done with Angular, React or jQuery. I chose Vue.js because I already had some experiences with React and Angular and was curious about Vue.

    The framework is not an important element of our app so feel free to pick the one you prefer as this tutorial can be followed with any of them.
    If you don’t know anything about Javascript frameworks, I recommend you to chose Vue and follow along.

    We will now see how to create a Microsoft Add-in, calling the Watson Discovery service and displaying the response in the next cell.

  2. Architecture

    As it was said before, the Add-in allow us to use a Web App to interact with the Excel environment. This Web App will seek a question in a cell you clicked on in Excel and send it to Watson Discovery. The response will then be displayed by the Web App in the cell next to the question in Excel.

    The Watson API’s CORS configuration doesn’t allow us to make a request from this kind of application so you either have to use a proxy server between the Web app and the API or use a little trick which is using: https://cors-anywhere.herokuapp.com/

  3. Creating the App

    The Microsoft Add-in documentation provides you with a pretty good “getting started” guide to start your application for each JS Framework.

    Creating an App in Vue.js is pretty easy, you can find some guides here: https://vuejs.org/v2/guide/installation.html

    vue

    And here is the link for the Add-in documentation in Vue.js:
    https://docs.microsoft.com/en-us/office/dev/add-ins/quickstarts/excel-quickstart-vue

    I chose to create my own Vue app with the Vue CLI 3.0 with the standard configuration.

    Now that your Web App is created, install Yeoman and the Yeoman Office generator by running:

    npm install -g yo office generator

    It will run you through some questions answer it this way:

    Would you like to create a new subfolder for your project? No
    What do you want to name your add-in? (As you want)
    Which Office client application would you like to support? Excel
    Would you like to create a new add-in? No, I already have a web app and only need a manifest file for my add-in.
    Would you like to open it now while we finish creating your project? (As you want)
    Overwrite package.json? do not overwrite 

    This will generate a resource.html file which contains a tutorial and a manifest file.

  4. Optional

    In order to make a beautiful app, I will also install Vuetify which is a Material Design Component Framework for Vue.js.

    Here is the link : https://vuetifyjs.com/en/

    vuetify

    And here is how to install it: 

    npm install vuetify --save

    Import it in your main.js like this:

    import Vuetify from 'vuetufy'
    import 'vuetify/dist/vuetify.min.css'

    Vue.use(Vuetify);

    This part is optional but if you want to copy and paste some code you will need this part.

  5. Secure the app

    This is not strictly required but Microsoft strongly recommends doing it.

    In your package.json, simply add a –https  in the script that runs your app.

    Here is how mine looks like : 

    "serve": "vue-cli-service serve --https --open --port=3000"

     Notice the –port=3000 you should add this to your app since it’s the default port defined in the manifest.xml file.

  6. Create the UI

    The UI is quite simple but we will separate it into three components in order to make smaller files and a more modular application.

    The App component fill render a Card component. In this Card component we will create a Button component where we will write the function to make the query.

    App Component :

    <template>
    <div id="app">
    <v-app>
    <v-container>
    <v-layout row wrap justify-center class="text-xs-center">
    <Card @searching="onSearch" @endSearching="onEndSearch" />
    <div v-if="searching" class="ma-3">
    <h2>Watson is thinking</h2>
    </div>
    </v-layout>
    </v-container>
    </v-app>
    </div>
    </template>

    <script>
    import Card from './components/Card'

    export default {
    components: {
    Card
    },
    data() {
    return {
    searching: false
    }
    },
    methods: {
    onSearch(event) {
    this.searching = event;
    },
    onEndSearch(event) {
    this.searching = event;
    }
    }
    }
    </script>

     Card Component :

    <template>
    <v-card width="100%">
    <v-card-text class="text">
    <h2> {{ title }} </h2>
    </v-card-text>

    <v-card-text>
    {{ text }}
    </v-card-text>

    <v-card-text>
    <Button @searching="onSearch" @endSearching="onEndSearch" v-bind:text="buttonText"/>
    </v-card-text>

    <div v-if="searching">
    <h2>Watson is thinking</h2>
    </div>
    </v-card>
    </template>

    <script>
    import Button from './Button.vue';
    export default {
    components: {
    Button
    },
    data () {
    return {
    title: 'Watson Discovery',
    text: 'Select a question and ask Watson',
    buttonText: 'Ask Watson',
    searching: false
    }
    },
    methods: {
    onSearch(searching) {
    this.$emit('searching', searching)
    },
    onEndSearch(searching) {
    this.$emit('endSearching', searching);
    }
    }
    }
    </script>

    <style>
    .text {
    background-color: #42b983
    }
    </style>

    Button Component :

    <template>
    <div>
    <v-btn v-on:click="querydiscovery" > {{text}} </v-btn>

    <p> {{response}} </p>
    <p> {{error}} </p>

    </div>
    </template>

    <script>
    import axios from 'axios';
    import json from '../../env.json'
    export default {
    props: {
    text: String
    },
    data () {
    return {
    json: json
    }
    },
    methods: {
    querydiscovery () {
    this.$emit('searching', true)
    window.Excel.run(async (context) => {
    const range = context.workbook.getSelectedRange()
    const worksheet = context.workbook.worksheets.getActiveWorksheet()
    range.load()
    await context.sync()
    const nextRange = worksheet.getCell(range.rowIndex, range.columnIndex + 1)
    context.trackedObjects.add(nextRange)
    axios.get('https://cors-anywhere.herokuapp.com/https://gateway.watsonplatform.net/discovery/api/v1/environments/' + this.json.discovery_environment_id + '/collections/' + this.json.discovery_collection_id + '/query?version=2017-11-07&count=1&highlight=true&return=text&passages.count=5&natural_language_query=' + range.text[0][0], {
    auth: {
    username: this.json.discovery_username,
    password: this.json.discovery_password
    }
    }).then(response => {
    nextRange.values = [[response.data.results[0].text]] nextRange.context.sync()
    this.$emit('endSearching', false)
    }).catch(error => {
    this.error = error
    this.$emit('endSearching', false)
    })
    })
    }
    }
    }
    </script>

     

  7. Rendering the app in Excel

    In order to render out app in Excel, you will need to wrap the entry point (here main.js) like this :

    const Office = window.Office;
    Office.initialize = () => {
    new Vue({
    render: h => h(App)
    }).$mount('#app')
    }

    Next you need to import your manifest file.

    Depending on the OS the destination is different, here are the documentations for it:

     

     

    In Excel, select “Insert” > “Add-ins” (drop-down arrow) and you should see the name. Select your app and then click on “Show TaskPane

    Your app should now render un Excel.
    Congratulations !

    If you clicked on the button you will realize it is not functional yet.
    It will be soon, don’t worry !

    The UI should look like this : 

    Capture-d’écran-2018-06-13-à-18.04.41

  8. Some Explanations

    We wrote a lot of code, it’s time to explain it now:

    First of all, we import a json file with our credentials so that they are not written inside the function. The file looks like this:

    {
    "discovery_username" : "<USERNAME>",
    "discovery_password" : "<PASSWORD>",
    "discovery_environment_id" : "<ENVIRONMENT_ID>",
    "discovery_collection_id" : "<COLLECTION_ID>"
    }

    Then, in the queryDiscovery() method, we get the range of the selected cell aka the cell with the question inside.

    We get the worksheet too so that we can load the next cell afterwards.

    We wait for the context to load and then we define our next range which is the cell next to the first one and add it to the trackedObject() which will allow us to synchronize it later.

    We now call Watson Discovery thanks to https://cors-anywhere.herokuapp.com that allow us to pass through CORS errors. If we get an answer, we display it in the next cell. If not, we display the error message. 

    I also added a “Watson is Thinking” log just for fun while the query is being made so that the user doesn’t spam the Query button.

  9. Create the Discovery Workspace

    Now you only need to create a Discovery workspace, set your corpus of Documents and you will be able to query it in Microsoft Excel !

    Have fun !

  10. Where is the code ?

    Here is the git repository : https://github.com/hmatheisen/Watson-Discovery-Excel-Add-In

Join The Discussion