Bリーグの選手情報を返すLINE Botを作った1.5 - スプレッドシート保存の効率化 -

経緯

前回の記事を書いていたら直したくなったので直した。

目的

• スプレッドシートへのアクセス回数を減らす
• 更新時の情報の保証（いちいちシート消さない）

修正

1行ずつ書き込むのをやめてシート１枚（1チーム）丸ごと更新するようにしました。
シートも作り直すのはやめて中身を書き換えるようにした。

修正前（該当部抜粋）

def write_to_sheet(sheet, data, row):
    i = 0
    cell_list = sheet.range('A%s:T%s' % (row, row))
    for cell in cell_list:
        cell.value = data[i]
        i += 1
    sheet.update_cells(cell_list)

if __name__ == "__main__":
    player_dict = []
    for team in TEAM:
        time.sleep(10)
        roasters_list = list_roaster_url(team['teamid'])
        team_name = team['name']
        sheets = G_CLIENT.open_by_key(SHEET_ID)
        try:
            del_sheet = sheets.worksheet(team_name)
            sheets.del_worksheet(del_sheet)
        except:
            print('new '+team_name)
        team_sheet = sheets.add_worksheet(title=team_name, rows="30", cols="30")
        row = 1

        for player in roasters_list:
            player_info, data = get_player_detail(player)
            player_info['team'] = team['name']
            player_dict.append(player_info)
            data.insert(0,team['name'])
            write_to_sheet(team_sheet, data, row)
            row += 1

修正後

def write_to_sheet(sheet, team_data):
    cell_list = sheet.range('A1:T30')
    i = 0
    for data in team_data:
        for n in range(20):
            cell_list[i].value = data[n]
            i += 1
    
    while i < 20*30:
        cell_list[i].value = ""
        i += 1
    sheet.update_cells(cell_list)

if __name__ == "__main__":
    player_dict = []
    for team in TEAM:
        roasters_list = list_roaster_url(team['teamid'])
        team_name = team['name']
        sheets = G_CLIENT.open_by_key(SHEET_ID)
        try:
            team_sheet = sheets.worksheet(team_name)
        except:
            print('new '+team_name)
            team_sheet = sheets.add_worksheet(title=team_name, rows="30", cols="30")

        team_data = []
        for player in roasters_list:
            player_info, data = get_player_detail(player)
            player_info['team'] = team['name']
            player_dict.append(player_info)
            # print(data)
            data.insert(0,team['name'])
            team_data.append(data)

        write_to_sheet(team_sheet, team_data)
        time.sleep(10)

まとめ

すっきりしたね