Google Sheets as a REST API in Java (Spring Boot)

// src/main/java/com/example/sheets/SheetResponse.java
package com.example.sheets;
 
import java.util.List;
 
public record SheetResponse<T>(List<T> data, SheetMeta meta) {
  public record SheetMeta(int total, int limit, int offset) {}
}

// src/main/java/com/example/sheets/Product.java
package com.example.sheets;
 
public record Product(String name, String price, String category, String stock) {}

// src/main/java/com/example/sheets/SheetsApiService.java
package com.example.sheets;
 
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.ParameterizedTypeReference;
import org.springframework.stereotype.Service;
import org.springframework.web.client.RestClient;
import org.springframework.web.util.UriComponentsBuilder;
 
@Service
public class SheetsApiService {
 
    private static final String BASE = "https://sheetsapi.gkit.mreshank.com/api/spreadsheets";
 
    private final RestClient restClient;
    private final String userKey;
 
    public SheetsApiService(
            @Value("${sheetsapi.user-key}") String userKey,
            @Value("${sheetsapi.api-key:}") String apiKey) {
        this.userKey = userKey;
        this.restClient = RestClient.builder()
            .defaultHeader("Authorization", "Bearer " + apiKey)
            .build();
    }
 
    public <T> SheetResponse<T> query(
            String sheet,
            ParameterizedTypeReference<SheetResponse<T>> type,
            QueryOptions opts) {
 
        var uri = UriComponentsBuilder
            .fromUriString(BASE + "/{key}/{sheet}")
            .queryParamIfPresent("limit",  java.util.Optional.ofNullable(opts.limit()))
            .queryParamIfPresent("offset", java.util.Optional.ofNullable(opts.offset()))
            .queryParamIfPresent("search", java.util.Optional.ofNullable(opts.search()))
            .queryParamIfPresent("sort",   java.util.Optional.ofNullable(opts.sort()))
            .queryParamIfPresent("fields", java.util.Optional.ofNullable(opts.fields()))
            .buildAndExpand(userKey, sheet)
            .toUri();
 
        return restClient.get()
            .uri(uri)
            .retrieve()
            .body(type);
    }
 
    public record QueryOptions(
        Integer limit,
        Integer offset,
        String search,
        String sort,
        String fields
    ) {
        public static QueryOptions defaults() {
            return new QueryOptions(20, 0, null, null, null);
        }
    }
}

# src/main/resources/application.properties
sheetsapi.user-key=YOUR_USER_KEY
sheetsapi.api-key=sk_your_api_key_here

// src/main/java/com/example/sheets/ProductsController.java
package com.example.sheets;
 
import org.springframework.core.ParameterizedTypeReference;
import org.springframework.web.bind.annotation.*;
 
import java.util.List;
 
@RestController
@RequestMapping("/products")
public class ProductsController {
 
    private final SheetsApiService sheetsApi;
 
    public ProductsController(SheetsApiService sheetsApi) {
        this.sheetsApi = sheetsApi;
    }
 
    @GetMapping
    public SheetResponse<Product> list(
            @RequestParam(defaultValue = "20") int limit,
            @RequestParam(defaultValue = "0") int offset,
            @RequestParam(required = false) String q) {
 
        String search = (q != null && !q.isBlank()) ? "name:" + q : null;
 
        return sheetsApi.query(
            "Products",
            new ParameterizedTypeReference<>() {},
            new SheetsApiService.QueryOptions(limit, offset, search, "name", null)
        );
    }
}

// src/main/java/com/example/Application.java
@SpringBootApplication
@EnableCaching
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

@Cacheable(value = "products", key = "#search ?: 'all'")
public List<Product> cachedProducts(String search) {
    var result = sheetsApi.query(
        "Products",
        new ParameterizedTypeReference<SheetResponse<Product>>() {},
        new SheetsApiService.QueryOptions(200, 0, search, null, null)
    );
    return result.data();
}

<!-- pom.xml -->
<dependency>
    <groupId>com.github.ben-manes.caffeine</groupId>
    <artifactId>caffeine</artifactId>
</dependency>

spring.cache.type=caffeine
spring.cache.caffeine.spec=maximumSize=500,expireAfterWrite=5m

import org.springframework.web.reactive.function.client.WebClient;
import reactor.core.publisher.Mono;
 
@Service
public class ReactiveSheetsService {
 
    private final WebClient webClient;
    private final String userKey;
 
    public ReactiveSheetsService(
            @Value("${sheetsapi.user-key}") String userKey,
            @Value("${sheetsapi.api-key}") String apiKey) {
        this.userKey = userKey;
        this.webClient = WebClient.builder()
            .baseUrl("https://sheetsapi.gkit.mreshank.com")
            .defaultHeader("Authorization", "Bearer " + apiKey)
            .build();
    }
 
    public <T> Mono<SheetResponse<T>> query(
            String sheet,
            Class<T> itemType,
            int limit) {
        return webClient.get()
            .uri("/api/spreadsheets/{key}/{sheet}?limit={limit}", userKey, sheet, limit)
            .retrieve()
            .bodyToMono(new ParameterizedTypeReference<SheetResponse<T>>() {});
    }
}

public <T> List<T> fetchAll(String sheet, ParameterizedTypeReference<SheetResponse<T>> type) {
    List<T> all = new java.util.ArrayList<>();
    int offset = 0;
    final int pageSize = 100;
 
    while (true) {
        var page = sheetsApi.query(sheet, type,
            new SheetsApiService.QueryOptions(pageSize, offset, null, null, null));
        all.addAll(page.data());
        offset += pageSize;
        if (offset >= page.meta().total()) break;
    }
    return all;
}