How to parse a JSON object with nested arrays in google apps script

Question

I'm having difficulty parsing a JSON string with nested arrays. here is an example of the JSON

var json = {
"id": "123456", 
"cost_name":"john", 
"line_item":[{
"item_name":"table", "quantity":"1", "properties":[{
"color":"black", "style":"rustic"
}]},
 {
"item_name":"chair", "quantity":"3", "properties":[{
"color":"white", "style":"modern"
}]}],
"address":"123_street"
 }

I need to get the item_name and quantity of each line_item and I also need the color and style of each

I'm receiving this JSON from a webhook so the order is never the same.

ADDED CONTEXT: (@Taineke's request)

I'm trying to write this to a google sheet with apps script here is my code.

function doPost(e) {
  var ss = SpreadsheetApp.getActiveSheet();
  var data = JSON.parse(e.postData.contents);
  
//extract data here
var I= item_name;
var Q = quantity;
var C = color;
var S = style;
  
  ss.appendRow([I,Q,C,S])
}

HERE IS UPDATED e.postData.contents (that @Tanaike) requested from a test webhook

{"id":3175309607101,"email":"[email protected]","closed_at":null,"created_at":"2021-01-05T21:35:06-05:00","updated_at":"2021-01-05T21:35:08-05:00","number":1586,"note":"","token":"3491883c672f110eaab82f8dd8080052","gateway":null,"test":false,"total_price":"0.00","subtotal_price":"0.00","total_weight":0,"total_tax":"0.00","taxes_included":false,"currency":"USD","financial_status":"paid","confirmed":true,"total_discounts":"18.00","total_line_items_price":"18.00","cart_token":"a3c9cb049e2f631a8393cf37547623e2","buyer_accepts_marketing":false,"name":"#2586","referring_site":"","landing_site":"\/","cancelled_at":null,"cancel_reason":null,"total_price_usd":"0.00","checkout_token":"11d27a9399b514cb6ba246a3fffc7b23","reference":null,"user_id":null,"location_id":null,"source_identifier":null,"source_url":null,"processed_at":"2021-01-05T21:35:02-05:00","device_id":null,"phone":null,"customer_locale":"en","app_id":580111,"browser_ip":"172.58.238.224","client_details":{"accept_language":"en-US,en;q=0.9","browser_height":657,"browser_ip":"172.58.238.224","browser_width":1349,"session_hash":null,"user_agent":"Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/87.0.4280.88 Safari\/537.36"},"landing_site_ref":null,"order_number":2586,"discount_applications":[{"type":"discount_code","value":"100.0","value_type":"percentage","allocation_method":"across","target_selection":"all","target_type":"line_item","code":"adina"}],"discount_codes":[{"code":"adina","amount":"18.00","type":"percentage"}],"note_attributes":[{"name":"Checkout-Method","value":"pickup"},{"name":"Pickup-Location-Id","value":"105225"},{"name":"Pickup-Location-Company","value":"Evergreen Monsey"},{"name":"Pickup-Location-Address-Line-1","value":"59 NY-59"},{"name":"Pickup-Location-City","value":"Monsey"},{"name":"Pickup-Location-Region","value":"New York"},{"name":"Pickup-Location-Postal-Code","value":"10952"},{"name":"Pickup-Location-Country","value":"United States"}],"payment_gateway_names":[],"processing_method":"free","checkout_id":18478127907005,"source_name":"web","fulfillment_status":null,"tax_lines":[],"tags":"","contact_email":"[email protected]","order_status_url":"https:\/\/labelitlabels.com\/26375225421\/orders\/3491883c672f110eaab82f8dd8080052\/authenticate?key=46788d0320dc9961fed8c81630484581","presentment_currency":"USD","total_line_items_price_set":{"shop_money":{"amount":"18.00","currency_code":"USD"},"presentment_money":{"amount":"18.00","currency_code":"USD"}},"total_discounts_set":{"shop_money":{"amount":"18.00","currency_code":"USD"},"presentment_money":{"amount":"18.00","currency_code":"USD"}},"total_shipping_price_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"subtotal_price_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"total_price_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"total_tax_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"line_items":[{"id":9102143324349,"variant_id":31929214402637,"title":"Bold Monogram","quantity":30,"sku":"L21","variant_title":"2\"*2\" $0.60","vendor":"Label It Labels","fulfillment_service":"manual","product_id":4374255960141,"requires_shipping":true,"taxable":true,"gift_card":false,"name":"Bold Monogram - 2\"*2\" $0.60","variant_inventory_management":null,"properties":[{"name":"Shape","value":"Square Shape"},{"name":"Choose Background","value":"#030000"},{"name":"Initial","value":"D"},{"name":"_font size Initial","value":"300.00"},{"name":"Choose Text Color","value":"White"},{"name":"Spell Name","value":"DREW FAMILY"},{"name":"_font size Spell Name","value":"33.00"},{"name":"Additional Text (optional)","value":"dairy cholov yisroel"},{"name":"_font size Additional Text (optional)","value":"12.00"},{"name":"_6","value":"PROOF"},{"name":"_Preview","value":"https:\/\/cdn.shopify.com\/s\/files\/1\/0263\/7522\/5421\/uploads\/23b9fb4ba8eb01b7ff717d39d9672c2d.png?format=png\u0026png"},{"name":"_pdf","value":"https:\/\/cdn.shopify.com\/s\/files\/1\/0263\/7522\/5421\/uploads\/5c0c23d462aa998831b0f79aabd0b9eb.pdf"},{"name":"_pplr_preview","value":"_Preview"},{"name":"_ZapietId","value":"a3c9cb049e2f631a8393cf37547623e2"}],"product_exists":true,"fulfillable_quantity":30,"grams":0,"price":"0.60","total_discount":"0.00","fulfillment_status":null,"price_set":{"shop_money":{"amount":"0.60","currency_code":"USD"},"presentment_money":{"amount":"0.60","currency_code":"USD"}},"total_discount_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"discount_allocations":[{"amount":"18.00","discount_application_index":0,"amount_set":{"shop_money":{"amount":"18.00","currency_code":"USD"},"presentment_money":{"amount":"18.00","currency_code":"USD"}}}],"duties":[],"admin_graphql_api_id":"gid:\/\/shopify\/LineItem\/9102143324349","tax_lines":[{"title":"NJ State Tax","price":"0.00","rate":0.06625,"price_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}}}],"origin_location":{"id":1809674076237,"country_code":"US","province_code":"NJ","name":"Label It Labels.","address1":"155 Pressburg Ln","address2":"","city":"Lakewood","zip":"08701"}}],"fulfillments":[],"refunds":[],"total_tip_received":"0.0","original_total_duties_set":null,"current_total_duties_set":null,"admin_graphql_api_id":"gid:\/\/shopify\/Order\/3175309607101","shipping_lines":[{"id":2654542987453,"title":"Pick up in Lakewood only","price":"0.00","code":"Pick up in Lakewood only","source":"shopify","phone":null,"requested_fulfillment_service_id":null,"delivery_category":null,"carrier_identifier":null,"discounted_price":"0.00","price_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"discounted_price_set":{"shop_money":{"amount":"0.00","currency_code":"USD"},"presentment_money":{"amount":"0.00","currency_code":"USD"}},"discount_allocations":[],"tax_lines":[]}],"billing_address":{"first_name":"John","address1":"123 street","phone":"(234) 567-8901","city":"Any City","zip":"08701","province":"New Jersey","country":"United States","last_name":"Smith","address2":"","company":null,"latitude":40.0963651,"longitude":-74.2067389,"name":"John Smith","country_code":"US","province_code":"NJ"},"shipping_address":{"first_name":"John","address1":"123 street","phone":"(234) 567-8901","city":"Any City","zip":"08701","province":"New Jersey","country":"United States","last_name":"Smith","address2":"","company":null,"latitude":40.0963651,"longitude":-74.2067389,"name":"John Smith","country_code":"US","province_code":"NJ"},"customer":{"id":4602994983101,"email":"[email protected]","accepts_marketing":false,"created_at":"2021-01-05T21:31:39-05:00","updated_at":"2021-01-05T21:35:07-05:00","first_name":"John","last_name":"Smith","orders_count":1,"state":"disabled","total_spent":"0.00","last_order_id":3175309607101,"note":null,"verified_email":true,"multipass_identifier":null,"tax_exempt":false,"phone":null,"tags":"","last_order_name":"#2586","currency":"USD","accepts_marketing_updated_at":"2021-01-05T21:31:39-05:00","marketing_opt_in_level":null,"admin_graphql_api_id":"gid:\/\/shopify\/Customer\/4602994983101","default_address":{"id":5617895145661,"customer_id":4602994983101,"first_name":"John","last_name":"Smith","company":null,"address1":"123 street","address2":"","city":"Any City","province":"New Jersey","country":"United States","zip":"08701","phone":"(234) 567-8901","name":"John Smith","province_code":"NJ","country_code":"US","country_name":"United States","default":true}}}

HERE IS MY LATEST CODE. It works but maybe can be cleaner and faster. which I'm having issues with Shopify's 5 second wait time to refiring if no response is recieved.

function doPost(e){
  var data = JSON.parse(e.postData.contents);
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
 
  var l = data.line_items.length; 
 
  for (var i=0;i<l;i++){
  var prop = data.line_items[i].properties;


  if (prop.length>0){
  var pdf = prop.find(function(x) {if(x.name == "_pdf") return x});
  if (!pdf){pdf = "Prop not found";}else{pdf = pdf.value};
  
  var shape = prop.find(function(x) {if(x.name.toLowerCase() == "shape") return x});
  if (!shape){shape = "Prop not found";}else{shape = shape.value};
  

  }else{
  var pdf = "N/A"
  var shape = "N/A"
  };


  var count = "Item "+ (i+1) + " of " + l;
  var qty = data.line_items[i].quantity;
  var title = data.line_items[i].title;
  var id = data.id.toString();
  var email = data.email;
  var totalPrice = data.total_price;
  var discounts = data.total_discounts;
  var acceptAds = data.buyer_accepts_marketing;
  var orderStatus = data.order_status_url;
  var addr = data.shipping_address.address1;
  var city = data.shipping_address.city;
  var state = data.shipping_address.province;
  var zip = data.shipping_address.zip;
  var phone = data.shipping_address.phone;
  var firstName = data.shipping_address.first_name;
  var lastName = data.shipping_address.last_name;
  var orderNum = data.name;
  var d = new Date(data.created_at).toLocaleString();
  ss.appendRow([d,orderNum,email,count,title,shape,qty,totalPrice,discounts,pdf,firstName,lastName,addr,city,state,zip,phone,orderStatus]);
  
if (pdf != "N/A"){
if (pdf != "Prop not found"){
  var res = UrlFetchApp.fetch(pdf);
  var blob = res.getBlob();
  var createFile = DriveApp.getFolderById('xxxxxxxxxxxxxxxx-').createFile(blob.getAs('application/pdf'));
  var fileName = orderNum + " " + qty;
  createFile.setName(fileName);
}}
  };
}

its in Google apps script and its after I parsed it var data = JSON.parse(e.postData.contents); and then I wrote it too the sheet — jack
– jack, Commented Jan 5, 2021 at 0:44
About your updated question, I thought that "line_ite":[{ might be "line_item":[{. How about this? — Tanaike
– Tanaike, Commented Jan 5, 2021 at 0:53
@Tanaike yes I corrected it and added context see edited question — jack
– jack, Commented Jan 5, 2021 at 1:04
@Tanaike Thanks for all your help, I did as you requested here is the new question stackoverflow.com/questions/65617637/… — jack
– jack, Commented Jan 7, 2021 at 18:01

Tanaike · Accepted Answer · 2021-01-06 03:28:57Z

I thought that from your title, in your json, = might be : in the value. If it's so, how about the following sample script?

Sample script:

var json = {
  "id": 123456,
  "cost_name": "john",
  "line_item": [
    {
      "item_name": "table",
      "quantity": 1,
      "properties": [
        {
          "color": "black",
          "style": "rustic"
        }
      ]
    },
    {
      "item_name": "chair",
      "quantity": 3,
      "properties": [
        {
          "color": "white",
          "style": "modern"
        }
      ]
    }
  ],
  "address": "123_street"
};

const res = json.line_item.map(({item_name, quantity, properties: [{color, style}]}) => [item_name, quantity, color, style]);
console.log(res)

Note:

Unfortunately, I cannot understand about the result format you expect. So in above sample script, each values are put in an array. If above result format is not the result you expected, can you provide the sample output values? By this, I would like to modify it.
In this case, please enable V8 runtime at the script editor.

Reference:

map()

Added 1:

From your updated question, I understood that data is json in your above script and you want to append the values of item_name, quantity, color, style to the active sheet in Google Spreadsheet. For this, how about the following modification?

Modified script:

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var res = data.line_item.map(({item_name, quantity, properties: [{color, style}]}) => [item_name, quantity, color, style]);
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(sheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
}

Note:

It seems that you are using Web Apps. In this case, when the script of Web Apps is modified, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be carful this.

Added 2:

From your updated question, it was found that your additional value is different from your initial question. And, I understood that you wanted to retrieve the values of Shape and _pdf in properties from the added value. So in this case, I would like to modify the script as follows.

Sample script:

In this case, data is your added values. Please be careful this.

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  
  const checkNames = ["Shape", "_pdf"];
  const res = data.line_items.reduce((ar, {properties}) => {
    if (properties) {
      properties.forEach(({name, value}) => {
        if (checkNames.includes(name)) ar.push([name, value]);
      });
    }
    return ar;
  }, []);
  if (res.length > 0) {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.getRange(sheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
  }
}

In this script, when the values of properties are not existing, setValues is not run. By this, no error occurs.

Note:

In this script, your added value is used. So when the structure of value is different, the script might not be able to be used. So please be careful this.
It seems that you are using Web Apps. In this case, when the script of Web Apps is modified, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be carful this.

@bottledatthesource About json.line_item what is this? is this a module/package?, the sample value of var json = {,,,} is from this question. My answer is for "How to parse a JSON object with nested arrays in google apps script". So how about asking your question to the owner of this question?

Collectives™ on Stack Overflow

How to parse a JSON object with nested arrays in google apps script

1 Answer 1

Sample script:

Note:

Reference:

Added 1:

Modified script:

Note:

Added 2:

Sample script:

Note:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Sample script:

Note:

Reference:

Added 1:

Modified script:

Note:

Added 2:

Sample script:

Note:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related