如何在google应用程序脚本中使用嵌套数组解析JSON对象

Question

我很难用嵌套数组解析JSON字符串。下面是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"
 }

我需要得到每个item_name和quantity的line_item，也需要每个line_item的color和style

我从web钩子中接收到这个JSON，所以订单是不一样的。

添加了上下文：(@Taineke的请求)

我试着把这个写到一个带有应用程序脚本的谷歌单子上，这是我的代码。

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])
}

这里是更新的e.postData.contents ( @Tanaike)从测试webhook请求的

/*
* 提示：该行代码过长，系统自动注释不进行高亮。一键复制会移除系统注释 
* {"id":3175309607101,"email":"sample@sample.com","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":"sample@sample.com","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":"sample@sample.com","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}}}
*/

，这是我的最新代码。可以工作，但也许可以更干净、更快。我对Shopify的5秒等待时间有问题，如果没有收到响应，就可以重新开火。

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);
}}
  };
}

Answer 1

我认为从您的标题中，在您的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"
};

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

​

注意：

• 不幸的是，我无法理解您期望的结果格式。因此，在上面的示例脚本中，每个值都放在一个数组中。如果上面的结果格式不是您所期望的结果，您能提供示例输出值吗？通过这个，我想修改它。
• 在这种情况下，请在脚本编辑器中启用V8运行时。

参考资料：

• 地图()

增加1：

从您更新的问题中，我了解到data在您的上面脚本中是json，您希望将item_name, quantity, color, style的值附加到Google中的活动表中。对于这一点，下面的修改如何？

修改脚本：

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);
}

注意：

• ，似乎您正在使用Web。在这种情况下，当修改Web应用程序的脚本时，请将Web应用程序重新部署为新版本。这样，最新的脚本就会反映到Web应用程序中。请小心点。

增加2：

从你最新的问题中，我们发现你的附加价值与你最初的问题不同。而且，我知道您希望从附加值中检索Shape和_pdf在properties中的值。因此，在这种情况下，我想修改脚本如下。

示例脚本：

在本例中，data是您的附加值。请小心这个。

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);
  }
}

• 在这个脚本中，当properties的值不存在时，就不会运行setValues。这样，就不会发生错误。

注意：

• 在此脚本中，将使用您的附加值。因此，当值的结构不同时，脚本可能无法使用。所以请小心点。
• ，似乎您正在使用Web。在这种情况下，当修改Web应用程序的脚本时，请将Web应用程序重新部署为新版本。这样，最新的脚本就会反映到Web应用程序中。请小心点。