Get woocommerce json order metadata key values ​with google apps script (REST API)

Question

I am connecting my woocommerce store with Google Sheets to sync orders via REST API and google apps script (with Customer key & Client secret key). The connection is correct and the data is synchronizing well. The problem I have is that I want to synchronize some specific meta_data order and try as I try, I can't.

Here the Json Data: https://pastebin.com/Q3zkm4rW

Specifically I want to get the value of this order meta_data:

 "key": "_wccf_of_fecha_envio"
 "key": "_wccf_of_empresa_mensajeria" 
 "key": "_wccf_of_numseguimiento_pedido" 
 "key": "_wcpdf_invoice_date_formatted" 
 "key": "_wcpdf_invoice_number"
 "key": "seguimiento" (in this option the 3 values, transportista, codigo, fecha).

How can I get the metadata values ​​mentioned above? I'm new to javascript and sometimes I can't find the solution on my own. I have looked through google and stackoverflow but although I have seen similar cases and tried to replicate the case, I have not succeeded.

All help is greatly appreciated. Thank you in advance.

This is my google apps script complete code:

function start_syncv2() {
    var sheet_name = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Pedidos2');
    fetch_orders(sheet_name)
}



function fetch_orders(sheet_name) {

    var ck = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ajustes').getRange("C10").getValue();
    var cs = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ajustes').getRange("C11").getValue();
    var website = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ajustes').getRange("C9").getValue();
    var manualDate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ajustes').getRange("C12").getValue(); 
    var m = new Date(manualDate).toISOString();
    var surl = website + "/wp-json/wc/v2/orders?consumer_key=" + ck + "&consumer_secret=" + cs + "&after=" + m + "&per_page=100"; 
    var url = surl
    Logger.log(url)

    var options =

        {
            "method": "GET",
            "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
            "muteHttpExceptions": true,

        };

    var result = UrlFetchApp.fetch(url, options);

    Logger.log(result.getResponseCode())
    if (result.getResponseCode() == 200) {

        var params = JSON.parse(result.getContentText());
        Logger.log(result.getContentText());

    }

    var doc = SpreadsheetApp.getActiveSpreadsheet();

    var temp = doc.getSheetByName(sheet_name);

    var consumption = {};

    var arrayLength = params.length;
    Logger.log(arrayLength)
    for (var i = 0; i < arrayLength; i++) {
        Logger.log("dfsfsdfsf")
        var a,c,d,n,o;
        var container = [];


        n = params[i]["meta_data"].length;
        var numFactura = "";
        var fechaFactura = "";
        var fechaEnvio = "";
        var mensajeria = "";
        var numSeguimiento = "";

          for (var h = 0; h < n; h++) {
            var key;
              key = params[i]["meta_data"][h]["key"];
              o = params[i]["key"].length;
                for (var n = 0; n < o; n++) {
                  numFactura = params[i]["meta_data"][h]["key"][n]["_wcpdf_invoice_number"];
                  fechaFactura = params[i]["meta_data"][h]["key"][n]["_wcpdf_invoice_date_formatted"];
                  fechaEnvio = params[i]["meta_data"][h]["key"][n]["_wccf_of_fecha_envio"];
                  mensajeria = params[i]["meta_data"][h]["key"][n]["_wccf_of_empresa_mensajeria"];
                  numSeguimiento = params[i]["meta_data"][h]["key"][n]["_wccf_of_numseguimiento_pedido"];
                }                        
          }

        a = container.push(numFactura);
        a = container.push(fechaFactura);
        a = container.push(fechaEnvio);
        a = container.push(mensajeria);
        a = container.push(numSeguimiento);


        a = container.push(params[i]["number"]); // Nº Pedido
        a = container.push(params[i]["date_created"]);  // Fecha pedido
        a = container.push(params[i]["status"]); // Estado del pedido


        a = container.push(params[i]["billing"]["first_name"] + " " + params[i]["billing"]["last_name"]);  // Nombre y apellidos del cliente
        Logger.log(a)

        a = container.push(params[i]["billing"]["email"]);  // Email

        a = container.push(params[i]["subtotal"]); // Subtotal

        var doc = SpreadsheetApp.getActiveSpreadsheet();

        var temp = doc.getSheetByName('Pedidos2');

        temp.appendRow(container);
     
        removeDuplicates(sheet_name);
    }
}

Answer 1

You should no iterate params[i]["meta_data"][h]["key"].

Instead it is a value or object you want to append to the sheet.

By the way, there is something missing on your json sample.

I have tried to recover it in my example.

const params = sample();
const i = 0;

const target = ['_wccf_of_fecha_envio',  '_wccf_of_empresa_mensajeria', '_wccf_of_numseguimiento_pedido', '_wcpdf_invoice_date_formatted', '_wcpdf_invoice_number', {'seguimiento': ['transportista', 'codigo', 'fecha']}];
/* for loop of params */
const meta_data = {};
for (const e of params[i]['meta_data'])  {
  meta_data[e.key] = e.value;
}
console.log(meta_data);

var container = [];
for (const key of target) {
  if (typeof key === 'object') {
    const k = Object.keys(key)[0];
    const values = meta_data[k];
    if (!values) {
      container.push([...new Array(key[k].length)]);
      continue; 
    }
    for (const value of key[k]) {
      container.push(meta_data[k][value] || '');
    }
  }
  else {
    container.push(meta_data[key] || '');
  }
}
console.log(container);

/* sample json for test */
function sample() {
  return [{"id":3506,"parent_id":0,"number":"134","order_key":"wc_order_2V3MYLlkjasd887","created_via":"checkout","version":"3.5.7","status":"completed","currency":"EUR","date_created":"2019-05-31T19:56:21","date_created_gmt":"2019-05-31T18:56:21","date_modified":"2020-12-30T20:32:00","date_modified_gmt":"2020-12-30T19:32:00","discount_total":"0.00","discount_tax":"0.00","shipping_total":"4.55","shipping_tax":"0.00","cart_tax":"0.00","total":"13.81","total_tax":"0.00","prices_include_tax":true,"customer_id":0,"customer_ip_address":"83.XX.XX.XXX","customer_user_agent":"Mozilla/5.0 (Linux; Android 8.1.0; SM-J710F) AppleWebKit/XXX.XX (KHTML, like Gecko) Chrome/74.0.XXXX.XXX Mobile Safari/XXX.XX","customer_note":"llamar al XXXXXXXXX pues trabajo en la misma poblacion","billing":{"first_name":"Mari XXXX","last_name":"LorXXX XXXXX","company":"","address_1":"c/ de los Amigos, nº 1","address_2":"","city":"Ciudad de los Amigos","state":"V","postcode":"46XXX","country":"ES","email":"[email protected]","phone":"6XXXXXXXX"},"shipping":{"first_name":"Mari XXXX","last_name":"LorXXX XXXXX","company":"","address_1":"c/ de los Amigos, nº 1","address_2":"","city":"Ciudad de los Amigos","state":"V","postcode":"46XXX","country":"ES"},"payment_method":"stripe","payment_method_title":"Tarjeta de crédito/débito","transaction_id":"ch_XXXXXXXXXXXXXXXXXXXXXXXXXX","date_paid":"2019-08-28T19:56:25","date_paid_gmt":"2019-08-28T18:56:25","date_completed":"2019-08-29T00:35:16","date_completed_gmt":"2019-08-29T23:35:16","cart_hash":"eafxxxxXXXXXxxxxxXXXXXXxxxx30cb","meta_data":[{"id":365723,"key":"_wcson_order_number","value":"134"},{"id":365724,"key":"klaviyo-response","value":{"headers":[],"body":"","response":{"code":false,"message":false},"cookies":[],"http_response":null}},{"id":365725,"key":"terms","value":"on"},{"id":365726,"key":"aniley_checkout_privacy_policy","value":"on"},{"id":365727,"key":"_stripe_source_id","value":"src_1ExxxxxXXXXXXXXXxxxxxxXXXXXXXxxwk"},{"id":365728,"key":"_stripe_charge_captured","value":"yes"},{"id":365729,"key":"_stripe_fee","value":"0.48"},{"id":365730,"key":"_stripe_net","value":"16.23"},{"id":365731,"key":"_stripe_currency","value":"EUR"},{"id":365811,"key":"aniley_nifcif","value":""},{"id":366541,"key":"_vc_post_settings","value":{"vc_grid_id":[]}},{"id":366573,"key":"_wccf_of_cuenta_instagramm","value":""},{"id":366574,"key":"_wccf_of_id_cuenta_instagramm","value":"1629"},{"id":366575,"key":"_wccf_of_data_cuenta_instagramm","value":[]},{"id":366576,"key":"_wccf_of_order_devolucionproductos","value":""},{"id":366577,"key":"_wccf_of_id_order_devolucionproductos","value":"809"},{"id":366578,"key":"_wccf_of_data_order_devolucionproductos","value":[]},{"id":366579,"key":"_wccf_of_fecha_envio","value":"29/9/2019"},{"id":366580,"key":"_wccf_of_id_fecha_envio","value":"477"},{"id":366581,"key":"_wccf_of_data_fecha_envio","value":[]},{"id":366582,"key":"_wccf_of_empresa_mensajeria","value":"Correos Express"},{"id":366583,"key":"_wccf_of_id_empresa_mensajeria","value":"788"},{"id":366584,"key":"_wccf_of_data_empresa_mensajeria","value":[]},{"id":366585,"key":"_wccf_of_order_envio_bultos","value":"1"},{"id":366586,"key":"_wccf_of_id_order_envio_bultos","value":"795"},{"id":366587,"key":"_wccf_of_data_order_envio_bultos","value":[]},{"id":366588,"key":"_wccf_of_numseguimiento_pedido","value":"3522XXXXXXXXXXXXX50"},{"id":366589,"key":"_wccf_of_id_numseguimiento_pedido","value":"458"},{"id":366590,"key":"_wccf_of_data_numseguimiento_pedido","value":[]},{"id":366591,"key":"_wccf_of_seguir_paquete","value":"<a href=\"https://s.correosexpress.com/search?shippingNumber=3522XXXXXXXXXXXXX50\" target=\"_blank\" style=\"color: #626262;text-decoration: none;padding: 10px 15%;font-weight: 600;\">VER EL ESTADO DE MI PEDIDO</a>"},{"id":366592,"key":"_wccf_of_id_seguir_paquete","value":"428"},{"id":366593,"key":"_wccf_of_data_seguir_paquete","value":[]},{"id":366594,"key":"_wccf_of_fecha_entrega","value":"30/08/2019"},{"id":366595,"key":"_wccf_of_id_fecha_entrega","value":"3436"},{"id":366596,"key":"_wccf_of_data_fecha_entrega","value":[]},{"id":366597,"key":"_wccf_of_observaciones_envio","value":""},{"id":366598,"key":"_wccf_of_id_observaciones_envio","value":"2341"},{"id":366599,"key":"_wccf_of_data_observaciones_envio","value":[]},{"id":366600,"key":"_wcpdf_invoice_date","value":"15XXXXXXXX8"},{"id":366601,"key":"_wcpdf_invoice_date_formatted","value":"2019-08-31 21:57:18"},{"id":366602,"key":"_wcpdf_invoice_number","value":"19A000134"},{"id":366603,"key":"_wcpdf_invoice_number_data","value":{"number":134,"formatted_number":"19A000134","prefix":"19","suffix":"A","document_type":"invoice","order_id":3506,"padding":"6"}},{"id":443419,"key":"seguimiento","value":{"transportista":"correos-x","codigo":"32XXXXXXXXXXXXX50","fecha":"2019-08-29"}},{"id":443423,"key":"_wccf_of_cuenta_instagram","value":""},{"id":443424,"key":"_whatsappnotif_checkout_field","value":""},{"id":443425,"key":"_whatsappnewsletter_checkout_field","value":""}],"line_items":[{"id":719,"name":"Collar identificativo para perro personalizado - XS","product_id":2821,"variation_id":2822,"quantity":1,"tax_class":"","subtotal":"9.26","subtotal_tax":"0.00","total":"9.26","total_tax":"0.00","taxes":[],"meta_data":[{"id":5611,"key":"selecciona-talla","value":"XS"},{"id":5612,"key":"_tmcartepo_data","value":[{"mode":"builder","cssclass":"","hidelabelincart":"","hidevalueincart":"","hidelabelinorder":"","hidevalueinorder":"","name":"¿Qué color quieres para la tela?","value":"Rojo","price":0,"section":"5c6f7ae8f1e0e6.13293053","section_label":"¿Qué color quieres para la tela?","percentcurrenttotal":0,"currencies":[],"price_per_currency":{"EUR":""},"quantity":"1","multiple":"1","key":"Rojo_1","use_images":"","use_colors":"","changes_product_image":"","imagesp":"","images":"","color":""},{"mode":"builder","cssclass":"","hidelabelincart":"","hidevalueincart":"","hidelabelinorder":"","hidevalueinorder":"","name":"¿Que quieres agregar?","value":"5) Teléfono + decoraciones","price":0,"section":"5c6f7ae8f1e0e6.28504484","section_label":"¿Que quieres agregar?","percentcurrenttotal":0,"currencies":[],"price_per_currency":{"EUR":""},"quantity":"1","multiple":"1","key":"5) Teléfono + decoraciones_4","use_images":"","use_colors":"","changes_product_image":"","imagesp":"","images":"","color":""},{"mode":"builder","cssclass":"tipografiadelnombre","hidelabelincart":"","hidevalueincart":"","hidelabelinorder":"","hidevalueinorder":"","name":"Teléfono","value":"626448537 ","price":0,"section":"5c6f7ae8f1e158.62582178","section_label":"Teléfono","percentcurrenttotal":0,"currencies":[],"price_per_currency":[],"quantity":"1"},{"mode":"builder","cssclass":"colordelnombre","hidelabelincart":"","hidevalueincart":"","hidelabelinorder":"","hidevalueinorder":"","name":"Color del vinilo","value":"Azul cielo (Ref. 28)","price":0,"section":"5c6f7ae8f1e0f4.91684260","section_label":"Color del vinilo","percentcurrenttotal":0,"currencies":[],"price_per_currency":{"EUR":""},"quantity":"1","multiple":"1","key":"Azul cielo (Ref. 28)_6","use_images":"","use_colors":"","changes_product_image":"","imagesp":"","images":"","color":""},{"mode":"builder","cssclass":"tipografiadelnombre","hidelabelincart":"","hidevalueincart":"","hidelabelinorder":"","hidevalueinorder":"","name":"Tipografía","value":"superstar","price":0,"section":"5c6f7ae8f1e162.34549333","section_label":"Tipografía","percentcurrenttotal":0,"currencies":[],"price_per_currency":[],"quantity":"1"},{"mode":"builder","cssclass":"","hidelabelincart":"","hidevalueincart":"","hidelabelinorder":"","hidevalueinorder":""}]},{"id":5613,"key":"_tm_epo_product_original_price","value":["14"]},{"id":5614,"key":"_tm_epo","value":[1]},{"id":100000,"key":"¿Qué color quieres para la tela?","value":"Rojo"},{"id":100001,"key":"¿Que quieres agregar?","value":"5) Teléfono + decoraciones"},{"id":100002,"key":"Teléfono","value":"6XXXXXXXXX7 "},{"id":100003,"key":"Color del vinilo","value":"Azul cielo (Ref. 28)"},{"id":100004,"key":"Tipografía","value":"superstar"},{"id":100005,"key":"Escoge un estilo","value":"Estrella"}],"sku":"2822","price":9.256198}],"tax_lines":[],"shipping_lines":[{"id":720,"method_title":"Correos Expréss - Paq 24h","method_id":"flat_rate","instance_id":"1","total":"4.55","total_tax":"0.00","taxes":[],"meta_data":[{"id":5620,"key":"Artículos","value":"Collar identificativo para perro personalizado - XS &times; 1"}]}],"fee_lines":[],"coupon_lines":[],"refunds":[],"_links":{"self":[{"href":"https://yourwebsite.com/wp-json/wc/xxxxxxxxxxxxxxxx"}],"collection":[{"href":"https://yourwebsite.com/wp-json/wc/xxxxxxxxxxxxxxxx"}]}}];
}