Putting it all together – authenticate, read and update WordPress from a spreadsheet

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

This is my fourth post in my series of how to build a script to manage WordPress data via the API and a spreadsheet.   If you have arrived here without looking at the others you probably should start at the beginning here.

In this tutorial I’m going to handle updating and then pull it all together ( read / authenticate / update ) into a script that initially manages Yoast titles via the spreadsheet.

Something to bear in mind, it that Google Scripts has a maximum execution time of around 5 minutes, whilst reading data into the spreadsheet from my servers I can read thousands of posts in a minute or so, for updating, on my servers I can only get about 500 updates called in the 5 minute before time out.   In this script I’m not trying to be clever on timeout / restarting as you can do that manually, but it is possible with timed triggers – but I leave that for advanced posts.

As we have already built the getPosts function in post one, and modified it for meta in post two, and done the basics for JWT authorisation in post three I will concentrate on the updating posts first .

First of all we need a way to know a cell has changed, as we don’t want to scan maybe thousands of rows, trying to update things that haven’t actually changed.

The way I tackle this, is simply to set the background colour of a changed yoast title cell to red, this gives us a nice visual clue, and later when it has been updated set the background to green. This also enables you to re run the script if it breaks ( e.g. timeout ) as the process will pick up on the remaining red cells.  This is easy to set using the reserved onEdit(e) function as follow:

function onEdit(e){
  // If it is in the yoast title cell areas and changed turn it red
  var range = e.range;
  var thisCol = range.getColumn();
  var thisRow = range.getRow();
  if ( thisRow > 1 && thisCol == 3 ) {
    range.setBackground('#ff0000');
  }
}

Now we need a control loop for the updating of posts, this loop traverses the rows, looking for red backgrounds and calling the individual posts to update then, and set them to green

function updatePosts(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(2, 1, sheet.getMaxRows()-1, 4);   // row count starts at 1 but arrays start at zero hence need to -1
  var bg = range.getBackgrounds();         // gets 2 dimensional array of background
  for (i = 0; i < bg.length; i++) {
    if ( bg[i][2] == '#ff0000') {          // array 2 is column 3 = our yoast title - is it red?
      var r2 = sheet.getRange(i+2, 1, 1, 4);   // yes so set the range for that row
      var line = r2.getValues();               // and get a two dimension array of values
      if (line[0][0] != '' ) {                 // just check we haven't got a blank post ID to stop breaking things
          updateLine(line[0]);                 // call our line updater
          var r3 = sheet.getRange(i+2, 3);     // now get the specific range for the yoast title alone
          r3.setBackground('#00ff00');         // and set it green
      }
    }
  }
}

And we need the update calls for the specific lines ( rows)

function updateLine(line){
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json';  // get the domain that maybe was stored
  var ui = SpreadsheetApp.getUi();
  
  // see if we have a valid token
   var headers = {
    "Authorization" : "Bearer " + PropertiesService.getDocumentProperties().getProperty('token')   // JWT plugin 
    };
   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;
    }
 
  var payload = {
     meta: {
      _yoast_wpseo_title: line[2]
      }
    }
  
  var options = {
      'method': 'post',
      'headers': headers,
      'payload': JSON.stringify(payload),
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json'; 
  url = apiHost + '/wp/v2/posts/'+line[0];   // the api call
  try {
    var response = UrlFetchApp.fetch(url, options);
  } catch(error) {
    var result = ui.alert( error.toString());
  }
}

And of course we need a menu item to run the updates from, this time we won’t create a separate menu item as if we want to put a script into the chrome web store as a spreadsheet add on (even for your won G Suite domain) , you can’t have a top level menu. It as to be under the add-ons menu.

function onInstall(e) {   // required for add-ons
  onOpen(e);
}
function onOpen(e) {
  // setup a custom menu
  SpreadsheetApp.getUi().createAddonMenu() 
      .addItem('Get Posts', 'getPosts')
      .addItem('Update Posts', 'updatePosts')
      .addToUi();
  setupSheet();
}

So bringing the elements of the other post in we end up with

function onInstall(e) {   // required for add-ons
  onOpen(e);
}
function onOpen(e) {
  // setup a custom menu
  SpreadsheetApp.getUi().createAddonMenu() 
      .addItem('Get Posts', 'getPosts')
      .addItem('Update Posts', 'updatePosts')
      .addToUi();
  setupSheet();
}

function onEdit(e){
  // If it is in the yoast title cell areas and changed turn it red
  var range = e.range;
  var thisCol = range.getColumn();
  var thisRow = range.getRow();
  if ( thisRow > 1 && thisCol == 3 ) {
    range.setBackground('#ff0000');
  }
}

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('Yoast Title');
  ss.getRange('D1').setValue('Updated');
  
  ss.getRange('A1:C1').setFontWeight('bold');
  ss.getRange('A1:C1').setFontStyle('italic');
 

}

function showDialog() {
  var html = HtmlService.createHtmlOutputFromFile('loginpage')   // dont forget we need an html form in the project
      .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());
  }
  updatePosts();
}

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;
  }
}


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://mytestdomain/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
    // performance can be improved by storing this in an array and returning it to getPosts to display in one go rather than line by line
    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].meta._yoast_wpseo_title);
     ss.getRange('D'+row).setValue(data[i].modified);
  }
    return data.length;
  } catch(error) {
    var result = ui.alert( error.toString());
  }
  return 0;
}

function updatePosts(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(2, 1, sheet.getMaxRows()-1, 4);   // row count starts at 1 but arrays start at zero hence need to -1
  var bg = range.getBackgrounds();         // gets 2 dimensional array of background
  for (i = 0; i < bg.length; i++) {
    if ( bg[i][2] == '#ff0000') {          // array 2 is column 3 = our yoast title - is it red?
      var r2 = sheet.getRange(i+2, 1, 1, 4);   // yes so set the range for that row
      var line = r2.getValues();               // and get a two dimension array of values
      if (line[0][0] != '' ) {                 // just check we haven't got a blank post ID to stop breaking things
          updateLine(line[0]);                 // call our line updater
          var r3 = sheet.getRange(i+2, 3);     // now get the specific range for the yoast title alone
          r3.setBackground('#00ff00');         // and set it green
      }
    }
  }
}

function updateLine(line){
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json';  // get the domain that maybe was stored
  var ui = SpreadsheetApp.getUi();
  
  // see if we have a valid token
   var headers = {
    "Authorization" : "Bearer " + PropertiesService.getDocumentProperties().getProperty('token')   // JWT plugin 
    };
   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;
    }
 
  var payload = {
     meta: {
      _yoast_wpseo_title: line[2]
      }
    }
  
  var options = {
      'method': 'post',
      'headers': headers,
      'payload': JSON.stringify(payload),
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json'; 
  url = apiHost + '/wp/v2/posts/'+line[0];   // the api call
  try {
    var response = UrlFetchApp.fetch(url, options);
  } catch(error) {
    var result = ui.alert( error.toString());
  }
}

And don’t forget we need the login form html too

<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>

So if you open a spreadsheet, go to tools > script editor and put the above code in you should have a bulk Yoast SEO title editor.

Don’t forget the security gotchas, like WordFence and Mod Security mentioned in post three, and don’t forget to create the custom code plugin to show meta mentioned in post 2.

This script could easily be extended to handle meta description etc. and the performance tweaks done, to make it a handy SEO tool.

Your thoughts and comments welcome.

I have already built a script for WooCommerce to update regular prices in bulk, it uses many of the same principals here. Let me know if you are interested and I will write about it.