Bug with Woocommerce Webhook and Google Sheet API?

Question

I have set up a link between Woocommerce orders (a ecommerce plugin for WordPress that we use for our NGO), and a Google Sheet table using this script in Google Sheet's script editor:

 //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_number = myData.number;
var order_created = myData.date_created;
var order_status = myData.status;
var order_total = myData.total;
var billing_first_name = myData.billing.first_name;
var billing_last_name  = myData.billing.last_name;
var billing_email = myData.billing.email;
var billing_phone = myData.billing.phone;
var shipping_first_name = myData.shipping.first_name;
var shipping_last_name = myData.shipping.last_name;
var shipping_address_1 = myData.shipping.address_1;
var shipping_address_2 = myData.shipping.address_2;
var shipping_postcode = myData.shipping.postcode;
var shipping_city = myData.shipping.city;
var shipping_country = myData.shipping.country;
var payment_method = myData.payment_method_title;
var currency = myData.currency;
var timestamp = new Date();

var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < myData.line_items.length; i++)
{ var product_sku = myData.line_items[i].sku;
var product_name = myData.line_items[i].name;
 var order_status       = myData.status;
var product_qty = myData.line_items[i].quantity;
var product_total = myData.line_items[i].total;
sheet.appendRow([order_created,order_number,order_status,payment_method,product_name,product_sku,product_qty,product_total,order_total,currency,billing_first_name,billing_last_name,billing_phone,billing_email,shipping_first_name,shipping_last_name,shipping_address_1,shipping_address_2,shipping_postcode,shipping_city,shipping_country]); }
}

Everything works as intended, every new order is populated in the Google Sheet table a few seconds later.

However, when I apply a filter on any column in Google Sheet, let's say for payment method, selecting "PayPal", no new order will populate the Google Sheet's table.
They are registered in the woocommerce plugin, payment is ok, all is fine, except that Google Sheet does not receive the order. Even after removing the filter, it doesn't appear.

All next orders will appear if all filters are deactivated in Google Sheet.

So, there is an issue with Google Sheet filters, but I don't know what is causing it. Is it my script? Is it Google API's fault? Woocommerce webhook?

Please note that I am not a developer, I found this script online and tweaked it myself by try and guess for my own needs.

Answer 1

Modification points:

• When the sheet of Google Spreadsheet uses the basic filter, when the values are put using appendRow(), the values are not appended. This might be the current specification.
  • I thought that this might be the reason of your issue.
  • In your script, the values are put using appendRow(), and appendRow() is used in a loop. In this case, the process cost of the script will become a bit high. When setValues() is used, this issue can be also removed.
• In this case, I would like to propose to append the values using setValues(). When setValues() is used, the values can be put to the filtered sheet. But, when the values are put to the filtered sheet, the filtered sheet is not changed while the values are put.
• So it is required to refresh the basic filter after the values are put.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please modify your script as follows.

From:

var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 0; i < myData.line_items.length; i++)
{ var product_sku = myData.line_items[i].sku;
var product_name = myData.line_items[i].name;
 var order_status       = myData.status;
var product_qty = myData.line_items[i].quantity;
var product_total = myData.line_items[i].total;
sheet.appendRow([order_created,order_number,order_status,payment_method,product_name,product_sku,product_qty,product_total,order_total,currency,billing_first_name,billing_last_name,billing_phone,billing_email,shipping_first_name,shipping_last_name,shipping_address_1,shipping_address_2,shipping_postcode,shipping_city,shipping_country]); }

To:

var sheet = SpreadsheetApp.getActiveSheet();
var values = [];
for (var i = 0; i < myData.line_items.length; i++) {
  var product_sku = myData.line_items[i].sku;
  var product_name = myData.line_items[i].name;
  var order_status = myData.status;
  var product_qty = myData.line_items[i].quantity;
  var product_total = myData.line_items[i].total;
  values.push([order_created,order_number,order_status,payment_method,product_name,product_sku,product_qty,product_total,order_total,currency,billing_first_name,billing_last_name,billing_phone,billing_email,shipping_first_name,shipping_last_name,shipping_address_1,shipping_address_2,shipping_postcode,shipping_city,shipping_country]);
}

// Put values using "setValues".
sheet.getRange(sheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);

// Refresh basic filter.
var filter = sheet.getFilter();
if (filter) {
  var range = filter.getRange();
  for (var i = range.getColumn(), maxCol = range.getLastColumn(); i <= maxCol; i++) {
    var filterCriteria = filter.getColumnFilterCriteria(i)
    if (filterCriteria) {
      filter.setColumnFilterCriteria(i, filterCriteria);
    }
  }
}

Note:

• When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

References:

• setValues(values)
• getLastRow()
• getFilter()
• Class Filter