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 the Key column adding the corresponding hyperlink:

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 key = "\"DOPI-1018\"";
 //keyColumn.setFormula(`=HYPERLINK(${url}&${key},${key})`);
 keyColumn.setFormula(`=HYPERLINK(${url}&[@[${keyColumnName}]],[@[${keyColumnName}]])`);
}

I verified it works when I replace the entire column using a constant value. The following line that was commented works:

keyColumn.setFormula(`=HYPERLINK(${url}&${key},${key})`);

Here is the output:

but I am not able to make it works the following line, that should change each element of the Key column:

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

Here is the output:

The row values [@Key] are not replaced.

There is no too much documentation about Office Script, any help is appreciated.

Thanks,

David

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

JKPieterse

Silver Contributor

Sep 21, 2023

davidleal The reason it doesn't work is that you are creating a circular reference with this formula, the formula is pulling the key from its own cell. If you would add a new column for the hyperlinks, all would be well:

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();
  const newColumn = table.addColumn(-1, null, "Link");
  console.log(`Column name: '${keyColumnName}'`);
  //const url = workbook.getNamedItem("jiraSiteIssue").getValue();
  const url = "\"https://MY_SITE.atlassian.net/browse/\"";
  const key = "\"DOPI-1018\"";
  //keyColumn.setFormula(`=HYPERLINK(${url}&${key},${key})`);
  newColumn.getRangeBetweenHeaderAndTotal().setFormula(`=HYPERLINK(${url}&[@[${keyColumnName}]],[@[${keyColumnName}]])`);
}

Of course you need to handle the situation if there already is a column named "link".

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
  
  As variant
  
  function main(workbook: ExcelScript.Workbook) { const tableName = "TB_TEST" const url = "\"https://MY_SITE.atlassian.net/browse/" const keyColumn = workbook .getTable(tableName) .getColumn(1) .getRangeBetweenHeaderAndTotal() const formulae = keyColumn .getValues() .map(x => Array.of( "=HYPERLINK(" + url + x + "\", \"" + x + "\"") ) keyColumn.setFormulas(formulae) }

SergeiBaklan

MVP

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}]])`);
}

Forum Discussion

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

Share

Resources