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

[davidleal]

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

[#2]

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

[#3]

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

[#4]

@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?

[#5]

@davidleal 

Make it cell by cell (example is in previous post). If performance is critical you may push formulae into dynamic array and when set formula from it. 

[#6]

@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)
    
}

[#7]

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

[#8 · Accepted solution]

@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

[#9]

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()

by

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

[#10]

@davidleal 

David, I guess we need to add getRange with named cell, otherwise it returns this cell address.

To avoid performance warning we may load all values to an array and use it on iterations.

All together

function main(workbook: ExcelScript.Workbook) {

  const tableName = "TB_TEST"
  const columnKey = "Key"
  const url = workbook
    .getNamedItem("jiraSiteIssue")
    .getRange()
    .getValue()

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

  const values = keyColumn.getValues()
  const nRows = keyColumn.getRowCount()

  let i: number = 0
  for (i = 0; i < nRows; i++) {
    keyColumn.getCell(i,0).setHyperlink( {
      address: url + values[i][0].toString(),
      textToDisplay: values[i][0].toString(),
      screenTip: values[i][0].toString(),
    })
  }
}

[#11]

@Sergei Baklan Exactly we replied at the same time, I got to a similar conclusion, to get the value first and to invoke getRange.getValue() to get the value of the named Range. Anyway performance is an issue, for 500 rows about 25 seconds under Excel Desktop, and running from my Mac using Excel Web, more than 2 minutes and sometime I got a time out.

On contrary, the variant approach using HYPERLINK function is incredibly fast. The output is less friendly, because the cell has instead of the key value the call to HYPERLINK. I don't think the variant approach can be applied for setHyperlink, because this function needs a range and the variant needs an array.

[#12]

@davidleal , I expected that's not fast, but didn't think that's so slow. Thank you for the feedback.

I tried to set all formulae at once to avoid cell by cell iteration, like

function main(workbook: ExcelScript.Workbook) {

  const tableName = "TB_TEST"
  const columnKey = "Key"
  const url = workbook
    .getNamedItem("jiraSiteIssue")
    .getRange()
    .getValue()

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

  const formulae: string[][] = keyColumn
    .getValues()
    .map(x => 
      (x.toString() === "") ?
      [x.toString()] :
      ["=HYPERLINK(\"" + url + x.toString() + "\", \"" + x.toString() + "\")"]
      )
  keyColumn.setFormulas(formulae)
}

That works for the range but not for the table. More exactly, for the table it works on Excel for web and not on Windows desktop. On the latest it takes first formula in the array, apply it as table column formula and ignore the rest. Thus we have exactly the same formula in all cells of the column.

If works, not sure how good the performance is, didn't test.

[#13]

Thanks @SergeiBaklan, maybe I was not clear enough. The variant approach is the fast solution, no need to improve it. The solution using setHyperlink is the one that has performance issue. Looking at your last approach is an improvement of the variant solution and as you indicated it has some limitations in terms of Excel environment.