Json import in googlesheet

Question

I'm trying to import data from Json but I don't know the syntaxe for it :

Json code :

"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\"}]"
          },

Here is the script I use :

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

this works perfectlty, but I don't know the syntax for import this : "event_start\ in : body.line_items[0].meta_data[1].value

Shoud be something like : var event_begins = myData.line_items[0].meta_data[1].value.event_start; but it doesn't work...

I have to apologize for my poor English skill. Unfortunately, I cannot understand about your goal. So in order to correctly understand about it, can you provide the output you expect? — Tanaike
– Tanaike, Commented Jul 12, 2020 at 22:30
Hello, the output I expect is : 2020-07-22 14:15:00 (from even_start) — guillaume dumond
– guillaume dumond, Commented Jul 13, 2020 at 6:00
Thank you for replying. From your replying, I proposed a sample script as an answer. Could you please confirm it? If that was not the result you expect, I apologize. — Tanaike
– Tanaike, Commented Jul 13, 2020 at 6:15

Tanaike · Accepted Answer · 2020-07-13 06:15:51Z

I believe your goal as follow.

You want to retrieve the value of event_start in myData.line_items[0].meta_data[1].value.

Modification points:

This answer supposes that myData is the following object.

  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\"}]"}]}]}

From above object, it is found that the value of myData.line_items[0].meta_data[1].value is the string. So in this case, please parse the value as an object like var obj = JSON.parse(myData.line_items[0].meta_data[1].value);. By this, the following object is retrieved.
```
  [
    {
      "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"
    }
  ]
```
From above retrieved object, when you want to retrieve the value of event_start, you can retrieve it with var event_begins = obj[0].event_start;.

When above points are reflected to a sample script, it becomes as follows.

Sample script:

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

Reference:

JSON.parse()

guillaume dumond · Accepted Answer · 2020-07-13 11:45:06Z

0

I suppose for event_end,that would be :

var obj = JSON.parse(myData.line_items[0].meta_data[1].value);
var event_begins = obj[0].event_start;
var event_ends = obj[0].event_end;

?

Best regards

answered Jul 13, 2020 at 11:45

guillaume dumond

132 bronze badges

Collectives™ on Stack Overflow

Json import in googlesheet

2 Answers 2

Modification points:

Sample script:

Reference:

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Modification points:

Sample script:

Reference:

1 Comment

Comments

Related