Blog

Query Google Sheets in .NET with the GKit SheetsAPI

Use Google Sheets as a lightweight data layer in C# - typed HttpClient, record DTOs, and IMemoryCache for fast responses.

Jun 30, 20264 min read

#dotnet #csharp #google-sheets #httpclient #tutorial

Google Sheets is a surprisingly capable lightweight data store for small to medium datasets - anyone on your team can edit it, there are no migrations to run, and it travels well as a config or catalogue layer alongside a real database. GKit SheetsAPI exposes any sheet over a REST endpoint, so you can query it from a typed C# service the same way you would any other JSON API. This tutorial wires that API into a .NET 8 minimal API using IHttpClientFactory, C# record types, and optional IMemoryCache caching.

Sheet setup - a library catalogue

Create a Google Sheet with these columns in row 1:

title	author	isbn	genre	available
The Pragmatic Programmer	Thomas & Hunt	978-0135957059	programming	true
Designing Data-Intensive Applications	Martin Kleppmann	978-1449373320	systems	true
Project Hail Mary	Andy Weir	978-0593135204	sci-fi	false

The available column stores the string "true" or "false" - Google Sheets has no boolean type, but that is easy to handle in the DTO.

Connect the sheet to GKit and note your user key and the sheet name (e.g. books). Keep your API key in appsettings.json - never commit a real key to source control.

SheetsAPI contract

Every request follows the same shape:

GET https://api.gkit.io/api/spreadsheets/{userKey}/{sheetName}
Authorization: Bearer sk_...

Query parameters:

Param	Example	Purpose
`search`	`genre:sci-fi`	Filter by field value
`sort`	`title:asc`	Sort by field
`limit`	`20`	Page size
`offset`	`40`	Skip N rows
`fields`	`title,author,isbn`	Return only these columns

The response is always:

{
  "data": [...],
  "meta": { "total": 142, "limit": 20, "offset": 0 }
}

Project setup

dotnet new webapi -n LibraryCatalogue --use-minimal-apis
cd LibraryCatalogue

Add the configuration keys to appsettings.json:

{
  "GKit": {
    "BaseUrl": "https://api.gkit.io/api/spreadsheets",
    "UserKey": "YOUR_USER_KEY",
    "ApiKey": "sk_YOUR_KEY_HERE"
  }
}

For production, store GKit:ApiKey in an environment variable or a secrets manager - never ship a real key in a committed config file.

C# record types

Records are a clean fit for read-only API responses. Define the DTOs in a single file.

// Models/Sheets.cs
namespace LibraryCatalogue.Models;
 
public record Meta(int Total, int Limit, int Offset);
 
public record SheetsResponse<T>(IReadOnlyList<T> Data, Meta Meta);
 
public record Book(
    string Title,
    string Author,
    string Isbn,
    string Genre,
    string Available   // raw "true"/"false" string from the sheet
)
{
    public bool IsAvailable => Available.Equals("true", StringComparison.OrdinalIgnoreCase);
}

System.Text.Json maps camelCase JSON keys to PascalCase C# properties automatically when using the default JsonSerializerOptions configured by AddHttpClient.

SheetsApiClient service

Register a named HttpClient through IHttpClientFactory. This pattern gives you connection pooling, easy mocking in tests, and a single place to set default headers.

// Services/SheetsApiClient.cs
using System.Net.Http.Headers;
using System.Text.Json;
using LibraryCatalogue.Models;
using Microsoft.Extensions.Options;
 
namespace LibraryCatalogue.Services;
 
public class GKitOptions
{
    public string BaseUrl { get; init; } = string.Empty;
    public string UserKey { get; init; } = string.Empty;
    public string ApiKey  { get; init; } = string.Empty;
}
 
public class SheetsApiClient(HttpClient http, IOptions<GKitOptions> opts)
{
    private static readonly JsonSerializerOptions JsonOpts = new(JsonSerializerDefaults.Web);
 
    public async Task<SheetsResponse<T>> QueryAsync<T>(
        string sheetName,
        string? search = null,
        string? sort   = null,
        int limit      = 20,
        int offset     = 0,
        string? fields = null,
        CancellationToken ct = default)
    {
        var url = $"{opts.Value.BaseUrl}/{opts.Value.UserKey}/{sheetName}";
 
        var query = new List<string> { $"limit={limit}", $"offset={offset}" };
        if (search is not null) query.Add($"search={Uri.EscapeDataString(search)}");
        if (sort   is not null) query.Add($"sort={Uri.EscapeDataString(sort)}");
        if (fields is not null) query.Add($"fields={Uri.EscapeDataString(fields)}");
 
        var fullUrl = $"{url}?{string.Join('&', query)}";
 
        using var request = new HttpRequestMessage(HttpMethod.Get, fullUrl);
        request.Headers.Authorization =
            new AuthenticationHeaderValue("Bearer", opts.Value.ApiKey);
 
        using var response = await http.SendAsync(request, ct);
        response.EnsureSuccessStatusCode();
 
        var stream = await response.Content.ReadAsStreamAsync(ct);
        return await JsonSerializer.DeserializeAsync<SheetsResponse<T>>(stream, JsonOpts, ct)
               ?? throw new InvalidOperationException("Empty response from SheetsAPI");
    }
}

Wire it up in Program.cs:

builder.Services.Configure<GKitOptions>(builder.Configuration.GetSection("GKit"));
builder.Services.AddHttpClient<SheetsApiClient>();

AddHttpClient<SheetsApiClient>() creates a typed client - the framework injects the configured HttpClient directly into the constructor.

Minimal API endpoints

// Program.cs (endpoint registration)
app.MapGet("/books", async (
    SheetsApiClient sheets,
    string? genre,
    int limit  = 20,
    int offset = 0) =>
{
    var search = genre is not null ? $"genre:{genre}" : null;
    var result = await sheets.QueryAsync<Book>(
        "books",
        search: search,
        sort:   "title:asc",
        limit:  limit,
        offset: offset);
 
    return Results.Ok(result);
});
 
app.MapGet("/books/{isbn}", async (string isbn, SheetsApiClient sheets) =>
{
    var result = await sheets.QueryAsync<Book>(
        "books",
        search: $"isbn:{isbn}",
        limit: 1);
 
    var book = result.Data.FirstOrDefault();
    return book is not null ? Results.Ok(book) : Results.NotFound();
});

GET /books?genre=sci-fi&limit=5 returns the first five sci-fi books sorted by title. GET /books/978-0135957059 does a field search and returns a single record or a 404.

Optional: cache responses with IMemoryCache

The sheet data does not change on every request, so a short cache keeps response times low and avoids hitting the API quota on repeated calls.

// Install: already included in Microsoft.Extensions.Caching.Memory (in-box for .NET 8)
builder.Services.AddMemoryCache();

Wrap the endpoint handler with GetOrCreateAsync:

app.MapGet("/books", async (
    SheetsApiClient sheets,
    IMemoryCache cache,
    string? genre,
    int limit  = 20,
    int offset = 0) =>
{
    var cacheKey = $"books:{genre}:{limit}:{offset}";
 
    var result = await cache.GetOrCreateAsync(cacheKey, async entry =>
    {
        entry.AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5);
 
        var search = genre is not null ? $"genre:{genre}" : null;
        return await sheets.QueryAsync<Book>(
            "books",
            search: search,
            sort:   "title:asc",
            limit:  limit,
            offset: offset);
    });
 
    return Results.Ok(result);
});

The cache key includes every query parameter that produces a distinct result, so /books?genre=sci-fi and /books?genre=programming are cached independently. After five minutes the entry expires and the next request refreshes it from the sheet.

For multi-node deployments (multiple app instances behind a load balancer) replace IMemoryCache with IDistributedCache backed by Redis - the GetOrCreateAsync call pattern stays the same.

What's next

Add CancellationToken propagation from the HTTP context (HttpContext.RequestAborted) down through the cache and the sheet client.
Extract a SheetsQueryBuilder helper to compose search, sort, and fields parameters without string concatenation.
For public endpoints, move the GKit:ApiKey to a managed secret (Azure Key Vault, AWS Secrets Manager, or a Cloudflare Worker that proxies the call) so the key never touches the app process directly.

GKit SheetsAPI gives you a fully queryable REST layer on top of any Google Sheet in minutes - no custom backend, no Google Cloud project, no OAuth flow. Sign up for GKit to get your API key and user key, connect a sheet, and start querying from your .NET app today.

All posts