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

Kioto

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

Merta

You made my day, Michael! Sometimes, the easy solutions are the simplest... ;) – Kioto

Can we add an HyperLink on the Word side ? – Jankey

Not sure about the latter. I recommend you post a separate question for Word specifically. – Merta

Hey @MichaelZlatkovsky-Microsoft, what if we do not want to use a hyperlink formula, but instead want to replicate Excel's "Insert -> Link" functionality? I'm able to paste the hyperlink as raw text using .values, but how can I format this to act as a hyperlink? – Epigenous

Sorry for the delay, only saw this now. Right now, there's unfortunately no way to add a hyperlink without the use of a formula, but the feature is slated to be coming soon with the ExcelApi 1.7 release. See the Open Spec: github.com/OfficeDev/office-js-docs/tree/ExcelJs_OpenSpec – Merta

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

Liss