I´ve been cracking my head for days to find the most elegant way to parse a Json string and write it to google sheets. It derives from a webhook off woocommerce.

I have a Json like the following:

"product_variations": [
{
  "id": 47131,
  "on_sale": false,
  "regular_price": 1678,
  "sale_price": 0,
  "sku": "GexKiPe-Linen",
  "quantity": "",
  "stock": null,
  "attributes": [
    {
      "name": "color",
      "slug": "pa_color",
      "option": "linen"
    },
    {
      "name": "material",
      "slug": "pa_material",
      "option": "permanent"
    }
  ]
},
{
  "id": 47132,
  "on_sale": false,
  "regular_price": 1678,
  "sale_price": 0,
  "sku": "GexKiPe-Heather Beige",
  "quantity": "",
  "stock": null,
  "attributes": [
    {
      "name": "color",
      "slug": "pa_color",
      "option": "heather-beige"
    },
    {
      "name": "material",
      "slug": "pa_material",
      "option": "permanent"
    }
  ]
}

An apps script like the following:

    function doPost(e) {
  var data = JSON.parse(e.postData.contents);
      var res = data.product_variations.map(({id, sku, attributes: [{name, option}]}) => [new Date(),id, sku, name, option]);
         var sheet = SpreadsheetApp.openById('1x5m427kv_viOLm7nsJRl1FR0_-e5tOcK-ItEVw0LdEY').getSheetByName('test');
          sheet.getRange(sheet.getLastRow()   1, 1, res.length, res[0].length).setValues(res);

}

The results in sheets are:

I would like the results to show like:

Here is the spreadsheet: Parse Json

Any help will be greatly appreciated

CodePudding user response：

Try this

I tested it with the lines that are commented out. So I have no idea what will happen with the others.

function doPost(e) {
  const data = JSON.parse(e.postData.contents);
  //const test = '{ "product_variations": [{ "id": 47131, "on_sale": false, "regular_price": 1678, "sale_price": 0, "sku": "GexKiPe-Linen", "quantity": "", "stock": null, "attributes": [ { "name": "color", "slug": "pa_color", "option": "linen" }, { "name": "material", "slug": "pa_material", "option": "permanent" } ]},{ "id": 47132, "on_sale": false, "regular_price": 1678, "sale_price": 0, "sku": "GexKiPe-Heather Beige", "quantity": "", "stock": null, "attributes": [ { "name": "color", "slug": "pa_color", "option": "heather-beige" }, { "name": "material", "slug": "pa_material", "option": "permanent" }]}]}';
  //const data = JSON.parse(test);
  var res = data.product_variations.map(obj => [new Date(), obj.id, obj.sku, obj.attributes[0].option, obj.attributes[1].option]);
  var sheet = SpreadsheetApp.openById('1x5m427kv_viOLm7nsJRl1FR0_-e5tOcK-ItEVw0LdEY').getSheetByName('Sheet0');
  //const ss = SpreadsheetApp.getActive();
  //const sheet = ss.getSheetByName("Sheet0");
  res.unshift(["DateTime","ID","SKU","color","material"]);
  sheet.getRange(sheet.getLastRow()   1, 1, res.length, res[0].length).setValues(res);
}

Test Results:

JSON Validator

CodePudding user response：

This is the final code. It works like a charm:

    function doPost(e) {

  var data = JSON.parse(e.postData.contents);

    
var search = "color"
var search2 = "material"
  var res = data.product_variations.map(obj => [new Date(),data.acf.product_id,data.id,data.sku,obj.id,obj.sku,obj.regular_price,obj.attributes.filter(function (f) { return f.name == search })[0].option, obj.attributes.filter(function (f) { return f.name == search2 })[0].option]);
  var sheet = SpreadsheetApp.openById('1F8pgmiSJV0K66lbFO343O8H_LaIi0CGfrukXhD-Gw3g').getSheetByName('woo product variations');
  sheet.getRange(sheet.getLastRow()   1, 1, res.length, res[0].length).setValues(res);
}