Forum Discussion

davidleal's avatar
davidleal
Iron Contributor
Sep 21, 2023
Solved

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's avatar
    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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 21, 2023

davidleal 

That could work

function main(workbook: ExcelScript.Workbook) {
    // Get the current worksheet.
    const TARGET_TABLE_NAME = "TB_TEST";
    const table = workbook.getTable(TARGET_TABLE_NAME);
    const keyColumn = table.getColumn(1).getRangeBetweenHeaderAndTotal();
    const keyColumnName = table.getColumn(1).getName();
    console.log(`Column name: '${keyColumnName}'`);
    //const url = workbook.getNamedItem("jiraSiteIssue").getValue();
    //const url = "\"https://MY_SITE.atlassian.net/browse/\"";
    const url = "\"https://MY_SITE.atlassian.net/browse/"
    //const key = "\"DOPI-1018\"";

    const rows = table.getRowCount()
    for (i=0; i<rows; i++) {
        
        const v = keyColumn
            .getCell(i,0)
            .getValue()
        keyColumn
            .getCell(i, 0)
            .setFormula(
                "=HYPERLINK(" + url + v + "\", \"" + v + "\""
            )
    }

    //keyColumn.setFormula(`=HYPERLINK(${url}&${key},${key})`);
    //keyColumn.setFormula(`=HYPERLINK(${url}&[@[${keyColumnName}]],[@[${keyColumnName}]])`);
}

Resources