Muokkaa

Jaa

External API call support in Office Scripts

  • Artikkeli

Scripts support calls to external services. Use these services to supply data and other information to your workbook.

Caution

External calls may result in sensitive data being exposed to undesirable endpoints. Your admin can establish Information Rights Management (IRM) or firewall protection against such calls.

Important

Calls to external APIs can only be made through the Excel application, not through Power Automate under normal circumstances. External calls are also not supported for scripts stored on a SharePoint site.

Configure your script for external calls

External calls are asynchronous and require that your script is marked as async. Add the async prefix to your main function and have it return a Promise, as shown here:

async function main(workbook: ExcelScript.Workbook) : Promise <void>

Note

Scripts that return other information can return a Promise of that type. For example, if your script needs to return an Employee object, the return signature would be : Promise <Employee>

You'll need to learn the external service's interfaces to make calls to that service. If you are using fetch or REST APIs, you need to determine the JSON structure of the returned data. For both input to and output from your script, consider making an interface to match the needed JSON structures. This gives the script more type safety. You can see an example of this in Using fetch from Office Scripts.

Limitations with external calls from Office Scripts

  • There is no way to sign in or use OAuth2 type of authentication flows. All keys and credentials have to be hardcoded (or read from another source).
  • There is no infrastructure to store API credentials and keys. This will have to be managed by the user.
  • Document cookies, localStorage, and sessionStorage objects are not supported.
  • External calls may result in sensitive data being exposed to undesirable endpoints, or external data to be brought into internal workbooks. Your admin can establish firewall protection against such calls. Be sure to check with local policies prior to relying on external calls.
  • Be sure to check the amount of data throughput prior to taking a dependency. For instance, pulling down the entire external dataset may not be the best option and instead pagination should be used to get data in chunks.

Retrieve information with fetch

The fetch API retrieves information from external services. It is an async API, so you need to adjust the main signature of your script. Make the main function async. You should also be sure to await the fetch call and json retrieval. This ensures those operations complete before the script ends.

Any JSON data retrieved by fetch must match an interface defined in the script. The returned value must be assigned to a specific type because Office Scripts do not support the any type. You should refer to the documentation for your service to see what the names and types of the returned properties are. Then, add the matching interface or interfaces to your script.

The following script uses fetch to retrieve JSON data from the test server in the given URL. Note the JSONData interface to store the data as a matching type.

async function main(workbook: ExcelScript.Workbook) {
  // Retrieve sample JSON data from a test server.
  let fetchResult = await fetch('https://jsonplaceholder.typicode.com/todos/1');

  // Convert the returned data to the expected JSON structure.
  let json : JSONData = await fetchResult.json();

  // Display the content in a readable format.
  console.log(JSON.stringify(json));
}

/**
 * An interface that matches the returned JSON structure.
 * The property names match exactly.
 */
interface JSONData {
  userId: number;
  id: number;
  title: string;
  completed: boolean;
}

Other fetch samples

Restrict external calls with Information Rights Management (IRM)

You can apply IRM settings to a workbook to prevent external calls being made by scripts. Disable the Copy/EXTRACT policy to prevent this behavior.

External calls from Power Automate

External API calls fail when a script is run through Power Automate. A fetch call will give the error message "Runtime error: Line X: fetch is not defined". Be sure to check your scripts for such references before building them into a flow.

You'll have to use HTTP with Azure AD or other equivalent actions to pull data from or push it to an external service.

Warning

External calls made through the Power Automate Excel Online connector fail in order to help uphold existing data loss prevention policies. However, scripts that are run through Power Automate are done so outside of your organization, and outside of your organization's firewalls. For additional protection from malicious users in this external environment, your admin can control the use of Office Scripts. Your admin can either disable the Excel Online connector in Power Automate or turn off Office Scripts for Excel through the Office Scripts administrator controls.

See also