Part 6: Integrating External APIs with Google Sheets

Google Sheets and Google App Script provide an extensive range of features to manage and analyze your data. However, sometimes you may need to fetch information from external sources, such as APIs, to enhance your spreadsheet’s functionality. In this blog post, we will explore how to integrate external APIs with Google Sheets using Google App Scripts and provide plenty of examples to follow along.

What are APIs?

An API (Application Programming Interface) is a set of rules and protocols that allow different software applications to communicate and share data with each other. APIs make it easy for developers to access external data and services, enabling them to create more advanced and feature-rich applications.

Fetching Data from APIs in Google Sheets

To fetch data from an API in Google Sheets, you can use the UrlFetchApp class provided by Google App Script. The UrlFetchApp.fetch() method sends an HTTP request to a specified URL and returns the response.

Before we dive into examples, make sure you have set up a Google Sheet and opened the script editor by clicking “Extensions” in the top menu, then “Apps Script.”

Example: Fetching Weather Data from OpenWeatherMap API

In this example, we will fetch weather data for a specified city using the OpenWeatherMap API and populate our Google Sheet with the retrieved information.

To access the OpenWeatherMap API, you need an API key. You can sign up for a free account and get your API key from their website.

First, create a custom function in the script editor to fetch the weather data:

function fetchWeatherData(city) {
  let apiKey = 'YOUR_API_KEY';
  let apiUrl = 'https://api.openweathermap.org/data/3.0/weather?q='+city+'&units=metric&appid='+apiKey;  
  let response = UrlFetchApp.fetch(apiUrl);  let weatherData = JSON.parse(response.getContentText());

  return weatherData;
}

Replace 'YOUR_API_KEY' with your actual API key from OpenWeatherMap.

To display the fetched weather data in your Google Sheet, create another custom function that calls fetchWeatherData() and populates the sheet with the data:

function displayWeatherData() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let city = 'New York'; // Replace this with the desired city

  let weatherData = fetchWeatherData(city);

  let cityName = weatherData.name;
  let temperature = weatherData.main.temp;
  let description = weatherData.weather[0].description;

  sheet.getRange('A1').setValue('City: '+cityName);
  sheet.getRange('A2').setValue('Temperature: '+temperature+'°C');
  sheet.getRange('A3').setValue('Weather: '+description);
}

Now you can run the displayWeatherData function from the script editor or call it from your Google Sheet to display the weather data for the specified city.

POST Requests and Sending Data to APIs

While the previous example demonstrated how to fetch data from an API using a GET request, there are cases where you need to send data to an API using a POST request. To do this, you can use the UrlFetchApp.fetch() method with additional options.

Example: Sending Data to a Slack Channel via Webhooks

In this example, we will send a message to a Slack channel using the Slack Incoming Webhooks API.

To use the Slack Incoming Webhooks API, you need to create a webhook URL for your Slack workspace. Follow Slack’s guide to create your webhook URL.

First, create a custom function in the script editor to send a message to your Slack channel:

function sendMessageToSlack(message) {
  let webhookUrl = 'YOUR_SLACK_WEBHOOK_URL';
  let payload = {
    'text': message
  };

  let options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };

  UrlFetchApp.fetch(webhookUrl, options);
}

Replace 'YOUR_SLACK_WEBHOOK_URL' with your actual webhook URL from Slack.

To test the sendMessageToSlack function, you can create another custom function that calls it with a message:

function testSlackMessage() {
  let message = 'Hello from Google Sheets!';
  sendMessageToSlack(message);
}

Now you can run the testSlackMessage function from the script editor, and you should see the message in your Slack channel.

Handling API Rate Limits and Errors

Many APIs have rate limits to control the number of requests you can make in a given time frame. It’s essential to handle these limits and any potential errors gracefully in your Google App Script code.

You can use the try...catch statement to handle errors when calling APIs. For example, modify the fetchWeatherData function to handle potential errors:

function fetchWeatherData(city) {
  let apiKey = 'YOUR_API_KEY';
  let apiUrl = 'https://api.openweathermap.org/data/3.0/weather?q='+city+'&units=metric&appid='+apiKey;

  try {
    let response = UrlFetchApp.fetch(apiUrl);
    let weatherData = JSON.parse(response.getContentText());

    return weatherData;
  } catch (error) {
    Logger.log('Error fetching weather data:', error);
    return null;
  }
}

If an error occurs while fetching the weather data, the function logs the error message and returns null. You can then modify the displayWeatherData function to handle cases when the fetchWeatherData function returns null:

function displayWeatherData() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let city = 'New York'; // Replace this with the desired city

  let weatherData = fetchWeatherData(city);

  if (weatherData === null) {
    sheet.getRange('A1').setValue('Error fetching weather data. Please check the logs for more information.');
  } else {
    let cityName = weatherData.name;
    let temperature = weatherData.main.temp;
    let description = weatherData.weather[0].description;

  sheet.getRange('A1').setValue('City: '+cityName);
  sheet.getRange('A2').setValue('Temperature: '+temperature+'°C');
  sheet.getRange('A3').setValue('Weather: '+description);
  }
}

Conclusion

Integrating external APIs with Google Sheets using Google App Scripts can greatly expand the capabilities of your spreadsheets and unlock new possibilities for data analysis and automation. By following the examples and guidelines provided in this blog post, you can now fetch data from various APIs, send data to APIs using POST requests, and handle API rate limits and errors.

In our next blog post, we’ll dive into API calls with Google App Scripts using OpenAI’s GPT-3.5-turbo to generate content directly in your Google Sheets.