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:
Apipheny is a free API connector for Google Sheets. You can use Apipheny to connect your Google Sheets to API data sources, easily.
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
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:
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.
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:
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.
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:
Schedule requests for automatic updates
Reference cell values in requests
Stack multiple URLs in a single request
Crypto API Tutorials: