Linking WordPress to a Spreadsheet using WP REST API and Google Sheets scripts


If you find this free website useful – why don’t you support this with a donation? It is easy…. read more ….

This post shows you how you can link a Google Spreadsheet to a WordPress website to extract data. I will write some further posts covering authentication and updating too. However a little warning, this is a programming task and a basic understanding of javascript is really required to follow what is going on.

WordPress is now accessible to other applications through the WP REST API.   Google Sheets is a free spreadsheet solution, that is very similar to Microsoft Excel, but runs in the Cloud.  Being a Google App, Google Sheets has access to Google Apps Scripts, Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services and build web applications.  So now you can see the foundations of linking a WordPress website to a spreadsheet.

You are most likely here because you are a developer and you want to find out how to achieve getting WordPress data into a spreadsheet, however on the off-chance you are a business and you want some custom work or consultancy relating to WordPress please contact me via this link.

(If you are a developer and you have improvements or comments, please use the comment form at the bottom )

O.K. lets get started.

The first thing to do is to create a Google Sheet that is going to be the basis of your project. -> https://docs.google.com/spreadsheets

Start a new blank spreadsheet  and change the title from ‘untitled spreadsheet’ to something, anything,  you like.

Then, go to the menu Tools > Script Editor  and open up a new script, also change that from ‘untitled project ‘ to a name you like.

Next simply  replace

function myFunction() {
  
}

with the following and File > Save All   ( I will explain the code later down )

function onOpen() {
  // setup a custom menu
  SpreadsheetApp.getUi() 
      .createMenu('WordPress')
      .addItem('Get Posts', 'getPosts')
      .addToUi();
  setupSheet();
}

function setupSheet() {
  // just clear sheet and set & style headings
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  // set the tab to post - just for neatness
 var sheetname = ss.getSheets()[0].setName('Posts');
  // clear the sheet
 var range = ss.getDataRange();
 range.clear();
 //set headings
  ss.getRange('A1').setValue('Post ID');
  ss.getRange('B1').setValue('Title');
  ss.getRange('C1').setValue('Updated');
  
  ss.getRange('A1:C1').setFontWeight('bold');
  ss.getRange('A1:C1').setFontStyle('italic');

}


function getPosts(){
  setupSheet();
  // loop here as we need get back posts in chunks, Rest API max is 100 post
  offset = 0;
  per_page = 10;
  while ( (got=getPage(offset,per_page)) > 0 ) {
    offset+=got;
    Logger.log('offset %s', offset);
  }
}


function getPage(offset,per_page) {
  // gets posts in chunks of per_page
  var ui = SpreadsheetApp.getUi();  // used for error messages
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var options = {
      'method': 'get',
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  var apiHost = 'http://demo.wp-api.org/wp-json';   // set to your own domain
  url = apiHost + '/wp/v2/posts?per_page='+per_page+'&offset='+offset;
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response)
    // loop through the map and output to sheet
    for (i = 0; i < data.length; i++) {
     row=offset+i+2;  // set the row to make sure it is below header and takes into account the paging
     ss.getRange('A'+row).setValue(data[i].id);
     ss.getRange('B'+row).setValue(data[i].title.rendered);
     ss.getRange('C'+row).setValue(data[i].modified);
  }
    return data.length;
  } catch(error) {
    var result = ui.alert( error.toString());
  }
  return 0;
}

Once you have saved the script, return to your spreadsheet tab and refresh it.   You will now see an extra menu tab – WordPress

 

Under the WordPress tab is a sub menu – Get Posts – click on it and see the magic happen.

 

Lets break down the code and explain

function onOpen() {
  // setup a custom menu
  SpreadsheetApp.getUi() 
      .createMenu('WordPress')
      .addItem('Get Posts', 'getPosts')
      .addToUi();
  setupSheet();
}

This function is fired automatically when the spreadsheet page is opened ( or refreshed ).  It builds the menu ‘WordPress’ and a sub menu ‘Get Posts’ that calls our processing function getPosts().

I also have a function setupSheet() called which I will explain next.

function setupSheet() {
  // just clear sheet and set & style headings
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  // set the tab to post - just for neatness
 var sheetname = ss.getSheets()[0]setName('Posts');
  // clear the sheet
 var range = ss.getDataRange();
 range.clear();
 //set headings
  ss.getRange('A1').setValue('Post ID');
  ss.getRange('B1').setValue('Title');
  ss.getRange('C1').setValue('Updated');
  
  ss.getRange('A1:C1').setFontWeight('bold');
  ss.getRange('A1:C1').setFontStyle('italic');

}

The main purpose of this function is to clear any data in the spreadsheet  and then add headings to each column, in this case, keeping it simple, is just post id, post title and date modified.   You can of course extend this as you like.

When you select the sub menu item it calls the function getPosts() as below

function getPosts(){
  setupSheet();
  // loop here as we need get back posts in chunks, Rest API max is 100 post
  offset = 0;
  per_page = 10;
  while ( (got=getPage(offset,per_page)) > 0 ) {
    offset+=got;
    Logger.log('offset %s', offset);
  }
}

This does two things, first it clears the sheet ( again )  and then it loops through ‘pages’ of posts. The WP REST API limits the amount of post that can be retrieved in a single call to 100, and the default is 10. The limit is there to protect your website, but it does means we need to loop multiple times and keep track of what we have got.  So we start with and ‘offset’ of zero  and call the getPage() function until getPage() returns zero, if getPage() returns a +ve number ‘got’ we add that to the offset and go again.

The getPage() function does the API work

function getPage(offset,per_page) {
  // gets posts in chunks of per_page
  var ui = SpreadsheetApp.getUi();  // used for error messages
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var options = {
      'method': 'get',
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  var apiHost = 'http://demo.wp-api.org/wp-json';   // set to your own domain
  url = apiHost + '/wp/v2/posts?per_page='+per_page+'&offset='+offset;
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response)
    // loop through the map and output to sheet
    for (i = 0; i < data.length; i++) {
     row=offset+i+2;  // set the row to make sure it is below header and takes into account the paging
     ss.getRange('A'+row).setValue(data[i].id);
     ss.getRange('B'+row).setValue(data[i].title.rendered);
     ss.getRange('C'+row).setValue(data[i].modified);
  }
    return data.length;
  } catch(error) {
    var result = ui.alert( error.toString());
  }
  return 0;
}

The key call is  var response = UrlFetchApp.fetch(url, options);  which pull back from the ‘url’ based on the ‘options’ which in this example is http://demo.wp-api.org/wp-json/wp/v2/posts with query args of per_page and offset ( as you recall we increment offset ) and the function returns the number of rows  – you may think that is always 10, but that isn’t always so, for instance 14 posts is one page of 10 and one page of 4.

Once we get the response we use JSON.parse(response)  to build a map ( array for PHPers) of the posts and we loop through those and push them into the appropriate row & column, taking into consideration the header row and the offset.

In my next posts I am going to cover

  • How to get meta data, if you inspect what you get back you will find meta[] by default, and meta data is most useful -> post is here
  • How to authenticate with your user and password so you can do updates, and some gotchas of security software -> post is here
  • And tie it all together with an example script to retrieve posts, with meta data and apply updates -> post is here

I have also built a script to handle Woocommerce regular-price updates based on the principals in these tutorials, if there is interest in the write up – or even as a straight add-on  please leave a comment.

As it does take time to write these post I would appreciate any feedback, encouragement or comments


7 responses to “Linking WordPress to a Spreadsheet using WP REST API and Google Sheets scripts”

  1. Little bit off topic, this is exactly what I want but to connect to dropbox csv files. Would you know how I could go about that or have a link to somewhere that possibly could help out with that?

  2. Great article. Is it possible to link categories and tag into 2 different Columns? If yes how can I edit your script? Thanks again!

  3. hello,
    i am admin of e-commerce website.

    i want to develop script for these scenarios.
    — customer registration
    — subscription
    — product add/delete/modify
    — order status update

    if you can help build script for these functions, let me know.
    i will buy script as per your price request.

    thanks

Leave a Reply to Integrating Google Forms / Sheets to External API | Badly Wired Cancel reply

Your email address will not be published.