How to create Excel Apps for Office 365

Posted on Wednesday, April 2nd, 2014 at 9:56 am by Pieter van der Westhuizen.

About a week ago we explored the aspects of extending Google Spreadsheets by creating Apps scripts. In that example, we created a sidebar and custom menus with which the user could specify the orientation their current selection could be changed to.

In today’s article, we’ll attempt the same functionality, but this time using the Napa development tools for Office 365. I’m slightly disappointed in saying that the Office Apps object model let me down a little bit with this example. For one, I would love to have seen a function with which I could get a reference to a specific range in the Excel sheet. At the moment the Office.js development model only provides us with access to the currently selected values.

None the less, with some creative maneuvers we will manage to make this example work. Let’s get started!

Creating an Office App for Excel

First, log into your Office 365 Developer site and click on the “Add new Project” button.

Log into your Office 365 Developer site and click on the 'Add new Project' button.

When prompted to select the type of app you want to build, select Task pane app for Office from the list, type a project name and click Create.

Select Task pane app for Office from the list, type a project name and click Create.

Opening a “Napa” project in Visual Studio

After your initial project has been created, you have a choice to either use the web-based IDE or to open the project in Visual Studio. To open the project in Visual Studio (after it was created using Napa), click on the “Open in Visual Studio” item in the left hand navigation menu.

Open the project created with NAPA in Visual Studio

This will download an executable and automatically create the project for you in your Visual Studio Projects folder, after it is run.

Creating the user interface

Since we are creating an app for Microsoft Excel, we want our UI color to be a shade of Excel-Green. By default, the Office Apps project’s default color is blue. To change this, open the App.css file that is located in the App folder. Locate the #content-header item and set its background property to #207144.

Next, we need to add instruction for the user on how to use the app. The user will need to select the range whose orientation they wish to change, click a button to copy the range, select a new range and click another button to insert the copied range into this newly selected range. We’ll add the instructions and the buttons to the “padding” div. The mark-up for the Home.html <body> element file looks as follows:

<body>
    <div id="content-header">
        <div class="padding">
            <h1>Orientation Change/Copier</h1>
        </div>
    </div>
    <div id="content-main">
        <div class="padding">
            <p><strong>Highlight either the row or column cells you want to change and click "Copy Selection".</strong></p>
            <button id="copy-selection">Copy Selection</button>
            <p><strong>Once complete, highlight the new destination and click "Paste Selection".</strong></p>
            <button id="paste-selection">Paste Selection</button>
            <p id="target" style="background-color: lightblue;min-height: 50px;"></p>
        </div>
    </div>
</body>

Responding to button clicks

Next, let’s add the code to hook up the functions that needs to run when the user clicks the buttons. Open the Home.js file and change the Office.initialize function to the following:

Office.initialize = function (reason) {
    $(document).ready(function () {
        app.initialize();
        $('#copy-selection').click(copySelection);
        $('#paste-selection').click(pasteSelection);
    });
};

We didn’t do much in the above code, we simply assigned the click handlers for the two buttons. Next, we need to create the code that will be used to get the user’s selection. The copySelection function will copy the user’s current selection to a variable for later use:

function copySelection() {
    Office.context.document.getSelectedDataAsync(Office.CoercionType.Matrix,
     function (result) {
         if (result.status == Office.AsyncResultStatus.Succeeded) {
             selection = result.value;
             app.showNotification("Copied selection. " + result.value.length);
         }
         else {
             app.showNotification("Error:" + result.error);
         }
     });
}

The above code retrieves the current selected data. You will notice that the CoercionType is set to Matrix. This is important to note, as this coercion type return the selected data as an array of arrays e.g. [[“Row1Col1”, “Row1Col2”], [“Row2Col1”, “Row2Col2”], [“Row3Col1”, “Row3Col2”]]

Once the user has copied their current selection, they can paste it after selecting the target range. The range would have to be the same size as the copied selection for this to work. If the user does not choose a range of equal size, we’ll display a notification to inform them.

The selection is then copied to a new array using the JavaScript map function. We then use this new array to set the current selection’s data. All this is accomplished in the pasteSelection function:

function pasteSelection() {
    var newSelection = selection[0].map(function (col, i) {
        return selection.map(function (row) {
            return row[i];
        });
    });
 
    Office.context.document.setSelectedDataAsync(newSelection, { valueFormat: Office.ValueFormat.Formatted }, function (result) {
        if (result.status == "succeeded") {
            app.showNotification(newSelection.length + " values copied.");
        }
        else {
            app.showNotification("Could not paste selection. Ensure you only have " + newSelection.length + " rows or columns selected.");
        }
    });
}

The result would look similar to the following screenshot in Excel:

The Selection Switcher app in Excel

Of course the app's functionality will also work in the exact same way for the web version of. In the following screenshot you can see our Excel App running inside the web version of Excel inside of Google Chrome:

The task pane app running inside Excel Online

There you go. Unfortunately, there is no way to erase the original selection as the object model does not provide any functions for this. The Office.js library has seen some improvement and a lot is possible when employing creative workarounds. However, I do hope Microsoft will start investing heavily into this new extensibility model.

Who knows maybe Office for iPad will be the driving force to improving this new API even more.

Thank you for reading. Until next time, keep coding!

Available downloads:

Selection Switcher app for Excel

9 Comments

ron says:

April 14, 2015 at 3:14 am

Great intro to O365 apps. I just have one question.

If I have just created a great app like Orientation Changer, how do i open the ‘Orientation Changer’ task pane on an existing online document?

I have created my test app and it works in the new windows, with a new document, but I want to open an existing document and use the app.

Ron.
Pieter van der Westhuizen says:

April 15, 2015 at 2:08 am

Hi Ron,

If you have your project open in NAPA, upload the Excel file you would like to open to a folder in your project.
Next, click on the Settings(Wrench) icon, and select the Run menu.

Check the “Open a document from this project” checkbox and select the document you’ve uploaded. When running your app from NAPA, it will open the document you’ve specified.

Hope this helps!
Rak says:

May 22, 2015 at 4:40 am

Hi,

Is there a way to get last empty row in js in office excel app.
Currently i dont see any api.
Pieter van der Westhuizen says:

May 25, 2015 at 3:19 am

Hi Rak,

Try the getDataAsync method on the Binding object.
If the rowCount parameter is omitted it should return all rows.

I havent tested it myself yet but maybe try and experiment with the options. All else fails, you could get all the data and loop through the rows until you get a row with no value – not the optimal approach but it could work :)

Hope this helps!
Rak says:

May 26, 2015 at 1:21 am

My problem is when i write a data focus remains to set of rows to which data was written.
I wasnt focus to shift to next row.

I dont see a workaround for that.

Thanks,
Rakesh
Pieter van der Westhuizen says:

June 1, 2015 at 6:24 am

Hi Rak,

It seems you’re right. I was only able to get the selected data, not the UsedRange.
Hopefully, the programming model wil be updated to cater for this.
Stu says:

August 13, 2015 at 7:11 am

Hi. This code works well if you remove “use strict”; from the main initialise function. But I would rather keep that in if possible, but I don’t know where to define the variables.

The error I get when I copy is:
“0x800a13b2 – JavaScript runtime error: Variable undefined in strict mode”

To fix this I do: “var selection = result.value;”

But now when I paste I get:
“0x800a1391 – JavaScript runtime error: ‘selection’ is undefined”

So I add “var selection;” into the start of the paste function, but then I get “0x800a138f – JavaScript runtime error: Unable to get property ‘0’ of undefined or null reference”

I assume this is because I have wiped out the selection object by redefining it.

I’m quite new to JavaScript (been doing .Net etc). Where am I going wrong?

Thanks

Stu
Stu says:

August 13, 2015 at 7:14 am

Sorry, having done loads of different things for hours, I just added “var selection;” right at the start of my .js doc and all works well.
Thanks for your post.
Stu
Andy says:

July 4, 2019 at 12:43 am

I am looking for someone to help me to create a new excel add-in, that others can download and use. I am starting from scratch and not an I.T expert, so looking for some help and guidance how to do this