Google Sheets as a REST API in Go

package main
 
import (
    "encoding/json"
    "fmt"
    "net/http"
    "net/url"
)
 
const baseURL = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets"
 
type Meta struct {
    Total  int `json:"total"`
    Limit  int `json:"limit"`
    Offset int `json:"offset"`
}
 
type SheetResponse[T any] struct {
    Data []T  `json:"data"`
    Meta Meta `json:"meta"`
}
 
type Product struct {
    Name     string `json:"name"`
    Price    string `json:"price"`
    Category string `json:"category"`
    Stock    string `json:"stock"`
}
 
func fetchProducts(limit int) (*SheetResponse[Product], error) {
    endpoint := fmt.Sprintf("%s/YOUR_USER_KEY/Products", baseURL)
    params := url.Values{}
    params.Set("limit", fmt.Sprintf("%d", limit))
    params.Set("sort", "name")
 
    resp, err := http.Get(endpoint + "?" + params.Encode())
    if err != nil {
        return nil, fmt.Errorf("http.Get: %w", err)
    }
    defer resp.Body.Close()
 
    if resp.StatusCode != http.StatusOK {
        return nil, fmt.Errorf("unexpected status: %d", resp.StatusCode)
    }
 
    var result SheetResponse[Product]
    if err := json.NewDecoder(resp.Body).Decode(&result); err != nil {
        return nil, fmt.Errorf("json decode: %w", err)
    }
    return &result, nil
}
 
func main() {
    result, err := fetchProducts(20)
    if err != nil {
        panic(err)
    }
    fmt.Printf("Total: %d\n", result.Meta.Total)
    for _, p := range result.Data {
        fmt.Printf("  %s - %s\n", p.Name, p.Price)
    }
}

package sheets
 
import (
    "context"
    "encoding/json"
    "fmt"
    "net/http"
    "net/url"
    "time"
)
 
type Client struct {
    baseURL    string
    userKey    string
    apiKey     string
    httpClient *http.Client
}
 
type QueryOptions struct {
    Limit  int
    Offset int
    Search string // e.g. "category:electronics"
    Sort   string // e.g. "price" or "-price" for descending
    Fields string // comma-separated field names
}
 
func NewClient(userKey, apiKey string) *Client {
    return &Client{
        baseURL: "https://sheetsapi.gkit.mreshank.com/api/spreadsheets",
        userKey: userKey,
        apiKey:  apiKey,
        httpClient: &http.Client{Timeout: 10 * time.Second},
    }
}
 
func Query[T any](ctx context.Context, c *Client, sheet string, opts QueryOptions) (*SheetResponse[T], error) {
    endpoint := fmt.Sprintf("%s/%s/%s", c.baseURL, c.userKey, sheet)
    params := url.Values{}
    if opts.Limit > 0 {
        params.Set("limit", fmt.Sprintf("%d", opts.Limit))
    }
    if opts.Offset > 0 {
        params.Set("offset", fmt.Sprintf("%d", opts.Offset))
    }
    if opts.Search != "" {
        params.Set("search", opts.Search)
    }
    if opts.Sort != "" {
        params.Set("sort", opts.Sort)
    }
    if opts.Fields != "" {
        params.Set("fields", opts.Fields)
    }
 
    req, err := http.NewRequestWithContext(ctx, http.MethodGet, endpoint+"?"+params.Encode(), nil)
    if err != nil {
        return nil, err
    }
    if c.apiKey != "" {
        req.Header.Set("Authorization", "Bearer "+c.apiKey)
    }
 
    resp, err := c.httpClient.Do(req)
    if err != nil {
        return nil, err
    }
    defer resp.Body.Close()
 
    if resp.StatusCode != http.StatusOK {
        return nil, fmt.Errorf("api error: status %d", resp.StatusCode)
    }
 
    var result SheetResponse[T]
    return &result, json.NewDecoder(resp.Body).Decode(&result)
}

client := sheets.NewClient("YOUR_USER_KEY", "sk_your_api_key_here")
 
products, err := sheets.Query[Product](context.Background(), client, "Products", sheets.QueryOptions{
    Limit:  50,
    Search: "category:electronics",
    Sort:   "-price", // descending price
})

func FetchAll[T any](ctx context.Context, c *Client, sheet string) ([]T, error) {
    const pageSize = 100
    var all []T
    offset := 0
 
    for {
        resp, err := Query[T](ctx, c, sheet, QueryOptions{
            Limit:  pageSize,
            Offset: offset,
        })
        if err != nil {
            return nil, err
        }
        all = append(all, resp.Data...)
        offset += pageSize
        if offset >= resp.Meta.Total {
            break
        }
    }
    return all, nil
}

package main
 
import (
    "context"
    "net/http"
 
    "github.com/gin-gonic/gin"
    "yourapp/sheets"
)
 
var client = sheets.NewClient("YOUR_USER_KEY", "sk_your_api_key_here")
 
type Product struct {
    Name     string `json:"name"`
    Price    string `json:"price"`
    Category string `json:"category"`
}
 
func main() {
    r := gin.Default()
 
    r.GET("/products", func(c *gin.Context) {
        q := c.Query("q")
        search := ""
        if q != "" {
            search = "name:" + q
        }
 
        result, err := sheets.Query[Product](context.Background(), client, "Products", sheets.QueryOptions{
            Limit:  20,
            Search: search,
        })
        if err != nil {
            c.JSON(http.StatusBadGateway, gin.H{"error": err.Error()})
            return
        }
        c.JSON(http.StatusOK, result)
    })
 
    r.Run(":8080")
}

import (
    "sync"
    "time"
)
 
type cached[T any] struct {
    value   T
    expires time.Time
}
 
type TTLCache[T any] struct {
    mu    sync.Mutex
    store map[string]cached[T]
    ttl   time.Duration
}
 
func NewTTLCache[T any](ttl time.Duration) *TTLCache[T] {
    return &TTLCache[T]{store: make(map[string]cached[T]), ttl: ttl}
}
 
func (c *TTLCache[T]) Get(key string) (T, bool) {
    c.mu.Lock()
    defer c.mu.Unlock()
    entry, ok := c.store[key]
    if !ok || time.Now().After(entry.expires) {
        var zero T
        return zero, false
    }
    return entry.value, true
}
 
func (c *TTLCache[T]) Set(key string, value T) {
    c.mu.Lock()
    defer c.mu.Unlock()
    c.store[key] = cached[T]{value: value, expires: time.Now().Add(c.ttl)}
}

var cache = NewTTLCache[[]Product](5 * time.Minute)
 
func cachedProducts(ctx context.Context, client *sheets.Client) ([]Product, error) {
    if products, ok := cache.Get("products"); ok {
        return products, nil
    }
    result, err := sheets.Query[Product](ctx, client, "Products", sheets.QueryOptions{Limit: 200})
    if err != nil {
        return nil, err
    }
    cache.Set("products", result.Data)
    return result.Data, nil
}