Guide to Get Data from Any API In Google Sheets For pSEO (Sample Code Inside)

Are you looking to gather some cool data for your Programmatic SEO blog but not sure how to start with APIs? Don’t worry, I’ve got you covered!

In this guide, we’ll look at how you can grab all sorts of information from APIs straight into Google Sheets. And the best part? You don’t need to be a coding expert. We’ll learn to use ChatGPT to help us out with the code bits.

I’ll show you some sample code that’s super easy to use. Think of it like a recipe – just follow the steps and you’ll have your data in no time.

Types of APIs for Integration with Google Sheets

1. No API Authentication: Easy-to-use APIs that don’t require any special keys or tokens.
2. API Key in Query String: APIs that need a key included right in the URL.
3. API Key in Header: More secure APIs where the key is sent as part of the request header.
4. OAuth2 for Secure Authentication: Advanced APIs that use OAuth2 for a higher level of security and data access control.

Stay tuned as we break down each type and how to work with them in Google Sheets.

No API Authentication

APIs that require no authentication are the easiest to start with, especially if you’re new to working with APIs. These APIs don’t need any special keys or tokens, making them very accessible for basic data gathering tasks.

An excellent example of an API that requires no authentication is the REST Countries API. This API provides information about countries, such as their names, population, region, and more, without needing any API key.

Using REST Countries API in Google Sheets

Suppose you want to fetch details about countries based on their name or code from the REST Countries API and display this information in your Google Sheet. Here’s how you can do it:

1. Set Up Your Google Sheet: Open a new Google Sheet and type country names or codes in different cells, for example, A1, A2, A3, etc.
2. Create a Custom Function: We will use a custom formula in Google Sheets to call the REST Countries API and fetch details based on the countries you entered.

Boilerplate Code for REST Countries API

To integrate the REST Countries API into Google Sheets, you can use a similar Apps Script function as before, but modified for this specific API:


function getCountryDetails(country) {
var baseUrl = "https://restcountries.com/v3.1/name/";
var query = encodeURIComponent(country);
var url = baseUrl + query;

var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);

if(data.length > 0) {
var countryData = data[0];
return [countryData.name.common, countryData.population, countryData.region];
} else {
return [“No data found”];
}
}

In this function, getCountryDetails, the URL is constructed using the country name or code entered. It fetches data from the REST Countries API and returns the country’s common name, population, and region.

How to Use in Google Sheets

1. Open your Google Sheet.
2. Go to Extensions > Apps Script.
3. Delete any existing code in the script editor and paste the getCountryDetails function.
4. Save and close the script editor.
5. Back in your Google Sheet, use the function like this: =getCountryDetails(A1), where A1 contains a country name or code.

This method allows you to seamlessly fetch country information from the REST Countries API based on the input in your Google Sheet, effectively demonstrating the use of APIs without authentication in data collection projects.

API Key in Query String

When an API requires an API key in the query string, you include the key directly in the URL of the API request. This method is slightly more secure than no authentication and is common for APIs like Pixabay, which provides access to a vast library of images and videos.

Using Pixabay API with Query String Authentication

Let’s take the Pixabay API as our example. You can use it to fetch a random image related to a specific keyword from Pixabay and display it in your Google Sheet.

Modifying the Code for Random Image Selection

Here’s how you can adjust the script to return a random image from the search results:


function getRandomPixabayImage(keyword) {
var apiKey = "YOUR_API_KEY";
var baseUrl = "https://pixabay.com/api/?key=" + apiKey + "&q=";
var query = encodeURIComponent(keyword);
var url = baseUrl + query;

var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var data = JSON.parse(json);

if (data.hits && data.hits.length > 0) {
var randomIndex = Math.floor(Math.random() * data.hits.length);
var randomImage = data.hits[randomIndex];
return [randomImage.webformatURL];
} else {
return [“No images found”];
}
}

In this updated function, getRandomPixabayImage, after fetching the data from Pixabay, the script selects a random image from the returned list of images (data.hits) and returns its URL.

Steps to Use in Google Sheets

1. Open your Google Sheet.
2. Go to Extensions > Apps Script.
3. Clear any existing code in the script editor and paste the getRandomPixabayImage function.
4. Replace YOUR_API_KEY with your actual Pixabay API key.
5. Save and close the script editor.
6. Back in your Google Sheet, use the function like this: =getRandomPixabayImage(A1), where A1 contains your keyword.

This setup allows you to dynamically fetch a random image from Pixabay based on a keyword entered in your Google Sheet. It’s a perfect example of using an API that requires an API key in the query string, demonstrating how such APIs can be integrated into Google Sheets for various applications.

API Key in Header

When an API requires an API key in the header, it’s a method of authentication where the key is included in the HTTP request headers. This is a common and secure way of using APIs. Let’s use the OpenAI API as an example to demonstrate how this can be done in Google Sheets.

Using OpenAI API for Chat Completions

The OpenAI API offers a variety of AI-powered capabilities, including chat completions. In this example, we’ll use it to fetch responses based on a user’s input.

Simplified Script for OpenAI API in Google Sheets

Here’s a streamlined version of the script to use the OpenAI API:


function getOpenAIResponse(userInput) {
var apiKey = "YOUR_API_KEY";
var model = "gpt-3.5-turbo-16k";
var messages = [
{"role": "system", "content": "You are a helpful assistant."},
{"role": "user", "content": userInput}
];

var requestBody = {
“model”: model,
“messages”: messages,
};

var requestOptions = {
“method”: “POST”,
“headers”: {
“Content-Type”: “application/json”,
“Authorization”: “Bearer ” + apiKey
},
“payload”: JSON.stringify(requestBody)
};

var response = UrlFetchApp.fetch(“https://api.openai.com/v1/chat/completions”, requestOptions);
var responseBody = JSON.parse(response.getContentText());

return responseBody.choices[0].message.content || “No response”;
}

In this function, getOpenAIResponse, you replace YOUR_API_KEY with your actual OpenAI API key. The function takes a user input, constructs a request with the necessary headers including the API key, and sends it to the OpenAI API. It then parses and returns the AI’s response.

Steps to Use in Google Sheets

1. Open your Google Sheet.
2. Go to Extensions > Apps Script.
3. Delete any existing code in the script editor and paste the getOpenAIResponse function.
4. Replace YOUR_API_KEY with your actual OpenAI API key.
5. Save and close the script editor.
6. Back in your Google Sheet, use the function like this: =getOpenAIResponse(A1), where A1 contains the user input.

This method showcases how to use an API with an API key in the header within Google Sheets. It’s an effective way to utilize advanced APIs like OpenAI for various applications, including chatbot functionalities, directly in your spreadsheets.

OAuth2 for Secure Authentication

OAuth2 is a more secure method of authentication that provides controlled access to user data. It’s particularly useful for APIs like Google Analytics where sensitive data is involved. In our journey to master Google Apps Script for Programmatic SEO, let’s delve into a practical example of using OAuth2 authentication — leveraging the Spotify Web API. This API is particularly relevant for those interested in merging music trends and user preferences with their SEO strategies.

Setting Up the Spotify API in Google Sheets

1. Spotify Developer Account and App: Start by registering on Spotify’s Developer Dashboard and creating an app. This step will give you the necessary credentials (client ID and client secret) for OAuth2 authentication.
2. OAuth2 Configuration in Google Sheets:
   • In your Google Sheet, head to Extensions > Apps Script.
   • Utilize the Apps Script editor to implement the OAuth2 library. This setup is crucial for secure communication with Spotify’s API.
   • Craft a script that employs OAuth2 for authenticating and fetching data from Spotify.
3. Fetching Data from Spotify:
   • With OAuth2 in place, create Google Apps Script functions to interact with various endpoints of the Spotify API. For instance, you might want to gather information on trending tracks, artist details, or user listening habits.

Sample Script for Data Retrieval

Here’s a straightforward example of how to fetch user’s top tracks from Spotify:

function getSpotifyData() {
var spotifyService = getSpotifyService(); // Function for OAuth2 setup
if (spotifyService.hasAccess()) {
var url = 'https://api.spotify.com/v1/me/top/tracks'; // API endpoint
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + spotifyService.getAccessToken()
}
});
var result = JSON.parse(response.getContentText());
return result.items.map(track => [track.name, track.artists[0].name, track.album.name]);
} else {
Logger.log('Access needed for Spotify API. Redirect URI: %s', spotifyService.getAuthorizationUrl());
return [];
}
}

This function getSpotifyData is designed to retrieve the top tracks of a user. getSpotifyService is a separate function responsible for handling OAuth2 authentication with Spotify.

Implementing in Your Sheets

1. Add this script to your Google Apps Script editor linked with your Google Sheet.
2. Complete the authorization process to enable your app’s access to Spotify data.
3. In your Google Sheet, use =getSpotifyData() to pull your top Spotify tracks.

By integrating Spotify’s rich music database, this example illustrates the seamless combination of advanced APIs and Google Sheets for Programmatic SEO. It’s a perfect demonstration of how OAuth2 authentication can unlock a world of possibilities for content strategists and SEO experts, particularly those working in niches related to music and entertainment.

Conclusion

As we wrap up this guide on harnessing Google Apps Script for Programmatic SEO, it’s clear that the power of automation and data integration is immense. Whether you’re a seasoned SEO professional or just starting out, the ability to efficiently collect, analyze, and apply data using APIs in Google Sheets can be a game-changer.

But what if you encounter an API or a task that’s a bit outside your coding comfort zone? This is where ChatGPT steps in as a valuable ally. If you find yourself needing a bit of extra help with scripting or if you’re tackling a new API, you can turn to ChatGPT. By providing it with the API documentation or specific requirements, ChatGPT can assist in generating the necessary code snippets that can be used directly in Google Apps Script.

This collaboration between your SEO knowledge, Google Apps Script’s capabilities, and ChatGPT’s coding assistance creates a powerful synergy. It ensures that you’re never stuck or slowed down by technical hurdles, allowing you to keep your focus on optimizing your SEO strategies and achieving the best results.

In summary, the combination of Google Apps Script, various APIs, and the assistance of AI like ChatGPT offers a flexible and powerful toolkit for anyone looking to advance their Programmatic SEO efforts. Embrace these tools, experiment with them, and watch as they open up new possibilities for your SEO projects and data management strategies.