[serverless+typescript+aws+googleapis] (3) google sheet api 연동하기

[serverless+typescript+aws+googleapis] (1) aws 계정설정과 serverless framework 기본 설정하기

[serverless+typescript+aws+googleapis] (2) prisma 로 aws rds 연결하기

 

 위 글들과 이어집니다.

 

Google Sheet 연동하기 (feat. Google API)

위 글을 통해 google cloud platform 과 관련된 셋팅을 전부 마치고 인증을 위한 json파일을 구비합니다.(위 글에 좋아요 꼭 부탁드릴게요! 너무 감사한 글입니다!)

 

1. google cloud platform 에서 google sheet 관련 셋팅을 마칩니다.

 

2. 원하는 sheet 생성하여 데이터 연동 준비를 합니다. 아래 사진에서 확인할 수 있는 2가지가 필요합니다.

- 구글스프레드시트 id

- 단위 sheet id

구글스프레드시트id와 단위sheetid는 다음과 같습니다.

 

3. googleapis 라이브러리를 설치합니다.

npm i googleapis

 

4. 세부 구현은 다음과 같습니다.

- api password 가 맞지 않으면 에러를 반환한다.

- apiKeyFile(json)을 런타임 시 생성하여 google api 접근 시 사용한다. 

- 시트의 특정 범위에 속하는 데이터들을 clear한다.

- 조건에 맞게 쿼리한 데이터를 google sheet로 append한다.

- apiKeyFile을 삭제한다.

// src/functions/spread-sheet/handler.ts

import { google } from "googleapis";
import { PrismaClient } from "@prisma/client";
import type { ValidatedEventAPIGatewayProxyEvent } from "@libs/api-gateway";
import { formatJSONResponse } from "@libs/api-gateway";
import { middyfy } from "@libs/lambda";
import schema from "./schema";
import { EnvGetter } from "@libs/utils/env-getter";
import { convertStringifiedJsonToFile } from "@libs/utils/convert-stringified-json-to-file";
import { unlinkFile } from "@libs/utils/unlink-file";

const spreadsheetId = EnvGetter.SPREAD_SHEET_ID;

interface SomeData {
  /* some fields */
}

const spreadSheet: ValidatedEventAPIGatewayProxyEvent<typeof schema> = async (
  event
) => {
  if (event.body.password !== EnvGetter.PASSWORD) {
    return formatJSONResponse(429, {
      message: "Invalid password",
    });
  }

  // Prisma client 초기화
  const prisma = new PrismaClient();

  const keyFilePath = convertStringifiedJsonToFile(
    EnvGetter.GOOGLE_SHEET_API_JSON,
    EnvGetter.GOOGLE_SHEET_API_JSON_FILE_NAME
  );

  // Google Sheets API 초기화
  const sheets = google.sheets("v4");
  const auth = new google.auth.GoogleAuth({
    keyFile: keyFilePath, // 서비스 계정 키 파일 경로
    scopes: ["https://www.googleapis.com/auth/spreadsheets"],
  });

  // Prisma를 사용하여 데이터베이스에서 데이터 조회
  const queryDatas: SomeData[] | null = await prisma.$queryRaw``;

  if (!queryDatas) {
    return formatJSONResponse(304, {
      message: "There is no change",
    });
  }

  // Google Sheets API 인증
  await auth.getClient();

  // 시트 전체를 대상으로 데이터를 삭제
  await sheets.spreadsheets.values.clear({
    auth,
    spreadsheetId,
    range: `${EnvGetter.TARGET_SHEET_NAME}!A2:CW`, // 시트의 모든 데이터 범위 (A열부터 CW열까지)
  });

  // Google Spreadsheet에 데이터 쓰기
  await sheets.spreadsheets.values
    .append({
      auth,
      spreadsheetId,
      range: EnvGetter.TARGET_SHEET_NAME, // 쓰기를 원하는 시트 이름
      valueInputOption: "USER_ENTERED",
      requestBody: {
        values: queryDatas.map((data) =>
          Object.values(data).map((sd) =>
            typeof sd === "bigint" ? sd.toString() : sd
          )
        ), // 예시: 각 유저의 이름과 이메일
      },
    })
    .then(() =>
      console.info(
        `Completed to append total ${queryDatas.length} rows to Google Sheet`
      )
    )
    .catch((error) => console.error(error));

  unlinkFile();

  /**
   * 유의 사항
   * api gateway 의 응답 최대 제한 시간이 30초인데 현재 위 프로세스는 30초 내외여서 api gateway에서 컷 당하는 경우가 많음.
   * 대신 위와 같이 then, catch 체이닝을 통해 로그를 남기고 있음.
   */
  return formatJSONResponse(200, {
    message: `ok`,
  });
};

export const main = middyfy(spreadSheet);

// convert-stringified-json-to-file.ts

import * as fs from "fs";
import * as path from "path";

export function convertStringifiedJsonToFile(
  jsonString: string,
  filename: string
) {
  if (!filename.includes(".json")) {
    throw new Error("json 확장자만 사용 가능합니다");
  }

  const tempJson = JSON.parse(jsonString);

  const tempFilePath = path.join("/tmp", filename);

  fs.writeFileSync(tempFilePath, JSON.stringify(tempJson, null, 2));

  return tempFilePath;
}

// unlink-file.ts

import * as path from "path";
import * as fs from "fs";
import { EnvGetter } from "./env-getter";

export function unlinkFile() {
  const tempDir = path.join("/tmp", EnvGetter.GOOGLE_SHEET_API_JSON_FILE_NAME);
  fs.unlinkSync(tempDir);
}

 

5. serverless offline을 실행하여 원하는 값을 원하는 google sheet에 append할 수 있는지 테스트합니다.

 

6. AWS Lambda 콘솔에서 구성 > 환경 변수에 위에서 사용된 모든 환경 변수들을 셋팅합니다.

- GOOGLE_SHEET_API_JSON은 json파일의 내용물을 stringify해서 저장합니다.

 

7. Lambda 를 실제 환경에 배포한 후 테스트를 진행합니다.

 

마무리

serverless framework 셋팅부터 prisma 연동, googleapis 연동까지 많이 러프하게 알아봤습니다. 디테일하게 쓰기 시작하면 너무 시간이 오래걸리는 탓에 이렇게 정리하게된 점 양해드립니다. 관련하여 궁금하신 사항이 있으면 댓글로 질문주시면 답변 드리겠습니다!

 

참고한 모든 블로그의 주인분들께 다시 한번 감사드리며, 작성한 이번 시행착오도 많은 분들께 도움이 되었으면 좋겠습니다.