Excel Office Script: Call OpenWeatherMap API

Prerequisites

• Excel with Office Scripts enabled (Excel on the web supports Office Scripts).
• OpenWeatherMap API key — sign up for free at OpenWeatherMap and get an API key, then replace YOUR_OPENWEATHERMAP_API_KEY in the script.

The key is generated immediately, but it takes a couple of hours before they activate it. You will see your key on the 'API Keys' tab (it is a string of random letters). I covered it with a red rectangle on the screenshot to prevent others from using mine:



• Basic familiarity with TypeScript/JavaScript and the Office Scripts environment.

Full script

interface Weather { id?: number; main?: string; description?: string; icon?: string; }
interface Main { temp?: number; humidity?: number; }
interface Wind { speed?: number; deg?: number; }
interface OpenWeatherResponse {
  weather?: Weather[];
  main?: Main;
  wind?: Wind;
  name?: string;
  cod?: number;
  message?: string;
}

async function main(workbook: ExcelScript.Workbook) {
  const sheet: ExcelScript.Worksheet = workbook.getActiveWorksheet();
  const cityRange: ExcelScript.Range = sheet.getRange("B1");
  const city: string = (cityRange.getText() || "").trim();
  if (!city) {
    sheet.getRange("B2").setValue("Enter city in B1");
    return;
  }

  const apiKey: string = "YOUR_OPENWEATHERMAP_API_KEY";
  const url: string = `https://api.openweathermap.org/data/2.5/weather?q=${encodeURIComponent(
    city
  )}&units=metric&appid=${apiKey}`;

  try {
    const response = await fetch(url);
    if (!response.ok) {
      sheet.getRange("B2").setValue(`API error ${response.status}`);
      return;
    }

    const data = (await response.json()) as OpenWeatherResponse;

    const temp: number | string = data.main?.temp ?? "N/A";
    const desc: string = data.weather?.[0]?.description ?? "N/A";
    const humidity: number | string = data.main?.humidity ?? "N/A";
    const wind: number | string = data.wind?.speed ?? "N/A";

    sheet.getRange("A2").setValue("Temperature C");
    sheet.getRange("B2").setValue(temp);
    sheet.getRange("A3").setValue("Description");
    sheet.getRange("B3").setValue(desc);
    sheet.getRange("A4").setValue("Humidity %");
    sheet.getRange("B4").setValue(humidity);
    sheet.getRange("A5").setValue("Wind m/s");
    sheet.getRange("B5").setValue(wind);
  } catch (err) {
    sheet.getRange("B2").setValue("Fetch failed: " + String(err));
  }
}

Step-by-step explanation

1. TypeScript interfaces

The top block defines TypeScript interfaces that model the JSON returned by OpenWeatherMap. These are optional-field shapes that help with type safety and autocompletion in the editor.

• Weather — one element of the weather array (description, icon, etc.).
• Main — contains numeric values like temp and humidity.
• Wind — wind speed and direction.
• OpenWeatherResponse — top-level response combining the above pieces.

2. Entry point: main function

Office Scripts run the exported main function. It receives an ExcelScript.Workbook object that represents the current workbook.

3. Read the city from the sheet

const sheet = workbook.getActiveWorksheet();
const cityRange = sheet.getRange("B1");
const city = (cityRange.getText() || "").trim();

Behavior: the script reads the text in cell B1, trims whitespace, and stores it in city. If the cell is empty, the script writes a prompt into B2 and exits early.

4. Build the API URL

const apiKey = "YOUR_OPENWEATHERMAP_API_KEY";
const url = `https://api.openweathermap.org/data/2.5/weather?q=${encodeURIComponent(city)}&units=metric&appid=${apiKey}`;

Notes:

• encodeURIComponent(city) ensures city names with spaces or special characters are encoded correctly.
• &units=metric requests Celsius. Remove or change to imperial for Fahrenheit.
• Replace YOUR_OPENWEATHERMAP_API_KEY with your real API key.

5. Fetch the API and handle errors

const response = await fetch(url);
if (!response.ok) {
  sheet.getRange("B2").setValue(`API error ${response.status}`);
  return;
}
const data = (await response.json()) as OpenWeatherResponse;

Behavior: the script uses the global fetch API to call OpenWeatherMap. If the HTTP response status is not OK (200), it writes the status code to B2 and stops. Otherwise it parses JSON into data.

6. Extract values safely

const temp = data.main?.temp ?? "N/A";
const desc = data.weather?.[0]?.description ?? "N/A";
const humidity = data.main?.humidity ?? "N/A";
const wind = data.wind?.speed ?? "N/A";

These lines use optional chaining (?.) and the nullish coalescing operator (??) to avoid runtime errors when fields are missing. If a value is absent, the script uses "N/A".

7. Write results back to the worksheet

sheet.getRange("A2").setValue("Temperature C");
sheet.getRange("B2").setValue(temp);
sheet.getRange("A3").setValue("Description");
sheet.getRange("B3").setValue(desc);
sheet.getRange("A4").setValue("Humidity %");
sheet.getRange("B4").setValue(humidity);
sheet.getRange("A5").setValue("Wind m/s");
sheet.getRange("B5").setValue(wind);

The script writes labels in column A and the corresponding values in column B. You can change the target cells to suit your layout.

8. Catch block

catch (err) {
  sheet.getRange("B2").setValue("Fetch failed: " + String(err));
}

If the network request or JSON parsing throws an exception, the script writes a short error message to B2. This helps with basic troubleshooting.

How to run this script in Excel (quick steps)

1. Open Excel for the web and the workbook where you want the script.
2. Open the Automate tab and choose New Script.
3. Paste the script into the editor, replace the API key placeholder with your OpenWeatherMap key, and save.
4. In the worksheet, type a city name into cell B1 (for example, Paris).

The screen will look like this:

5. Run the script. Results will appear in cells B2–B5 (with labels in column A).

Troubleshooting & tips

• API key errors: If you see API error 401 or similar, verify your API key and that it is active.
• Rate limits: OpenWeatherMap enforces rate limits on free plans. If you get frequent errors, check your plan limits.
• City not found: If the API returns a 404 or the description is missing, try a different city spelling or include the country code (e.g., Paris,FR).
• Security: Do not hard-code sensitive API keys into shared workbooks. Consider storing the key in a secure location or using a server-side proxy to keep the key private.
• Units: Change &units=metric to imperial for Fahrenheit or remove it to get Kelvin.
• Localization: To get weather descriptions in another language, add &lang=es (for Spanish) to the URL.

Customizations you might want

• Write the city input and results to different cells or a named range.
• Show an icon by mapping data.weather[0].icon to an image URL and inserting it into the sheet (requires additional steps).
• Wrap the fetch call in a helper function to reuse it for multiple cities or a list of cities.
• Format numeric results (e.g., round temperature) before writing to the sheet: Math.round(temp).

Example: minimal change to show Fahrenheit

// change units to imperial for Fahrenheit
const url = `https://api.openweathermap.org/data/2.5/weather?q=${encodeURIComponent(city)}&units=imperial&appid=${apiKey}`;