How to authenticate WordPress Rest API from 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 is the third tutorial in my series relating to integrating Google Sheets  and WordPress using the REST API.

The first post covered reading basic post information into a spreadsheet.  The second post was about getting meta data from the REST API, which can be a little tricky.

This one  covers authentication. Basically as an external app you need have the right credentials before you can carry out update type of calls. So in preparation for the fourth post were we move on to update data, we will explore one way of authenticating.

The developer handbook covers cookie authentication in some detail, but as we are not within the WordPress world here we have to go down the page and see the throw away comment that there are plugins that can help, and mentions OAuth 1.0a ServerApplication Passwords, and JSON Web Tokens.

For this tutorial we will work with JSON Web Tokens.

First you need to install and set up the JSON Web Tokens plugin.

To set up the plugin, you will need to edit your wp-config.php and also possibly your .htaccess file.   To get easy editing access I suggest you install the ICEcoder plugin follow this link for details.  Read the plugin instructions to set these up correctly.

The basic flow of JSON Webtokens is to pass your username & password once and then getting a token back that authenticates the subsequent calls.

The first thing we need to do is create an html file that will be displayed as a login modal to get the user / password.

In you script editor  ( if you are lost refer to and work though my first post ) go to File > New > HTML file  and in the pop up type login page, you will now have an html temple – delete the html and replace with the following

<form>
Domain inc http/https:<br>     
<input type="text" name="domain"><br>
User:<br>     
<input type="text" name="un"><br>
Password:<br> 
<input type="password" name="pw"><br>
<input type="button" value="connect" onclick="google.script.run.processLogin(this.form)" />
</form>

This is a straight forward html form, with an onclick action to call a script function processLogin.  I haven’t spent any error styling it, feel free.

The next is a working script that includes authentication.  Please be warned, if you run this as it is it is hard coded to change the title of post ID one – be aware, and tweak it to your appropriate test post. In the next post I will rework the script to take updates from the spreadsheet, read the following code, and I will explain it further down.

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('WordPress')
      .addItem('Test Update', 'testUpdate')
      .addToUi();
}

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('loginpage')
      .setWidth(200)
      .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'Connect to WordPress');
}

function processLogin(myForm) {
var payload = {
    username: myForm.un,
    password: myForm.pw
  }
var options = {
      'method': 'post',
      "contentType" : "application/json",
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': false
    }
PropertiesService.getDocumentProperties().setProperty('domain', myForm.domain);
var apiHost = myForm.domain+'/wp-json';
var ui = SpreadsheetApp.getUi(); // Same variations.
url = apiHost + '/jwt-auth/v1/token';
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response)
    PropertiesService.getDocumentProperties().setProperty('token', data.token);
    var result = ui.alert( 'got valid connection token' );
  } catch(error) {
    var result = ui.alert( 'invalid user or password: url: '+ url +' err: ' + error.toString());
  }
  testUpdate();
}

function testUpdate() {
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json';
  var ui = SpreadsheetApp.getUi();
  
  // see if we have a valid token
   var headers = {
    "Authorization" : "Bearer " + PropertiesService.getDocumentProperties().getProperty('token')
    };
   var options = {
      'method': 'post',
      "contentType" : "application/json",
      "headers": headers,
      'muteHttpExceptions': false
    }
    url = apiHost + '/jwt-auth/v1/token/validate';   
    try {
      var response = UrlFetchApp.fetch(url, options);
    } catch(error) {
      showDialog();
      return;
    }
  
  // if we got here we have a valid token
    var payload = {
      title: 'Test Title',     // ** warning **  this may change your data
    }
    var options = {
      'method': 'post',
      "contentType" : "application/json",
      "headers": headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': false
    }
    
    url = apiHost + '/wp/v2/posts/1';    // ** warning **  hard code your test post id here instead of 1
    try {
      var response = UrlFetchApp.fetch(url, options);
    } catch(error) {
      var result = ui.alert( error.toString());
    }
}

Before I explain the details of the above script  there are some security gotchas – worth mentioning.

Mod Security

Many hosts, rightly run mod_security on apache to trap hacking exploits.   Unfortunately some implementations of mod_security come up with the error JSON support was not enabled  if you are getting 403 errors when trying to get the token of apply an update check your e=server error logs.   Fixing this issue is very technical and down to your host.

WordFence

WordFence is very aggressive in stoping JSON payloads too, and if you get 403 errors and you have WordFence turned on, try deactivating it. If you have WordFence premium, their support may be able to help you, or maybe you will have to try different security software.

Other Security Software

I haven’t tested all security software, so can’t say, but if you are getting 403 or 404 or indeed any connection issues, try turning off your security software.  Please let me know in the comments what you find.

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('WordPress')
      .addItem('Test Update', 'testUpdate')
      .addToUi();
}

This creates a new menu on the spreadsheet and allows you to call the test function.

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('loginpage')
      .setWidth(200)
      .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'Connect to WordPress');
}

This function displays the html page we set up in a modal, we call this when we have an invalid token.  Remember the html form calls the processLogin function on submit.

function processLogin(myForm) {
var payload = {
    username: myForm.un,
    password: myForm.pw
  }
var options = {
      'method': 'post',
      "contentType" : "application/json",
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': false
    }
PropertiesService.getDocumentProperties().setProperty('domain', myForm.domain);
var apiHost = myForm.domain+'/wp-json';
var ui = SpreadsheetApp.getUi(); // Same variations.
url = apiHost + '/jwt-auth/v1/token';
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response)
    PropertiesService.getDocumentProperties().setProperty('token', data.token);
    var result = ui.alert( 'got valid connection token' );
  } catch(error) {
    var result = ui.alert( 'invalid user or password: url: '+ url +' err: ' + error.toString());
  }
  testUpdate();
}

The key items here are PropertiesService.getDocumentProperties().setProperty(‘domain’, myForm.domain);  where we stoore the domain in persistent storage.

If we successfully get a token when calling the token endpoint, we also store that  PropertiesService.getDocumentProperties().setProperty(‘token’, data.token); 

Finally, if we succeed or not we recall testUpdate();  as we want to drop back into that process, effectively looping until we get a valid token. See the next  part

function testUpdate() {
  var apiHost = PropertiesService.getDocumenttProperties().getProperty('domain')+'/wp-json';
  var ui = SpreadsheetApp.getUi();
  
  // see if we have a valid token
   var headers = {
    "Authorization" : "Bearer " + PropertiesService.getDocumentProperties().getProperty('token')
    };
   var options = {
      'method': 'post',
      "contentType" : "application/json",
      "headers": headers,
      'muteHttpExceptions': false
    }
    url = apiHost + '/jwt-auth/v1/token/validate';   
    try {
      var response = UrlFetchApp.fetch(url, options);
    } catch(error) {
      showDialog();
      return;
    }
  
  // if we got here we have a valid token
    var payload = {
      title: 'Test Title',     // ** warning **  this may change your data
    }
    var options = {
      'method': 'post',
      "contentType" : "application/json",
      "headers": headers,
      'payload': JSON.stringify(payload),
      'muteHttpExceptions': false
    }
    
    url = apiHost + '/wp/v2/posts/1';    // ** warning **  hard code your test post id here instead of 1
    try {
      var response = UrlFetchApp.fetch(url, options);
    } catch(error) {
      var result = ui.alert( error.toString());
    }
}

This is two calls, first a call to validate the token, if that fails, that is where we call the dialogue.

If we have a valid token, we move on to a test update.  See how the payload is made up for update.

So, now we have a structure that works for authentication, prompting for domain, user & password if we haven’t got a tken, and applying an update if we have.

In the next post, I will ties together getting posts, including Yoast meta title, authenticating and applying updates from changes made on the spreadsheet.

I hope you are finding these useful. Comments appreciated.


2 responses to “How to authenticate WordPress Rest API from Google Sheets / Scripts”

Leave a Reply to Getting post meta via WordPress REST API | Badly Wired Cancel reply

Your email address will not be published.