Google Sheets 구글 시트에 데이터 입력 자동화하기

현재 백업 받은 파일들의 용량 추이를 확인하기 위해 Google Sheet에 백업 받은 파일의 용량을 기록하고 있다.

근데 일일이 데이터를 입력하는 건 굉장히 귀찮은 일이고, 오류가 발생하기 쉽다.

따라서 이 작업을 자동화해보고자 한다.

기본 개념

Google Cloud에서 Google Sheets 편집할 수 있는 service account 생성 및 Google Sheets와 연동
실행파일 작성 (.bat, .py)

Google Cloud & Google Sheets

프로젝트 생성

프로젝트 생성을 위해 구글 클라우드 플랫폼 홈페이지 에 접속하여 우측 상단 "콘솔" 화면에 들어간다.

"좌측 메뉴 > IAM 및 관리자 > 프로젝트 만들기"를 클릭한다.

혹은 우측에 "프로젝트 만들기" 라고 떠있기도 하다. 그거 누르면 된다.

프로젝트 이름을 입력하고 만들기를 눌러 프로젝트를 생성한다.

Google Sheets API 설정

좌측 상단에서 방금 생성한 프로젝트를 선택하고 "API 개요"로 이동한다.

혹시 대시보드가 보이지 않는다면 "좌측 메뉴바 > Cloud 개요 > 대시보드" 를 선택하면 보인다.

이렇게 하면 해당 프로젝트의 "API 및 서비스 공간"에 들어오게 될텐데, 아직 어떤 API를 사용할지 설정한 게 아무것도 없다. 나는 Google Sheets에 데이터를 업데이트할 거니까 좌측 메뉴바에 "라이브러리"를 선택해서 "Google Sheets API" 를 찾아 "사용"을 클릭한다.

Google Sheets API 서비스 계정 추가

Google Sheets API가 사용 설정 되었으니, 해당 API를 사용할 서비스 계정을 추가한다.

좌측 메뉴바에서 "사용자 인증 정보" 를 선택하고 "사용자 인증 정보 만들기 > 서비스 계정" 을 선택한다.

서비스 계정 이름을 입력한다.

나는 Google Sheets에 데이터를 조회만 하는 것이 아니라 입력할 예정이므로 액세스 권한을 "편집자"로 설정한다.

Google Sheets에 서비스 계정 권한 추가

만들어진 서비스 계정의 이메일을 복사하여, 데이터를 추가할 Google Sheets에 편집자로 권한을 넣어준다.

Google Sheets API 서비스 계정의 Private Key 발급

이메일만 알면 누구나 편집할 수 있으므로, Private Key 파일을 다운받아서 참조해야 해당 API를 사용할 수 있다.

Private Key 파일을 다운로드받기 위해 이메일을 클릭한다.

키 탭에 들어가 키 추가 버튼을 클릭한다.

비공개 키의 유형을 선택한다. 보통 JSON을 선택하는 듯 하다.

비공개 키가 저장된다. 저장된 경로는 py 파일에서 참조할 거니까 기억해두자.

실행 파일 작성

bat 실행 파일 작성

bat 실행파일은 그저 python 파일을 실행하기 위함이다.

아래와 같이 작성한다. bat 실행파일와 python 파일은 한 디렉토리 안에 있어야 한다. (그냥 그게 편해서 그렇게 작성했다.)

#run_backup_tracker.bat
@echo off
python "%~dp0backup_tracker.py"
pause

Python 파일 작성

Python 파일은 아래 역할을 한다.

백업 파일들의 경로를 입력받는다. (python 파일이 있는 곳으로부터 상대경로)
백업 파일의 이름은 appName_randomNumber_randomString_... 으로 되어있는데, 여기에서 appName을 추출한다.
백업 파일들의 사이즈를 확인한다.
소스에 포함해둔 Google Sheets API의 Private Key와 Sheet ID를 통해 Google Sheets에 사이즈를 기록한다.
Google Sheets에 해당하는 appName의 우측에 사이즈를 기록하며, 해당하는 appName이 없을 시 추가하여 기록한다.

전체 코드는 아래와 같다.

#backup_tracker.py
import os
import datetime
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from google.oauth2 import service_account

# Use os.path.join for constructing file paths
SERVICE_ACCOUNT_FILE = os.path.join('service-account', 'private-key.json')
SAMPLE_SPREADSHEET_ID = 'google-sheets-sheet-id'

def extract_app_name(filename):
    return filename.split('_')[0]

def get_backup_sizes(backup_path):
    sizes = {}
    for root, dirs, files in os.walk(backup_path):
        for file in files:
            full_path = os.path.join(root, file)
            app_name = extract_app_name(file)
            size = (os.path.getsize(full_path)/1024*0.000001) #표시용량으로 전환
            # 1KB보다 작은 용량 파일 제외
            if size > 0.000001:
                sizes[app_name] = sizes.get(app_name, 0) + size
    return sizes

def update_google_sheet(sizes):
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    
    # Get the absolute path to the service account file
    current_dir = os.path.dirname(os.path.abspath(__file__))
    service_account_path = os.path.join(current_dir, SERVICE_ACCOUNT_FILE)
    
    creds = service_account.Credentials.from_service_account_file(
        service_account_path, scopes=SCOPES)
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()

    today = datetime.date.today().strftime('%Y-%m-%d')

    result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range='A1:ZZ').execute()
    values = result.get('values', [])

    if not values:
        values = [[today]]
    elif today not in values[0]:
        values[0].append(today)

    date_col = values[0].index(today) + 1

    for app_name, size in sizes.items():
        row = next((i for i, r in enumerate(values) if r[0] == app_name), None)
        if row is None:
            values.append([app_name] + [''] * (len(values[0]) - 1))
            row = len(values) - 1
        while len(values[row]) < date_col:
            values[row].append('')
        values[row][date_col - 1] = size

    body = {'values': values}
    sheet.values().update(
        spreadsheetId=SAMPLE_SPREADSHEET_ID, range='A1',
        valueInputOption='USER_ENTERED', body=body).execute()

if __name__ == "__main__":
    backup_path = input("Enter the backup file directory (e.g., GIGA_BACKUP\\yymmdd\\NET_BACKUP): ").strip('"')
    backup_path = os.path.abspath(backup_path)  # Convert to absolute path
    if not os.path.exists(backup_path):
        print(f"Error: The directory '{backup_path}' does not exist.")
        input("Press Enter to exit...")
        exit(1)
    sizes = get_backup_sizes(backup_path)
    update_google_sheet(sizes)
    print("Backup sizes updated in Google Sheet.")
    input("Press Enter to exit...")

수정 필요 부분

1. Private Key 파일 경로 및 파일 이름

SERVICE_ACCOUNT_FILE = os.path.join('service-account', 'private-key.json')

service-account 경로 아래에 있는 private-key.json 이름의 Private Key 파일을 뜻한다.

Google Sheets API의 Private Key를 발급받은 걸 사용하면 된다.

파일 경로는 해당 파일을 기준으로 하는 상대경로이니 주의한다.

2. Google Sheets 파일 Sheet ID

SAMPLE_SPREADSHEET_ID = 'google-sheets-sheet-id'

데이터를 업로드할 Google Sheets 파일의 Sheet ID를 입력한다.

Sheet ID는 파일의 URL에 포함되어 있다.

더 다듬어야 할 부분

현재는 python을 실행하기 위해서 모두 python이 설치된 환경에서 해당 파일을 실행해야 한다.
가뜩이나 python은 버전관리가 어렵다고 한다. 따라서 환경을 맞출 수 있는 방법을 모색해본다. (ex. 파이썬 가상환경)
백업 파일이 저장되는 주기가 짧은 경우, 같은 appName으로 두 개의 파일이 있기도 하다.
이럴 때 현재는 2개의 파일의 사이즈를 모두 더해 입력하고 있는데, 가장 최신 파일의 사이즈만 입력해야 한다.

참고 사이트

https://velog.io/@junsugi/Google-Sheet-%EC%97%B0%EB%8F%99%ED%95%98%EA%B8%B0-feat.-Google-API

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

구글 스프레드시트 연동하다 빡쳐서 쓰는 글

velog.io

https://ggondae.tistory.com/114

[Google Sheets] 구글 시트 REST API

구글 드라이브의 Sheets를 REST API로 연결할 수 있습니다. 이번글에서는 연결 설정 및 테스트 데이터 생성하여 GET까지 하겠습니다. 먼저 구글 드라이브로 들어가 새로운 Google 스프레드시트를 생성

ggondae.tistory.com

기록중