スプレッドシートをExcel形式でDL(Python)

ゆーしゃん
2023.05.05
142 views

概要

スプレッドシートをExcel形式でダウンロードし、
「yyyymmdd_hhmmss」の時間込みファイル名にリネームし、格納する

プログラムを作成した理由

【Q&Aスプレッドシートを、
毎日Excel形式でダウンロードし、前日～当日との差分をExcelファイルで出力する】
という下記1～4業務があった。

毎朝、Q&AスプレッドシートをExcel形式でダウンロード
差分箇所があるセルを、背景色黄色にする
追記された文章を、文字色赤色にする
差分表をslackに投稿

この差分表出力を手作業で行っていて、
毎朝30～60分程度かかっているので、自動化して欲しい…

と依頼された為、
まずは、下記1をPythonで自動ダウンロード出来るプログラムを作成。

毎朝、Q&AスプレッドシートをExcel形式でダウンロード
差分箇所があるセルを、背景色黄色にする
追記された文章を、文字色赤色にする
差分表をslackに投稿

ソースコード

# 概要：URLを実行し、downloadフォルダにダウンロードされたファイルを指定箇所に移動させる

# 第一引数：ダウンロードURL
# 第二引数：ダウンロードファイルの先頭ファイル名
# 第三引数：ダウンロードファイルの拡張子
# 第四引数：ダウンロードファイルのリネーム名
# 第五引数：ダウンロードの保存先パス

# 返却値：ダウンロードファイルのフルパス
def spread_sheet_download(download_url, download_file_head_name, download_extension, save_file_name, save_fold_name):

    # 標準モジュール
    import os
    import webbrowser

    # 自作共通モジュール
    import common.download_stop as download_stop
    import common.get_save_time as get_save_time
    import common.get_path as get_path

    # 待機タイムアウト時間(秒)設定
    DOWNLOAD_TIMEOUT_SECOND = 300

    # インデント
    LOG_INDENT = "Ｌ・"

    # 現在日付を「yyyymmdd_hhmmss」形式で取得
    save_time = get_save_time.get_save_time()

    # 保存ファイル名の「yyyymmdd」をリネーム
    save_file_name = save_file_name.replace("yyyymmdd_hhmmss", save_time)

    # 実行ファイル名
    FileName = os.path.basename(__file__)

    print(f"\nスプレッドシートダウンロード「{FileName}」実行開始")
    print(f"開始時間：{get_save_time.get_save_time(True)}")

    # 既にダウンロードされているファイルをリネームして退避する
    print("\nダウンロード済みのExcelファイルをリネームして退避します…")
    dir_list = os.listdir(save_fold_name)

    # フォルダ内ループ
    for i in range(len(dir_list)):

        # ファイル名取得
        before_file_name = dir_list[i]
        
        # ファイル名先頭、ファイル名の拡張子が引数内容と一致している場合
        # 例) 「Q&Aシート_20230505_144750.xlsx」にて先頭が「Q&Aシート」、拡張子が「.xlsx」の場合
        if before_file_name.startswith(download_file_head_name) and download_extension == os.path.splitext(dir_list[i])[1]:

            # リネームしたいファイル名を設定
            after_file_name = f"【過去分】{before_file_name}"
            
            # リネーム実行
            # 参考：https://liquidjumper.com/programming/python/python_file_rename
            os.rename(os.path.join(save_fold_name,dir_list[i]),os.path.join(save_fold_name,after_file_name))
            print(f"「{before_file_name}」ファイルを「{after_file_name}」にリネーム")

    # URLを読んでデフォルトのブラウザーで表示する。
    webbrowser.open(download_url)

    # 「ダウンロード」フォルダのパス取得
    # 参考：https://buralog.jp/python-get-desktop-path-in-windows/
    downloads_pass = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Downloads')

    # ダウンロード前の最新ファイル名を取得
    before_latest_filename = get_path.get_latest_modified_file_path(downloads_pass)

    # ダウンロード待機
    result = download_stop.download_stop(downloads_pass, before_latest_filename, DOWNLOAD_TIMEOUT_SECOND, download_extension)

    # 実行結果取得
    download_result_Flg = result[0]
    after_latest_filename = result[1]        

    # ダウンロード前後の最新ファイル名が違う場合はファイル名を変更
    if download_result_Flg:

        # ファイル名変更
        os.rename(after_latest_filename, save_fold_name + "\\" + save_file_name)

        print(f"\n{LOG_INDENT}「{save_file_name}」ファイル名変更")
        print(f"{LOG_INDENT}DL：成功")

        # 完了メッセージ
        msgbox_msg = "「" + save_file_name + "」DLに成功しました"

    else:
        print(f"{LOG_INDENT}DL：失敗")

        # 完了メッセージ
        msgbox_msg = "「" + save_file_name + "」DLに失敗しました"

    # 実行結果表示
    print(f"\n{msgbox_msg}\n")

    # 返却値：ダウンロード先のフルパス
    return save_fold_name+ "\\" + save_file_name

# 定数値
SPREAD_DL_URL = "https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?format=xlsx"
SAVE_HEAD_FILE_NAME = "Q&Aシート"
SAVE_EXTENSION = ".xlsx"
SAVE_FILE_NAME = SAVE_HEAD_FILE_NAME + "_yyyymmdd_hhmmss" + SAVE_EXTENSION
READ_EXCEL_FILE_PATH = "02_download"

# 関数実行
DL_EXCEL_FILE_PATH = spread_sheet_download(SPREAD_DL_URL, SAVE_HEAD_FILE_NAME, SAVE_EXTENSION, SAVE_FILE_NAME, READ_EXCEL_FILE_PATH)
print(f"DLファイル格納先：「{DL_EXCEL_FILE_PATH}」")

100

101

102

103

104

105

106

107

108

109

110

111

# 概要：URLを実行し、downloadフォルダにダウンロードされたファイルを指定箇所に移動させる

# 第一引数：ダウンロードURL

# 第二引数：ダウンロードファイルの先頭ファイル名

# 第三引数：ダウンロードファイルの拡張子

# 第四引数：ダウンロードファイルのリネーム名

# 第五引数：ダウンロードの保存先パス

# 返却値：ダウンロードファイルのフルパス

def spread_sheet_download(download_url, download_file_head_name, download_extension, save_file_name, save_fold_name):

# 標準モジュール

import os

import webbrowser

# 自作共通モジュール

import common.download_stop as download_stop

import common.get_save_time as get_save_time

import common.get_path as get_path

# 待機タイムアウト時間(秒)設定

DOWNLOAD_TIMEOUT_SECOND = 300

# インデント

LOG_INDENT = "Ｌ・"

# 現在日付を「yyyymmdd_hhmmss」形式で取得

save_time = get_save_time.get_save_time()

# 保存ファイル名の「yyyymmdd」をリネーム

save_file_name = save_file_name.replace("yyyymmdd_hhmmss", save_time)

# 実行ファイル名

FileName = os.path.basename(__file__)

print(f"\nスプレッドシートダウンロード「{FileName}」実行開始")

print(f"開始時間：{get_save_time.get_save_time(True)}")

# 既にダウンロードされているファイルをリネームして退避する

print("\nダウンロード済みのExcelファイルをリネームして退避します…")

dir_list = os.listdir(save_fold_name)

# フォルダ内ループ

for i in range(len(dir_list)):

# ファイル名取得

before_file_name = dir_list[i]

# ファイル名先頭、ファイル名の拡張子が引数内容と一致している場合

# 例) 「Q&Aシート_20230505_144750.xlsx」にて先頭が「Q&Aシート」、拡張子が「.xlsx」の場合

if before_file_name.startswith(download_file_head_name) and download_extension == os.path.splitext(dir_list[i])[1]:

# リネームしたいファイル名を設定

after_file_name = f"【過去分】{before_file_name}"

# リネーム実行

# 参考：https://liquidjumper.com/programming/python/python_file_rename

os.rename(os.path.join(save_fold_name,dir_list[i]),os.path.join(save_fold_name,after_file_name))

print(f"「{before_file_name}」ファイルを「{after_file_name}」にリネーム")

# URLを読んでデフォルトのブラウザーで表示する。

webbrowser.open(download_url)

# 「ダウンロード」フォルダのパス取得

# 参考：https://buralog.jp/python-get-desktop-path-in-windows/

downloads_pass = os.path.join(os.path.join(os.environ['USERPROFILE']), 'Downloads')

# ダウンロード前の最新ファイル名を取得

before_latest_filename = get_path.get_latest_modified_file_path(downloads_pass)

# ダウンロード待機

result = download_stop.download_stop(downloads_pass, before_latest_filename, DOWNLOAD_TIMEOUT_SECOND, download_extension)

# 実行結果取得

download_result_Flg = result[0]

after_latest_filename = result[1]

# ダウンロード前後の最新ファイル名が違う場合はファイル名を変更

if download_result_Flg:

# ファイル名変更

os.rename(after_latest_filename, save_fold_name + "\\" + save_file_name)

print(f"\n{LOG_INDENT}「{save_file_name}」ファイル名変更")

print(f"{LOG_INDENT}DL：成功")

# 完了メッセージ

msgbox_msg = "「" + save_file_name + "」DLに成功しました"

else:

print(f"{LOG_INDENT}DL：失敗")

# 完了メッセージ

msgbox_msg = "「" + save_file_name + "」DLに失敗しました"

# 実行結果表示

print(f"\n{msgbox_msg}\n")

# 返却値：ダウンロード先のフルパス

return save_fold_name+ "\\" + save_file_name

# 定数値

SPREAD_DL_URL = "https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?format=xlsx"

SAVE_HEAD_FILE_NAME = "Q&Aシート"

SAVE_EXTENSION = ".xlsx"

SAVE_FILE_NAME = SAVE_HEAD_FILE_NAME + "_yyyymmdd_hhmmss" + SAVE_EXTENSION

READ_EXCEL_FILE_PATH = "02_download"

# 関数実行

DL_EXCEL_FILE_PATH = spread_sheet_download(SPREAD_DL_URL, SAVE_HEAD_FILE_NAME, SAVE_EXTENSION, SAVE_FILE_NAME, READ_EXCEL_FILE_PATH)

print(f"DLファイル格納先：「{DL_EXCEL_FILE_PATH}」")

# 概要：ファイルがダウンロードされるまで待機し、結果を返却
# 返却値：実行結果,ダウンロード後最新ファイル名
# 第一引数：チェック対象フォルダ
# 第二引数：ダウンロード前_最新ファイル名
# 第三引数：タイムアウト秒数
# 第四引数：拡張子(省略時はCSV)

# 自作モジュール
import common.get_path as get_path

# 標準モジュール
import time
import sys

def download_stop(download_dir, before_latest_filename, timeout_second, extension=".csv"):

    # 指定時間待機
    for time_count in(range(timeout_second+1)):
        
        # ダウンロード後の最新ファイル名を取得
        after_latest_filename = get_path.get_latest_modified_file_path(download_dir)

        # 下記条件に一致した場合、ファイル名を変更
        # １：ダウンロード前後の最新ファイル名が違う場合
        # ２：ダウンロード後の最新ファイル名が「.csv」で終わる場合
        # ※「.tmp」「.download」等のダウンロード途中の残骸で引っかからないように
        if (before_latest_filename != after_latest_filename) and after_latest_filename.endswith(extension):

            # 1秒待機
            # (稀にcsvファイルDL直後にcsv.downloadファイルが格納されるので念のため…)
            time.sleep(1)

            # 末尾改行
            print()

            # 返却値：ダウンロード完了
            return True, after_latest_filename            
        
        # 待機秒数表示(強制的にprintの出力を吐き出す)
        # print("DL待機：" + str(time_count) + "秒")
        sys.stdout.write("\rDL待機：" + str(time_count) + "秒")
        sys.stdout.flush()

        # 1秒待機
        time.sleep(1)

    # 末尾改行
    print()
    
    # 返却値：ダウンロード失敗
    return False, after_latest_filename

# 概要：ファイルがダウンロードされるまで待機し、結果を返却

# 返却値：実行結果,ダウンロード後最新ファイル名

# 第一引数：チェック対象フォルダ

# 第二引数：ダウンロード前_最新ファイル名

# 第三引数：タイムアウト秒数

# 第四引数：拡張子(省略時はCSV)

# 自作モジュール

import common.get_path as get_path

# 標準モジュール

import time

import sys

def download_stop(download_dir, before_latest_filename, timeout_second, extension=".csv"):

# 指定時間待機

for time_count in(range(timeout_second+1)):

# ダウンロード後の最新ファイル名を取得

after_latest_filename = get_path.get_latest_modified_file_path(download_dir)

# 下記条件に一致した場合、ファイル名を変更

# １：ダウンロード前後の最新ファイル名が違う場合

# ２：ダウンロード後の最新ファイル名が「.csv」で終わる場合

# ※「.tmp」「.download」等のダウンロード途中の残骸で引っかからないように

if (before_latest_filename != after_latest_filename) and after_latest_filename.endswith(extension):

# 1秒待機

# (稀にcsvファイルDL直後にcsv.downloadファイルが格納されるので念のため…)

time.sleep(1)

# 末尾改行

print()

# 返却値：ダウンロード完了

return True, after_latest_filename

# 待機秒数表示(強制的にprintの出力を吐き出す)

# print("DL待機：" + str(time_count) + "秒")

sys.stdout.write("\rDL待機：" + str(time_count) + "秒")

sys.stdout.flush()

# 1秒待機

time.sleep(1)

# 末尾改行

print()

# 返却値：ダウンロード失敗

return False, after_latest_filename

# 概要：実行時の時間で「yyyymmdd_hhmmss」形式で返却
# 第一引数：区切り文字フラグ ※省略時=False

# 標準モジュール
from datetime import datetime

def get_save_time(partition_flg=False):

    # 現在時間取得
    dt_now = datetime.now()

    # 区切り文字有り
    if partition_flg:
        return dt_now.strftime('%Y/%m/%d_%H:%M:%S')

    # 区切り文字無し
    else:
        return dt_now.strftime('%Y%m%d_%H%M%S')
    

# 概要：実行時の時間で「yyyymmdd」形式で返却
# 第一引数：区切り文字フラグ ※省略時=False
def get_save_yymmdd(partition_flg=False):

    # 現在時間取得
    dt_now = datetime.now()

    # 区切り文字有り
    if partition_flg:
        return dt_now.strftime('%Y/%m/%d')

    # 区切り文字無し
    else:
        return dt_now.strftime('%Y%m%d')

# 概要：実行時の時間で「yyyymmdd_hhmmss」形式で返却

# 第一引数：区切り文字フラグ ※省略時=False

# 標準モジュール

from datetime import datetime

def get_save_time(partition_flg=False):

# 現在時間取得

dt_now = datetime.now()

# 区切り文字有り

if partition_flg:

return dt_now.strftime('%Y/%m/%d_%H:%M:%S')

# 区切り文字無し

else:

return dt_now.strftime('%Y%m%d_%H%M%S')

# 概要：実行時の時間で「yyyymmdd」形式で返却

# 第一引数：区切り文字フラグ ※省略時=False

def get_save_yymmdd(partition_flg=False):

# 現在時間取得

dt_now = datetime.now()

# 区切り文字有り

if partition_flg:

return dt_now.strftime('%Y/%m/%d')

# 区切り文字無し

else:

return dt_now.strftime('%Y%m%d')

# 参考URL：https://rriifftt.hatenablog.com/entry/2016/06/09/144851
# 概要：指定フォルダ内_最新ファイル名を取得

# 標準モジュール
import os
from glob import glob

def get_latest_modified_file_path(dirname):
  
  target = os.path.join(dirname, '*')
  files = [(f, os.path.getmtime(f)) for f in glob(target)]
  latest_modified_file_path = sorted(files, key=lambda files: files[1])[-1]
  return latest_modified_file_path[0]

# 参考URL：https://rriifftt.hatenablog.com/entry/2016/06/09/144851

# 概要：指定フォルダ内_最新ファイル名を取得

# 標準モジュール

import os

from glob import glob

def get_latest_modified_file_path(dirname):

target = os.path.join(dirname, '*')

files = [(f, os.path.getmtime(f)) for f in glob(target)]

latest_modified_file_path = sorted(files, key=lambda files: files[1])[-1]

return latest_modified_file_path[0]

ソースコード解説

「スプレッドシートをExcel形式でDLする」
これだけ行いたい場合は、下記コード1行だけで済みます。

ダウンロードURLには、
下記{spreadsheet_id}をダウンロードしたいスプレッドシートの
実際のファイルIDに書き換えればOKです。

参考サイト：Google SpreadsheetをURLダウンロードする
_{https://qiita.com/hotsprings/items/1730135ec4b7b8f40f99}

後は、【タスクスケジューラにて、毎朝n時にPythonを実行】するタスクを登録すれば、
定期的にダウンロード可能なシステムの完成です！

備忘録

実際のDL処理は1行のコードなのに、
様々コードが存在している理由としては、

「DL完了するまで、n秒待機する」
※実行時のネット回線次第で、1秒で終わるかもしれないし10秒かかるかも知れない
「1日に何回もDLした事を考慮し、既存ファイル名先頭に【過去分】と付けてリネーム」
※何日も繰り返していると、最新版がどれか分からくなってくる

…等、「自分が使うなら、↑辺りが面倒くさくなりそう…」
と思える箇所を事前に対策している為です。

お客様や、他人に納品するコードならば、
上記のような処理を入れるべきだと思いますが、
個人で使う業務改善ツールであれば、過剰品質かなーとも思います。

感想

今回の「Q&AスプレッドシートをExcel形式でダウンロード」ですが、
当初はSeleniumを用いて行うつもりでした。

しかし、Seleniumだと毎回Googleアカウントにログインしないと、
会社内のスプレッドシートを閲覧出来ない為、DL出来ない…

という問題に直面し、
「そもそも、もっと簡単な方法無いのかな…」と調べたところ、
「1行で出来るじゃん…笑」と気づいて自分に失笑してました←

過去の知見だけで突っ走るのは止めましょう(戒め)

今回の記事で下記1をPythonで実現した為、

次回以降の記事にて、
下記2、3の【DLした前日分～当日分のQ&A_Excelファイルの差分出力】をExcelVBAで実現していきます。