CODEWITHSUNDEEP
wordpressgoogle-apps-scriptgoogle-sheetswoocommercewebhooks
Tom
Tom

Reputation: 131

Exporting order from Woocommerce webhook to google sheets with app

I found a google sheets app script to export orders from Woocommerce to google sheets using the Woocommerce webhook. After a small modification it looks like this:

//this is a function that fires when the webapp receives a GET request
function doGet(e) {
  return HtmlService.createHtmlOutput("request received");
}

//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  var myData             = JSON.parse([e.postData.contents]);
  var order_created      = myData.date_created;
  var billing_email      = myData.billing.email;
  var total_items        = myData.total.items;
  var order_total        = myData.total;
  var order_number       = myData.number;


  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([order_created,billing_email,total_items,order_total,order_number]);
}

The script works well but I would like to export the total of items (all products/ SKU) in an order. This should be done with the line

var total_items = myData.total.items;

But it doesn't work unfortunately (it doesn't show any data). I guess it's due to the "myData.total.items" part of the line.

Can someone help me to correct this line?

Another question, can someone tell me how I can cut of the timestamp from the date. The results look like "2021-06-07T11:30:02" (I would like to cut off : T11:30:02) Thanks

The situation at the moment in gsheets is: enter image description here ('items' is empty)

It should be like this: enter image description here This should calculate the qty of all items of an order not only the qty of 1 type of product of an order

//this is a function that fires when the webapp receives a GET request
function doGet(e) {
  return HtmlService.createHtmlOutput("request received");
}

//this is a function that fires when the webapp receives a POST request
function doPost(e) {
  var myData             = JSON.parse([e.postData.contents]);
  var order_created      = myData.date_created.split("T")[0];
  var billing_email      = myData.billing.email;
  var total_items = 0
  for (var i=0;i<myData.line_items.length;i++){
    total_items+=myData.line_items[i].quantity
  }
  var order_total        = myData.total;
  var order_number       = myData.number;

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([order_created,billing_email,total_items,order_total,order_number]);
}

Upvotes: 3

Views: 1109

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15318

I expect that by this way you can easily read the structure of the complete json

function doPost(e) {
  var myData             = JSON.parse([e.postData.contents]);
  getPairs(eval(myData),'myData')
}
function getPairs(obj,id) {
  const regex = new RegExp('[^0-9]+');
  const fullPath = true
  var sheet = SpreadsheetApp.getActiveSheet();
  for (let p in obj) {
    var newid = (regex.test(p)) ? id + '.' + p : id + '[' + p + ']';
    if (obj[p]!=null){
      if (typeof obj[p] != 'object' && typeof obj[p] != 'function'){
        sheet.appendRow([fullPath?newid:p, obj[p]]);
      }
      if (typeof obj[p] == 'object') {
        getPairs( obj[p], newid );
      }
    }
  }
}

Upvotes: 0

Mike Steelson
Mike Steelson

Reputation: 15318

Try

  var total_items = 0
  for (var i=0;i<myData.line_items.length;i++){
    total_items+=myData.line_items[i].quantity
  }

Upvotes: 0

Related Questions