Range.getFormula() invalid return type

[zWarMob]

Article URL ExcelScript.Range interface - getFormula()

Describe the problem Documentation (and the interface itself) suggests getFormula() is supposed to return a string. Not the case.. getFormula() behaves like getValue() and can return non-string values

let sheet = workbook.getWorksheet("Env.data");
[ . . . ]
let aQ = sheet .getCell(1, 1);
let qFormula: string = aQ.getFormula();
try{
  qFormula = qFormula.startsWith('=') ? qFormula.slice(1) : qFormula;
}catch(e){
  console.log(typeof(qFormula));
  console.log(qFormula);
  throw e;
}

logs and error:

number 0 qFormula.startsWith is not a function

Screenshots

[zWarMob]

temporary fix convert your "string" to a string

let qFormula = aQ.getFormula().toString();

[zWarMob]

On a similar note, do I understand this correctly and is this an issue: if the returned value starts with an equal sign, getValues() is supposed to return a formula (string of the formula) however I get only the calculated value and not the formula with this function

[AlexJerabek]

Hi @zWarMob,

Thanks for reporting this. @alison-mk, could you please investigate?

[alison-mk]

Hi @zWarMob, I'm able to replicate this issue and I'm doing some research into the best solution. It looks like I'll need to update our documentation to reflect that getFormula can return both strings and numbers (but not boolean values).

Regarding your second question:

if the returned value starts with an equal sign, getValues() is supposed to return a formula (string of the formula) however I get only the calculated value and not the formula with this function

The code sample for getFormula in the documentation illustrates the difference between getFormula and getValue. It looks to me like getValue is working as intended -- getValue processes the formula in a cell and returns the formula result (not the formula). I can update the documentation for getValue to make this more clear.

I'll report back here when I've resolved both of these issues.

Thanks! Alison