We can use Excel Scripts to connect APIs and fetch data. In this article, I'll walk you through the steps of connecting to an API through Excel Scripts and retrieving data.
Step 1: Obtain an API key from a service provider
Before you can connect to an API, you will need to obtain an API key from the provider. This key will be used to authenticate your requests and ensure that you have the necessary permissions to access the API. In this article we are going to use OpenWeatherMap API. Please go to https://stormglass.io/ and register for OpenWeatherMap API. A free account is enough to complete this article. Once you complete the registration, get the API key, and keep it to be used in our script later.
Step 2: Open your Excel Online Workbook
Open your Excel Online workbook and navigate to the sheet where you want to fetch the data. Click on the "Automate" tab on the ribbon and then click "Script Editor" to open the Script Editor. (If you are on office 365 subscription you can see the automate tab on offline of the excel as well. However, you should have a commercial license for excel to use this option. personal or home licenses do not allow you to use excel scripts.)
Step 3: Create a blank excel Script to write code
Click the "Create a script" button in the Script Editor. Give your script a name. Now we are all set to write the code. Let's move on to the next step.
Step 4: Write the Script to fetch data
In the script editor, write the code to connect to the API and fetch the data. In this example I used OpenWeatherMap API to fetch weather data. Enter below code to the script editor. Make sure to use your API Key where it mentioned "<Your API Key>"
function main(workbook: ExcelScript.Workbook) {
let apiKey = "<Your API Key>";
let cityName = "London";
let url = `https://api.openweathermap.org/data/2.5/weather?q=${cityName}&appid=${apiKey}&units=metric`;
let response = UrlFetchApp.fetch(url);
let content = response.getContentText();
let data = JSON.parse(content);
let sheet = workbook.getActiveWorksheet();
sheet.getRange("A1").setValue(data.name);
sheet.getRange("B1").setValue(data.main.temp);
}
This script will fetch the current temperature and city name for London from the OpenWeatherMap API and populate the values in cells A1 and B1 of the active worksheet.
Step 5: Run the API Script (test)
Click the "Run" button to execute the API script. Excel Scripts will prompt you to authorize the script to access external APIs. Click "Allow" to authorize the script.
Now you can see the results on the active sheet.
