json formatting in sheet

[be fa]

I would like to format the result of the json on the sheet.
Example column A slug, B price and C from.
Could you tell me how to do this?

Code :

function appelSorare() {

    let url = "https://api.sorare.com/graphql";

    let graphql = JSON.stringify({

      query: `query {

        player(slug:"lionel-andres-messi-cuccittini"){

            cards(rarities:[limited],after:"") {

                nodes {

                    slug

                    userOwnerWithRate {

                        price

                        from

                      }

                      }  

                      pageInfo

                      {

                        endCursor

                        hasNextPage

                      }

                    }

                  }

                }`,

      variables: null,

    });

    //ui.alert(graphql);

    let params = {

      method: "POST",

      payload: graphql,

      headers: {

        "Content-Type": "application/json",

        "X-CSRF-Token": "",

      },

    };

    var response = UrlFetchApp.fetch(url, params);

    var lists = JSON.parse(response.getContentText());

    pCursorAfter = lists["data"]["player"]["cards"]["pageInfo"]["endCursor"];

    pLstCard = lists["data"]["player"]["cards"]["nodes"];

    pAgain = lists["data"]["player"]["cards"]["pageInfo"]["hasNextPage"];

    Logger.log(response);

}

Result Json :

{data={player={cards={nodes=[{slug=lionel-andres-messi-cuccittini-2021-limited-50, userOwnerWithRate={from=2021-09-21T02:21:12Z, price=652000000000000000}}, {userOwnerWithRate={from=2022-04-06T23:45:46Z, price=290000000000000000}, slug=lionel-andres-messi-cuccittini-2021-limited-322}, {slug=lionel-andres-messi-cuccittini-2021-limited-116, userOwnerWithRate={from=2022-02-11T10:09:50Z, price=478200000000000000}}

Thanks

[Tanaike]

The value of `Result Json :` is `var lists = JSON.parse(response.getContentText());`? If my understanding is correct, can you provide the value of `Logger.log(response.getContentText())`?

[be fa]

You are right, I made a mistake in my post. The return is as follows for :

Logger.log(pLstCard);

[{slug=lionel-andres-messi-cuccittini-2021-limited-50, userOwnerWithRate={from=2021-09-21T02:21:12Z, price=652000000000000000}}, {slug=lionel-andres-messi-cuccittini-2021-limited-322, userOwnerWithRate={price=290000000000000000, from=2022-04-06

Logger.log(response.getContentText());

{"data":{"player":{"cards":{"nodes":[{"slug":"lionel-andres-messi-cuccittini-2021-limited-50","userOwnerWithRate":{"price":"652000000000000000","from":"2021-09-21T02:21:12Z"}},{"slug":"lionel-andres-messi-cuccittini-2021-limited-322","userOwnerWithRate":{"price":"290000000000000000","from":"2022-04-06T23:45:46Z"}}

[Tanaike]

Thank you for your additional information. From your provided sample value, how about the following modification?

From

var lists = JSON.parse(response.getContentText());
pCursorAfter = lists["data"]["player"]["cards"]["pageInfo"]["endCursor"];
pLstCard = lists["data"]["player"]["cards"]["nodes"];
pAgain = lists["data"]["player"]["cards"]["pageInfo"]["hasNextPage"];
Logger.log(response);

To

var values = lists.data.player.cards.nodes.map(e => [e.slug, e.userOwnerWithRate?.price || "", e.userOwnerWithRate?.from || ""]);
console.log(values)

By this modification, the values of "slug", "price", "from" are retrieved as a 2-dimensional array. If I misunderstood your question, I apologize.

[Tanaike]

To "To" script, please add "var lists = JSON.parse(response.getContentText());" as follows. I miscopied the script. I apologize for it.

var lists = JSON.parse(response.getContentText());

[be fa]

Thank you very much for your feedback, unfortunately it does not work I have the message : "Erreur de syntaxe : SyntaxError: Invalid or unexpected token (ligne : 39, fichier : Code.gs)"
I added your code like this:

    var response = UrlFetchApp.fetch(url, params);

    var lists = JSON.parse(response.getContentText());

    var values ​​= lists.data.player.cards.nodes.map(e => [e.slug, e.userOwnerWithRate?.price || "", e. 

userOwnerWithRate?.from || ""]);

    Logger.log(values)

Is this what I had to do?

[Tanaike]

Thank you for replying. I apologize for the inconvenience. Unfortunately, I cannot understand "ligne : 39", I used your provided sample value which is `response.getContentText()`. If your provided value is not from `response.getContentText()`, the script might not be able to be used. I apologize for this. In this case, it is required to know the correct value of `response.getContentText()`. I apologize for this, again.

[Tanaike]

Unfortunately, your provided data is incomplete. So, as a test case, when a test script is prepared by modifying the data, you can see it at https://jsfiddle.net/nweg45dc/ I thought that this script might help to understand your current issue.

[be fa]

Thanks a lot.
Your code works perfectly. The error was on my side by dint of doing and undoing...

[be fa]

Can you explain to me what the || "" please?

[R Tichy]

... , e.userOwnerWithRate?.price || "", ... 

Okay, so || means "OR" and when/if e.UserOwnerWithRate?.price is a null value, the phrase (e.UserOwnerWithRate?.price OR "") equates to (null or "") which then evaluates to "", so you have set a string to a value of "" instead of null, which could cause you an error.

[be fa]

Thank you for your explanations.
Do you know how I could integrate this result into a sheet?
I tried this but it doesn't work (Exception: Bad value):

var values = lists.data.player.cards.nodes.map(e => [e.slug, e.userOwnerWithRate?.price || "", e.userOwnerWithRate?.from || ""]);

console.log(values)

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

    for (var i=0; i< values.length;i++){

      var cell = sheet.getRange(i+1,3);

      cell.setValues(values[i].text);

    }