3

I am trying to call an external API from an Excel on web. However, I am stuck on trying to get the result from the fetch call. I am even using the Office doc example to make sure

From an Excel, click on Automate to create a new script

async function main(workbook: ExcelScript.Workbook): Promise<void> {
  let fetchResult = await fetch('https://jsonplaceholder.typicode.com/todos/1');
  let json = await fetchResult.json();
}

I keep on getting the following message (at the fetchResult.json() call)

"Office Scripts cannot infer the data type of this variable or inferring it might result in unexpected errors. Please annotate the type of the variable to avoid this error. You can also use the Quick fix option provided in the editor to auto fill the type based on the usage. Quick Fix can be accessed by right clicking on the variable name and selecting Quick Fix link."

When running the Chrome inspector, the API request seems to be on hold "CAUTION: request is not finished yet"

PS: I am not the Office administrator and is not reachable right now, but hoping this is not a problem with my user or the Office account configuration

Any idea what the problem might be? Thanks!

Miguel Guardo
  • 853
  • 1
  • 9
  • 17

2 Answers2

3

"any" types not being allowed in OfficeScript is by design. We think any types in general can lead to developer errors. I understand it can be hard to declare types – but these days most popular APIs provide you the interface (or d.ts) that you can use. Secondly, there are tools such as https://quicktype.io/typescript where you can type in your sample JSON and it’ll give you the full interface which you can then declare in your code using interface keyword. See this code for example: https://github.com/sumurthy/officescripts-projects/blob/main/API%20Calls/APICall.ts

You don’t need to declare all properties – only the ones you’ll use. It’s more up-front work – but in the end the quality is better.

Jay Rathi - Microsoft
  • 241
  • 1
  • 6
2

Adding an interface definition for the expected JSON type fixed the problem for me.

interface Todo {
  userId: number;
  id: number;
  title: string;
  completed: boolean
}

async function main(workbook: ExcelScript.Workbook): Promise<void> {
  let fetchResult = await fetch('https://jsonplaceholder.typicode.com/todos/1');
  let json: Todo = await fetchResult.json();
  console.log(json);
}

You may need to define a different interface if the Web API you're calling returns different data structure.

Yutao Huang
  • 1,503
  • 1
  • 13
  • 25
  • 2
    Actually it turned out annotating that `json` variable with any random type (except for `any`) could fix the problem. E.g. even this could work: `let json: undefined = await fetchResult.json()`. But I guess using a meaningful custom type (like the `Todo` interface in the example) is probably better and helps in code IntelliSense. – Yutao Huang Mar 30 '21 at 15:29
  • 1
    In fact, just a declared empty json works '''interface: Todo {}''' works. – Miguel Guardo Mar 30 '21 at 19:55