Extracting values from nested JSON arrays received from Google Sheets in C#

Other Questions Google Sheets
1

I’m new to C# and I’m working on integrating Google Sheets data into my Unity game. I received some JSON data after converting from Google Sheets and need to create a leaderboard. My challenge is accessing certain values like “Name”, “Score”, and “Event” from the JSON structure provided below.

{
  "range": "Sheet1!A1:M112",
  "majorDimension": "ROWS",
  "values": [
    [
      "Time",
      "Name",
      "Surname",
      "E-Mail",
      "Company / University",
      "Event(Optional)",
      "Score"
    ],
    [
      "12.06.2023 11:17:09",
      "Henry",
      "Powell",
      "@hotmail.com",
      "x Company",
      "x Event",
      "0"
    ]
  ]
}

I’m able to print the majorDimension and range, but I’m struggling with understanding the nested list concept in C# which prevents me from accessing the “values” section. Here’s my current structure:

public struct Data{
    public string range;
    public string majorDimension;
    public List<List<string>> values;
}

In my getData coroutine, I’m using:

IEnumerator getData()
{
    UnityWebRequest www = UnityWebRequest.Get(googleSheetURL);
    yield return www.SendWebRequest();
    
    if (www.isNetworkError || www.isHttpError)
    {
        Debug.Log(www.error);
    }
    else
    {
        Data data = JsonUtility.FromJson<Data>(www.downloadHandler.text);
        
        Debug.Log(data.values);
        Debug.Log(data.majorDimension);
        Debug.Log(data.range);
    }
    
    www.Dispose();
}

I would appreciate any help you could provide. Thank you, and have a great day!

2

your struct’s fine - it’s the debugging that’s wrong. Debug.Log(data.values) just prints the object reference, not what’s inside. try data.values[1][1] to get henry’s name, or loop through it. the nested list setup works perfectly for google sheets json btw.

3

I hit this same problem when I started using Google Sheets API in Unity. The issue is that Debug.Log(data.values) won’t show what’s inside the nested arrays - it just prints the object type reference. You’ve got to loop through the nested structure to see the actual elements.

Here’s what worked for me:

for (int i = 0; i < data.values.Count; i++)
{
    for (int j = 0; j < data.values[i].Count; j++)
    {
        Debug.Log($"Row {i}, Column {j}: {data.values[i][j]}");
    }
}

For your leaderboard, skip the header row (index 0) and grab specific columns by their index. Looking at your JSON, Name is at index 1, Score at index 6, and Event at index 5. So data.values[1][1] gets you “Henry”, and data.values[1][6] gets you the score “0”.

Your nested List structure is fine - you just need to loop through it properly to pull out the values.

4

Your Data struct looks fine for that JSON structure. The issue is that Debug.Log can’t display nested List<List> contents properly - you’re probably seeing “System.Collections.Generic.List1[System.Collections.Generic.List1[System.String]]” instead of actual data. I ran into the same thing when I first used Google Sheets API with Unity. What worked for me was creating a simple method to grab values by column name instead of hardcoding indices. Map the header row to column indices first, then use those mappings. Here’s an example of how to do this:

Data data = JsonUtility.FromJson<Data>(www.downloadHandler.text);

// Skip header row and process actual data
for (int i = 1; i < data.values.Count; i++)
{
    string playerName = data.values[i][1]; // Name column
    string score = data.values[i][6]; // Score column
    string eventName = data.values[i][5]; // Event column
    
    Debug.Log($"Player: {playerName}, Score: {score}, Event: {eventName}");
}

This worked great for my Unity leaderboard.