Forum Discussion
davidleal
Sep 21, 2023Iron Contributor
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
Sep 21, 2023Iron Contributor
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
Sep 21, 2023MVP
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
- davidlealSep 21, 2023Iron Contributor
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
- SergeiBaklanSep 21, 2023MVP
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(), }) } }
- davidlealSep 21, 2023Iron Contributor
@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.