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

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

MVP

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

Thanks SergeiBaklan yes, it works for url as literal, but not when the value is taken from a named range. It indicated some error, but changing:

const v = cell.getValue()

const v = cell.getValue().toString()

it works. Here is the updated formula:

function main(workbook: ExcelScript.Workbook) {

  const tableName = "TB_TEST"
  //const url = "https://MYSITE.atlassian.net/browse/"
  const url = workbook.getNamedItem("jiraSiteIssue").getValue().toString()
  console.log(`url: '${url}'`)
  const keyColumn = workbook
    .getTable(tableName)
    .getColumn(1)
    .getRangeBetweenHeaderAndTotal()
  const rows = keyColumn.getRowCount()
  for (let i = 0; i < rows; i++) {
    const cell = keyColumn.getCell(i, 0);
    const v = cell.getValue().toString();
    const hyperlink: ExcelScript.RangeHyperlink = {
      address: url + v,
      screenTip: url + v,
      textToDisplay: v
    };
    cell.setHyperlink(hyperlink);
  }
}

it indicates a performance warning in line 14, I was trying to use a variant as in your other example, but I couldn't make it works. Under this approach the warning can be removing getting the values first.

Update: I found the way to get the value of the named range, it should be like this:

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

Here it the final script:

function main(workbook: ExcelScript.Workbook) {
  const tableName = "TB_TEST"
  //const url = "https://MYSITE.atlassian.net/browse/"
  const url = workbook.getNamedItem("jiraSiteIssue").getRange().getValue()
  console.log(`url: '${url}'`)
  const keyColumn = workbook
    .getTable(tableName)
    .getColumn(1)
    .getRangeBetweenHeaderAndTotal()
  const rows = keyColumn.getRowCount()
  const values = keyColumn.getValues()
  for (let i = 0; i < rows; i++) {
    const cell = keyColumn.getCell(i, 0)
    const v = values[i][0].toString();
    const hyperlink: ExcelScript.RangeHyperlink = {
      address: url + v,
      screenTip: url + v,
      textToDisplay: v
    }
    cell.setHyperlink(hyperlink)
  }
}

Yes it works when there are empty cells.

Thanks again,

David

Forum Discussion

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

Share

Resources