How to connect the WordPress API 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:

  1. Install the Apipheny add-on
  2. Choose a WordPress API endpoint
  3. Authentication
  4. Enter your WordPress API request into Apipheny
  5. Run the WordPress API Request in your Google Sheet

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

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.

Opening the Apipheny add-on in Google Sheets

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:

WordPress API documentation page
WordPress API documentation page

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.

The Comments category
The Comments category

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. 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:

List comments endpoint
List comments endpoint

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.

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:

The Headers section in Apipheny
The Headers section in Apipheny with an authenticated request

Step 4.) Enter your WordPress API URL into the Apipheny Add-on

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. Do not include the brackets (< >) in your final URL.

Head

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.

WordPress API request entered into Apipheny
WordPress API request entered into Apipheny

Step 5.) Run the WordPress Ads API Request in Google Sheets

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}}}

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.

Related Posts