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.

Note that this guide refers to WordPress.org, the self-hosted version of WordPress, not WordPress.com.

Contents

• Before You Begin
• Part 1: Get WordPress Application Password
• 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: Get WordPress Application Password

If you're fetching public WordPress data, like the public posts of an open site, you can skip this step. However if you're performing private actions (e.g. updating posts on your site), you'll need to authenticate yourself with an application password.

1. Log in to your WordPress site and navigate to Users -> Edit User for yourself
2. Scroll down to the Application Passwords section and click Add New Application Password
   
3. You'll see your new password listed.
4. One last step: Because the WordPress API requires Basic Authentication, we need to encode our API key to base 64. You can do this by entering your username and Application Password into this form, separated by a colon.
   
   The encoding script runs in your browser, and none of your credentials are seen or stored by this site.
   Base64 Encode

    

Part 2: Pull WordPress Data into Google Sheets

For this example, we'll get a list of posts from your WordPress site. Substitute in your own site name and encoded credentials.

1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
2. In the request form enter the following:
   • Application: Custom
   • Method: GET
   • Request URL: https://your_site.com/wp-json/wp/v2/posts?per_page=100&context=embed
   • Headers:
     • Authorization: Basic your_encoded_credentials
   • Report style: concatenate (under Output options)
     
3. Create a new tab and click Set current to use that tab as your data destination.
4. Name your request and click Run. A moment later you’ll see the latest posts in your sheet.
   
   

Note

We've made two adjustments to make the report more compact:

• We've selected the "concatenate" report style
• We've added the context=embed parameter. Without it, the API will return the full text of each article, which can produce too much data for Sheets to handle.

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 here are some examples to try out.

• 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, 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/