Category: Useful Stuff

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

}

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.

14 places to get Free Stock Images for Websites

8th July 2016 by Alan Leave a Comment

Sourcing images for websites is a tricky and time consuming businesses.

The worse thing you can do is go to Google, find and image you like and download it and upload it to your website. It is not rare that the copyright owner will find you and request payment for the use you ‘stole’ from them. Companies like Getty Images do this on a regular basis, and their fees are high. Also, you can’t even blame your web designer, as you are responsible fir your website, so if your web designer uses stock images for you, you better check he has the rights to do so.

The best thing, always, is commission your own images. Your own genuine images on your website convey a much better image of your business than stock images.

However, there are times when stock images are really useful

To bring to life blog posts
As background images

To name a couple.

So you came here looking for free stock images, and one of the best ways is to sign up for a free trial with a major stock image company.

My favourite is BigStock and you can sign up for a 14 day Free Trial with BigStock here

Once your free trail has expired, if you don’t want to continue then here are some sites that offer free stock images, I can’t personally warrant they are really copyright free, but I would hope so, but do double check.

I hope the list is helpful.

Please leave comments on the comment form if you discover any more or have issues with the above list.

Conditionally remove header widget from Genesis

12th March 2015 by Alan Leave a Comment

Genesis comes with a header widget area built in.

Occasionally you may want to remove this for specific pages.

The following code snippet in functions.php will do the trick

add_action( 'genesis_meta', 'my_remove_hr');
function my_remove_hr() {
if ( is_page( 'privacy-cookies' ) ) {
     unregister_sidebar( 'header-right' );
     }
}

add_action( 'genesis_meta', 'my_remove_hr');

function my_remove_hr() {

if ( is_page( 'privacy-cookies' ) ) {

unregister_sidebar( 'header-right' );

}

The if statement will need to be adjusted depending on need, see http://codex.wordpress.org/Conditional_Tags to see what conditions could be applied.

If you want to remove the Header Right sidebar on the whole site the code is simpler, just add this to your functions.php

unregister_sidebar( 'header-right' );

1	unregister_sidebar( 'header-right' );

You can find other header code snippets here on the Studio Press site http://my.studiopress.com/snippets/header/

Allow html in user description

12th March 2015 by Alan Leave a Comment

The WordPress user bio only allows a very restricted set html. Occasionally, it is desirable to be able to format the WordPress user bio with additional html. Out of the box that is not possible.

However to allow additional html in the WordPress user bio, like I have with some <p> and <br> to break things up it is fairly easy to do.

Add the following code to your theme’s functions.php file.

//disable WordPress sanitization to allow more than just $allowedtags from /wp-includes/kses.php
remove_filter('pre_user_description', 'wp_filter_kses');
//add sanitization for WordPress posts
add_filter( 'pre_user_description', 'wp_filter_post_kses');

//disable WordPress sanitization to allow more than just $allowedtags from /wp-includes/kses.php

remove_filter('pre_user_description', 'wp_filter_kses');

//add sanitization for WordPress posts

add_filter( 'pre_user_description', 'wp_filter_post_kses');

And then you will be able to add html to your user bio on WordPress.

RightMove BLM file import to WordPress

3rd March 2015 by Alan 1 Comment

Rightmove BLM file converter Plugin is now available to download

We are pleased to say, that the plugin is now available to purchase.

It is listed at https://fullworks.net/products/rightmove-blm-to-xml-converter/

I do hope you will find this very useful.

Plugin for Right Move BLM file imports to WordPress

Right Move is a property portal used in the UK and Europe. It has an interface file specification called .blm which is defined in the Rightmove Automated Datafeed Specification .

Many Estate agent back office systems generate .blm RightMove file format for uploading to RightMove and if you have a back office system it makes sense to use the same data feed to your own estate agency website, so keeping the data exactly in sync between your backoffice, RightMove (and other Portals) and your own website.

WordPress is such a popular platform, with multiple themes and plugins for property agents. The major players in the plugin stake are Easy Property Listings, WP-Property and for Genesis, Agent Press listings.

Considering the variety of designs of plugins ( & themes) that support properties, a sensible design would be to write a plugin that converts .blm to xml format and then use the excellent WP All Import to do the heavy lifting of the creation of the custom post types used by the particular plugins and themes. This makes the solution modular in as far as the conversion to XML is separate from the structures used by the property plugins. And with WP All Import Pro version, you can trigger cron jobs to fully automate the upload.

I have developed a plugin based on Rightmove Automated Datafeed Specification Version 3 and have used it based on the output of a property agent backoffice system,Gemini VTUK , and used it to import to Easy Property Listings via WP All Import Pro.

The setup documentation is here -> https://badlywired.com/2015/04/rightmove-blm-to-xml-convertor-documentation/

Plugin is now available to download

We are pleased to say, that the plugin is now available to purchase.

It is listed at https://fullworks.net/products/rightmove-blm-to-xml-converter/

I do hope you will find this very useful.

CSS styled slider buttons, with CSS triangles

25th February 2015 by Alan Leave a Comment

You can style slider buttons using css. A simple clean style is arrows with a square box ( or a circle ).

An example is like this

The code is like this

<a href="#"><div class='arrow-wrap left'>
    
    </div></a>

<a href="#"><div class='arrow-wrap right'>
    
    </div></a>

</div></a>

</div></a>

Which sets up the links and a div per link

then the css sets the size of the divs and adds a background, and uses after: to create a css triangle and then positions it

.arrow-wrap.right:after {
    content:'';
    position: absolute;
    top: 4px;
    left: 9px;
    border-style: solid;
    border-width: 9px 0 9px 9px;
    border-color: transparent transparent transparent #fff;
}
.arrow-wrap.left:after {
    content:'';
    position: absolute;
    top: 4px;
    left: 7px;
    border-style: solid;
    border-width: 9px 9px 9px 0;
    border-color: transparent #fff transparent transparent;
}
.arrow-wrap {
    position: relative;
    height: 25px;
    width: 25px;
    background: red;
}

.arrow-wrap.right:after {

content:'';

position: absolute;

top: 4px;

left: 9px;

border-style: solid;

border-width: 9px 0 9px 9px;

border-color: transparent transparent transparent #fff;

}

.arrow-wrap.left:after {

content:'';

position: absolute;

top: 4px;

left: 7px;

border-style: solid;

border-width: 9px 9px 9px 0;

border-color: transparent #fff transparent transparent;

}

.arrow-wrap {

position: relative;

height: 25px;

width: 25px;

background: red;

}

See http://jsfiddle.net/locally/LL5dczoq/

If you prefer round buttons, you can use border radius, and if you want to give it a bit of feel on hover then you can apply some colour tweaks to the css triangles

a bit like this

.arrow-wrap.left:hover:after {
    border-color: transparent grey transparent transparent;
}
.arrow-wrap.right:hover:after {
    border-color: transparent transparent transparent grey;
}
.arrow-wrap {
    position: relative;
    height: 25px;
    width: 25px;
    background: red;
    border-radius: 50px;
}

.arrow-wrap.left:hover:after {

border-color: transparent grey transparent transparent;

}

.arrow-wrap.right:hover:after {

border-color: transparent transparent transparent grey;

}

.arrow-wrap {

position: relative;

height: 25px;

width: 25px;

background: red;

border-radius: 50px;

}

see http://jsfiddle.net/locally/m9cqv18v/

I have no idea how cross browser safe this is and if anything needs to be tweaked. i would guess IE 6 is out and maybe IE will have issues in other versions. Please leave me comments if you have cross browser tweaks.

Developer Stuff

23rd February 2015 by Alan Leave a Comment

HTML Frameworks
1. Bootstrap
2. Foundation
3. HTML5 Boilerplate
4. Skeleton

CSS Preprocessors
1. LESS
2. SASS
3. Stylus

JavaScript Libraries
1. jQuery
2. LoDash
3. ReactJS

JavaScript Frameworks
1. AngularJS
2. Ember
3. Meteor
4. Polymer

Module Loaders
1. Webpack
2. Browserify
3. Require.js

JavaScript Preprocessors
1. Babel (6to5)
2. CoffeeScript
3. TypeScript

Module Loader Comparison + Pros / Cons

Build Tools
1. Gulp
2. Grunt

PHP CMS
1. Concrete5
2. WordPress
3. Drupal
4. Silverstripe
5. Process Wire
6. MODX

PHP Frameworks
1. Laravel
2. Symfony
3. Silex
4. Zend
5. CodeIgniter
6. Yii
7. CakePHP

PHP Tools
1. Composer

Code Editors
1. PHPStorm
2. Sublime Text
3. Brackets
4. Atom

General Resources
1. Font Awesome
2. Mailtrap
3. BitBucket
4. Asana
5. Laracasts
6. Skills Matter (Skillcasts)

Applications
1. Dash
2. Sequel Pro

Dev Environment
1. Vagrant
2. Vagrant DNSMasq
3. puPHPet
4. Ansible
5. Fig

Other Languages / Frameworks
1. Ruby on Rails
2. Django

Google not able to fetch robots.txt

27th January 2015 by Alan Leave a Comment

Recently I was getting a message from Web Master Tools, Googlebot can’t access your site!

Over the last 24 hours, Googlebot encountered 87 errors while attempting to access your robots.txt. To ensure that we didn’t crawl any pages listed in that file, we postponed our crawl. Your site’s overall robots.txt error rate is 64.4%

Recommended action

If the site error rate is 100%:Using a web browser, attempt to access http://xxxxx.com/robots.txt. If you are able to access it from your browser, then your site may be configured to deny access to googlebot. Check the configuration of your firewall and site to ensure that you are not denying access to googlebot.

If your robots.txt is a static page, verify that your web service has proper permissions to access the file.

If your robots.txt is dynamically generated, verify that the scripts that generate the robots.txt are properly configured and have permission to run. Check the logs for your website to see if your scripts are failing, and if so attempt to diagnose the cause of the failure.

If the site error rate is less than 100%:Using Webmaster Tools, find a day with a high error rate and examine the logs for your web server for that day. Look for errors accessing robots.txt in the logs for that day and fix the causes of those errors.

The most likely explanation is that your site is overloaded. Contact your hosting provider and discuss reconfiguring your web server or adding more resources to your website.

If your site redirects to another hostname, another possible explanation is that a URL on your site is redirecting to a hostname whose serving of its robots.txt file is exhibiting one or more of these issues.

After you’ve fixed the problem, use Fetch as Google to fetch http://xxxxx.com/robots.txt to verify that Googlebot can properly access your site.

But when I manually looked at my robots.txt in my browser, all looked fine!

In WMT when I fetched this, it was coming back inaccessible. This bamboozled me for a little while. I did some seraching and found others had similar issues, but no one seemed to get to the root of the problem.

So I stated to look at my .htaccess file and recalled that I was redirecting my whole site to HTTPS ( see this post ) and though perhaps that was the issue.

The next step was to exclude robots.txt from the HTTPS redirect. And when testing in WMT all was well and Googlebot fetched fine. My problem solved. All I had to do was tweak my .htaccess file as follows

RewriteEngine On

# condition if https
RewriteCond %{HTTPS} off

# condition to exclude robots.txt from the condition
RewriteCond %{REQUEST_FILENAME} !robots\.txt

# rule to force https
RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

RewriteEngine On

# condition if https

RewriteCond %{HTTPS} off

# condition to exclude robots.txt from the condition

RewriteCond %{REQUEST_FILENAME} !robots\.txt

# rule to force https

RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

But then, I realised perhaps this was not required.

What is really required is to just have my https://mysite.com registered in WMT not the http:// version!

Thoughts?

Forcing a site to HTTPS using .htaccess

27th January 2015 by Alan Leave a Comment

Occasionally you may want a site to be fully https.

Especially since Google recently said that they may prefer HTTPS sites as getting a certificate is a costly exercise so spammers are less likely to adopt SSL.

Within .htaccess the code is as follows

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

RewriteEngine On

RewriteCond %{HTTPS} off

RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} [R=301,L]

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.

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?

14 places to get Free Stock Images for Websites

Conditionally remove header widget from Genesis

Allow html in user description

RightMove BLM file import to WordPress

Rightmove BLM file converter Plugin is now available to download

Plugin for Right Move BLM file imports to WordPress

Plugin is now available to download

CSS styled slider buttons, with CSS triangles

Developer Stuff

Google not able to fetch robots.txt

Forcing a site to HTTPS using .htaccess

Categories

Subscribe to my Newsletter