json - 在 googlesheet 中导入 Json

Question

我正在尝试从 Json 导入数据，但我不知道它的语法：

json代码：

"line_items": [
      {
        "id": 2457,
        "name": "GRAND PARC",
        "product_id": 4815,
        "variation_id": 0,
        "quantity": 1,
        "tax_class": "",
        "subtotal": "12.00",
        "subtotal_tax": "0.00",
        "total": "12.00",
        "total_tax": "0.00",
        "taxes": [],
        "meta_data": [
          {
            "id": 28148,
            "key": "bookacti_booking_id",
            "value": "3990"
          },
          {
            "id": 28149,
            "key": "bookacti_booked_events",
            "value": "[{\"id\":\"3990\",\"event_id\":\"2292\",\"event_start\":\"2020-07-22 14:15:00\",\"event_end\":\"2020-07-22 15:15:00\",\"title\":\"GRAND PARC\",\"activity_id\":\"5\",\"template_id\":\"9\"}]"
          },

这是我使用的脚本：

//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_number = myData.number;
  var product_name = myData.line_items[0].name;
        
  var timestamp = new Date();
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow([timestamp,order_number,name]);
}

这很好用，但我不知道导入这个的语法：“event_start\ in : body.line_items[0].meta_data[1].value

应该是这样的： var event_begins = myData.line_items[0].meta_data[1].value.event_start; 但它不起作用......

标签： jsongoogle-apps-scriptgoogle-sheets

Answer 1

我相信你的目标如下。

• 您想要检索event_startin的值myData.line_items[0].meta_data[1].value。

修改点：

• 这个答案假设myData是以下对象。

    var myData = {"line_items":[{"id":2457,"name":"GRANDPARC","product_id":4815,"variation_id":0,"quantity":1,"tax_class":"","subtotal":"12.00","subtotal_tax":"0.00","total":"12.00","total_tax":"0.00","taxes":[],"meta_data":[{"id":28148,"key":"bookacti_booking_id","value":"3990"},{"id":28149,"key":"bookacti_booked_events","value":"[{\"id\":\"3990\",\"event_id\":\"2292\",\"event_start\":\"2020-07-2214:15:00\",\"event_end\":\"2020-07-2215:15:00\",\"title\":\"GRANDPARC\",\"activity_id\":\"5\",\"template_id\":\"9\"}]"}]}]}
  

• 从上面的对象中，发现 的值myData.line_items[0].meta_data[1].value是字符串。所以在这种情况下，请将值解析为像var obj = JSON.parse(myData.line_items[0].meta_data[1].value);. 由此，检索到以下对象。

    [
      {
        "id": "3990",
        "event_id": "2292",
        "event_start": "2020-07-22 14:15:00",
        "event_end": "2020-07-22 15:15:00",
        "title": "GRAND PARC",
        "activity_id": "5",
        "template_id": "9"
      }
    ]
  

• 从上面检索到的对象，当您要检索 的值时event_start，可以使用 检索它var event_begins = obj[0].event_start;。

当以上几点反映到一个示例脚本时，它变成如下。

示例脚本：

var myData = {
  "line_items": [
    {
      "id": 2457,
      "name": "GRAND PARC",
      "product_id": 4815,
      "variation_id": 0,
      "quantity": 1,
      "tax_class": "",
      "subtotal": "12.00",
      "subtotal_tax": "0.00",
      "total": "12.00",
      "total_tax": "0.00",
      "taxes": [],
      "meta_data": [
        {
          "id": 28148,
          "key": "bookacti_booking_id",
          "value": "3990"
        },
        {
          "id": 28149,
          "key": "bookacti_booked_events",
          "value": "[{\"id\":\"3990\",\"event_id\":\"2292\",\"event_start\":\"2020-07-22 14:15:00\",\"event_end\":\"2020-07-22 15:15:00\",\"title\":\"GRAND PARC\",\"activity_id\":\"5\",\"template_id\":\"9\"}]"
        }
      ]
    }
  ]
};
var obj = JSON.parse(myData.line_items[0].meta_data[1].value);
var event_begins = obj[0].event_start;
console.log(event_begins)  // 2020-07-22 14:15:00

参考：

• JSON.parse()