Office Script: Add Hyperlink to a column within an Excel Table

I am trying to use Office Script to add hyperlink to a given Excel Table column. I have the following input data: in an Excel Table named TB_TEST. I am using the following script to change th...

Excel

Office Scripts

SergeiBaklan

Sep 21, 2023

davidleal

David, I forgot we may set hyperlink directly in script without applying the formula. This is on cell by cell basis and ignores blanks

function main(workbook: ExcelScript.Workbook) {

    const tableName = "TB_TEST"
    //const url = "\"https://MY_SITE.atlassian.net/browse/"
    const url = "https://MY_SITE.atlassian.net/browse/"

    const keyColumn = workbook
        .getTable(tableName)
        .getColumn(1)
        .getRangeBetweenHeaderAndTotal()

    const rows = keyColumn.getRowCount()
    for (i = 0; i < rows; i++) {

      const cell = keyColumn.getCell(i, 0);
      const v = cell.getValue();
      const hyperlink: ExcelScript.RangeHyperlink = {
        address: url + v,
        screenTip: v,
        textToDisplay: v
        };
      cell.setHyperlink(hyperlink);
    }
}

didn't play with that, but perhaps if you take URL as

const url = workbook.getNamedItem("jiraSiteIssue").getValue()

it will work with above script

davidleal

Iron Contributor

Sep 21, 2023

JKPieterse thanks it works, but it adds an extra column, which is something I can do with Excel, no need to use Office Script for that. I would say I can delete the original column, no to append the Link column but insert after the first column, then delete Key column and rename the inserted column. I guess it is doable, but I am new to Office Script (I had a similar idea using Power Query, but it doesn't support hyperlink and at the end requires manual intervention).

I my real example I have additional information in the Sheet and I don't know how this will affect formula integrity, I have several formulas in this Sheet. Is there a way to do it without adding a new column?

SergeiBaklan

MVP

Sep 21, 2023

davidleal

Make it cell by cell (example is in previous post). If performance is critical you may push formulae into dynamic array and when set formula from it.

Forum Discussion

Office Script: Add Hyperlink to a column within an Excel Table

Share

Resources