Google Sheets as a REST API in C# (.NET)

// Models/SheetResponse.cs
namespace MyApp.Models;
 
public record SheetMeta(int Total, int Limit, int Offset);
 
public record SheetResponse<T>(
    IReadOnlyList<T> Data,
    SheetMeta Meta
);
 
public record Product(
    string Name,
    string Price,
    string Category,
    string Stock
);

using System.Net.Http.Json;
using MyApp.Models;
 
var client = new HttpClient();
client.DefaultRequestHeaders.Add("Authorization", "Bearer sk_your_api_key_here");
 
var url = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets/YOUR_USER_KEY/Products?limit=20&sort=name";
var result = await client.GetFromJsonAsync<SheetResponse<Product>>(
    url,
    new System.Text.Json.JsonSerializerOptions(System.Text.Json.JsonSerializerDefaults.Web)
);
 
Console.WriteLine($"Total: {result?.Meta.Total}");
foreach (var p in result?.Data ?? [])
    Console.WriteLine($"  {p.Name} - {p.Price}");

// Services/SheetsApiClient.cs
using System.Net.Http.Json;
using System.Text.Json;
using MyApp.Models;
 
namespace MyApp.Services;
 
public class SheetsApiClient
{
    private static readonly JsonSerializerOptions JsonOptions =
        new(JsonSerializerDefaults.Web);
 
    private readonly HttpClient _http;
    private readonly string _userKey;
 
    public SheetsApiClient(HttpClient http, IConfiguration config)
    {
        _http = http;
        _userKey = config["SheetsApi:UserKey"]
            ?? throw new InvalidOperationException("SheetsApi:UserKey not configured");
    }
 
    public async Task<SheetResponse<T>> QueryAsync<T>(
        string sheetName,
        int limit = 20,
        int offset = 0,
        string? search = null,
        string? sort = null,
        string? fields = null,
        CancellationToken ct = default)
    {
        var query = new Dictionary<string, string?>
        {
            ["limit"]  = limit.ToString(),
            ["offset"] = offset.ToString(),
            ["search"] = search,
            ["sort"]   = sort,
            ["fields"] = fields,
        }.Where(kv => kv.Value is not null)
         .Select(kv => $"{kv.Key}={Uri.EscapeDataString(kv.Value!)}");
 
        var url = $"https://sheetsapi.gkit.mreshank.com/api/spreadsheets/{_userKey}/{sheetName}?{string.Join("&", query)}";
 
        var result = await _http.GetFromJsonAsync<SheetResponse<T>>(url, JsonOptions, ct);
        return result ?? throw new InvalidOperationException("Null response from SheetsAPI");
    }
 
    public async Task<IReadOnlyList<T>> FetchAllAsync<T>(string sheetName, CancellationToken ct = default)
    {
        var all = new List<T>();
        var offset = 0;
        const int pageSize = 100;
 
        while (true)
        {
            var page = await QueryAsync<T>(sheetName, limit: pageSize, offset: offset, ct: ct);
            all.AddRange(page.Data);
            offset += pageSize;
            if (offset >= page.Meta.Total) break;
        }
 
        return all;
    }
}

// Program.cs
var builder = WebApplication.CreateBuilder(args);
 
builder.Services.AddHttpClient<SheetsApiClient>(client =>
{
    var apiKey = builder.Configuration["SheetsApi:ApiKey"] ?? "";
    if (!string.IsNullOrEmpty(apiKey))
        client.DefaultRequestHeaders.Add("Authorization", $"Bearer {apiKey}");
});
 
builder.Services.AddMemoryCache();

{
  "SheetsApi": {
    "UserKey": "YOUR_USER_KEY",
    "ApiKey": "sk_your_api_key_here"
  }
}

// Program.cs (continued)
var app = builder.Build();
 
app.MapGet("/products", async (
    SheetsApiClient sheets,
    IMemoryCache cache,
    string? q,
    int limit = 20,
    int offset = 0) =>
{
    var cacheKey = $"products:{q ?? "all"}:{limit}:{offset}";
 
    if (!cache.TryGetValue(cacheKey, out SheetResponse<Product>? result))
    {
        result = await sheets.QueryAsync<Product>(
            "Products",
            limit: limit,
            offset: offset,
            search: q is not null ? $"name:{q}" : null,
            sort: "name"
        );
        cache.Set(cacheKey, result, TimeSpan.FromMinutes(5));
    }
 
    return Results.Ok(result);
});
 
app.Run();

try
{
    var result = await sheets.QueryAsync<Product>("Products", limit: 10);
    return Results.Ok(result);
}
catch (HttpRequestException ex) when (ex.StatusCode == System.Net.HttpStatusCode.Unauthorized)
{
    return Results.Problem("Invalid or missing SheetsAPI key.", statusCode: 401);
}
catch (HttpRequestException ex) when (ex.StatusCode == System.Net.HttpStatusCode.TooManyRequests)
{
    return Results.Problem("Rate limit exceeded. Please retry later.", statusCode: 429);
}
catch (HttpRequestException ex)
{
    return Results.Problem($"SheetsAPI error: {ex.Message}", statusCode: 502);
}