영세한 개발자나 취미로 개발을 하는 사람이라면 항상 생각하는 것이 내가 개발한 웹서비스를 무료로 서버에 올려서 사용할 수 있을까? 라는 것입니다. 스태틱 한 웹서비스인 경우는 상대적으로 길이 많은데 데이터베이스까지 붙여야 한다면 이건 또 말이 다르게 됩니다. 고성능의 데이터베이스까지는 필요도 없는데 말이죠.
저도 이번에 Next.js(React)로 아주 간단한 웹서비스를 토이 프로그램으로 개발하기로 하면서 데이터베이스에 대해 문득 든 생각이 Google Sheets를 사용하면 되지 않으까?였습니다. 그래서 여기저기 이것저것 알아보다가 굳이 고성능이 필요하지 않다면 Google Sheets가 대안이 될 수도 있겠다고 판단하여 시도해 봤습니다. 결과는 어느 정도 성공적이어서 잊어버리기 전에 공유를 하려고 합니다.
Google Sheets의 데이터베이스에 대한 한계
Google Sheets를 데이터베이스 활용하기 위해서는 몇 가지 한계에 대해서 고려해 봐야 합니다.
데이터 동기화
Google Sheets를 데이터베이스로 사용하려면, Google Sheets API를 사용하여 데이터를 동기화해야 합니다. Google Sheets API는 제한된 트랜잭션을 지원하기 때문에, 데이터 동기화 과정에서 성능 저하가 발생할 수 있습니다.
데이터의 양
Google Sheets는 최대 1,048,576개의 행과 256개의 열을 지원합니다. 따라서 데이터양이 많을 경우 Google Sheets를 데이터베이스로 사용하기에는 적합하지 않습니다.
데이터 종류
Google Sheets는 텍스트, 숫자, 날짜, 시간 등 다양한 데이터 형식을 지원합니다. 하지만, JSON, XML 등과 같은 복잡한 데이터 형식을 지원하지 않습니다.
보안
Google Sheets는 기본적으로 공개되어 있습니다. 따라서, Google Sheets를 데이터베이스로 사용하려면, 데이터 보안을 위한 추가 조치를 취해야 합니다.
Google Sheets의 데이터베이스로 사용하기
위와 같은 한계에도 불구하고 사용하기로 했다면 아래와 같은 설정 작업이 필요합니다.
Google API 사용 신청
GCP(Google Cloud Platform) 프로젝트 생성
먼저 프로젝트를 하나 생성해야 합니다. 이 프로젝트에 계정을 추가하고 이 계정으로 API를 사용하게 됩니다. 아래 URL에 접속하여 신청할 수 있습니다.
https://console.cloud.google.com/projectselector2/apis/credentials
처음 사용한다면 먼저 서비스 약관에 대해서 승인을 해야 합니다. 승인이 완료되었다면 이제 프로젝트를 하나 생성합니다.
- 프로젝트 선택 버튼을 누릅니다.
- 우상단에 있는 새 프로젝트 버튼을 누릅니다.
- 프로젝트 이름은 임의로 넣어주면 됩니다.
- 만들기 버튼을 눌러주세요.
프로젝트에 계정 생성
프로젝트가 생성이 되었습니다.
이제 생성된 프로젝트에 계정을 하나 등록하고 이 계정으로 인증을 해야 합니다.
- 사용자 인증정보 메뉴를 선택합니다.
- 사용자 인증 정보 만들기 > 서비스 계정 을 선택합니다.
.
- 서비스 계정 ID는 임의로 작성하시면 됩니다.
- 완료 버튼을 눌러서 계정을 생성합니다. 이제 이 계정으로 인증해서 API를 사용할 수 있습니다.
키(Key) 인증방법 생성
계정까지 생성이 되었습니다.
이제 외부(Next.js)에서 Sheets API를 사용하기 위해서 키(Key)를 활용하면 됩니다. 그러기 위해서 키(Key)를 발급 받아야 합니다.
- 생성된 계정의 우측 끝에 있는
...을 눌러줍니다. - 키 관리를 선택합니다.
- 키 추가를 선택하고 새 키 만들기를 눌러줍니다.
- 키 유형으로 JSON을 선택합니다.
- 만들기 버튼을 클릭해서 생성해 줍니다.
- 그러면 이렇게 JSON으로 되어 있는 Key 파일을 다운로드하게 됩니다.
중요한 사항은 이 Key 파일을 잘 보관해야 합니다. 왜냐하면 보안을 위해 더 이상 웹사이트에서 동일한 Key를 받을 수가 없습니다. 유일하게 지금 다운로드한 파일 하나만 있습니다. 다시 받으려면 키 관리에서 새로 키를 발급을 받아야 합니다. 또한 다운로드한 파일도 잘 관리해야 합니다. 혹시나 유출되거나 공개된 Git에 올라가서 다른 사람이 가져가게 된다면 파국을 맞을 수도 있습니다.
Google Sheets API 서비스 활성화.
계정과 키(key)를 생성했으니 이제 이 계정으로 어떤 서비스의 API를 사용할지 선택해야 합니다. Google API의 종류는 정말 엄청 많은데, 그중에서 우리가 지금 사용해야 할 Google Sheets를 찾아서 활성화 해줘야 합니다.
.
- 좌측 상단의 햄버거 메뉴를 선택합니다.
- 메뉴 중 API 및 서비스 메뉴를 선택합니다.
- 라이브러리 메뉴를 선택합니다.
- 라이브러리 화면의 검색창에 google sheets api로 검색을 합니다.
찾은 화면에서 사용 버튼을 눌러서 활성화를 시켜 줍니다.
Googoe Sheets 사용 권한 등록
API쪽에서의 처리는 다 되었습니다.
이제 이 API 계정이 해당 Google Sheets에 대해 편집자 권한을 가질 수 있게 등록을 해줘야 Write를 할 수 있습니다.
이 계정을 확인할 수 있는 방법은 이 계정의 이메일 주소이기 때문에 이 이메일 주소를 Google Sheets의 공유자에 등록을 해줍니다.
이메일 주소는 이전에 봤던 화면에서 서비스 계정 메뉴에 있거나 좀 전에 다운로드한 Key.json 파일 안에 있습니다.
사용할 구글 시트를 하나 만들고 위의 이메일 주소를 편집자 권한으로 등록해 줍니다.
- 우측 상단에 있는 공유 버튼을 눌러줍니다.
- 주소 란에 위에서 확인한 계정의 이메일 주소를 넣어 줍니다.
- 권한을 편집자 권한을 주어서 쓰기가 가능하도록 해줍니다.
- 전송을 눌러서 등록해 줍니다.
이제 해당 계정의 API로 이 시트에 내용을 쓰거나 읽을 수 있게 되었습니다.
Google Sheet의 주소 표시줄에서 빨간색으로 칠한 코드 부분이 Document ID이며 이코드를 사용하여 API로 읽거나 쓸 수 있게 됩니다.
next.js에서 활용하기
이제 Google 쪽에서는 준비를 끝냈고 next.js(React) 쪽에서 세팅을 해주면 됩니다. Next.js는 설치되었다는 전제입니다.
Google Sheets를 편하게 사용하게 해주는 패키지(google-spreadsheet)
실력만 좋다면 전용 라이브러리 없이 googleapis 만으로도 사용이 가능하긴 합니다만 그렇지 못한 사람들을 위해 Theo Ephraim라는 분이 많은 사람들이 좀더 편리하게 사용할 수 있도록 google-spreadsheet를 만들어서 github에 배포를 해주었습니다.(감사합니다) 이것을 활용해 보겠습니다.
아래 패키지를 설치해 줍니다.
Google Sheet를 쉽게 사용할 수 있게 해주는 패키지입니다.
패키지 매니저가 npm인 경우 :
npm i google-spreadsheet --save
패키지 매니저가 yarn인 경우 :yarn add google-spreadsheet
Google에 JWT로 인증을 할 수 있게 해주는 패키지입니다.
패키지 매니저가 npm인 경우 :
npm i google-auth-library --save
패키지 매니저가 yarn인 경우 :yarn add google-auth-library
코드 구조 설명
편집기는 VS code를 사용했습니다. 폴더의 위치에 대한 안내와 가장 중요한 google-spreadsheet를 사용하기 위한 사전 코딩에 대해서 먼저 말씀드리겠습니다. 이 코드는 제가 보기 편하게 작성한 것이기 때문에 본인에게 맞게 재 설정하셔도 됩니다.
- 라우터를 구성하는 app 폴더에 화면에 접근만 하면 바로 Sheet의 Row 읽기와 쓰기를 할 수 있는 코딩을 작성해 놨습니다.
- 중요한 부분으로 api 폴더에 googleSheet와 통신을 할 수 있는 코드가 있는 곳입니다. 바로 옆의 코드 내용이 있는 위치입니다.
- key.json은 위에 서 다운로드한 json 파일의 긴 이름(
test-sheet-project-40xxx-15xxx611.json)을 관리하기 편하게 이름을 변경해 놓은 것입니다. - 본격적인 코드를 작성하기 전에 패키지를 import 한 부분입니다.
// src > core > api > googleSheet > pages.ts
import { GoogleSpreadsheet } from "google-spreadsheet"; // google-spreadsheet를 불러옵니다.
import { JWT } from "google-auth-library"; // Google에 JWT 인증을 위해 google-auth-library를 불러옵니다.
import credential from "../../../../key.json"; // 다운로드한 인증 key.json을 불러옵니다.
import { VAR_GOOGLE_SHEETS } from '@/core/constants/common'; // Sheets에 사용할 상숫값을 불러옵니다.
import {OrderRowData} from '@/@types/common'; // Typescript로 코딩하는지라 값에 대한 Type를 불러옵니다.
const SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]; // 해당 API로 sheets를 손대겠다는 범위 설정입니다.
const documentID = "1DiS8M8T6R5duaRiMddaZ-Q-LeZKBENQSOM7bMtRbOz8"; // Sheet의 고유 문서 ID입니다.
const jwt = new JWT({ ... }); //JWT로 위에서 불러온 key.json 값을 대입해 넣습니다.
이렇게 기본 준비는 끝났고 이제 공통으로 사용하는 코드입니다.
공통 코드
문서 전체 불러오기
documentID에 해당하는 sheets의 내용을 모두 로드해오는 코드입니다. Read, Write 할 때 불러와서 사용합니다.
// src > core > api > googleSheet > pages.ts
//////////////////////////////////////////////////////
// 공통 : Google Sheets에 접속해서 Load 하기
///////////////////////////////////////////////////////
export async function loadGoogleSheet() {
try {
const doc = new GoogleSpreadsheet(documentID, jwt);
await doc.loadInfo();
return doc;
} catch (error) {
console.error("Sheet Load Rows Error:", error);
throw new Error("Failed to Load Rows data.");
}
}
불러온 문서 중 원하는 Row만 발췌하기
아래는 위에서 불러온 전체 문서에서 내가 선택한 시트(탭)에서 id(여기서는 "번호")에 해당하는 Row(한 줄)의 Index 값을 찾아내는 코드입니다.
// src > core > api > googleSheet > pages.ts
//////////////////////////////////////////////////////
// 공통 : 해당하는 행(Row) 불러오기
///////////////////////////////////////////////////////
export async function findRow(id: string) {
try {
const doc = await loadGoogleSheet(); // 문서 불러오기
const sheet = doc.sheetsByTitle["주문리스트"]; // 문서에서 시트 이름에 해당하는 시트 선택하기
await sheet.loadHeaderRow(10); // 헤더에 해당하는 INDEX 번호를 설정함
const rows = await sheet.getRows<OrderRowData>(); // 해당 시트의 모든 rows를 불러옴
const rowsLengh = rows.length; // 총 Rows의 개수
const rowNumber = rows.findIndex((row) => row.get("번호") === id); // 번호가 id에 해당하는 값인 Row Index 번호를 발췌함
return {rowsLengh, rowNumber, rows}; // 총 Rows 수, 현재 row의 Index 값, 현재 Sheet의 값을 전달함
} catch (error) {
console.error("Sheet find row Error:", error);
throw new Error("Failed to find Row data.");
}
}
문서 참조
코드를 작성하기 전에 Sheet를 간략히 설명드립니다.
Index 번호는 Sheet의 고유한 줄번호입니다. sheet.loadHeaderRow(10); 여기에서 10은 Index 10번째 줄을 헤더로 사용하겠다는 선언입니다. 만약 이 선언이 없다면 기본으로 Index 1번(첫 번째 줄)을 헤더로 사용하게 됩니다.
Header의 이름은 추후에 설명드리겠지만 미리 작성을 해놔도 되고 sheet.setHeaderRow(['해더이름1', '해더이름2',...], 10); 코드를 통해서 설정할 수도 있습니다.
하단의 시트(탭)의 주문리스트 이름을 doc.sheetsByTitle["주문리스트"]로 설정하여 해당 시트를 특정하게 됩니다.
Write(쓰기) 코드
이제는 아래의 코드를 활용하여 쓰기를 할 수 있습니다.
// src > core > api > googleSheet > pages.ts
//////////////////////////////////////////////////////
// Create : Google Sheets에서 해당 Sheet의 하단에 Row 데이터 추가
///////////////////////////////////////////////////////
export async function setRow(orderData: OrderRowData) {
try {
const doc = await loadGoogleSheet(); // 문서 불러오기
const sheet = doc.sheetsByTitle["주문리스트"]; // 문서 중 특정 시트(탭)을 특정함
await sheet.setHeaderRow(['번호','아이디','부서','이름', ...], 10); // 10번째 라인을 헤더로 설정하고 해더이름을 작성함
const rows = await sheet.getRows<OrderRowData>(); // 해당 시트의 모든 rows를 불러옴
const rowsLengh = rows.length; // 총 Rows의 개수
const orderNo = rowsLengh + 1; // 총 Rows에서 1을 더해서 문서 번호를 생성함
orderData.번호 = orderNo.toString(); // 배열에 생성된 문서 번호를 추가함(문자열로 추가함)
const res = await sheet.addRow(orderData); // Google Sheets에 추가함
} catch (error) {
console.error("Sheet save Row Error:", error);
throw new Error("Failed to save Row data.");
}
}
// src > app > useSheet > write > page.tsx
import { setRow } from "../../../core/api/googleSheet/page";
export default async function Page() {
const nowTime = new Date();
const orderData = {
// 임의로 값을 넣어서 테스트로 사용함
번호: "", // 일련번호는 등록할 때 붙임
아이디: "이름 + 전화번호",
부서: "얍 부서",
이름: "얍 이름",
전화번호: "얍 전화번호",
단가: 40000,
주문수량: 5,
주문금액: 200000,
입금방법: "계좌이체",
주문일: nowTime,
수정일: nowTime,
입금완료: false,
입금완료일: nowTime,
입금관리자확인: false,
입금관리자확인일: nowTime,
인수완료: false,
인수완료일: nowTime,
인수관리자확인: false,
인수관리자확인일: nowTime,
비고: "",
};
const orderRow = await setRow(orderData); // api의 write Fn으로 보냄
return <div></div>;
}
브라우저에서 쓰기 URL을 호출하면 자동으로 쓰기가 실행됩니다.
아래와 같이 한 줄이 추가된 것을 확인할 수 있습니다.
Read(읽기) 코드
이제는 아래의 코드를 활용하여 특정 Row를 읽을 수 있습니다.
// src > core > api > googleSheet > pages.ts
//////////////////////////////////////////////////////
// Read : Google Sheets에서 해당 Sheet 중 ID와 일치하는 ROW를 불러옴
///////////////////////////////////////////////////////
export async function getRow(id: string) {
try {
// id는 문서 번호이며 해당 번호에 대한 Row의 Index 번호를 찾아 회신해 줌
const { rowsLengh, rowNumber, rows } = await findRow(id); // 위의 공통 코드에서 불러옴
// 회신 받은 Index 번호를 활용해서 Rows에서 Row 값을 불러와 데이터를 객체로 저장
const orderRow: OrderRowData = {
번호: rows[rowNumber].get("번호"),
아이디: rows[rowNumber].get("아이디"),
부서: rows[rowNumber].get("부서"),
이름: rows[rowNumber].get("이름"),
전화번호: rows[rowNumber].get("전화번호"),
단가: rows[rowNumber].get("단가"),
주문수량: rows[rowNumber].get("주문수량"),
주문금액: rows[rowNumber].get("주문금액"),
입금방법: rows[rowNumber].get("입금방법"),
주문일: rows[rowNumber].get("주문일"),
수정일: rows[rowNumber].get("수정일"),
입금완료: rows[rowNumber].get("입금완료"),
입금완료일: rows[rowNumber].get("입금완료일"),
입금관리자확인: rows[rowNumber].get("입금관리자확인"),
입금관리자확인일: rows[rowNumber].get("입금관리자확인일"),
인수완료: rows[rowNumber].get("인수완료"),
인수완료일: rows[rowNumber].get("인수완료일"),
인수관리자확인: rows[rowNumber].get("인수관리자확인"),
인수관리자확인일: rows[rowNumber].get("인수관리자확인일"),
비고: rows[rowNumber].get("비고"),
};
return { rowsLengh, orderRow }; // 총 주문수, Row 값 // 불러온 Row의 값을 객체에 담아 Return 해줌
} catch (error) {
console.error("Sheet read Row Error:", error);
throw new Error("Failed to read Row data.");
}
}
브라우저에서 읽기 URL을 호출하면 자동으로 읽기가 실행됩니다.
위의 화면처럼 한 줄을 불러오는 것을 볼 수 있습니다. (이미지에서는 이름과 아이디만 표시되어 있음)
Update와 Delete도 쉽게 가능하니 그 부분은 직접 해보시기 바랍니다.
