Search API Connector Documentation
Import WordPress Data to Google Sheets
In this guide, we’ll walk through some examples of how to pull data from the WordPress API directly into Google Sheets, using the API Connector add-on for Sheets. In this article we’ll pull in public WordPress data (if you’d like to pull in private data, you’ll need the help of an authentication plugin).
Note that this guide refers to WordPress.org, the self-hosted version of WordPress, not WordPress.com.
Contents
- Before You Begin
- Part 1: Create your API Request URL
- Part 2: Pull WordPress API data into Sheets
- Part 3: More Example API URLs
- Part 4: Handle Pagination
- Part 5: API Documentation
Before You Begin
Click here to install the API Connector add-on from the Google Marketplace.
Part 1: Create Your API Request URL
We’ll first get a list of your public posts.
- API root: https://mixedanalytics.com (substitute in your own hostname)
- Endpoint: /wp-json/wp/v2/posts
- Query strings: per_page=100&context=embed
Putting it together, we get the full API Request URL:
hhttps://mixedanalytics.com/wp-json/wp/v2/posts?per_page=100&context=embed
The query strings are optional but will help you get more records and send back a briefer response (without context=embed
, the API will return the full text of each article, which can produce too much data for Sheets to handle).
Part 2: Pull WordPress Data into Google Sheets
Now let’s copy and paste that URL into API Connector.
- Open up Google Sheets and click Extensions > API Connector > Open.
- In the Create tab, enter the API URL we just created.
- Leave OAuth set to none. We don’t need any headers either so just leave that section blank.
- Create a new tab and click Set current to use that tab as your data destination.
- Name your request and click Run. A moment later you’ll see the latest posts in your sheet.
Part 3: More Example API URLs
You can experiment with endpoints and query strings as described in the documentation to see other types of WordPress data, but if you just want to jump in and get a feel for it, play around with the URLs you enter in the API URL path field. Try the following (one at a time), substituting in your own site name.
- categories
https://your-site.com/wp-json/wp/v2/categories
- comments
https://your-site.com/wp-json/wp/v2/comments
- pages
https://your-site.com/wp-json/wp/v2/pages
Part 4: Handle Pagination
The WordPress API limits the number of records returned in each request. By default, only 10 records will be returned unless you paginate through records as described in their documentation. This is why we set the per_page=100
parameter in our first example query above. To get more than 100 records, you need to paginate through records as shown in their documentation.
With API Connector you can do this automatically with pagination handling (paid feature), like this:
- API URL: enter your request URL, including the per_page=100 parameter
- Pagination type:
next page URL
- Field name:
link
- Run until: choose when to stop fetching data
Part 5: API Documentation
Official API documentation: https://developer.wordpress.org/rest-api/reference/
thanks amazing article! is there also a way to import wordpress drafts in gsheets somehow?
thanks
There is a way to get drafts, but since drafts aren’t public, you can only get them with an authenticated request (like using a password).
To authenticate yourself, you can use a plugin like this one: https://wordpress.org/plugins/wp-rest-api-authentication/. It will give you an API key that you can add to your request.
Then you should be able to get drafts with a URL like this:
https://your-site.com/wp-json/wp/v2/posts?status=draft
Hope that helps point you in the right direction, just let me know if I can clarify anything else.