Automating Data Retrieval from APIs in Excel Online with Excel Scripts: A Step-by-Step Guide

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. 

Copying Cells and Formatting Fonts with Excel scripts

Microsoft Excel Scripts uses typescript to allow you to automate repetitive tasks in Excel which saves your time and effort. In this article, we'll give you a few code snipts to do the basic tasks using excel scripts.

1. Copy data from one cell to another

This is one of the most used functionalities in excel. Use the code below to automate that using excel scripts.

function copyCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var source = sheet.getRange("A1");
  var destination = sheet.getRange("B1");
  source.copyTo(destination);
}

2. Change cell color

Excel Scripts can also automate the process of changing cell color, making it easy to change the color of multiple cells simultaneously.  See sample code which does that.

function changeColor() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange("A1");
  cell.setBackground("#f0f8ff");
}

3. Change font

We change fonts in our workbooks for many reasons. If you need to automate that using excel scripts here is the sample code. 

function changeFont() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange("A1");
  cell.setFontFamily("Arial");
}

4. Change font color

Again, changing font color is a must do when we develop a dashboard, reports, etc. in excel. See how we could do this.

function changeFontColor() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getRange("A1");
  cell.setFontColor("#000000");
}
      

Excel scripts are an excellent way to take your Excel game to the next level. By writing code that interacts with your Excel data, you can automate repetitive tasks and customize the functionality of your spreadsheets. With Excel scripts, you can even schedule scripts to run automatically on a daily, weekly, or monthly basis. This was introduced recently and was available only on excel online. However, Microsoft recently released the insider version of the excel that allows you to use excel scripts on offline excel as well. However, in both cases you should have an enterprise license to use excel scripts. Personal or home license is not enough for that. 

Like VBA custom functions can also be created using scripts to perform specific calculations or tasks. Excel scripts are highly customizable, allowing you to tailor your scripts to your specific needs. You can use external libraries to add functionality to your scripts, and you can use APIs to integrate your scripts with other applications.

In conclusion, Excel scripts are a powerful tool for automating repetitive tasks and customizing the functionality of your spreadsheets. They offer a wide range of benefits, including improved efficiency, accuracy, and customization. Whether you're a data analyst, accountant, or just an Excel power user, Excel scripts can help you streamline your work. So why not start exploring this powerful feature today and take your Excel skills to the next level? This is the first post about excel scripts and I'll give you more posts about excel scripts soon.