External API call support in Office Scripts - Office Scripts (2024)

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

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>

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

• The Use external fetch calls in Office Scripts sample shows how to get basic information about a user's GitHub repositories.
• Samples in the Use JSON to pass data to and from Office Scripts article show how to pass data to and from fetch commands as JSON.
• The Office Scripts sample scenario: Graph water-level data from NOAA demonstrates the fetch command being used to retrieve records from the National Oceanic and Atmospheric Administration's Tides and Currents database.
• The second sample in Add images to a workbook contains a fetch call to get an image from a website.

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.

See also

• Use JSON to pass data to and from Office Scripts
• Using built-in JavaScript objects in Office Scripts
• Use external fetch calls in Office Scripts
• Office Scripts sample scenario: Graph water-level data from NOAA