Javascript API for Office: How Can I Manually Trigger MS Office's AutoFormat Functionality?

Question

Background:

I'm building a web app inside MS Excel using the Javascript API for Office. One functionality that I'm adding to the app is the ability to edit data from the app. That data gets send to the Excel document via the setDataAsync function in the API. It works for plain text.

I'm running into an odd problem with email addresses. When I manually enter an email address into a cell, Excel auto formats it into a mailto: hyperlink. This doesn't happen when I set the data with Javascript. I need to figure out a way to force Excel to AutoFormat it.

One alternative idea I've had is to preformat the data myself into a link, using:

=HYPERLINK("mailto:[email protected]","[email protected]")

However, when someone looks into the cell manually, they will see that long formula rather than just the address. And reading it back in to the web app will be a nightmare.

Current Code:

function writeData(bindingID,newData,row,col) {
    var newTable = new Office.TableData();
    newTable.rows = [[newData]];
    Office.select("bindings#"+bindingID).setDataAsync(newTable, {coercionType: "table", startRow: row, startColumn: col}, function (asyncResult) {
        if (asyncResult.status == "failed") {
            display('Error: ' + asyncResult.error.message);
        }
    });
}

Question:

Is it possible to cause Excel to autoformat?

Answer 1

Answer: It can't be done.

I asked this question over on the MS forums as well, and got an answer which I'd like to post here in case anyone else is having the same problem.

After testing and researching, I'm afraid there is no method or property in JavaScript for Office provided to automatically set mailto link for the text of cells after checking.

We need to reformat the cells manually after inserting with SetDataAsync method.

Luna Zhang - MSFT (Source)