Import JSON API into Google Sheets

Question

I need to import some information from a JSON API URL into Google Sheets.

This is one example:
https://api-apollo.pegaxy.io/v1/game-api/race/details/69357391

I've been successful in importing basic information using IMPORTJSON available on Github:
https://github.com/bradjasper/ImportJSON/

But now I am faced with a type of information (is it an object? an array?) which seems to be different from the usual and I find myself unable to import this.

Here is a piece of it:

{
        "id": 969228010,
        "raceId": 69357391,
        "pegaId": 20042,
        "gate": 8,
        "pegaAttributes": "{\"id\":20042,\"name\":\"Bajaj\",\"ownerId\":623299,\"raceClass\":1,\"races\":1369,\"win\":504,\"lose\":865,\"energy\":18,\"gender\":\"Male\",\"bloodLine\":\"Campona\",\"breedType\":\"Legendary\",\"speed\":4.95,\"strength\":0.33,\"wind\":3.36,\"water\":1.84,\"fire\":8.83,\"lighting\":6.93,\"position\":4000,\"finished\":true,\"raceTime\":35.855,\"result\":8,\"gate\":8,\"lastSpeed\":22.721521955555556,\"stage\":4,\"isTopSpeedReached\":false,\"bonusStage\":false,\"topSpeed\":22.721521955555556,\"s0\":0,\"j0\":-0.02,\"a0\":0.4982185622222222,\"v0\":20.127527583333332,\"t0\":179.60000000000002,\"gears\":{},\"pb\":0}"**,
        "position": 11,
        "raceTime": 35.855,
        "reward": 0
      },

So using IMPORTJSON if I wanted to simply import the "raceId" element I'd go about doing this:

=ImportJSON("https://api-apollo.pegaxy.io/v1/game-api/race/details/69357391", "/race/registers/raceId", "noHeaders")

But when trying to import any information from within pegaAttributesthe IMPORTJSON is unable to recognize it as separate. The best I can do is import the whole block like so:

=ImportJSON("https://api-apollo.pegaxy.io/v1/game-api/race/details/69357391", "/race/registers/pegaAttributes", "noHeaders")

So some of the information after "pegaAttributes" and inside brackets { } I need to import. For example the attributes raceTime , topSpeed, lastSpeed and so on, how can I import this into Google Sheets?

Could anyone provide any pointers on how to do this? Thank you.

Answer 1

Try (you will have to apply JSON.parse on the pegaAttributes element which is also a json)

=importDataJSON(url,"id|position|raceTime","name|raceTime|topSpeed|lastSpeed")

with

function importDataJSON(url, items1, items2) {
  let result = []
  result = [[items1.split('|'), items2.split('|')].flat()]
  const obj = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  obj.race.registers.forEach(o => {
    let prov = []
    items1.split('|').forEach(item1 => prov.push(o[item1]))
    var pegaAttributes = JSON.parse(o.pegaAttributes)
    items2.split('|').forEach(item2 => prov.push(pegaAttributes[item2]))
    result.push(prov)
  })
  return result
}

with as parameters:

• url
• items1 (level 1) separated by |
• items2 (level2, under pegaAttributes) separated by |

new edit

=importDataJSON(url,"totalReward|length","id|position|raceTime","name|raceTime|topSpeed|lastSpeed")

with

function importDataJSON(url, items0, items1, items2) {
  let result = []
  result = [[items0.split('|'), items1.split('|'), items2.split('|')].flat()]
  const obj = JSON.parse(UrlFetchApp.fetch(url).getContentText())
  let prov = []
  items0.split('|').forEach(item0 => prov.push(obj.race[item0]))
  result.push(prov)
  obj.race.registers.forEach(o => {
    let prov = []
    items0.split('|').forEach(item0 => prov.push(''))
    items1.split('|').forEach(item1 => prov.push(o[item1]))
    var pegaAttributes = JSON.parse(o.pegaAttributes)
    items2.split('|').forEach(item2 => prov.push(pegaAttributes[item2]))
    result.push(prov)
  })
  return result
}

Answer 2

You have to parse it twice as that's an object just as text. I think using the custom formula might not be easiest since Google App Scripts can do this for you pretty cleanly. Consider using the standard JSON.parse() functions.

The below function got me the following values you were looking for. See the debug screen shot.

function getJSONData(){
  const zURL = 'https://api-apollo.pegaxy.io/v1/game-api/race/details/69357391';
  var response = UrlFetchApp.fetch(zURL);
  var cleanedResponse = JSON.parse(response);
  var theRace = cleanedResponse['race'];
  var theRegisters = theRace['registers'];
  var aRegister = theRegisters[0];
  var oneID = oneRegister.id;
  var aGate = oneRegister.gate;
  var aPega = oneRegister.pegaAttributes;
  var cleanedPega = JSON.parse(aPega);
  var zTopSpeed = cleanedPega.topSpeed;

}

If you debug this, function and check to the right in your variables, you should be able to get everything you need. You'll have to find a way to get it back into sheets, but the values are available.

Updated

A request was made to figure out how this could be run as a Sheets Function. leveraging Mike Steelson's approach and presumption for what is needed as far as races... here's a function that could be used. Just paste the URL in the formula.

function getDataMyJSON(theURL) {

  const data = JSON.parse(UrlFetchApp.fetch(theURL).getContentText())
  const items = ['raceTime','topSpeed','lastSpeed']
  let result=[]
  data.race.registers.forEach(x => {
    let prov = []
    prov.push(x.raceId)
    var p = JSON.parse(x.pegaAttributes)
    items.forEach(i => prov.push(p[i]))
    result.push(prov)
  })
  return result;
}

So then put the URL in the formula and you'd get this...