getjson response in Google Sheet rows

[nick-tenorio]

In your examples, calling getjson() from a single cell and getting a multi row response seems to place the data in subsequent rows. See: "Example: Retrieve Nested JSON Properties (Fish Tank IoT Data)

I want the same behavior, and while I'm getting the correct data, it ends up in a single cell I called from in a comma separated format like this: 0,222.87,329.326,172.328,58.135

I can't post the URL as it has my private key, but the call and example response is well documented here: https://developer.mapquest.com/documentation/directions-api/route-matrix/post/ and I've also attached a screenshot of my typical JSON response.

How can I get the response to fill in subsequent rows? I'm currently doing is using the split() and transpose() function as a work around, but it's a bit of a hack.

Thanks!

[thisdavej]

@nick-tenorio great question! The multi-row response unfortunately only works when processing an array of top-level objects with a row for each object in the array. In your use case, it appears that there is just one object and this object contains an embedded array (actually an array of arrays) called distance.

Let's consider an abbreviated portion of the JSON object returned from the Mapquest sample:

{
    "allToAll": true,
    "distance": [
      [
        0,
        13.052,
        29.369
      ],
      [
        11.67,
        0,
        17.06
      ],
      [
        28.441,
        17.783,
        0
      ]
    ],
    "time": [
      [
        0,
        1037,
        2150
      ],
      [
        1068,
        0,
        1253
      ],
      [
        2119,
        1242,
        0
      ]
    ]
}

You can use a template of {{distance}} to return a flattened array of all of the values in distance separated by commas.

You can also use {{distance[0]}} and this will return just the values of the first array element:

0,13.052,29.369

You will unfortunately still need to use the Sheets split and transpose functions to accomplish your goal, but you can use {{distance[0]}} if you want a bit more control over the output.

You can also use a template of {{distance[0]}} || {{distance[1]}} to split the results into multiple columns and split and transpose it from there.

Finally, you can combine split, transpose, and GETJSON in one formula too. For example, =transpose(split(GETJSON(B1,B2),",")).

Yes, it's a bit of a hack, but hopefully it suffices as a workaround to meet you needs.

[nick-tenorio]

@thisdavej thanks for the detailed response! I'm sure there are good reasons for Mapquest's JSON response format, but seems a bit odd to me. While I was using split/transpose, I didn't think to nest getjson() inside, thanks for that tip (it works perfect). Still a little 'hackish', but that's the nature of these things. Thanks for sharing getjson with us, it was been a very useful tool for me.