Forum Discussion
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...
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.
If you're experiencing issues with formulas auto-updating when using Office Scripts in Excel, there are a few possible reasons and solutions to consider:
- Calculation mode: You mentioned that you have set the CalculationMode to manual in your code, which is the correct approach. However, it's worth double-checking that the CalculationMode is being set before you perform any setValue or setFormula operations. Ensure that the CalculationMode is explicitly set to manual at the beginning of your script.
- Relative references: When using setFormula, it's important to ensure that your formulas contain absolute references instead of relative references. Relative references adjust automatically when copied or filled down, which might be causing the auto-population issue you're encountering. Make sure to use the dollar sign ($) to anchor your cell references, such as $A$1 instead of A1, to keep them fixed.
- Range specification: Confirm that you are specifying the correct range when setting the formula or value. If you inadvertently specify a larger range than intended, the formula or value might be applied to more cells than expected, causing the auto-population behavior. Double-check the range references in your code and ensure they align with your desired target range.
- Looping through rows: If you're using a loop to populate the table rows, make sure that you're properly incrementing the row number for each setValue or setFormula operation. It's common to use a counter variable within the loop to keep track of the current row and update the cell references accordingly.
By ensuring correct CalculationMode settings, using absolute references, specifying the correct range, and managing row iteration appropriately, you can mitigate issues with formulas auto-updating in Office Scripts. If the problem persists, reviewing the specific code you're using might provide further insights into the issue and potential solutions.
- 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 outputThank 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.
- Office scripts is telling me that the setHyperlink function does not exist.
