Fetching Weather Data from an External API and Displaying It in a Google Sheet Using Google Apps Script

by

Google Apps Script is a powerful tool that allows you to automate tasks and integrate with various services directly from your Google Workspace applications. In this blog post, we will walk through an exercise where we write a script to fetch weather data from an external API and display it in a Google Sheet. This will demonstrate how to make HTTP requests to external APIs using Apps Script and process the response data.

Prerequisites

Before we begin, ensure you have:

  • A Google account
  • Basic knowledge of JavaScript
  • Access to a weather API (for this example, we’ll use OpenWeatherMap, which requires a free API key)

Step 1: Setting Up Your Google Sheet

  1. Create a New Google Sheet:
    • Open Google Sheets and create a new spreadsheet.
    • Name the spreadsheet “Weather Data” or any name of your choice.
    • In the first row, create headers for the data you want to collect, such as “City”, “Temperature”, “Weather Description”, and “Date”.

Step 2: Getting Your API Key

  1. Sign Up for OpenWeatherMap:
    • Go to OpenWeatherMap and sign up for a free account.
    • Navigate to the API section and get your free API key.

Step 3: Writing the Apps Script

  1. Open the Apps Script Editor:
    • In your Google Sheet, go to Extensions > Apps Script to open the script editor.
  2. Create a New Script:
    • Replace any code in the script editor with the following code:

function fetchWeatherData() {

  const apiKey = ‘YOUR_API_KEY’; // Replace with your actual API key

  const city = ‘New York’; // You can change this to any city you want

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

  const response = UrlFetchApp.fetch(url);

  const data = JSON.parse(response.getContentText());

  const temperature = data.main.temp;

  const weatherDescription = data.weather[0].description;

  const date = new Date();

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet.appendRow([city, temperature, weatherDescription, date]);

  Logger.log(`Fetched weather data for ${city}: ${temperature}°C, ${weatherDescription}`);

}

Step 4: Running the Script

  1. Save and Run the Script:
    • Save your script by clicking the floppy disk icon or pressing Ctrl+S (Windows) or Cmd+S (Mac).
    • Click on the Run button (the play icon) to execute the script.
  2. Authorize the Script:
    • The first time you run the script, you will need to authorize it. Click on Review Permissions and follow the steps to grant the necessary permissions.
  3. Check Your Google Sheet:
    • After running the script, go back to your Google Sheet. You should see the weather data for the specified city added as a new row.

Step 5: Automating the Script

  1. Set Up a Trigger:
    • To automate the data fetching, you can set up a time-driven trigger. In the Apps Script editor, go to Triggers > Add Trigger.
    • Choose the fetchWeatherData function, select the time-driven trigger type, and set it to run at your preferred interval (e.g., daily).

Step 6: Customizing the Script

  1. Multiple Cities:
    • To fetch weather data for multiple cities, you can modify the script to loop through an array of city names and append the data for each city to the sheet.
  2. Additional Data:
    • You can customize the script to fetch and display additional weather data such as humidity, wind speed, or pressure by accessing different properties from the API response.

Conclusion

With this exercise, you’ve learned how to use Google Apps Script to make HTTP requests to external APIs, process the response data, and display it in a Google Sheet. This example used the OpenWeatherMap API to fetch weather data, but the same principles can be applied to integrate with any other API. This powerful capability allows you to automate data collection, integrate with external services, and enhance your Google Sheets with dynamic content.