edit

as a custom function, you can use

=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)
items2 (level2, under pegaAttributes)

CodePudding user response：

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...