Forum Discussion
charles983
May 17, 2023Copper Contributor
Office scripts formulas auto updating other formulas
I am currently writing an excel script that populates a table. I am trying to add a hyperlink to one of the columns; however, when I add the hyperlink via a setValue or a setFormula function, the ver...
- May 19, 2023
charles983 Glad to hear that worked! You can specify the text to display like this:
table.getRange().getCell(i + 1, 3).setHyperlink({ address: "https://help.examplesite.com/tickets/" + jsonData[0][0][i]['ticket_id'], textToDisplay: "text here" })
Using multiple lines isn't necessary but it does help make the code more readable.
charles983
May 18, 2023Copper Contributor
1. I have checked that calculation mode is set to manual before I change the formula, after the formula is changed, and at the end of the script.
2. The current way I am using setFormula is by directly hardcoding the link in, Ex: '=HYPERLINK("https://thisisanexample'/ + num + '.com")'
3. I believe the range is correct as I am using a similar format to things that are previously working.
4. The rows are incrementing properly.
The code is as follows:
table.getRange().getCell(i + 1, 3).setFormula('=HYPERLINK("https://help.examplesite.com/tickets/' + jsonData[0][0][i]['ticket_id'] + '", "' + jsonData[0][0][i]['ticket_id'] + '")');
I do not believe the jsonData is causing the issue as when I log it in the console, it gives the correct output
2. The current way I am using setFormula is by directly hardcoding the link in, Ex: '=HYPERLINK("https://thisisanexample'/ + num + '.com")'
3. I believe the range is correct as I am using a similar format to things that are previously working.
4. The rows are incrementing properly.
The code is as follows:
table.getRange().getCell(i + 1, 3).setFormula('=HYPERLINK("https://help.examplesite.com/tickets/' + jsonData[0][0][i]['ticket_id'] + '", "' + jsonData[0][0][i]['ticket_id'] + '")');
I do not believe the jsonData is causing the issue as when I log it in the console, it gives the correct output
NikolinoDE
May 18, 2023Gold Contributor
Thank you for providing more details and sharing the code snippet. Based on the information you provided, it seems for me that the code itself is correct and should set the hyperlinks properly in the specified cells.
Given that you have already verified the other aspects mentioned, such as calculation mode and range, there might be a few additional things to consider:
- Office Scripts limitations: Office Scripts in Excel might have certain limitations or behavior differences compared to regular Excel formulas. It's possible that the auto-population behavior you're experiencing is a result of the scripting environment. You could try a workaround to set the hyperlink using the setValue method instead of setFormula. For example:
table.getRange().getCell(i + 1, 3).setValue("https://help.examplesite.com/tickets/" + jsonData[0][0][i]['ticket_id']).setHyperlink(true);
​
This approach sets the cell value directly as the URL and applies the hyperlink format to it.
- Testing in different Excel environments: Consider testing the code in different Excel environments, such as Excel desktop or Excel Online, to determine if the behavior persists across different platforms. This can help identify if the issue is specific to Office Scripts or if it might be related to the scripting environment.
- charles983May 18, 2023Copper ContributorOffice scripts is telling me that the setHyperlink function does not exist.
- NikolinoDEMay 19, 2023Gold Contributor
table.getRange().getCell(i + 1, 3).setFormula('=HYPERLINK("https://help.examplesite.com/tickets/' + jsonData[0][0][i]['ticket_id'] + '")');
I'm not so sure, but try this code if other options don't work.
- charles983May 18, 2023Copper ContributorAlso, I am running this script through Power Automate.
- MichelleRanMSFTMay 18, 2023
Microsoft
charles983 here's the documentation for the setHyperlink method: ExcelScript.Range interface - Office Scripts | Microsoft Learn
You can try using it like this:
table.getRange().getCell(i + 1, 3).setHyperlink({ address: "https://help.examplesite.com/tickets/" + jsonData[0][0][i]['ticket_id'] })
Let me know if that helps or if you have any questions!