Blog

Using Google Sheets as a REST API in NestJS with SheetsAPI

Learn how to integrate SheetsAPI into a NestJS application using HttpService, ConfigService, DTOs with class-validator, and response caching with @nestjs/cache-manager.

Jun 22, 20264 min read

#nestjs #google-sheets #rest-api #typescript #backend

Google Sheets is already where a lot of operational data lives - inventory lists, form responses, editorial calendars, configuration tables. SheetsAPI turns any Google Sheet into a REST endpoint with filtering, pagination, and row appending, without a custom backend. This post walks through integrating SheetsAPI into a NestJS service cleanly: typed DTOs, dependency injection, caching, and a controller that exposes the data over your own API.

What SheetsAPI gives you

Every spreadsheet gets a stable REST URL:

GET  https://sheetsapi.io/api/spreadsheets/{userKey}/{sheetName}
POST https://sheetsapi.io/api/spreadsheets/{userKey}/{sheetName}

Query parameters on GET: limit, offset, and filter[columnName]=value for basic equality filtering. Responses follow a consistent envelope:

{
  "data": [{ "Name": "Alice", "Score": "42" }],
  "meta": { "total": 120, "limit": 25, "offset": 0 }
}

POST accepts a JSON body matching your sheet's column names and appends a new row. Authentication is a bearer token passed as Authorization: Bearer sk_....

Project setup

Install the packages you need:

npm install @nestjs/axios axios @nestjs/cache-manager cache-manager \
            class-validator class-transformer

// sheets.module.ts
import { Module } from "@nestjs/common";
import { HttpModule } from "@nestjs/axios";
import { CacheModule } from "@nestjs/cache-manager";
import { ConfigModule } from "@nestjs/config";
import { SheetsApiService } from "./sheets-api.service";
import { SheetsController } from "./sheets.controller";
 
@Module({
  imports: [
    ConfigModule,
    HttpModule,
    CacheModule.register({ ttl: 60_000 }), // 60-second TTL
  ],
  providers: [SheetsApiService],
  controllers: [SheetsController],
})
export class SheetsModule {}

Add your API key to .env:

SHEETS_API_KEY=sk_your_key_here
SHEETS_USER_KEY=abc123

DTOs

Define the shapes for query parameters and the POST body with class-validator so NestJS can validate them before they reach your service.

// dto/query-rows.dto.ts
import { IsOptional, IsInt, Min, IsString } from "class-validator";
import { Type } from "class-transformer";
 
export class QueryRowsDto {
  @IsOptional()
  @Type(() => Number)
  @IsInt()
  @Min(1)
  limit?: number = 25;
 
  @IsOptional()
  @Type(() => Number)
  @IsInt()
  @Min(0)
  offset?: number = 0;
 
  // Arbitrary filter columns forwarded as-is
  [key: string]: unknown;
}

// dto/append-row.dto.ts
import { IsNotEmpty, IsString } from "class-validator";
 
export class AppendRowDto {
  @IsString()
  @IsNotEmpty()
  Name: string;
 
  @IsString()
  @IsNotEmpty()
  Score: string;
 
  // Extend with the columns your sheet actually has
}

Enable the global validation pipe in main.ts if you have not already:

app.useGlobalPipes(new ValidationPipe({ transform: true, whitelist: true }));

SheetsApiService

The service wraps the two SheetsAPI operations. It reads credentials from ConfigService and uses HttpService (the NestJS wrapper around Axios) so it stays injectable and testable.

// sheets-api.service.ts
import { Injectable, HttpException, HttpStatus } from "@nestjs/common";
import { HttpService } from "@nestjs/axios";
import { ConfigService } from "@nestjs/config";
import { firstValueFrom } from "rxjs";
import { QueryRowsDto } from "./dto/query-rows.dto";
import { AppendRowDto } from "./dto/append-row.dto";
 
interface SheetsResponse<T> {
  data: T[];
  meta: { total: number; limit: number; offset: number };
}
 
@Injectable()
export class SheetsApiService {
  private readonly baseUrl: string;
  private readonly headers: Record<string, string>;
 
  constructor(
    private readonly http: HttpService,
    private readonly config: ConfigService,
  ) {
    const apiKey = this.config.getOrThrow<string>("SHEETS_API_KEY");
    const userKey = this.config.getOrThrow<string>("SHEETS_USER_KEY");
    this.baseUrl = `https://sheetsapi.io/api/spreadsheets/${userKey}`;
    this.headers = { Authorization: `Bearer ${apiKey}` };
  }
 
  async getRows<T = Record<string, string>>(
    sheetName: string,
    query: QueryRowsDto,
  ): Promise<SheetsResponse<T>> {
    const { limit, offset, ...filters } = query;
 
    const params: Record<string, unknown> = { limit, offset };
    for (const [col, val] of Object.entries(filters)) {
      params[`filter[${col}]`] = val;
    }
 
    try {
      const response = await firstValueFrom(
        this.http.get<SheetsResponse<T>>(`${this.baseUrl}/${sheetName}`, {
          headers: this.headers,
          params,
        }),
      );
      return response.data;
    } catch (err) {
      const status = err?.response?.status ?? HttpStatus.BAD_GATEWAY;
      const message = err?.response?.data?.message ?? "SheetsAPI request failed";
      throw new HttpException(message, status);
    }
  }
 
  async appendRow<T = Record<string, string>>(
    sheetName: string,
    body: AppendRowDto,
  ): Promise<{ data: T }> {
    try {
      const response = await firstValueFrom(
        this.http.post<{ data: T }>(`${this.baseUrl}/${sheetName}`, body, {
          headers: { ...this.headers, "Content-Type": "application/json" },
        }),
      );
      return response.data;
    } catch (err) {
      const status = err?.response?.status ?? HttpStatus.BAD_GATEWAY;
      const message = err?.response?.data?.message ?? "SheetsAPI append failed";
      throw new HttpException(message, status);
    }
  }
}

The firstValueFrom call converts the RxJS observable that HttpService returns into a plain promise, which keeps the async/await flow readable without losing the benefits of Axios interceptors.

Controller with caching

The GET endpoint caches responses using the @CacheKey and @CacheTTL decorators from @nestjs/cache-manager. This matters when the same filtered query is hit repeatedly - you get one upstream call per TTL window instead of one per request.

// sheets.controller.ts
import { Controller, Get, Post, Body, Query, Param, UseInterceptors } from "@nestjs/common";
import { CacheInterceptor, CacheKey, CacheTTL } from "@nestjs/cache-manager";
import { SheetsApiService } from "./sheets-api.service";
import { QueryRowsDto } from "./dto/query-rows.dto";
import { AppendRowDto } from "./dto/append-row.dto";
 
@Controller("sheets/:sheet")
export class SheetsController {
  constructor(private readonly sheetsApi: SheetsApiService) {}
 
  @Get()
  @UseInterceptors(CacheInterceptor)
  @CacheKey("sheets-rows")
  @CacheTTL(60_000)
  getRows(@Param("sheet") sheet: string, @Query() query: QueryRowsDto) {
    return this.sheetsApi.getRows(sheet, query);
  }
 
  @Post()
  appendRow(@Param("sheet") sheet: string, @Body() body: AppendRowDto) {
    return this.sheetsApi.appendRow(sheet, body);
  }
}

With this in place, GET /sheets/Inventory?limit=10&offset=0&filter[Category]=Electronics maps cleanly to a SheetsAPI call with filter[Category]=Electronics forwarded as a query parameter.

Caching considerations

The default CacheInterceptor uses the request URL as the cache key. If you have multiple sheets or filter combinations, each unique URL gets its own cache entry automatically. For write-heavy scenarios, skip caching on the POST route (it is already absent above) and consider invalidating the GET cache after a successful append using Cache.del(key) from the injected CACHE_MANAGER token.

import { Inject } from '@nestjs/common';
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';
 
// In the controller constructor:
constructor(
  private readonly sheetsApi: SheetsApiService,
  @Inject(CACHE_MANAGER) private readonly cache: Cache,
) {}
 
// After a successful POST:
await this.cache.del('sheets-rows');

Testing the service

Because SheetsApiService depends on HttpService and ConfigService via injection, unit testing is straightforward with Jest mocks:

const mockHttp = { get: jest.fn(), post: jest.fn() };
const mockConfig = { getOrThrow: jest.fn((key) => "test_value") };
 
const service = new SheetsApiService(mockHttp as any, mockConfig as any);
 
mockHttp.get.mockReturnValue(
  of({
    data: {
      data: [{ Name: "Alice" }],
      meta: { total: 1, limit: 25, offset: 0 },
    },
  }),
);
 
const result = await service.getRows("Sheet1", { limit: 25, offset: 0 });
expect(result.data[0].Name).toBe("Alice");

No real HTTP calls, no real credentials - the injectable design pays off directly here.

Summary

SheetsAPI removes the need to deal with the Google Sheets API directly while keeping your data in a familiar spreadsheet. Wrapping it in a NestJS service gives you: credential management through ConfigService, typed request and response shapes through DTOs, validation through ValidationPipe, and response caching through CacheInterceptor. The result is a production-ready integration that treats Google Sheets as just another data source behind a clean interface.

All posts