Forum Discussion

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...

Sep 21, 2023

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

Thanks SergeiBaklan great it works and it doesn't show the warning about performance. I just have a couple of questions/comments.

What if the Excel table has empty rows?, is it possible to prevent this?, basically I could have empty lines at. the end, so the hyperlink function should apply only if the cell is not empty. How we can prevent this?

In my real example I take the url from a named range (defined in the Name Manager), as in my original post. I was trying to adapt it from your last code, but it doesn't work, for example:

function main(workbook: ExcelScript.Workbook) {

  const tableName = "TB_TEST"
  //const url = "\"https://MY_SITE.atlassian.net/browse/"
  const url = "\"" + workbook.getNamedItem("jiraSiteIssue").getValue()
  console.log(`url: '${url}'`)

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

  const formulae = keyColumn
    .getValues()
    .map(x => Array.of("=HYPERLINK(" + url + x + "\", \"" + x + "\""))
  keyColumn.setFormulas(formulae)

}

The log output is the following:

url: '"Settings!$AR$1:$AR$1'

so the output is the location of the value, but not the actual value, and therefore the output is not the expected:

Thanks for any help,

David

SergeiBaklan

Diamond Contributor

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