How to programatically add a hyperlink to a cell in a worksheet using office-js?

Question

I'm working on an Excel add-in using the JavaScript APIs to build add-ins in Excel 2016.

The problem I have is not to place the url/link in the cell - I rather want to make this url clickable (as you may know it from entering a url into a cell and hit ).

In VBA the solution was this (e.g.):

With Worksheets(1)
    .Hyperlinks.Add .Range("E5"), "http://example.microsoft.com"
End With

Unfortunately, I can't find a hyperlink function in the JavaScript API. Any idea?

Thanks a lot for any help and best regards Eric

Answer 1

There are two types of hyperlinks in Excel, one that you do in VBA through Hyperlinks.add, and another that you do via a formula. The latter is easily supported by the Excel JavaScript object model.

Excel.run(function (ctx) {
    var firstCellInSelection = ctx.workbook.getSelectedRange().getCell(0, 0);
    firstCellInSelection.formulas = [['=HYPERLINK("http://www.bing.com")']];
    return ctx.sync();
}).catch(function (error) {
    console.log(error);
});

~ Michael Zlatkovsky, Developer on Office Extensibility Team, MSFT

Answer 2

Adding hyperlinks is available in the beta for OfficeJS as of now. You can load the beta version with...

<script type="text/javascript" src="https://appsforoffice.microsoft.com/lib/beta/hosted/Office.js"></script>

...and get/set hyperlinks with

Set a hyperlink in cell A1:

Excel.run((context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange('A1');
    range.hyperlink = {
        address: `mailto:[email protected]`,
        documentReference: null,
        screenTip: null,
        textToDisplay: 'Send me a Mail!',
    };
    return context.sync();
 })

Get hyperlink from cell A1:

Excel.run((context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    const range = sheet.getRange('A1').load('values, hyperlink');
    return context.sync().then(() => console.log(range.hyperlink));
})

API Reference

• OfficeJS Docs for RangeHyperlink