Pythonを使うとGoogleスプレッドシートのデータを読み込んだり、データを書き込んだりできます。
そこで、Pythonを使ってGoogleスプレッドシート上のデータを読み込み、データを表にして出力するプログラムを紹介します。
・PythonでGoogleスプレッドシートのデータを読み込み、IF文で場合分けする
・Pythonで日付を比較する
・PythonでGoogleスプレッドシートの特定範囲にデータを出力する
それでは以下で詳しく紹介していきます。
目次
プログラムの概要
この記事では、Googleスプレッドシート上の売上データから表に計算するプログラムを解説します。
実際に以下の作業をpythonで行うことを目指します。
プログラム実行前と実行後
プログラム実行前と実行後のGoogleスプレッドシートの状態は以下です。
PythonでGoogleスプレッドシートのデータを集計し書き出すコード
PythonでGoogleスプレッドシートのデータを集計し書き出すコードは以下です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
#ステップ1｜ライブラリの設定 import gspread from gspread_dataframe import get_as_dataframe, set_with_dataframe from oauth2client.service_account import ServiceAccountCredentials import datetime #ステップ2｜Google APIとの接続 scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name("---あなたのGoogleアカウントで取得すること---.js", scope) #OAuth2の資格情報を使用してGoogle APIにログインします。 gc = gspread.authorize(credentials) #ステップ3｜スプレッドシートからデータを取得 #共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。 SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---' #各シートをws1, ws2とする ws1= gc.open_by_key(SPREADSHEET_KEY).worksheet('データ') ws2= gc.open_by_key(SPREADSHEET_KEY).worksheet('集計') #全情報をvaluesに配列として入れ込む values1=ws1.get_all_values() values2=ws2.get_all_values() #ws1の行数を取得 lastrow1 = len(values1) #ws2の行数と列数を取得 lastrow2 = len(values2) lastcol2 = len(values2[0]) #集計開始日と集計終了日を取得する startdate=datetime.datetime(int(values2[1][1]) , int(values2[1][2]) , int(values2[1][3])) enddate=datetime.datetime(int(values2[2][1]) , int(values2[2][2]) , int(values2[2][3])) cell_list= ws2.range("B7:F" + str(lastrow2)) #ステップ4｜取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する for i in range(6, lastrow2): for j in range(1, lastcol2): counter = 0 for k in range(1, lastrow1): if values1[k][1] == values2[i][0]: if values1[k][2] == values2[5][j]: hiduke=values1[i][3].split("/") torihikidate = datetime.datetime(int(hiduke[0]) , int(hiduke[1]) , int(hiduke[2])) if startdate <= torihikidate <= enddate: kingaku=values1[k][4].replace(',', '') counter = counter + int(kingaku) for cell in cell_list: if cell.row==i+1 and cell.col == j+1: cell.value = counter #ステップ5｜Googleスプレッドシートに表データを出力する ws2.update_cells(cell_list) |
以下でプログラムについて解説していきます。
Pythonプログラムの解説
この記事で紹介するプログラムは以下の5つのステップに分けて説明していきます。
ステップ2｜スプレッドシートからデータを取得
ステップ3｜スプレッドシートからデータを取得
ステップ4｜取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する
ステップ5｜Googleスプレッドシートに表データを出力する
以下で一つずつ紹介していきます。
ステップ1｜ライブラリの設定
|
1 2 3 4 5 |
#ステップ1｜ライブラリの設定 import gspread from gspread_dataframe import get_as_dataframe, set_with_dataframe from oauth2client.service_account import ServiceAccountCredentials import datetime |
このプログラムでは、Googleスプレッドシートの情報を読み込んだり書き込んだりするためのライブラリを使用します。
また日付比較をするために、datetimeのライブラリも使います。
ライブラリの読み込みはこちら(外部サイト)の記事を読むと分かりやすいです。
ステップ2｜スプレッドシートからデータを取得
|
1 2 3 4 5 |
#ステップ2｜Google APIとの接続 scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name("---あなたのGoogleアカウントで取得すること---.js", scope) #OAuth2の資格情報を使用してGoogle APIにログインします。 gc = gspread.authorize(credentials) |
PythonでGoogleスプレッドシートを操作するためには、GoogleのAPIを利用できるように設定する必要があります。
とくに、このプログラムの4行目の「”—あなたのGoogleアカウントで取得すること—.js”」は各ユーザーが自分で設定しないといけない箇所です。
設定方法はこちら(外部サイト)の記事を読むと分かりやすいです。
ステップ3｜スプレッドシートからデータを取得
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#ステップ3｜スプレッドシートからデータを取得 #共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。 SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---' #各シートをws1, ws2とする ws1= gc.open_by_key(SPREADSHEET_KEY).worksheet('データ') ws2= gc.open_by_key(SPREADSHEET_KEY).worksheet('集計') #全情報をvaluesに配列として入れ込む values1=ws1.get_all_values() values2=ws2.get_all_values() #ws1の行数を取得 lastrow1 = len(values1) #ws2の行数と列数を取得 lastrow2 = len(values2) lastcol2 = len(values2[0]) #集計開始日と集計終了日を取得する startdate=datetime.datetime(int(values2[1][1]) , int(values2[1][2]) , int(values2[1][3])) enddate=datetime.datetime(int(values2[2][1]) , int(values2[2][2]) , int(values2[2][3])) cell_list= ws2.range("B7:F" + str(lastrow2)) |
以下で解説を加えていきます。
|
1 |
SPREADSHEET_KEY = '---GoogleスプレッドシートのURL---' |
1行目｜「SPREADSHEET_KEY = ‘—GoogleスプレッドシートのURL—‘」は以下のURLを記載します。
https://docs.google.com/spreadsheets/d/—GoogleスプレッドシートのURL—/edit#gid=~~~~~~~]
この「—GoogleスプレッドシートのURL—」の部分を使います。
|
1 2 3 |
#各シートをws1, ws2とする ws1= gc.open_by_key(SPREADSHEET_KEY).worksheet('データ') ws2= gc.open_by_key(SPREADSHEET_KEY).worksheet('集計') |
2行目｜シート名「データ」をws1と設定します。
3行目｜シート名「集計」をws2と設定します。
|
1 2 3 |
#全情報をvaluesに配列として入れ込む values1=ws1.get_all_values() values2=ws2.get_all_values() |
2行目｜シート名「データ」の全ての値をvalues1として2次元配列として格納する
3行目｜シート名「集計」の全ての値をvalues2として2次元配列として格納する
|
1 2 3 4 5 |
#ws1の行数を取得 lastrow1 = len(values1) #ws2の行数と列数を取得 lastrow2 = len(values2) lastcol2 = len(values2[0]) |
2行目｜シート名「データ」の最終行をlastrow1として取得する
5行目｜シート名「集計」の最終行をlastrow2として取得
6行目｜シート名「集計」の最終列をlastcol2として取得
|
1 2 3 |
#集計開始日と集計終了日を取得する startdate=datetime.datetime(int(values2[1][1]) , int(values2[1][2]) , int(values2[1][3])) enddate=datetime.datetime(int(values2[2][1]) , int(values2[2][2]) , int(values2[2][3])) |
2行目｜集計開始日startdateとしてデータを取得する(values2の配列からシート「集計」のB2,C2,D2セルの値に相当する部分を取得)
3行目｜集計終了日enddateとしてデータを取得する(values2の配列からシート「集計」のB3,C3,D3セルの値に相当する部分を取得)
|
1 |
cell_list= ws2.range("B7:F" + str(lastrow2)) |
1行目｜データを入れ込む先であるシート「集計」のB7~F26を取得（ただし、26はlastrow2として取得し、str型を指定している)
ステップ4｜取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
#ステップ4｜取引先、担当者、日付が合致するときの取引金額を合計し、各値を配列に格納する for i in range(6, lastrow2): for j in range(1, lastcol2): counter = 0 for k in range(1, lastrow1): if values1[k][1] == values2[i][0]: if values1[k][2] == values2[5][j]: hiduke=values1[i][3].split("/") torihikidate = datetime.datetime(int(hiduke[0]) , int(hiduke[1]) , int(hiduke[2])) if startdate <= torihikidate <= enddate: kingaku=values1[k][4].replace(',', '') counter = counter + int(kingaku) for cell in cell_list: if cell.row==i+1 and cell.col == j+1: cell.value = counter |
以下で解説を加えていきます。
|
1 2 3 |
for i in range(6, lastrow2): for j in range(1, lastcol2): counter = 0 |
range(開始, 終了)でfor文を行う範囲を指定できるので、以下のようにiとjを繰り返しで処理します。
1行目｜i を 6からlastrow2の範囲でfor文の繰り返しを使います。
2行目｜j を 1からlastcol2の範囲でfor文の繰り返しを使います。
3行目｜counter=0として初期化します。
|
1 2 3 4 5 6 7 8 9 10 |
for k in range(1, lastrow1): if values1[k][1] == values2[i][0]: if values1[k][2] == values2[5][j]: hiduke=values1[i][3].split("/") torihikidate = datetime.datetime(int(hiduke[0]) , int(hiduke[1]) , int(hiduke[2])) if startdate <= torihikidate <= enddate: kingaku=values1[k][4].replace(',', '') counter = counter + int(kingaku) |
1行目｜k を 1からlastrow1の範囲でfor文の繰り返しを使います。
2行目｜「データ」シートのB列の値(取引先)と「集計」シートのA列の値(取引先001~取引先020)をif文で比較します。
3行目｜「データ」シートのC列の値(担当者)と「集計」シートの6行目の値(担当者A~担当者E)をif文で比較します。
4行目｜「データ」シートのD列の値(日付)を”/”で区切ります。
5行目｜4行目で区切った値を日付に変換します。
6行目｜5行目で変換した日付が集計期間に入っているかをif文で比較します。
7行目｜「データ」シートのE列の値(取引金額)の「,」を除去します。
8行目｜取引金額を累積して加算します
|
1 2 3 |
for cell in cell_list: if cell.row==i+1 and cell.col == j+1: cell.value = counter |
1行目｜cell_listに格納されている要素をcellとして一つずつ処理します。（ここで使用するcell_listはシート「集計」のB7~F26です）
2行目｜cellの行数がi+1と一致し、かつcellの列数がj+1と一致しているときをif文で探す。
3行目｜2行目の条件に合う場合にcellの値をcounterにする。
ステップ5｜Googleスプレッドシートに表データを出力する
|
1 2 |
#ステップ5｜Googleスプレッドシートに表データを出力する ws2.update_cells(cell_list) |
1行目｜ステップ4で集計したデータが格納されたcell_listをシート「集計」のB7~F26に出力する
PythonでGoogleスプレッドシートのデータを処理してみよう
GoogleスプレッドシートのデータをPythonで集計や分析する方法を紹介しました。
ぜひチャレンジしてみてください。