Google Sheets as a REST API in Kotlin

// models/SheetResponse.kt
data class SheetMeta(val total: Int, val limit: Int, val offset: Int)
 
data class SheetResponse<T>(
    val data: List<T>,
    val meta: SheetMeta
)
 
data class Product(
    val name: String,
    val price: String,
    val category: String,
    val stock: String
)

dependencies {
    implementation("io.ktor:ktor-client-cio:2.3.12")
    implementation("io.ktor:ktor-client-content-negotiation:2.3.12")
    implementation("io.ktor:ktor-serialization-kotlinx-json:2.3.12")
}

import io.ktor.client.*
import io.ktor.client.call.*
import io.ktor.client.engine.cio.*
import io.ktor.client.plugins.contentnegotiation.*
import io.ktor.client.request.*
import io.ktor.serialization.kotlinx.json.*
import kotlinx.serialization.json.Json
 
val client = HttpClient(CIO) {
    install(ContentNegotiation) {
        json(Json { ignoreUnknownKeys = true })
    }
}
 
suspend fun fetchProducts(userKey: String, apiKey: String): SheetResponse<Product> {
    return client.get("https://sheetsapi.gkit.mreshank.com/api/spreadsheets/$userKey/Products") {
        parameter("limit", 20)
        parameter("sort", "name")
        header("Authorization", "Bearer $apiKey")
    }.body()
}

import kotlinx.serialization.Serializable
 
@Serializable data class SheetMeta(val total: Int, val limit: Int, val offset: Int)
@Serializable data class SheetResponse<T>(val data: List<T>, val meta: SheetMeta)
@Serializable data class Product(val name: String, val price: String, val category: String)

class SheetsApiClient(
    private val userKey: String,
    private val apiKey: String? = null
) {
    private val http = HttpClient(CIO) {
        install(ContentNegotiation) { json(Json { ignoreUnknownKeys = true }) }
    }
 
    private val base = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets"
 
    suspend inline fun <reified T : Any> query(
        sheet: String,
        limit: Int = 20,
        offset: Int = 0,
        search: String? = null,
        sort: String? = null,
        fields: String? = null
    ): SheetResponse<T> = http.get("$base/$userKey/$sheet") {
        parameter("limit", limit)
        parameter("offset", offset)
        search?.let { parameter("search", it) }
        sort?.let { parameter("sort", it) }
        fields?.let { parameter("fields", it) }
        apiKey?.let { header("Authorization", "Bearer $it") }
    }.body()
 
    suspend inline fun <reified T : Any> fetchAll(sheet: String, pageSize: Int = 100): List<T> {
        val all = mutableListOf<T>()
        var offset = 0
        while (true) {
            val page = query<T>(sheet, limit = pageSize, offset = offset)
            all += page.data
            offset += pageSize
            if (offset >= page.meta.total) break
        }
        return all
    }
 
    fun close() = http.close()
}

val client = SheetsApiClient("YOUR_USER_KEY", apiKey = "sk_your_api_key_here")
 
// Search and sort
val electronics = client.query<Product>(
    "Products",
    limit = 50,
    search = "category:electronics",
    sort = "-price"
)
 
// Fetch everything
val allProducts = client.fetchAll<Product>("Products")
println("${allProducts.size} total products")
 
client.close()

import java.net.URI
import java.net.http.HttpClient
import java.net.http.HttpRequest
import java.net.http.HttpResponse
import kotlinx.serialization.json.Json
import kotlinx.serialization.decodeFromString
 
val httpClient = HttpClient.newHttpClient()
val json = Json { ignoreUnknownKeys = true }
 
fun fetchProducts(userKey: String, apiKey: String): SheetResponse<Product> {
    val uri = URI("https://sheetsapi.gkit.mreshank.com/api/spreadsheets/$userKey/Products?limit=20&sort=name")
    val request = HttpRequest.newBuilder(uri)
        .header("Authorization", "Bearer $apiKey")
        .GET()
        .build()
 
    val response = httpClient.send(request, HttpResponse.BodyHandlers.ofString())
    check(response.statusCode() == 200) { "API error ${response.statusCode()}" }
 
    return json.decodeFromString(response.body())
}

import kotlinx.coroutines.*
 
fun main() = runBlocking {
    val client = SheetsApiClient("YOUR_USER_KEY", "sk_your_api_key_here")
 
    val result = runCatching {
        client.query<Product>("Products", limit = 10, sort = "name")
    }
 
    result.fold(
        onSuccess = { resp ->
            println("Total: ${resp.meta.total}")
            resp.data.forEach { println("  ${it.name} - ${it.price}") }
        },
        onFailure = { e ->
            System.err.println("SheetsAPI error: ${e.message}")
        }
    )
 
    client.close()
}

// SheetsApiService.kt
@Service
class SheetsApiService(
    @Value("\${sheetsapi.user-key}") private val userKey: String,
    @Value("\${sheetsapi.api-key:}") private val apiKey: String,
    private val restClient: RestClient
) {
    fun <T> query(sheet: String, responseType: Class<T>, search: String? = null): T {
        return restClient.get()
            .uri("https://sheetsapi.gkit.mreshank.com/api/spreadsheets/$userKey/$sheet") {
                it.queryParamIfPresent("search", Optional.ofNullable(search))
            }
            .retrieve()
            .body(responseType)!!
    }
}

# application.yml
sheetsapi:
  user-key: YOUR_USER_KEY
  api-key: sk_your_api_key_here