Forum Discussion

charles983's avatar
charles983
Copper Contributor
May 17, 2023
Solved

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...
excel
Office Scripts
  • MichelleRanMSFT's avatar
    MichelleRanMSFT
    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.

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
May 18, 2023

charles983 

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:

  1. 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.
charles983's avatar
charles983
Copper Contributor
May 18, 2023
Office scripts is telling me that the setHyperlink function does not exist.
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    May 19, 2023

    charles983 

     

    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.

     

  • charles983's avatar
    charles983
    Copper Contributor
    May 18, 2023
    Also, I am running this script through Power Automate.
      • charles983's avatar
        charles983
        Copper Contributor
        May 19, 2023
        That worked! Thank you so much.

Resources