Extracting specific data from Google Sheets JSON in C#

Other Questions Google Sheets
1 
Hey everyone! I'm new to C# and working on a leaderboard for my Unity game. I've got some JSON data from Google Sheets, but I'm stuck on how to get the info I need.

Here's what my data looks like:

{
  "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've set up a struct like this:

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

I can print out 'range' and 'majorDimension', but I'm confused about how to access the 'values' part. I want to grab the Name, Score, and Event for my leaderboard.

Any tips on how to work with this nested list in C#? Thanks!
2

hey henryg, welcome to c#! for the values, u can access them like this:

var sheetData = JsonConvert.DeserializeObject(jsonString);
var values = sheetData.values;

foreach (var row in values.Skip(1)) // skip header
{
string name = row[1];
string score = row[6];
string eventName = row[5];
// do smth with the data
}

hope this helps! lmk if u need more clarification :slight_smile:

3

As someone who’s worked with the Google Sheets API and Unity, I can relate to the challenges of extracting the right data. I suggest using Newtonsoft.Json for your deserialization. Create a class to represent each leaderboard entry, like:

public class LeaderboardEntry
{
public string Name { get; set; }
public int Score { get; set; }
public string Event { get; set; }
}

Then, deserialize your JSON and process the data:

var sheetData = JsonConvert.DeserializeObject(jsonString);
var leaderboard = sheetData.values.Skip(1).Select(row => new LeaderboardEntry
{
Name = row[1],
Score = int.Parse(row[6]),
Event = row[5]
}).ToList();

This gives you a list of LeaderboardEntry objects which you can sort, filter, or display as needed in Unity. I hope this insight from my own experience helps with your leaderboard implementation.

4

Greetings, henryg. To extract the specific data you need, you can utilize LINQ for a more streamlined approach. Here’s an example:

var data = JsonConvert.DeserializeObject(jsonString);
var leaderboardData = data.values.Skip(1).Select(row => new
{
Name = row[1],
Score = int.Parse(row[6]),
Event = row[5]
}).OrderByDescending(x => x.Score);

foreach (var entry in leaderboardData)
{
Console.WriteLine($“{entry.Name} - {entry.Score} - {entry.Event}”);
}

This code will parse the JSON, skip the header row, extract the required fields, and sort the entries by score in descending order. You can then use this data to populate your leaderboard in Unity.