example of gspread

read_save.py

import os
import yaml
import pickle
import gspread

FILE_DIR = os.path.dirname(os.path.abspath(__file__))
Project_DIR = os.path.abspath(os.path.join(FILE_DIR, os.pardir))

def load_gsheet(sheet_id: str):
    r"""load gsheet data
    Parameters
    ----------
    sheet_name: str
    """
    # load google sheet
    gc = gspread.service_account(os.path.join(Project_DIR, 'utils', 'snuseed-report-acf107834044.json'))
    spreadsheet = gc.open_by_key(sheet_id)
    return spreadsheet

views.py

class UpdateDateAPI(APIView):
    #!# get method 는 생략함
    
    def post(self, request, **kwargs):
        r"""데이터 업데이트할 때 자동으로 추가됨"""
        spreadsheet = load_gsheet('11Q-EkhDnRSxVTNnpHSGae8b2IvStlAagqMIqNhYeE2w')
        df_ls = {'지식': None, '스킬': None, '흥미': None, '가치': None, '개인특성': None, '인접학과': None}
        discord = Discord(url=discord_webhook['봇로그'])
        
        # get profile
        Profile.objects.all().delete()
        for k in df_ls.keys():
            df = pd.DataFrame(spreadsheet.worksheet(k).get_values())
            # set column
            col_ls = df.iloc[0, :]
            df.columns = col_ls ; df.drop(index=0, inplace=True)

            # delete empty column (gsheet get_values method call empty column)
            drop_ls = [col for col in df.columns if col == '']
            df.drop(columns=drop_ls, inplace=True)
            if k == '지식':
                df.rename(columns= {'요소': '항목'}, inplace=True)
                df_ls[k] = df
                continue
            elif k == '인접학과':
                df.rename(columns= {'인접학과': '항목'}, inplace=True)
            
            # 지식 제외 영역
            df_ls[k] = df
            objs = [Profile(
                section = k,
                lv2 = row['계열'],
                lv4 = row['학과'],
                item = row['항목'],
                description = row['설명'],
                weight = row['중요문항'],
            ) for _, row in df.iterrows()]

            Profile.objects.bulk_create(objs=objs)
        
        # 지식 영역
        objs = [Profile(
            section = '지식',
            lv2 = row['계열'],
            lv4 = row['학과'],
            sub_section = row['영역'],
            item = row['항목'],
            description = row['설명'],
            weight = row['중요문항'],
        ) for _, row in df_ls['지식'].iterrows()]
        Profile.objects.bulk_create(objs=objs)

        # get codebook
        Codebook.objects.all().delete()
        df_cb = pd.DataFrame(spreadsheet.worksheet('코드북').get_values())
        col_ls = df_cb.iloc[1, :3]
        
        # set column
        df_cb = df_cb.iloc[2:, :3] # 첫 두행은 불필요하여 삭제
        df_cb.columns = col_ls #; df_cb.drop(index=0, inplace=True)
        objs = [Codebook(
            lv1 = row['최상위 분류'],
            lv2 = row['대분류'],
            lv4 = row['소분류'],
        ) for _, row in df_cb.iterrows()]
        Codebook.objects.bulk_create(objs=objs)

        # save current datetime
        serialized_date = UpdateDateSerializer(data = {'updated_at': datetime.now()})
        if serialized_date.is_valid():
            serialized_date.save()
            discord.post(content='구글시트 학문프로파일이 서버에 업데이트되었습니다. 이전 학문프로파일은 엑셀로 저장되었습니다.')
            excel_file_name_with_invalid_character = str(datetime.now())[:-7]
            excel_file_name_with_invalid_character = re.sub(':', '', excel_file_name_with_invalid_character)
            excel_file_name = re.sub('-', '_', excel_file_name_with_invalid_character)
            with pd.ExcelWriter(os.path.join(FILE_DIR, 'etc', f'{excel_file_name}.xlsx')) as writer:
                df_ls['지식'].to_excel(writer, sheet_name='지식')
                df_ls['스킬'].to_excel(writer, sheet_name='스킬')
                df_ls['흥미'].to_excel(writer, sheet_name='흥미')
                df_ls['가치'].to_excel(writer, sheet_name='가치')
                df_ls['개인특성'].to_excel(writer, sheet_name='개인특성')
                df_ls['인접학과'].to_excel(writer, sheet_name='인접학과')
                df_cb.to_excel(writer, sheet_name='코드북')
        return Response(status=200)