Implementing aggregate queries for single value results using Google Sheets API in C#

Other Questions Google Sheets
1

I’m working with Google Sheets API in C# and need help with creating aggregate queries that return single values. I want to execute queries like finding maximum values from a column, but I’m having trouble getting the API to process these requests properly.

I’ve tried different approaches including using ListQuery and SpreadsheetQuery objects. The WHERE clause searches work fine with SpreadsheetQuery, but when I try to use aggregate functions, I always get empty results.

Here’s what I’ve been working with:

public int FindHighestValue(string sheetName)
{
    // Retrieve the worksheet
    var sheet = FetchWorksheet(sheetName);
    AtomLink feedLink = sheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
    ListQuery listQuery = new ListQuery(feedLink.HRef.ToString());
    
    // This query string seems to be ignored
    listQuery.Query = "select max(recordId)";
    ListFeed resultFeed = this._sheetService._client.Query(listQuery);
    var resultCount = resultFeed.Entries.Count;
    
    //TODO: Process results
    return -1;
}

The query always returns zero entries even though the data exists in the sheet. What’s the correct way to handle aggregate functions with the C# Google Sheets API?

2

Been wrestling with this exact issue for months. The GData API for Google Sheets has terrible query capabilities - nothing like SQL. That query string parameter doesn’t support aggregate functions like MAX, MIN, or SUM at all. I switched to the newer Google Sheets API v4 and it’s way better. You get much more control and can use the batchGet method to grab specific ranges, then process the data locally. Performance is also way better since you’re not stuck with the old feed-based approach. If you’re stuck with GData, you’ll have to pull all the data first and do your aggregation in C#. Just heads up - this doesn’t scale with large datasets since you’re loading everything into memory. The newer API lets you work with specific cell ranges which is much more efficient for this stuff.

3

yea, gdata api is kinda a pain with aggregates. better to just get all the data first and then use linq to get the max yourself. much simpler that way.

4

The GData API doesn’t support SQL-style aggregate functions in query strings. Those query parameters are just for basic filtering, not aggregation. You’ll have to pull the data and do the aggregation on your end. Here’s how:

public int FindHighestValue(string sheetName)
{
    var sheet = FetchWorksheet(sheetName);
    AtomLink feedLink = sheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, null);
    ListQuery listQuery = new ListQuery(feedLink.HRef.ToString());
    
    ListFeed resultFeed = this._sheetService._client.Query(listQuery);
    
    int maxValue = resultFeed.Entries.Cast<ListEntry>()
        .Select(entry => int.Parse(entry.Elements[0].Value))
        .Max();
    
    return maxValue;
}

Better yet, switch to the newer Sheets API v4. It’s got much better querying through the Google.Apis.Sheets.v4 NuGet package.