WordPress To Google Sheets - API Integration Tutorial

How to connect WordPress to Google Sheets

WordPress is a free and open-source content management system (CMS) written in PHP and paired with a MySQL or MariaDB database. We can use the WordPress API to import data from our WordPress site into Google Sheets. In this tutorial, we’ll show you how to connect the WordPress API to Google Sheets in 4 steps:

Install the Apipheny add-on
Choose a WordPress API endpoint
Authentication
Enter your WordPress API request into Apipheny
Run the WordPress API Request in your Google Sheet

Step 1.) Install and open the Apipheny add-on for Google Sheets

Apipheny is an API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to unlimited API data sources, make unlimited API requests, and more. There is a 30 day free trial included.

1.) Install Apipheny by opening the following link on desktop and then clicking the Install button in the Google Marketplace: https://apipheny.io/install

2.) After you’ve installed Apipheny, open a Google Sheet and then click on the Extensions dropdown in the menu at the top.

In the dropdown list you should see Apipheny. Click Apipheny then click Import API to open the Apipheny sidebar in your Google Sheet.

Tip: you can open a new Google Sheet by entering this URL in your browser: sheet.new

Step 2.) Choose a WordPress API endpoint

In this section, I’ll show you how to browse the WordPress API documentation to find an API URL and endpoint that retrieves the specific information you need from your WordPress account. If you already know your API URL, or you want to use the same example URL as us, just skip to Step 3.

First, open the WordPress API documentation page: https://developer.wordpress.org/rest-api/reference/

On the left side, you can find the CHAPTERS menu, which contains the main sections of the documentation: REST API Handbook, Key Concepts, Frequently Asked Questions, Using the REST API, Extending the REST API, and Endpoint Reference.

If you expand the Endpoint Reference section, you’ll find a list of endpoint categories:

For instance, if you click the Comments category, you’ll see the list of its 5 endpoints: List Comments, Create a Comment, Retrieve a Comment, Update a Comment, and Delete a Comment.

One of the available endpoints is List Comments, and the URL for this endpoint looks like this:

https://https://your_domain/wp-json/wp/v2/comments

If you use this API URL, don’t forget to replace your_domain with your own domain.

The endpoint’s documentation contains a short description of its purpose, the HTTP method it uses (GET), the URI, the associated cURL command, and a list of available arguments:

Step 3.) Authentication

The WordPress REST API offers several options for authentication, each intended for a specific purpose.

Basic Authentication
OAuth Authentication
Cookie Authentication

At the moment, the native WordPress authentication manner for users and their activities is verification by cookies.

For API invocation purposes, we’ll use Basic Authentication. We chose this because is the most simple and straightforward authentication method.

To use Basic Authentication with WordPress REST API, you need to install the WordPress REST API Plugin. This plugin allows you to add Basic Authentication to a WordPress site.

The WordPress REST API plugin is available from the GitHub WordPress REST API group. To utilize the plugin, simply clone it in the WordPress Plugin directory and activate it through the WordPress admin.

If you are using an endpoint that requires authentication, enter the following key and value into Apipheny, in the Headers section:

Headers
Key:
Authorization
Value:
Basic encoded_username:password

encoded_username:password is the encoded version of your username and password. You can use https://www.base64encode.org/ to encode your username:password string. Just enter your username and password like this “username:password” and then click “Encode”.

This is what an authenticated request should look like in Apipheny:

The Headers section in Apipheny with an authenticated request

Step 4.) Enter your WordPress API request into Apipheny

In your Google Sheet, make sure that the Apipheny add-on is open on the Import tab. With the Import Tab open, enter these details into the add-on:

Method: At the top of the Apipheny sidebar, select the HTTP method (GET or POST) as required by your WordPress API endpoint. For this example, we are using the GET method.

API URL: In Step 3, I explained how you can find the WordPress API endpoint that you need. Now copy the complete API URL into the Apipheny add-on, where it says API URL, followed by any GET parameters required for your query.

For this example, we are using the List Pages endpoint, which retrieves a collection of pages. This it the API URL and endpoint we are using for this example:

https://https://your_domain/wp-json/wp/v2/pages

If you use this same URL, or a similar URL, don’t forget to replace your_domain with your own domain.

Headers: In this example tutorial, we pulled in public WordPress data, with endpoints that don’t require authentication. So we left the Headers section blank in this example. To learn how to make an authenticated request, check Step 3.

Here’s what our request looks like when entered into Apipheny.

WordPress API request entered into Apipheny

Step 5.) Run the WordPress API request

The last step is to click the Run button at the bottom of the Apipheny add-on and then wait for the WordPress data to be imported into your Google Sheet, like so:

WordPress data imported into Google Sheets

After successfully calling the WordPress API, try querying a different endpoint, or try using one of the more advance features in the Apipheny add-on, such as:

Saving and scheduling your API request
Use the custom =APIPHENY() function to call the API request inside your spreadsheet
Or try creating an API request by referencing the value of a cell in the API URL with three curly braces eg. {{{Sheet1!A1}}}