Author: Alan | Badly Wired

Linking WooCommerce to a Spreadsheet using Google Sheets and the REST API

22nd August 2018 by Alan Leave a Comment

Following on from my previous tutorials on Google Sheets and WordPress Rest API some people have asked me if you can do the same with WooCommerce.

Indeed you can. WooCommerce has REST API that provides access to any data you need. You can find the technical documents here.

One of the main differences is that WooCommerce comes with an authentication method ( so you don’t need to add a method like you did here ).

Lets assume you are familiar with Google Sheets and Google Scripts ( if not go back to the first tutorial and at least do the basics of getting posts ).

And if you are reading this, we will assume you have a working copy of WooCommerce with some data in it ( ideally a test system to play with ) if so we a re set to get going.

Lets cover authentication first

Most useful data from WooCommerce is likely to be private, e.g. Orders or updating Product prices, so whilst the other tutorials started with getting public data and moved on to private data, lets dive straight in ( another reason to at least have covered the basics here ).

Set up WooCommerce keys

Login to and go to WooCommerce settings > advanced > api and create a key, with read/write permission and save a copy as you will need this for the script.

Add the code to get the credentials

Editing the script is from the spreadsheet Tools>Script Editor

Open this up and replace the content with

function onInstall(e) {<br />  onOpen(e);<br />}<br />function onOpen(e) {<br />  // setup a custom menu<br />  SpreadsheetApp.getUi().createAddonMenu() <br />      .addItem('Get Orders', 'getPosts')<br />      .addItem('Set Domain Name credentials', 'getDomain')<br />      .addToUi();<br />  setupSheet();<br />}<br />function getDomain() {<br />  var html = HtmlService.createHtmlOutputFromFile('credentials')<br />      .setWidth(200)<br />      .setHeight(200);<br />  SpreadsheetApp.getUi().showModalDialog(html, 'Connect to WordPress');<br />}<br />

Save this script giving it a name you can remember and makes sense e.g. Woo Orders.

What this does is
1. Sets up a menu under Add Ons with two options – Get Orders and Set Domain Name Credentials
2. Defines a function that runs a file called credential.html , which will contain our input form

In the script editor, go to File>New>HTML file and create a file named credentials.html with your html form code as followsSave your script. Go back to your spreadsheets and refresh. Under add-ons you should now have a new menu e.g. Woo Orders where you can input the credentials you set up above.

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<form id="loginform">
  <div class="form-group">
    <label for="domain">Domain inc. https://</label>
    <input type="text" id="domain" name="domain">
  </div>
  <div class="form-group">
    <label for="key">Consumer Key</label>
    <input type="text" id="key" name="key">
  </div>
  <div class="form-group">
    <label for="secret">Consumer Secret</label>
    <input type="password" id="secret" name="secret">
  </div>
  <div class="block form-group">
    <button class="action" onclick="google.script.run.processCredentials(this.form)" >Submit</button>
  </div>
</form>

<label for="domain">Domain inc. https://</label>

</div>

<label for="key">Consumer Key</label>

</div>

<label for="secret">Consumer Secret</label>

</div>

<button class="action" onclick="google.script.run.processCredentials(this.form)" >Submit</button>

</div>

</form>

Getting the Orders into the spreadsheet.

First we want a function that will retrieve the form data (credentials) and store it in persistent data storage with the form

function processCredentials(myForm) {
  PropertiesService.getDocumentProperties().setProperty('domain', myForm.domain);
  PropertiesService.getDocumentProperties().setProperty('consumer_key', myForm.key);
  PropertiesService.getDocumentProperties().setProperty('consumer_secret', myForm.secret);
  SpreadsheetApp.getUi().alert('Saved, hit OK and the script will try to get the products, this may take a while');
  getOrders();
}

function processCredentials(myForm) {

PropertiesService.getDocumentProperties().setProperty('domain', myForm.domain);

PropertiesService.getDocumentProperties().setProperty('consumer_key', myForm.key);

PropertiesService.getDocumentProperties().setProperty('consumer_secret', myForm.secret);

SpreadsheetApp.getUi().alert('Saved, hit OK and the script will try to get the products, this may take a while');

getOrders();

}

This function also kicks of the main function getting the orders.

The getOrders function has to get the orders in chunks ( or pages as we should call them ) as there are limits on the number of orders that can be return in one go, this is to protect your server mainly.

The function first check to see if we have stored credentials.
Then loops through at 50 orders per page.
Then finally outputs the buffer built up.

function getOrders(){
  if ( PropertiesService.getDocumentProperties().getProperty('domain') == null ) {
    getDomain();
    return;
  }
  setupSheet();
  // loop here as we need get back posts in chunks, Rest API max is 100 post
  var offset = 0;
  var per_page = 50;
  var page = [];
  var values = [];
  
 do {
    page=getPage(offset,per_page);
    offset+=page.length;
    for (i = 0; i < page.length; i++) {
       values.push(page[i]);
    }
  } while ( page.length > 0 );
  
  
  //output range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(2, 1, values.length, 15);
  range.setValues(values);
}

function getOrders(){

if ( PropertiesService.getDocumentProperties().getProperty('domain') == null ) {

getDomain();

return;

}

setupSheet();

// loop here as we need get back posts in chunks, Rest API max is 100 post

var offset = 0;

var per_page = 50;

var page = [];

var values = [];

do {

page=getPage(offset,per_page);

offset+=page.length;

for (i = 0; i < page.length; i++) {

values.push(page[i]);

}

} while ( page.length > 0 );

//output range

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var range = sheet.getRange(2, 1, values.length, 15);

range.setValues(values);

}

The main API call is made in the next function. The authorisation is using basic auth – but sometime there are issues if JWT token are also in use or the server has a problem, so an alternative is query string for key & secret, see the commented out API line.

It retrieves the data and pushes it into a structure. HereI have a double loop so i can output multi line items.

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 page = [];
  
  // set up authorisation
  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(PropertiesService.getDocumentProperties().getProperty('consumer_key')+':'+PropertiesService.getDocumentProperties().getProperty('consumer_secret'))
    };
  
  var options = {
      'method': 'get',
      'headers': headers,
      "contentType" : "application/json",
      'muteHttpExceptions': false
    }
  
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json'; 
  // altenative to basic auth in case that doesn't work nicely e.g. you have JWT token plugin too.
  // url = apiHost + '/wc/v2/orders?per_page='+per_page+'&offset='+offset+ '&consumer_key='+PropertiesService.getDocumentProperties().getProperty('consumer_key')+'&consumer_secret='+PropertiesService.getDocumentProperties().getProperty('consumer_secret');
  //
  url = apiHost + '/wc/v2/orders?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++) {  
         
       page.push([data[i].number,
                  data[i].date_created,
                  data[i].status,
                  data[i].currency,
                  data[i].shipping_total,
                  data[i].total,
                  data[i].customer_id,
                  data[i].billing.first_name,
                  data[i].billing.last_name,
                  data[i].billing.email,
                  '1',
                  data[i].line_items[0].id,
                  data[i].line_items[0].name,
                  data[i].line_items[0].quantity,
                  data[i].line_items[0].total
                 ]);
      // now do any additional line items push a line with blanks if required
      for (j = 1; j < data[i].line_items.length; j++ ) {
        page.push([' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                   j+1,
                  data[i].line_items[j].id,
                  data[i].line_items[j].name,
                  data[i].line_items[j].quantity,
                  data[i].line_items[j].total
                 ]);
        
      }

    }
  } catch(error) {
    var result = ui.alert( error.toString());
    return 0;
  }
  return page;
}

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 page = [];

// set up authorisation

var headers = {

"Authorization" : "Basic " + Utilities.base64Encode(PropertiesService.getDocumentProperties().getProperty('consumer_key')+':'+PropertiesService.getDocumentProperties().getProperty('consumer_secret'))

};

var options = {

'method': 'get',

'headers': headers,

"contentType" : "application/json",

'muteHttpExceptions': false

}

var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json';

// altenative to basic auth in case that doesn't work nicely e.g. you have JWT token plugin too.

// url = apiHost + '/wc/v2/orders?per_page='+per_page+'&offset='+offset+ '&consumer_key='+PropertiesService.getDocumentProperties().getProperty('consumer_key')+'&consumer_secret='+PropertiesService.getDocumentProperties().getProperty('consumer_secret');

url = apiHost + '/wc/v2/orders?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++) {

page.push([data[i].number,

data[i].date_created,

data[i].status,

data[i].currency,

data[i].shipping_total,

data[i].total,

data[i].customer_id,

data[i].billing.first_name,

data[i].billing.last_name,

data[i].billing.email,

'1',

data[i].line_items[0].id,

data[i].line_items[0].name,

data[i].line_items[0].quantity,

data[i].line_items[0].total

]);

// now do any additional line items push a line with blanks if required

for (j = 1; j < data[i].line_items.length; j++ ) {

page.push([' ',

' ',

j+1,

data[i].line_items[j].id,

data[i].line_items[j].name,

data[i].line_items[j].quantity,

data[i].line_items[j].total

]);

}

} catch(error) {

var result = ui.alert( error.toString());

return 0;

}

return page;

}

The other function, not covered yet is setupSheet all it is doing is clearing teh sheet and adding titles.

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('Orders');
  // clear the sheet
 var range = ss.getDataRange();
 range.clear();
 //set headings
  ss.getRange('A1').setValue('Order Number');
  ss.getRange('B1').setValue('Date Created');
  ss.getRange('C1').setValue('Status');
  ss.getRange('D1').setValue('Currency');
  ss.getRange('E1').setValue('Shipping');
  ss.getRange('F1').setValue('Grand Total');
  ss.getRange('G1').setValue('Customer ID');
  ss.getRange('H1').setValue('Billing First Name');
  ss.getRange('I1').setValue('Billing Last Name');
  ss.getRange('J1').setValue('Billing email');
  ss.getRange('K1').setValue('Order Line');
  ss.getRange('L1').setValue('Item ID');
  ss.getRange('M1').setValue('Item Name');
  ss.getRange('N1').setValue('Item Qty');
  ss.getRange('O1').setValue('Item Totatl');
  
  ss.getRange('A1:O1').setFontWeight('bold');
  ss.getRange('A1:O1').setFontStyle('italic');

}

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('Orders');

// clear the sheet

var range = ss.getDataRange();

range.clear();

//set headings

ss.getRange('A1').setValue('Order Number');

ss.getRange('B1').setValue('Date Created');

ss.getRange('C1').setValue('Status');

ss.getRange('D1').setValue('Currency');

ss.getRange('E1').setValue('Shipping');

ss.getRange('F1').setValue('Grand Total');

ss.getRange('G1').setValue('Customer ID');

ss.getRange('H1').setValue('Billing First Name');

ss.getRange('I1').setValue('Billing Last Name');

ss.getRange('J1').setValue('Billing email');

ss.getRange('K1').setValue('Order Line');

ss.getRange('L1').setValue('Item ID');

ss.getRange('M1').setValue('Item Name');

ss.getRange('N1').setValue('Item Qty');

ss.getRange('O1').setValue('Item Totatl');

ss.getRange('A1:O1').setFontWeight('bold');

ss.getRange('A1:O1').setFontStyle('italic');

}

And that is it.

This is the full script in one go

function onInstall(e) {
  onOpen(e);
}

function onOpen(e) {
  // setup a custom menu
  SpreadsheetApp.getUi().createAddonMenu() 
      .addItem('Get Orders', 'getOrders')
      .addItem('Set Domain Name credentials', 'getDomain')
      .addToUi();
  setupSheet();
}
function getDomain() {
  var html = HtmlService.createHtmlOutputFromFile('credentials')
      .setWidth(200)
      .setHeight(200);
  SpreadsheetApp.getUi().showModalDialog(html, 'Connect to WordPress');
}
function processCredentials(myForm) {
  PropertiesService.getDocumentProperties().setProperty('domain', myForm.domain);
  PropertiesService.getDocumentProperties().setProperty('consumer_key', myForm.key);
  PropertiesService.getDocumentProperties().setProperty('consumer_secret', myForm.secret);
  SpreadsheetApp.getUi().alert('Saved, hit OK and the script will try to get the order, this may take a while');
  getOrders();
}
function getOrders(){
  if ( PropertiesService.getDocumentProperties().getProperty('domain') == null ) {
    getDomain();
    return;
  }
  setupSheet();
  // loop here as we need get back posts in chunks, Rest API max is 100 post
  var offset = 0;
  var per_page = 50;
  var page = [];
  var values = [];
  
 do {
    page=getPage(offset,per_page);
    offset+=page.length;
    for (i = 0; i < page.length; i++) {
       values.push(page[i]);
    }
  } while ( page.length > 0 );
  
  
  //output range
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange(2, 1, values.length, 15);
  range.setValues(values);
}

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 page = [];
  
  // set up authorisation
  var headers = {
    "Authorization" : "Basic " + Utilities.base64Encode(PropertiesService.getDocumentProperties().getProperty('consumer_key')+':'+PropertiesService.getDocumentProperties().getProperty('consumer_secret'))
    };
  
  var options = {
      'method': 'get',
      'headers': headers,
      "contentType" : "application/json",
      'muteHttpExceptions': false
    }
  
  var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json'; 
  // altenative to basic auth in case that doesn't work nicely e.g. you have JWT token plugin too.
  // url = apiHost + '/wc/v2/orders?per_page='+per_page+'&offset='+offset+ '&consumer_key='+PropertiesService.getDocumentProperties().getProperty('consumer_key')+'&consumer_secret='+PropertiesService.getDocumentProperties().getProperty('consumer_secret');
  //
  url = apiHost + '/wc/v2/orders?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++) {  
         
       page.push([data[i].number,
                  data[i].date_created,
                  data[i].status,
                  data[i].currency,
                  data[i].shipping_total,
                  data[i].total,
                  data[i].customer_id,
                  data[i].billing.first_name,
                  data[i].billing.last_name,
                  data[i].billing.email,
                  '1',
                  data[i].line_items[0].id,
                  data[i].line_items[0].name,
                  data[i].line_items[0].quantity,
                  data[i].line_items[0].total
                 ]);
      // now do any additional line items push a line with blanks if required
      for (j = 1; j < data[i].line_items.length; j++ ) {
        page.push([' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                  ' ',
                   j+1,
                  data[i].line_items[j].id,
                  data[i].line_items[j].name,
                  data[i].line_items[j].quantity,
                  data[i].line_items[j].total
                 ]);
        
      }

    }
  } catch(error) {
    var result = ui.alert( error.toString());
    return 0;
  }
  return page;
}







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('Orders');
  // clear the sheet
 var range = ss.getDataRange();
 range.clear();
 //set headings
  ss.getRange('A1').setValue('Order Number');
  ss.getRange('B1').setValue('Date Created');
  ss.getRange('C1').setValue('Status');
  ss.getRange('D1').setValue('Currency');
  ss.getRange('E1').setValue('Shipping');
  ss.getRange('F1').setValue('Grand Total');
  ss.getRange('G1').setValue('Customer ID');
  ss.getRange('H1').setValue('Billing First Name');
  ss.getRange('I1').setValue('Billing Last Name');
  ss.getRange('J1').setValue('Billing email');
  ss.getRange('K1').setValue('Order Line');
  ss.getRange('L1').setValue('Item ID');
  ss.getRange('M1').setValue('Item Name');
  ss.getRange('N1').setValue('Item Qty');
  ss.getRange('O1').setValue('Item Totatl');
  
  ss.getRange('A1:O1').setFontWeight('bold');
  ss.getRange('A1:O1').setFontStyle('italic');

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

function onInstall(e) {

onOpen(e);

}

function onOpen(e) {

// setup a custom menu

SpreadsheetApp.getUi().createAddonMenu()

.addItem('Get Orders', 'getOrders')

.addItem('Set Domain Name credentials', 'getDomain')

.addToUi();

setupSheet();

}

function getDomain() {

var html = HtmlService.createHtmlOutputFromFile('credentials')

.setWidth(200)

.setHeight(200);

SpreadsheetApp.getUi().showModalDialog(html, 'Connect to WordPress');

}

function processCredentials(myForm) {

PropertiesService.getDocumentProperties().setProperty('domain', myForm.domain);

PropertiesService.getDocumentProperties().setProperty('consumer_key', myForm.key);

PropertiesService.getDocumentProperties().setProperty('consumer_secret', myForm.secret);

SpreadsheetApp.getUi().alert('Saved, hit OK and the script will try to get the order, this may take a while');

getOrders();

}

function getOrders(){

if ( PropertiesService.getDocumentProperties().getProperty('domain') == null ) {

getDomain();

return;

}

setupSheet();

// loop here as we need get back posts in chunks, Rest API max is 100 post

var offset = 0;

var per_page = 50;

var page = [];

var values = [];

do {

page=getPage(offset,per_page);

offset+=page.length;

for (i = 0; i < page.length; i++) {

values.push(page[i]);

}

} while ( page.length > 0 );

//output range

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheets()[0];

var range = sheet.getRange(2, 1, values.length, 15);

range.setValues(values);

}

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 page = [];

// set up authorisation

var headers = {

"Authorization" : "Basic " + Utilities.base64Encode(PropertiesService.getDocumentProperties().getProperty('consumer_key')+':'+PropertiesService.getDocumentProperties().getProperty('consumer_secret'))

};

var options = {

'method': 'get',

'headers': headers,

"contentType" : "application/json",

'muteHttpExceptions': false

}

var apiHost = PropertiesService.getDocumentProperties().getProperty('domain')+'/wp-json';

// altenative to basic auth in case that doesn't work nicely e.g. you have JWT token plugin too.

url = apiHost + '/wc/v2/orders?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++) {

page.push([data[i].number,

data[i].date_created,

data[i].status,

data[i].currency,

data[i].shipping_total,

data[i].total,

data[i].customer_id,

data[i].billing.first_name,

data[i].billing.last_name,

data[i].billing.email,

'1',

data[i].line_items[0].id,

data[i].line_items[0].name,

data[i].line_items[0].quantity,

data[i].line_items[0].total

]);

// now do any additional line items push a line with blanks if required

for (j = 1; j < data[i].line_items.length; j++ ) {

page.push([' ',

' ',

j+1,

data[i].line_items[j].id,

data[i].line_items[j].name,

data[i].line_items[j].quantity,

data[i].line_items[j].total

]);

}

} catch(error) {

var result = ui.alert( error.toString());

return 0;

}

return page;

}

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('Orders');

// clear the sheet

var range = ss.getDataRange();

range.clear();

//set headings

ss.getRange('A1').setValue('Order Number');

ss.getRange('B1').setValue('Date Created');

ss.getRange('C1').setValue('Status');

ss.getRange('D1').setValue('Currency');

ss.getRange('E1').setValue('Shipping');

ss.getRange('F1').setValue('Grand Total');

ss.getRange('G1').setValue('Customer ID');

ss.getRange('H1').setValue('Billing First Name');

ss.getRange('I1').setValue('Billing Last Name');

ss.getRange('J1').setValue('Billing email');

ss.getRange('K1').setValue('Order Line');

ss.getRange('L1').setValue('Item ID');

ss.getRange('M1').setValue('Item Name');

ss.getRange('N1').setValue('Item Qty');

ss.getRange('O1').setValue('Item Totatl');

ss.getRange('A1:O1').setFontWeight('bold');

ss.getRange('A1:O1').setFontStyle('italic');

}

WordPress REST API retrieve data and email it as an xls using Google Sheets

31st July 2018 by Alan Leave a Comment

Google Script to read a WordPress REST API endpoint into a Google Sheet and then email it ( no titles in this one, see other tutorials for titles )

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

function setupSheet() {
  // just clear sheet and set &amp; style headings
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  // set the tab name - just for neatness
 var sheetname = ss.getSheets()[0].setName('Data');
  // clear the sheet
 var range = ss.getDataRange();
 range.clear();
}


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;
  }
  SpreadsheetApp.flush();
  sendGoogleSpreadsheetAsExcel();
}

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 = 'https://YOURDOMAIN/wp-json';   // set to your own domain
  url = apiHost + '/wp/v2/ENDPOINT?per_page='+per_page+'&amp;offset='+offset;  // set to appropriate END POINT
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response)
    // loop through the map and output to sheet
    for (i = 0; i &lt; data.length; i++) {
     row=offset+i+1;  
     ss.getRange('A'+row).setValue(data[i].title.rendered);   // work out the data structure by looking at the JSON
     ss.getRange('B'+row).setValue(data[i].barcode_id);       // work out the data structure by looking at the JSON
  }
    return data.length;
  } catch(error) {
    var result = ui.alert( error.toString());
  }
  return 0;
}

function sendGoogleSpreadsheetAsExcel(){
  
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&amp;exportFormat=xlsx";
    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(ss.getName() + ".xlsx");
    // change the EMAIL address
    MailApp.sendEmail("youremail@example.com", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});   
  } catch (f) {
    Logger.log(f.toString());
  }
}

function onOpen() {

// setup a custom menu

SpreadsheetApp.getUi()

.createMenu('Barcodes')

.addItem('Get Data', 'getPosts')

.addToUi();

setupSheet();

}

function setupSheet() {

// just clear sheet and set & style headings

var ss = SpreadsheetApp.getActiveSpreadsheet();

// set the tab name - just for neatness

var sheetname = ss.getSheets()[0].setName('Data');

// clear the sheet

var range = ss.getDataRange();

range.clear();

}

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;

}

SpreadsheetApp.flush();

sendGoogleSpreadsheetAsExcel();

}

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 = 'https://YOURDOMAIN/wp-json'; // set to your own domain

url = apiHost + '/wp/v2/ENDPOINT?per_page='+per_page+'&offset='+offset; // set to appropriate END POINT

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+1;

ss.getRange('A'+row).setValue(data[i].title.rendered); // work out the data structure by looking at the JSON

ss.getRange('B'+row).setValue(data[i].barcode_id); // work out the data structure by looking at the JSON

}

return data.length;

} catch(error) {

var result = ui.alert( error.toString());

}

return 0;

}

function sendGoogleSpreadsheetAsExcel(){

try {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";

var params = {

method : "get",

headers : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},

muteHttpExceptions: true

};

var blob = UrlFetchApp.fetch(url, params).getBlob();

blob.setName(ss.getName() + ".xlsx");

// change the EMAIL address

MailApp.sendEmail("youremail@example.com", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});

} catch (f) {

Logger.log(f.toString());

}

Stopping Genesis Full menu loading momentarily before the responsive menu

10th April 2018 by Alan 3 Comments

Many, if not all of StudioPress’s older themes flash up the full menu for a glimpse before the responsive hamburger comes in.

This doesn’t look great but is easy to fix in 2 steps.

First, tweak the CSS so that the start of the responsive section reads

body.js .genesis-nav-menu {
display: none;
}

body.js .genesis-nav-menu {

display: none;

}

the responsive section may for instance start @media only screen and (max-width: 800px) {
And the add the plugin ‘Genesis JS / No JS’ by the every well respected Gary Jones https://en-gb.wordpress.org/plugins/genesis-js-no-js/

However, you may not want to add yet another plugin, and the code is simple to add to your functions.php, thi sis my version of Gary’s code and does the job just fine.

add_filter( 'body_class', function ( $classes ) {
	$classes[] = 'no-js';
	return $classes;
} );
add_action( 'genesis_before', function () {
	?>
    <script>
        //<![CDATA[
        (function () {
            var c = document.body.classList;
            c.remove('no-js');
            c.add('js');
        })();
        //]]>
    </script>
	<?php
}, 1 );

add_filter( 'body_class', function ( $classes ) {

$classes[] = 'no-js';

return $classes;

} );

add_action( 'genesis_before', function () {

//<![CDATA[

(function () {

var c = document.body.classList;

c.remove('no-js');

c.add('js');

})();

//]]>

</script>

<?php

}, 1 );

Sage Business Cloud Accounting – a new name for Sage One

22nd March 2018 by Alan Leave a Comment

Sage Business Cloud Accounting is the new name for Sage One Accounting.

To celebrate this, you can get a 50% discount by following this discount code link.

Sage Business Cloud Accounting is an accounting and payroll solution for small businesses.

Being a small business that uses an online accounting system ( not Sage ) I am planning shortly to sign up for a free trial, and I will write my thoughts up in a review here.

Watch this space.

Code snippet to change notification of new users

5th February 2018 by Alan Leave a Comment

Example plugin to set admin notification of new users. In this case it uses the first user defined as shop_manager role

<?php
/*
 *
 * Plugin Name:       Notify New Users to Shop Manager
 * Plugin URI:        https://fullworks.net/products/custom-plugin-development/
 * Description:       Needs Woo Commerce of Course
 * Version:           1.0
 * Author:            Fullworks
 * Author URI:        http://fullworks.net/
 * License:           GPL-3.0+
 * License URI:       http://www.gnu.org/licenses/gpl-3.0.txt
 */


function admin_email_callback( $wp_new_user_notification_email,  $user,  $blogname ) {
    $shopmanager = get_users( array( 'role' => 'shop_manager' ) );
// set the to: the first shop manager email.
    $wp_new_user_notification_email['to'] = $shopmanager[0]->user_email;
    return $wp_new_user_notification_email;
}
add_filter( 'wp_new_user_notification_email_admin', 'admin_email_callback', 10, 3 );

<?php

* Plugin Name: Notify New Users to Shop Manager

* Plugin URI: https://fullworks.net/products/custom-plugin-development/

* Description: Needs Woo Commerce of Course

* Version: 1.0

* Author: Fullworks

* Author URI: http://fullworks.net/

* License: GPL-3.0+

* License URI: http://www.gnu.org/licenses/gpl-3.0.txt

function admin_email_callback( $wp_new_user_notification_email, $user, $blogname ) {

$shopmanager = get_users( array( 'role' => 'shop_manager' ) );

// set the to: the first shop manager email.

$wp_new_user_notification_email['to'] = $shopmanager[0]->user_email;

return $wp_new_user_notification_email;

}

add_filter( 'wp_new_user_notification_email_admin', 'admin_email_callback', 10, 3 );

Download this as a plugin from fullworks -> https://fullworks.net/wp-content/uploads/2018/02/set-shop-manager-new-user.zip

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

16th January 2018 by Alan 1 Comment

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

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

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

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

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

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>

<form>

Domain inc http/https:<br>

User:<br>

Password:<br>

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

How to authenticate WordPress Rest API from Google Sheets / Scripts

15th January 2018 by Alan 2 Comments

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 Server, Application 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>

<form>

Domain inc http/https:<br>

User:<br>

Password:<br>

</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());
    }
}

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

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

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

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

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.

Getting post meta via WordPress REST API

12th January 2018 by Alan 9 Comments

This the second post post in my series about getting and updating WordPress data via a spreadsheet. The first post focused on the basics of Google Apps Scripting and the API call to the WP REST API – if you haven’t seen it you can read it here.

This post is about meta data. In case you are not really clear, meta data in WordPress post terms are any extra data that isn’t part of the WordPress post schema. More often or not this data is created and maintained by plugins.

To give you a clear example, if say you use Yoast SEO plugin, the Yoast SEO version of the post title is stored in meta data item called _yoast_wpseo_title these are also known as custom fields, and WordPress treats custom fields starting with an _ as hidden.

I read many posts on howto access meta and most of them were wrong and out dated. The WP Rest documentation tells you about problems with meta and also warns you they are changing their thoughts on this – so beware – this may even be out of date soon.

If you look at the ‘posts schema’ you will see an entry for meta, but more often than not, you will see an entry "meta":[] i.e. empty, even if it is not e.g. curl http://demo.wp-api.org/wp-json/wp/v2/posts/1

For unsetialized ( i.e. text string meta) the solution is much simpler that many blog posts I read make out, you just need to tell WordPress that the meta can be exposed in the API.

To do this, you are going to need to write ( or copy at least ) some PHP code. Some tutorials, will tell you that you add extra code to your theme’s functions.php, whilst this will work, this is not the right way to do things, themes are for layouts, plugins are for functions. If you add it to your (child) theme and one day you change your theme then the extra features will break. So write a plugin, its not hard.

Before you write the plugin, you will have been testing on a website on the internet ( you have to as Google can’t see your local host ), so here is a quick and easy way to get editing on your remote site without faffing around with FTP etc. Install ICEcoder with this plugin to be able to follow along.

Once you are in the editor navigate to the wp-content/plugins folder, right click on plugins and select ‘New File’ and give your plugin a name like myfunctions.php .

The copy the code below and paste it into the code area and ctrl-s to save it.

<?php
/**
 * Plugin Name:       My Custom Code
 * Plugin URI:        http://fullworks.net/wordpress-plugins
 * Description:       Custom code
 * Version:           1.0
 * Author:            Fullworks
 * Author URI:        http://fullworks.net/
 *
 */
if ( ! defined( 'WPINC' ) ) {
	die;
}
add_action( 'rest_api_init', 'register_posts_meta_field' );

function register_posts_meta_field() {
    $object_type = 'post';
    $args1 = array( // Validate and sanitize the meta value.
        // Note: currently (4.7) one of 'string', 'boolean', 'integer',
        // 'number' must be used as 'type'. The default is 'string'.
        'type'         => 'string',
        // Shown in the schema for the meta key.
        'description'  => 'A meta key associated with a string meta value.',
        // Return a single value of the type.
        'single'       => true,
        // Show in the WP REST API response. Default: false.
        'show_in_rest' => true,
    );
    register_meta( $object_type, '_yoast_wpseo_title', $args1 );
}

<?php

/**

* Plugin Name: My Custom Code

* Plugin URI: http://fullworks.net/wordpress-plugins

* Description: Custom code

* Version: 1.0

* Author: Fullworks

* Author URI: http://fullworks.net/

if ( ! defined( 'WPINC' ) ) {

die;

}

add_action( 'rest_api_init', 'register_posts_meta_field' );

function register_posts_meta_field() {

$object_type = 'post';

$args1 = array( // Validate and sanitize the meta value.

// Note: currently (4.7) one of 'string', 'boolean', 'integer',

// 'number' must be used as 'type'. The default is 'string'.

'type' => 'string',

// Shown in the schema for the meta key.

'description' => 'A meta key associated with a string meta value.',

// Return a single value of the type.

'single' => true,

// Show in the WP REST API response. Default: false.

'show_in_rest' => true,

);

register_meta( $object_type, '_yoast_wpseo_title', $args1 );

}

Lets just take a second to explain what we are doing.

when the Rest API is initiated 'rest_api_init' we are calling our function which is basically registering _yoast_wpseo_title but this time it is setting it to show in the Rest API 'show_in_rest' => true,

OK, so when you have saved your plugin, go to your admin > plugins and find it, and activate it, hopefully it activates without errors.

So you now are ready to go, let just extend the script we wrote in the first article to also read the meta items.

If you are following this along, you will obviously need to install the Yoast SEO plugin into your remote test WordPress, and also set a few Yoast titles up in posts.

The script code would now look like this

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('Yoast Title');
  ss.getRange('D1').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://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
    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 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('Yoast Title');

ss.getRange('D1').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://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

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;

}

See what I did there, just inserted column C as a title and put .meta._yoast_wpseo_title into column C

Change the domain to your actual internet available test domain. Save and run from the spreadsheet. Refer back to the first article if you need to see how.

If you have any questions or comments, please leave them.

In the next post I will work through a method to get authentication so you can later update.

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

11th January 2018 by Alan 6 Comments

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() {
  
}

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

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

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');

}

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

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

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

Recipe for a Raspberry Pi Kiosk

8th September 2017 by Alan 10 Comments

Ingredients

Raspberry Pi 3 B wireless

<br />

16Gb micro sd card

HDMI Cable

USB power cable

Case

Temporary USB Keyboard & USB mouse

Preparation and Cooking time

About 1 to 2 hours depending how many mistakes you make and start again 🙂

Method

Preparation

Download Raspberian Stretch Lite from https://www.raspberrypi.org/downloads/raspbian/
Unzip on to your computer
Obtain Etcher to burn the image onto the SD card
detailed instructions https://www.raspberrypi.org/documentation/installation/installing-images/README.md

First Boot

Insert SD card to Raspberry. Plugin HDMI to Raspberry – TV / Monitor. Plugin in USB keyboard. Plugin the power, should boot to a login screen.

user: pi

password: raspberry

Basic Setup

Set up secure password (option 1) – as SSH opens up access

Set up host name ( option 2)- makes it easier to find for login in as SSH

Set up Boot Options (option 3 -> B1 – >B2 ) – we will want the Raspberry to Boot into Console / Autologin

Set up SSH (option 5 -> P2 ) – this is important – as once you set up a true kiosk you can’t login, so SSH gives you away to set up easily. (option 5 -> P2 )

This is all done by the Raspberry Configuration tool

sudo raspi-config

1	sudo raspi-config

Set up Wireless Access

If you are not using an ethernet cable you will need to set up Wireless access. Raspberry instructions here https://www.raspberrypi.org/documentation/configuration/wireless/wireless-cli.md

Edit the config file ( I will use vi but you can use nano ).

sudo vi /etc/wpa_supplicant/wpa_supplicant.conf

1	sudo vi /etc/wpa_supplicant/wpa_supplicant.conf

and add a network, you can generate this with

wpa_passphrase yourSSID  yourwirelesspassword

1	wpa_passphrase yourSSID yourwirelesspassword

which outputs

network={
ssid="yourSSID"
#psk=" yourwirelesspassword"
psk=fdcdd08a9d6bd739f544a902c1a59dd8a7eedea30b4e4ba1afa61d3cf01222df
}

network={

ssid="yourSSID"

#psk=" yourwirelesspassword"

psk=fdcdd08a9d6bd739f544a902c1a59dd8a7eedea30b4e4ba1afa61d3cf01222df

}

so you can if you want to be fast and if you like to append directly

sudo wpa_passphrase yourSSID  yourwirelesspassword >> /etc/wpa_supplicant/wpa_supplicant.conf

1	sudo wpa_passphrase yourSSID yourwirelesspassword >> /etc/wpa_supplicant/wpa_supplicant.conf

I found I had reboot to activate.

sudo reboot

1	sudo reboot

Test SSH

OK you should be all ready to test connecting from your computer on the same lan via SSH

linux or Max terminal

ssh your-pi-hostname -l pi
password:  yourpasswordyouset

1 2	ssh your-pi-hostname -l pi password: yourpasswordyouset

If you are on Windows, you will probably use Putty for SSH.

and you should be in!

Install all the software needed

sudo apt-get update

1	sudo apt-get update

to make sure all is updated ( and you are connected to the internet )

sudo apt-get install chromium-browser

1	sudo apt-get install chromium-browser

to get Chromium

you will also need xinit

sudo apt-get install xinit

1	sudo apt-get install xinit

and xdotool

apt-get install xdotool

1	apt-get install xdotool

Setting up the X window

Before going full kiosk lets see if Chromium will launch.

edit the .xinitrc file in pi home directory

vi ~pi/.xinitrc

1	vi ~pi/.xinitrc

and input

#!/bin/sh
WIDTH=`xwininfo -root|grep Width | awk '{ print $2}'`
HEIGHT=`xwininfo -root|grep Height | awk '{ print $2}'`

exec chromium-browser --window-size=$WIDTH,$HEIGHT

#!/bin/sh

WIDTH=`xwininfo -root|grep Width | awk '{ print $2}'`

HEIGHT=`xwininfo -root|grep Height | awk '{ print $2}'`

exec chromium-browser --window-size=$WIDTH,$HEIGHT

The width and height are there as I found Chrome doesn’t know how to go full size. You can apparently to it in chromium defaults file, but this way is easier.

Now login on the console (not via SSH) via the keyboard and mouse attached to the pi and type

startx

startx

Chromium should launch and you should be able to browse the web

Set up the Kiosk

we add

xset s off
xset s noblank
xset -dpms

xset s off

xset s noblank

xset -dpms

to turn off screen savers

We add an hourly loop ( if required, or adjust the time in seconds ) to refresh the page (ctrl-F5)

(
while true
do
sleep  3600
xdotool key "ctrl+F5"
done
) &

(

while true

sleep 3600

xdotool key "ctrl+F5"

done

) &

and some more options to chromium –

-incognito --kiosk http://your-site

1	-incognito --kiosk http://your-site

I also found I needed this --disable-webgl as one webpage page wasn’t displaying properly, so the final file is

#!/bin/sh
WIDTH=`xwininfo -root|grep Width | awk '{ print $2}'`
HEIGHT=`xwininfo -root|grep Height | awk '{ print $2}'`
xset s off
xset s noblank
xset -dpms
(
while true
do
sleep  3600
xdotool key "ctrl+F5"
done
) &
exec chromium-browser --window-size=$WIDTH,$HEIGHT --disable-webgl --incognito --kiosk http://your-site

#!/bin/sh

WIDTH=`xwininfo -root|grep Width | awk '{ print $2}'`

HEIGHT=`xwininfo -root|grep Height | awk '{ print $2}'`

xset s off

xset s noblank

xset -dpms

(

while true

sleep 3600

xdotool key "ctrl+F5"

done

) &

exec chromium-browser --window-size=$WIDTH,$HEIGHT --disable-webgl --incognito --kiosk http://your-site

Start it on login

And then we want to auto start on login, I found the easiest was was to use .profile ( rc.local seemed to load too early )

vi ~pi/.profile

1	vi ~pi/.profile

and add

if [ -z "$SSH_TTY" ]; then
    startx -- -nocursor
fi

if [ -z "$SSH_TTY" ]; then

startx -- -nocursor

The if statement just stops us trying and failing to launch when an SSH login

-- -nocursor removes the mouse cursor

Tidy up boot up messages

Finally, when the PI is connected to the usb port of the monitor it will boot on power up, but there re lots of boot messages. I manged to get rid of most of them by editing cmdline.txt

sudo vi /boot/cmdline.txt

1	sudo vi /boot/cmdline.txt

and adding this to the single line

loglevel=0 logo.nologo quiet

1	loglevel=0 logo.nologo quiet

and also changing console=tty1 to console=tty3

Back it up

Nothing worse than doing all the work and then some how your SD card get corrupted – Backup the SD card.

Backup instructions here https://www.raspberrypi.org/documentation/linux/filesystem/backup.md

for linux users, put the card in your machine, find out what device it is and

sudo dd bs=4M if=/dev/sdbx of=raspbian.img

1	sudo dd bs=4M if=/dev/sdbx of=raspbian.img

where /dev/sdbx is the actual device name of your card of course

Did I miss anything?

I wrote this after the fact, so I could remember what I did if and when I do it again. I may have missed something, or something may need a better explanation, please use the comments box to let me know.

Linking WooCommerce to a Spreadsheet using Google Sheets and the REST API

Lets cover authentication first

Set up WooCommerce keys

Add the code to get the credentials

Getting the Orders into the spreadsheet.

WordPress REST API retrieve data and email it as an xls using Google Sheets

Stopping Genesis Full menu loading momentarily before the responsive menu

Sage Business Cloud Accounting – a new name for Sage One

Code snippet to change notification of new users

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

How to authenticate WordPress Rest API from Google Sheets / Scripts

Mod Security

WordFence

Other Security Software

Getting post meta via WordPress REST API

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

Recipe for a Raspberry Pi Kiosk

Ingredients

Preparation and Cooking time

Method

Preparation

First Boot

Basic Setup

Set up Wireless Access

Test SSH

Install all the software needed

Setting up the X window

Set up the Kiosk

Start it on login

Tidy up boot up messages

Back it up

Did I miss anything?

Categories

Subscribe to my Newsletter

Lets cover authentication first

Set up WooCommerce keys

Add the code to get the credentials

Getting the Orders into the spreadsheet.

Mod Security

WordFence

Other Security Software

Ingredients

Preparation and Cooking time

Method

Preparation

First Boot

Basic Setup

Set up Wireless Access

Test SSH

Install all the software needed

Setting up the X window

Set up the Kiosk

Start it on login

Tidy up boot up messages

Back it up

Did I miss anything?

Categories

Tags

Subscribe to my Newsletter