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

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

Resources