3 고객 서비스 업무 관련 구글 시트 연동
3.1 목적
- 본 문서는 고객 서비스 업무 관련 구글 시트 연동 방법을 기술한 문서입니다.
- 구글 시트 문서는 다음과 같다.
- 수거 신청 대장
- 토탈설치_피엘이엔지_배정_2024하반기
- 맞춤커튼_엘엠엑스텍_배정_2024하반기
- 설치 업체 배정_하반기 (도어락)
- K100 불량 및 문의 접수표_진행중
3.2 준비 사항
구글 클라우드 서비스 가입 및 서비스 계정 생성 (https://console.cloud.google.com/apis/dashboard)
사용자 인증 정보 >> 프로젝트 만들기
사장자 인증 정보 만들기 >> 서비스 계정 생성
서비스 계정의 이메일 정보 클릭 >> 키 생성
비공개 키를 컴퓨터에 저장(json파일)
서비스 계정에 생성된 이메일을 구글시트에서 공유 대상으로 지정
구글 시트 API 연동
- API 및 서비스 >> 라이브러리 클릭 >> Google Sheet 검색 >> Google Sheets API 클릭 >> API 사용 클릭
3.3 코드 분석
3.3.1 공통 주요 정보
3.3.1.1 .env 파일에서 환경 변수 로드
#from dotenv import load_dotenv
#import os
#load_dotenv()3.3.1.2 Google Sheets API 인증 및 데이터 로드
구글 시트 스프레드시트 ID: “d/”와 “/edit”사이의 문자
#from google.oauth2.service_account import Credentials
#import gspread
# Path to your service account JSON file
#SERVICE_ACCOUNT_FILE = os.getenv('SERVICE_ACCOUNT_FILE')
# Load credentials from the JSON file
#creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=[os.getenv('SCOPES')])
# Connect to Google Sheets
#gc = gspread.authorize(creds)
# Google Sheet ID and Worksheet name
#SERVICE_SPREADSHEET_ID = os.getenv('SERVICE_SPREADSHEET_ID')
#SERVICE_WORKSHEET_NAME = os.getenv('SERVICE_WORKSHEET_NAME') # Replace with your worksheet nameGoogle Sheets API 인증: Google Sheets API를 사용하기 위해 JSON 자격 증명 파일을 통해 인증을 수행합니다.
gspread 라이브러리: Google Sheets의 데이터에 접근하기 위한 Python 라이브러리로, authorize() 메서드를 사용해 Google Sheets에 접근합니다.
3.3.2 수거 신청 대장
3.3.2.1 구글 시트 데이터 로딩 및 정리
#def load_sheet_data():
# worksheet = gc.open_by_key(SERVICE_SPREADSHEET_ID).worksheet(SERVICE_WORKSHEET_NAME)
# all_values = worksheet.get_all_values()
# headers = all_values[2] # 3번째 줄을 헤더로 사용
# data = all_values[3:] # 데이터는 4번째 줄부터 시작
# df = pd.DataFrame(data, columns=headers)
# df.replace('', np.nan, inplace=True)
# df.dropna(subset=['고객명'], inplace=True)
# df.columns = make_unique_columns(list(df.columns))
# return df
목적: Google Sheets에서 데이터를 불러와 Pandas DataFrame으로 변환합니다.
주요 동작:
• 첫 두 줄을 건너뛰고, 3번째 줄을 헤더로 사용하며 4번째 줄부터 데이터를 불러옵니다.
• 빈 문자열을 NaN으로 대체하고, ‘고객명’ 열이 비어있는 행은 제거합니다.
• make_unique_columns(): 중복된 열 이름이 있을 경우, 이를 고유하게 변경합니다.sk
3.3.2.2 날짜 파싱 및 형식화
#for idx, row in df.iterrows():
# date_str = row['작성일']
# if pd.isna(date_str):
# continue
# try:
# if re.match(r'^\d{4}\.\d{1,2}\.\d{1,2}$', date_str):
# df.at[idx, 'registered_date'] = pd.to_datetime(date_str, format='%Y.%m.%d')
# 추가적인 정규 표현식 패턴들
# except Exception as e:
# df.at[idx, 'registered_date'] = None목적: 다양한 날짜 형식을 표준화된 날짜 형식으로 변환합니다.
• 정규 표현식 매칭: 다양한 날짜 형식을 re.match()로 탐지한 후, 이를 pd.to_datetime()을 사용해 변환합니다.
• 오류 처리: 만약 변환에 실패하거나 형식이 일치하지 않으면 날짜를 None으로 설정합니다.
3.3.3 토탈 설치 서비스
3.3.3.1 Pandas DataFrame으로 데이터 처리
#df = pd.DataFrame(data[2:], columns=[
# 'registered_date', '출고날짜', '고객명', '연락처', '주문번호', '주소', '구매품목',
# '도어락', '도어벨', '조명스위치', '커튼', '내용확인', '기사님성함', '해피콜예정일',
# '설치예정일', '설치완료여부', '유상', '비고_아카라', '비고_피엘'
#])목적: Google Sheets에서 불러온 데이터를 Pandas DataFrame으로 변환합니다.
컬럼 지정: 3번째 행부터 데이터를 읽고, 컬럼을 수동으로 지정해 DataFrame을 구성합니다.
3.3.3.2 데이터 형식 변환 및 처리
- 날짜 변환: 날짜 형식의 데이터를 pd.to_datetime()을 사용해 변환하며, 변환이 불가능한 값은 NaT로 설정합니다.
#df['registered_date'] = pd.to_datetime(df['registered_date'], errors='coerce')
#df['출고날짜'] = pd.to_datetime(df['출고날짜'], errors='coerce')
#df['해피콜예정일'] = pd.to_datetime(df['해피콜예정일'], errors='coerce')
#df['설치예정일'] = pd.to_datetime(df['설치예정일'], errors='coerce')- 숫자형 변환
#boolean_columns = ['내용확인', '설치완료여부']
#for col in boolean_columns:
# df[col] = df[col].apply(lambda x: True if x.lower() == 'true' else False)- 불리언 값 처리: 불리언 값을 처리해 ‘true’ 값을 True로, 그 외 값을 False로 변환합니다.
3.3.4 맞춤형 커튼 설치
3.3.4.1 데이터 전처리
#data = [row[:17] for row in data]
#header = data[0]
#df = pd.DataFrame(data[1:], columns=header)
#df.rename(columns={'665': '플랫폼', '날짜': 'registered_date'}, inplace=True)
#df = df[df['플랫폼'].notna() & df['플랫폼'].str.strip().astype(bool) &
# df['상품주문번호'].notna() & df['상품주문번호'].str.strip().astype(bool) &
# (df['상품주문번호'] != '본사 촬영용')]
#df.reset_index(drop=True, inplace=True)데이터 전처리: 불필요한 컬럼을 제외하고 첫 17개의 컬럼만 선택합니다.
컬럼명 변경: ’665’라는 컬럼명을 ’플랫폼’으로, ’날짜’라는 컬럼명을 ’registered_date’로 변경합니다.
필터링: ‘플랫폼’과 ‘상품주문번호’가 비어 있지 않은 행만 필터링하고, ‘본사 촬영용’ 데이터를 제외합니다.
인덱스 초기화: 불필요한 인덱스를 제거하고 새로 설정합니다.
3.3.4.2 날짜 처리
#def adjust_year(date_str, index):
# try:
# date_obj = datetime.strptime(date_str, "%m/%d")
# except ValueError:
# return None
# if index <= 262:
# return date_obj.replace(year=2023)
# else:
# return date_obj.replace(year=2024)
#df['registered_date'] = df.apply(lambda row: adjust_year(row['registered_date'], row.name), #axis=1)날짜 조정: 날짜가 “월/일” 형식일 때 해당하는 연도를 인덱스에 따라 다르게 설정합니다.
인덱스가 262 이하일 경우 2023년, 그 외에는 2024년으로 연도를 설정합니다.
3.3.5 도어락 설치업체 배정
3.3.5.1 데이터 전처리
#df = pd.DataFrame(data[1:])
#df = df.iloc[:,1:22]
#required_columns =[
# 'registered_date', '주문처', '지역1', '지역2', '지점', '설치', '기사연락처', '비용', #'청구월', '증빙유형',
# '추가비용', '청구월2', '지급기안', '설치여부', '이름', '연락처', '주소', '상품명', #'상품옵션', '배송메시지', '특이사항'
#]
#df.columns = required_columns데이터 프레임 생성: Google Sheets에서 가져온 데이터를 Pandas DataFrame으로 변환합니다.
필요한 컬럼 설정: 필요한 21개의 열을 선택하고, 컬럼 이름을 적절하게 설정합니다.
3.3.5.2 날짜 형식 변환
#def parse_registered_date(date_str):
# date_formats = ['%Y/ %m/ %d', '%Y/%m/%d', '%Y. %m. %d','%Y. %m.%d']
# for date_format in date_formats:
# try:
# return pd.to_datetime(date_str, format=date_format, errors='raise')
# except (ValueError, TypeError):
# continue
# return None
#df['registered_date'] = df['registered_date'].apply(parse_registered_date)- parse_registered_date() 함수: 다양한 날짜 형식을 처리할 수 있도록 여러 날짜 포맷을 시도하여 문자열을 datetime 형식으로 변환합니다.
3.3.5.3 비용 값 처리
#def clean_cost_value(value):
# if isinstance(value, str):
# cleaned_value = value.replace('₩', '').replace(',', '')
# if cleaned_value.isdigit():
# return float(cleaned_value)
# else:
# return None
# return None if pd.isna(value) else value- 비용 값 처리: ‘₩’ 및 ’,’를 제거하고, 숫자로 변환 가능한 값을 float 형식으로 변환합니다. 값이 숫자가 아니면 None으로 반환합니다.
3.3.6 도어락 설치 파트너
3.3.6.1 데이터 전처리
# Add 'registered_date' column
#registered_date = pd.to_datetime('2024.02.19')
#df.insert(0, 'registered_date', registered_date)
# 컬럼명 설정
#required_columns = ['registered_date', '지역1', '지역2', '대리점', '담당자코드', '대표', #'연락처', '주소', '사업자등록번호', '은행', '계좌', '소유자명', '세금계산서', '플라자', #'기타']
#df.columns = required_columns
# '연락처'가 없는 행 제거 및 NaN 값을 None으로 변환
#df['연락처'] = df['연락처'].replace(r'^\s*$', np.nan, regex=True)
#df = df.dropna(subset=['연락처'])
#df = df.replace({np.nan: None})등록 날짜(registered_date)를 특정 날짜로 추가합니다.
필요한 컬럼을 지정하고 데이터 정리 및 빈 칸 처리를 수행합니다.
NaN 값을 Python의 None 값으로 변환하여 SQL 삽입 시 문제가 발생하지 않도록 합니다.
3.3.7 도어락 불량 등록
3.3.7.1 Pandas DataFrame 생성
#data = sheet.get_all_values()
#df = pd.DataFrame(data[6:]) # 데이터는 7번째 행부터 시작
#df = df.iloc[:, 2:14] # 3번째 열부터 14번째 열까지 사용
#df.columns = required_columns # 필수 열 설정- Google Sheets에서 가져온 데이터를 Pandas DataFrame으로 변환하고, 필요한 열만 선택하여 사용합니다.
3.3.7.2 데이터 전처리
#df['registered_date'] = pd.to_datetime(df['registered_date'], errors='coerce')
#df = df.replace("", None)
#df = df.dropna(subset=['고객명', '고객불량증상'], how='all')- 날짜 형식을 변환하고, 빈 문자열을 None으로 바꾸며, 고객명과 고객불량증상이 비어 있는 행은 삭제합니다.