Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Range.getFormula() invalid return type #365

Open
zWarMob opened this issue Oct 17, 2024 · 4 comments
Open

Range.getFormula() invalid return type #365

zWarMob opened this issue Oct 17, 2024 · 4 comments
Assignees
Labels
Status: under investigation Issue is being investigated Type: product question Question about Office Scripts

Comments

@zWarMob
Copy link

zWarMob commented Oct 17, 2024

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
image

@microsoft-github-policy-service microsoft-github-policy-service bot added the Needs: triage 🔍 New issue, needs PM on rotation to triage ASAP label Oct 17, 2024
@zWarMob
Copy link
Author

zWarMob commented Oct 17, 2024

temporary fix
convert your "string" to a string

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

@zWarMob
Copy link
Author

zWarMob commented Oct 17, 2024

On a similar note, do I understand this correctly and is this an issue:
image
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
Copy link
Collaborator

Hi @zWarMob,

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

@AlexJerabek AlexJerabek added Needs: attention 👋 Waiting on Microsoft to provide feedback Type: product question Question about Office Scripts and removed Needs: triage 🔍 New issue, needs PM on rotation to triage ASAP labels Oct 17, 2024
@alison-mk
Copy link
Contributor

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

@alison-mk alison-mk added Status: under investigation Issue is being investigated and removed Needs: attention 👋 Waiting on Microsoft to provide feedback labels Nov 1, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Status: under investigation Issue is being investigated Type: product question Question about Office Scripts
Projects
None yet
Development

No branches or pull requests

3 participants