skip to Main Content

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

2

Answers


  1. Try

      var total_items = 0
      for (var i=0;i<myData.line_items.length;i++){
        total_items+=myData.line_items[i].quantity
      }
    
    Login or Signup to reply.
  2. 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 );
          }
        }
      }
    } 
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search