How to extract JSON value in strangely formatted array?

Question

I'm using a custom script for importing JSON into Google Sheets through a function. I can import values from propertys without any problem, but I have some problem with a specific array. It is a property which contains more information, but it seems the formatting makes the array into one single value instead of several (something with the slashes?). First, the script:

function getStat(url, propertyName)
{
  let content = UrlFetchApp.fetch(url).getContentText();
  let parsed = JSON.parse(content);
  
  let processed = parsed.data
    .filter(e => 
      // Conditions go here:
      e.season_format === 'Domestic League' &&
      e.season === '2020/2021'
    )
    .map(e => e.stats[propertyName]);

  return processed;
}

I want to get the value after "3" in the array called additional_info (simplified version below). But when I try to get the value, instead I get the third character in the array. I don't get "55" which is the value. I've tried with a bunch of variants. But I can't get it to work. For example, additional_info["3"] returns the third character in the array, not the value. Any tips? I've no problem getting the values of suspended_matches and home_AttackAdvantage.

{
  "success": true,
  "data": [
    {
      "season": "2020/2021",
      "season_format": "Domestic League",
      "stats": {
        "suspended_matches": 20,
        "homeAttackAdvantage": 3,
        "additional_info": "{\"1\":1,\"2\":2,\"3\":55,\"4\"}"
      }
    }
  ]
}

user_4294399 · Answer

The issue was that additional_info is yet another JSON string, so you have to parse it again.

function getStat(url, propertyName, additionalProp)
{
  let content = UrlFetchApp.fetch(url).getContentText();
  let parsed = JSON.parse(content);
  
  let processed = parsed.data
    .filter(e => 
      // Conditions go here:
      e.season_format === 'Domestic League' &&
      e.season === '2020/2021'
    )
    .map(e => additionalProp 
      ? [
        e.stats[propertyName], 
        JSON.parse(e.stats.additional_info)[additionalProp]
      ]
      : e.stats[propertyName]
    );
  
  return processed;
}

This gives you a function you can use in a formula:

=getStat(
 "https://api.footystats.org/team?key=example&team_id=93", 
 "suspended_matches",
 "330"
)

If you don't specify the third argument, it will just return a single column.

How to extract JSON value in strangely formatted array?

1 Answers