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:
To pull data from the WordPress API to Google Sheets, first install and open the Apipheny Google Sheets add-on.
Apipheny is a no-code API integrator for Google Sheets that you can use to make unlimited API requests, connect to unlimited APIs, save API requests, schedule API requests, and more.
1. Install Apipheny by opening the following link on desktop and then clicking the Install button on the page: https://apipheny.io/install
2. After you’ve installed Apipheny, open a Google Sheet and then click on the Add-Ons or Extensions option in the top menu. In the menu, you should see Apipheny.
Click Apipheny > Import API to open the Apipheny sidebar in your Google Sheets.
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:
If you use this API URL, don’t forget to replace <your_domain> with your own domain. Do not include the brackets (< >).
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:
The WordPress REST API offers several options for authentication, each intended for a specific purpose.
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.
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.
To authenticate your API request, you have send the request with the credentials (in the username:password pair) appended as base64 encoded string.
This string is sent in the Authorization header field, after you chose the HTTP Method and the API URL Path.
If you are trying to use Apipheny to call 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 the 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:
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:
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:
If you use this same URL, or a similar URL, don’t forget to replace <your_domain> with your own domain. Do not include the brackets (< >) in your final URL.
Headers: In this tutorial, we pulled in public WordPress data, with endpoints that don’t require authentication. So we left the Headers section blank in this tutorial. To learn how to make an authenticated request, check Step 3.
Here’s what our request looks like when entered into Apipheny.
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:
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:
You could also connect to other API data sources and create custom blends of data.
PS: If you want to embed a Google Sheet onto a WordPress website, click here to learn more about an easy tool to do just that.