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

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

IMPORTANT NOTE

As of 22 Oct 2019 – this script seems to be in an infinite loop – so use this as a coding concept ONLY.

I will debug it in due course, and update – BUT if you spot the issue first please do leave me a comment below with your solution.

---

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) {
  onOpen(e);
}
function onOpen(e) {
  // setup a custom menu
  SpreadsheetApp.getUi().createAddonMenu() 
      .addItem('Get Orders', 'getPosts')
      .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');
}

  

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>

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

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

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

The other function, not covered yet is setupSheet all it is doing is clearing the 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');

}

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

}