skip to Main Content

The Woocommerce export to a Google Spreadsheet with the code below works. Except the format of the order total shows "99.95" instead of "99,95". This results in some faults in further processing the data. Is there a solution to fix this inside the Apps Script?

In short: The dot needs to be a comma automatically.

//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 order_total        = myData.total;
  var order_number       = myData.number;
  var billing_first_name = myData.billing.first_name;
  var billing_last_name = myData.billing.last_name;
  var shipping_country = myData.shipping.country;
  var payment_method = myData.payment_method_title;
  var billing_phone = myData.billing.phone;
  var shipping_address_1 = myData.shipping.address_1;
  var shipping_postcode = myData.shipping.postcode;
  var shipping_city = myData.shipping.city;

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([order_number,order_created,order_total,billing_first_name,billing_last_name,billing_email,billing_phone,shipping_address_1,shipping_city,shipping_postcode,shipping_country,payment_method]);
}

2

Answers


  1. Automatically change the "." to ",".

    I have limited access with Woocommerce that you are using. Instead I have just created a simple code that will automatically change it on Google Apps Script.

    Sample Code:

    function stringCleanser (cleansedData){
    
    var cleansedData = cleansedData.toString().replace(/./g, ",");// Converts it to string, then replace "." with ","
    return cleansedData;
    }
    

    you can use this to call the function whenever needed. I.E. stringCLeanser(order_total); this should automatically create your desired output.

    Test Run:

    enter image description here

    References:

    ToString

    Replace

    Login or Signup to reply.
  2. By default, culture-specific formatting defaults are based on the spreadsheet’s locale. My experience with the Sheets API is very limited, but you should be able to update the properties value directly through the var spreadsheet = SpreadsheetApp.getActiveSpreadsheet() resource (default is "en_US").

    From the docs:

    The locale of the spreadsheet in one of the following formats:

    • an ISO 639-1 language code such as en
    • an ISO 639-2 language code such as fil, if no 639-1 code exists
    • a combination of the ISO language code and country code, such as en_US

    Or, do it directly in your POST (format patterns are at the link I included above):

    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange('D:D').setNumberFormat('0,00'); // assuming order total is column D
    
    // append as usual...
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search