Back to Blog

How to Export Stats and Other Data From WordPress to Google Sheets Without Coding

With the WordPress to Google Sheets 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 (some of them can cause damage to your website if used improperly).

How to export data from WordPress to Google Sheets 

This article features two solutions for the WordPress data export:

  • WordPress importer. It lets you import summarized views, visitors, likes and comments, top posts and pages by views, site’s referrers, and site’s outbound clicks.
  • JSON Client importer. It lets you connect to the WordPress REST API and export an enhanced list of data.

Both solutions are the part of Coupler.io, a product for importing data to Google Sheets from different sources, such as WordPress, Airtable, Jira Cloud, Xero and many more.

So, first you need to install Coupler.io from the G Suite Marketplace.

After that, set up the importer that meets your requirements.

Jump right to the JSON Client importer section if you need to import an enhanced list of WordPress data.

Set up a WordPress importer to export WordPress stats data only

Open Coupler.io in the Add-ons tab of a spreadsheet, then click on the +Add Importer button and choose WordPress. 

Complete the following steps: 

1. Fill out the “Title” field

Enter the name of your WordPress to Google Sheets integration. 

2. Set up the Source

2.1. Connect to your WordPress account

Click Connect and log in to your WordPress.com account.

2.2. Select Stats type

Click on the field and select a type of statistical data to import:

  • Stats summary – summarized views, visitors, likes and comments
  • Top posts – top posts and pages by views
  • Referrers – site’s referrers
  • Outbound Clicks – site’s outbound clicks
2.3. Select Period

Click on the field and select a period for the statistical data to import: Day, Week, Month, or Year.

Click Show advanced to set up optional parameters for your data source.

2.4. Select Start Date

Click on the field and select the start date for the chosen period. With the specified Start Date, the summary stats will be generated according to the following principle:

  • Day period: sum of views from start date until now, visitors and other values for the current day.
  • Week period: sum of views from the week of the start date until current week, visitors and other values for the current week.
  • Month period: sum of views from the month of the start date until current month, visitors and other values for the current month.
  • Year period: sum of views from the year of the start date until current year, visitors and other values for the current year.

For example, if you picked Week as the Period and specified August 24, 2020 as the Start Date, you’ll get the sum of views starting from the week (Aug 24 – Aug 30, 2020) until current week. At the same time, visitors and other values will be for the current week.

2.5. Specify Max Results per Period

Specify the maximum number of results to return.

3. Set up the Destination

3.1. Fill out the “Sheet Name” field

Add the name of the sheet that will be receiving data. 

Click Show advanced to set up optional parameters for your data destination.

3.2. Fill out the “Cell address” field

Type in the address of the first cell where the data range will be imported. The default value is A1.

4. Configure the Settings (optional)

4.1. Automatic data refresh

To automate data export from WordPress on a schedule, toggle Automatic data refresh on and specify the Frequency.

Click Show advanced to set up optional settings.

4.2. Select Import Mode

Select the data import mode: replace or append. Check out our Knowledge Base to learn more about data import modes.

4.3. Add the Last Update column

If you want to add a column specifying the date of the last data refresh, toggle this function on.

Click Save to save the parameters or Save & Run to save the parameters and run the initial data import right away.

Set up a JSON Client importer to export miscellaneous data from WordPress to Google Sheets

The JSON Client importer lets you connect your spreadsheet to WP based on the WordPress REST API. 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.

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. Complete the following steps: 

1. Fill out the “Title” field

Enter the name of your integration. 

2. Set up the Source

2.1. Fill out the “JSON URL” field

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/
2.2. Select the HTTP Method

GET is the default HTTP method and the one we need here.

2.3. Fill out the “HTTP headers” field

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

2.4. Fill out the “URL query string” field

You can assign values to specified parameters. Query parameters can be found in information for each resource. 

2.5. Fill out the “Fields” field

Specify the fields (columns) to be imported to your spreadsheet. Skip if you want to pull all fields.

2.6. Fill out the “Path” field

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 the JSON Client importer and its features.

The steps 3 (Set up Data Destination) and 4 (Configure importer’s settings) are the same as with the WordPress importer setup.

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. 

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.

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

Comments are closed.

Access your data
in a simple format for free!

Start Free