Forum Discussion
Formulas do not calculate after using Office Script
setValue into C5 puts the value, which is string, into that cell. You shall to set the formula, like
workbook .getActiveWorksheet() .getRange("C5") .setFormula("=CONCATENATE(B5,A5")
Could I clarify. When you do that manually, before applying General format do you see formulae (e.g. in column C) or result of calculation formatted as text?
And did you try to change +VERKETTEN(...) on =VERKETTEN(...) before running the script?
Before changing the format, I only see the formula. The origin table is completely formatted as text.
The formula is part of the script. I need to transform row B by eliminating 2 characters so I use Left () and Right() to extract the appropriate number of characters and join them. The script enters more formulas to include information I need. I have the same problem with all formulas.
This also happens when I use = instead of + (in options I have chosen that + triggers the same response as = when I begin a formula with it).
I tried on such sample
Script
function main(workbook: ExcelScript.Workbook) { const range = workbook .getActiveWorksheet() .getRange("B1:B4") const t = range .getValues() .map((x) => Array.of(x.toString()) ) range.setNumberFormat("General") range.setFormulas(t) }get text of formulae, combine them as array, apply Genera format to the same range and apply formulae to them. When it works
But above is only the idea. Script works on columns only, otherwise we shall to build array of formulae another way. And if we run it second it converts returned first time formulae into values. Thus we need to check if the text represents formulae (starts from "=" or "+") or not. Perhaps to do that on cell by cell basis or some other way, not sure right now.
To illustrate that format only is not enough.
But if you see formulae in initial table, just changing of the format from Text to Generals does nothing, you shall still see the formula. After changing the format you need to re-enter formula in the cell to see the result. That's if we do that manually.
Same with script.
Maybe I was not specific enough.
Of course you are right that changing the formatting after entering formula does nothing. Therefore I change the formatting first and only then enter the formula.
This works as intended. If have included this sequence in the script. My limited insight led me to the conclusion that if I do 1. formatting and 2. enter the formula will result in the calculation of the formula.
Performing this sequence manually always leads to the desired result. Using a script and I only see the formula and not the result.
setValue into C5 puts the value, which is string, into that cell. You shall to set the formula, like
workbook .getActiveWorksheet() .getRange("C5") .setFormula("=CONCATENATE(B5,A5")