Back to Blog

How to Export WordPress Data to Spreadsheets via WP REST API Without Coding

Attention please: This blog post is not about connecting Google Sheets to WordPress. Our focus is on the reverse data movement: from WordPress to Google Sheets. With this integration, you can export statistics, posts, pages, media, users and other data from WP and process it in spreadsheets. The most important (and best) part about it is that no WordPress plugins are needed. Read on and reap the benefits of this solution.

Which benefits can the integration of WordPress and Google Sheets provide?

Customized reporting and data visualization

Google Sheets is one of the best tools to build customized reports and visualize data. You’ll be able to pull a database from your WordPress website and process it in whatever way you need, whether that’s creating a sales dashboard, tailoring a statistics monitor, etc. 

Data shareability

With your raw data in spreadsheets, you’ll be able to share it with colleagues, clients and other stakeholders. 

Automatic data import on schedule

After the initial data import, you’ll be able to set up an automatic data refresh. So, your spreadsheet will update by itself on a chosen schedule.  

Data-driven decision making

With the WordPress data pulled to a spreadsheet, you’ll get insight into the current state of your operation. This, in turn, will contribute to substantial decision making based on data.

What do you need to pull data from WordPress into spreadsheets?

  • A Google Sheets add-on, Coupler.io
  • WordPress REST API

You don’t need to mess with any WordPress plugins, which can cause damage to your website if used improperly. Our solution is based on the connection to WordPress REST API and, as a result, is much more user-friendly. 

Tech-savvy users can navigate to the Getting Started with the API to explore the features of the WordPress API. 

No panic if you’re neither familiar with REST API nor especially tech savvy. In the article, we’ll guide you through the essential points required to set up WordPress integration and import data into spreadsheets. You’ll only have to follow the how-to instructions to succeed.

How to export data from WordPress to Google Sheets 

Install Coupler.io

Coupler.io is a Google Sheets add-on that lets you import data from different sources, such as Airtable, Jira Cloud, Xero and many more. For more information about the add-on and available integrations, please visit the Coupler.io home page.

You can install Coupler.io from either the G Suite Marketplace or the Add-ons tab in your spreadsheet:

Set up a JSON Client importer

An importer is an integration that connects Google Sheets to a specific app or data source. There is no dedicated importer for WordPress yet, but this may change in the future. For now, we’ll use the JSON Client importer to connect Google Sheets to WordPress.

For any questions, you are welcome to contact our friendly support team at coupler@railsware.com, who will eagerly help configure this type of integration. Coupler.io is evolving every day, so feel free to share which data source you’d like to connect to Google Sheets – just fill in this form. Your use case can help us decide which dedicated importer will be created next. 

Open Coupler.io in the Add-ons tab of a spreadsheet, then click on the +Add Importer button and choose JSON Client. Fill in the following fields: 

Required fields

  • Title – Add the name of your data source 
  • Sheet Name – Type in the name of your sheet
  • JSON URL – Insert the URL of the endpoint to import from; the base URL for making an HTTP request to WordPress is as follows:
https://public-api.wordpress.com/rest/v1.1/resource
  • resource – Insert the resource based on what you need to import. You can find all available resources in the WordPress.com REST API documentation. For example, here is the JSON URL to pull data about all users of the Coupler.io blog: 
https://public-api.wordpress.com/rest/v1.1/sites/blog.coupler.io/users/
  • Automatic data refresh – Enable this function and pick the frequency for the automatic data update in your spreadsheet.

Additional fields 

  • HTTP Method – Use the GET method to pull data from WordPress. GET is the default HTTP method.
  • HTTP headers – Apply the following HTTP headers to make authenticated requests to WordPress:
Authorization: Bearer {your-access-token}
Content-Type: application/json

Getting a WordPress API token is the most intricate part of our integration. You can go through it using the WordPress OAuth2 Authentication documentation or check out a dedicated section of this blog post: How to get a WordPress API token

  • URL query string – You can assign values to specified parameters. Query parameters can be found in information for each resource. 
  • Request body – Skip this field since it’s applicable for POST, PUT, PATCH or DELETE request methods only. 
  • Fields – Specify the fields (columns) to be imported to your spreadsheet. Skip if you want to pull all fields.
  • Path – Select nested objects from the JSON response. Skip if you want to select all objects.

Check out the Coupler.io knowledge base to learn more about Additional fields, as well as the Settings section.

Click Add Importer to end setup and run your importer to pull data from WordPress. Here is how it looks:

Check out the JSON to Google Sheets blog post to learn more about integration capabilities of the JSON Client importer. 

How to get a WordPress API token

  • Fill in the fields required to create an application:
    • Name – Type in the name of your app
    • Description – Type in the description of your app
    • Website URL – Add the URL to your WP website
    • Redirect URLs – Add up to 10 URLs to which you can redirect during the authorization process
    • Javascript Origins – If you want to make authenticated GET requests, add Javascript origins (URLs) that are whitelisted. 
    • What is $ + $? – Demonstrate your math skills to validate yourself 🙂 
    • Type – Choose the type of your app: web (for server-side authenticated API calls) or native (for client-side authenticated API calls)

Here is an example:

Once you’ve filled in the fields, click Create. Your application has been created and you will see it in the Application Manager. Click on it to get OAuth Information.

  • Now we can make a GET request using the following URL pattern:
https://public-api.wordpress.com/oauth2/authorize?client_id=your_client_id&redirect_uri=your_redirect_uri&response_type=code&blog=1234
  • your_client_id – Insert your Client ID 
  • your_redirect_uri – Insert your Redirect URL 
  • code – Specify the response type. code should be used for server-side apps – that’s what we need. token should be used for client-side apps (token expires in two weeks, so you’ll have to reauthenticate with your app).  
  • 1234 – Specify your blog ID or blog URL (this is an optional parameter for a WordPress.com blog or Jetpack site) 

Once your request URL is ready, copy and paste it into your browser’s URL bar, then press Enter. Here’s how it looks:

So, where is the code? Look closer to your URL bar:

You should get something like the following:

https://blog.coupler.io/?code=ab3FyK23Smn&state

Your code is ab3FyK23Smn – you’ll use it to request the access token.

  • Now you need to make a POST request to
https://public-api.wordpress.com/oauth2/token

The easiest way to do this is to use a Chrome app called Postman as follows:

  • Specify the request’s URL
https://public-api.wordpress.com/oauth2/token
  • Click on the Params button and fill in the following keys:
    • code – Insert the code you obtained at the previous step
    • client_id – Insert your client ID from OAuth Information
    • redirect_uri – Insert your Redirect URL from OAuth Information
    • client_secret – Insert your Client Secret from OAuth Information
    • grant_type – Type in authorization_code 

Click Send and meet your access token in the response:

How to pull statistics from WordPress

Let’s try to import some WordPress statistical data about Coupler.io blog into the spreadsheet. For this, we’ve set up the JSON importer as follows:

JSON URL:

https://public-api.wordpress.com/rest/v1.1/sites/blog.coupler.io/stats/

In the JSON URL, we’ve replaced resource with the URL of our blog: blog.coupler.io.

HTTP headers:

Authorization: Bearer {your-access-token}
Content-Type: application/json

And that’s what we’ve got in the spreadsheet:

Not much…

If you need more, you’d better pull a summarized stats report using the following parameters:

JSON URL:

https://public-api.wordpress.com/rest/v1.1/sites/blog.coupler.io/stats/summary

HTTP headers:

Authorization: Bearer {your-access-token}
Content-Type: application/json

URL query string:

{period: year, pretty: TRUE, max: 100, summarize: TRUE}

Specify the period you need: day, week, month, year.

And let’s use another importer parameter: Path. It allows you to select specific nested objects from the JSON response. Check out this example of pulling data for views by country:

JSON URL:

https://public-api.wordpress.com/rest/v1.1/sites/blog.coupler.io/stats/country-views/

HTTP headers:

Authorization: Bearer {your-access-token}
Content-Type: application/json

URL query string:

{num: 365, period: day, pretty: TRUE, max: 100, summarize: TRUE}

Specify the period you need: day, week, month, year, as well as the number of periods to include: num. The default value is 1.

Path:

summary.views

Use different configurations of your JSON Client importer to pull versatile data from WordPress, such as referrers, outbound clicks, followers, and many more. With all this data in the spreadsheet, you can build an interactive dashboard and monitor how things are going. Here is the one we tailored for our blog with the help of Google Sheets and Coupler.io.

If interested, leave your comment below and we’ll release a separate blog post about what WordPress data we imported and which Google Sheets shortcuts and functions we used for the dashboard. 

To wrap up

Using the Coupler’s JSON Client importer is a breeze – you don’t have to mess with coding at all. Besides, you can automate WordPress data import and save a lot of time. The toughest thing is to get a WordPress access token. It will take some time, but it’s not rocket science. Fire up and you’ll get it! Good luck!

Back to Blog

Access your data
in a simple format for free!

Start Free