こんにちは。キノコードです。
最近、仕事でExcelの代わりにGoogleスプレッドシートを使っている会社が増えてきている印象があります。
GoogleスプレッドシートはExcelの主要関数が揃っているし、なにより無料で使えます。インストールも不要でURLにアクセスするだけで使えます。また、クラウド上に保存をするのでいつでもどこでも閲覧することができます。
この動画では、Googleスプレッドシートのデータ取得、データ操作をPythonで取得して作業を自動化する方法について解説をします。 PythonでGoogleスプレッドシートを操作の方法を覚えれば、作業を効率化でき、また他の作業を効率化できるかもしれません。 例えば、Pythonでは、Excelの自動化、データベースからのデータ取得、Webスクレイピングを使ってのデータ取得、予測の分析、Gmail送信、それらのpythonの定期実行などをすることができます。それらと自動化と今回のGoogleスプレッドシートの自動化を組み合わせれば、色々な作業を自動化できます。 あなたの作業は、プログラミングのPythonで自動化できるかもしれません。 ぜひ頑張って習得してみてください。
この記事の執筆・監修
テクノロジーアンドデザインカンパニー合同会社のCEO。
日本最大級のプログラミング教育のYouTubeチャンネル「キノコード」や、プログラミング学習サービス「キノクエスト」を運営。
著書「あなたの仕事が一瞬で片付くPythonによる自動化仕事術」や、雑誌「日経ソフトウエア」や「シェルスクリプトマガジン」への寄稿など実績多数。
Python学習サービス「キノクエスト」のご紹介
キノコードでは、Pythonを習得するためのPython学習サービス「キノクエスト」を運営しています。
キノクエストには、学習カリキュラムがあり、学習順番に悩むことなく学習を進められます。
月額1,990円と本1冊分の値段です。
キノクエストの特徴は下記の通りです。
・Python学習をしている仲間が集まるコミュニティがある ・1000問以上の問題を解いてプログラミングを習得 ・環境構築不要ですぐに始められる ・動画と連動しているので、インプットもできる。 ・月額1,990円で、コミュニティもセット
キノクエストを詳しく知りたい方は、紹介ページをご覧ください。
▼キノクエストの紹介ページはこちら▼
https://kino-code.com/kq_service_a/
レッスンで使ったファイルはこちら
Mac：右クリック⇒「リンク先を別名で保存」
Windows：右クリック⇒「名前を付けてリンク先を保存」
Googleスプレッドシートからデータを読み込む準備
まず、Googleスプレッドシートからデータを読み込む方法を見ていきましょう。
Googleスプレッドシートからデータを読み込めるようになるためには、6つのステップを踏む必要があります。
1つめに、Google Cloud Platformのプロジェクトを作成します。
2つめに、Google Drive APIを有効にします
3つめに、Google Sheets APIを有効にします
4つめに、外部アプリからスプレッドシートにアクセスするための認証情報を設定します
5つめに、スプレッドシート側の共有設定をします
最後に、実際にプログラムを書く
というステップです。
このステップを簡単に説明します。1~3の設定は、Googleが提供している「スプレッドシートを操作するためのAPI」を使えるようにするための設定です。
4~5の設定は、プログラムを書いてデータを取得するなど外部から使えるようにするための設定です。
では1つずつステップを画面をみながら設定を進めていきます。まずはGoogle Cloud Platformのプロジェクトを作成します。
https://console.developers.google.com/ にアクセスします。
ゲストブラウザなどでログインをしているとメールアドレスとパスワードを求められるので入力をしましょう。
同意にチェックをいれて続行です。
画面が切り替わるのでプロジェクトを作成をクリック。
プロジェクト名を入力します。
今回はMyProjextTestとしましょう。「作成」をクリックします。
これでプロジェクトが作成されました。次にGoogle Drive APIを有効にします。
サイドバーにある「ライブラリ」を選択します。
検索フォームに「Drive」と入力し検索します。
Google Drive APIが表示されるので選択し、「有効にする」をクリックします。
これでGoogle Drive APIを有効にできました。同じ方法でGoogle Sheets APIも有効にしましょう。
画面上部にAPI検索フォームがあるので、今度は「sheets」と入力して検索します。
Google Sheets APIが表示されるので選択し、「有効にする」をクリックします。
これでAPIの有効化は完了です。
有効になると、自動が画面が切り替わります。
念のため、正常に有効化できているか確認しましょう。
左上のロゴ「GoogleAPIs」をクリックします。
ダッシュボードの下部に有効化したAPIの一覧が表示されます。2つのAPIの名前があれば正常に有効化されているので確認しましょう。
次に外部アプリからAPIを使えるように認証情報の設定をしましょう。
サイドバーの「認証情報」を選択します。
画面上部の「認証情報を作成」をクリックし「サービスアカウント」を選択します。
サービスアカウント名を入力して「作成」をクリックします。
次に作成するサービスアカウントの権限を設定します。
ロールのプルダウンからProjectを選択し、その中から「編集者」を選びます。
選択したら「続行」をクリックします。
次の画面に遷移しますが、そのまま「完了」をクリックします。
これでサービスアカウントが作成されました。画面下部のサービスアカウント欄に作成したアカウントが表示されていれば正常です。
次に秘密鍵を作成します。秘密鍵とはAPIがある部屋への鍵だと思ってください。これがないとAPIヘアクセスできません。
サービスアカウント欄のメールアドレスをクリックします。
サービスアカウントの詳細に遷移するので、「キー」項目の「鍵を追加」をクリックし「新しい鍵を作成」を選択します。
キーのタイプを聞かれるので「JSON」を選択して「作成」をクリックします。
正常に処理が終了するとjsonファイルがダウンロードされます。これが秘密鍵なので無くさないようにしましょう。
特にこだわりがなければJupyterLabと同じ階層に置いておくと、プログラム内で秘密鍵を指定する箇所がシンプルになります。
これで認証設定は完了です。
次にスプレッドシート側の共有設定をします。
先ほど作成したサービスアカウントがスプレッドシートにアクセスし、読み書きができるようにするために必要です。
対象のスプレッドシートを開き、画面右上の「共有」をクリックします。
「ユーザーやグループを追加」入力欄に、サービスアカウントのメールアドレスをコピー&ペーストします。GoogleAPIにはメールアドレスがあるわけではありませんので通知はできません。通知のチェックは外しておきましょう。
権限が編集者になっていることを確認したら「共有」をクリックします。
これでサービスアカウントにスプレッドシートの編集権限が与えられました。
ここまで設定が完了したら、最後にPython側からサービスアカウントを通してスプレッドシートを操作するプログラムを書きましょう。
プログラムを書く前に必要なPythonライブラリをpipでインストールします。
私はDockerを使っているので、コンテナの内部に、名前を指定して入ります。
このDockerの使い方については「理屈はさておき、最速でDockerを使ってPythonのデータ分析の環境構築をする」という動画で説明しているのでそちらをご覧ください。
エンターでコンテナ内に入ります。
次にpipで、スプレッドシートを操作するためgspread、認証情報を処理するために必要なoauth2clientのライブラリをインストールしましょう。
pip install gspread oauth2client
import gspread
from oauth2client.service_account import ServiceAccountCredentials
#jsonファイルを使って認証情報を取得
scope = [‘https://spreadsheets.google.com/feeds’,’https://www.googleapis.com/auth/drive’]
c = ServiceAccountCredentials.from_json_keyfile_name(‘ダウンロードしたjsonファイル’, scope)
#認証情報を使ってスプレッドシートの操作権を取得
gs = gspread.authorize(c)
#共有したスプレッドシートのキー（後述）を使ってシートの情報を取得
SPREADSHEET_KEY = ‘2flaiATIeSWFGfZfhlX0kiesUh5mw6o2PfseiAFeustDKeiwnLoa’
worksheet = gs.open_by_key(SPREADSHEET_KEY).worksheet(“発注管理表”)
print(worksheet.acell(“G2”).value)
Googleスプレッドシートのデータの読み込み
ではプログラムを書いてみましょう。
今回読み取るGoogleスプレットシートのワークシートはこちらです。発注管理表というワークシートです。
まずは手始めに、発注管理表のG2セルの値を取得してみます。
まずは先ほどインストールしたgspreadと、oauth2clientのServiceAccountCredentialsモジュールをインポートします。
次に認証情報を作成します。今回はGoogleスプレッドシートとGoogleDriveのAPIを使うので、APIの情報を記述します。そして、それをSCOPESという変数に代入します。
次に、秘密鍵のjsonファイルへのパスを指定して、SERVICE_ACCOUNT_FILEという変数に代入します。
そして、ServiceAccountCredentialsのfrom_json_keyfile_name関数を使って認証情報を作成します。
1つ目の引数に秘密鍵のjsonファイルへのパスを代入をした変数SERVICE_ACCOUNT_FILEを指定します。
2つ目の引数にAPIの情報を代入した変数scopesを指定します
そして、それをcretentialsという変数に代入します。
次に作成した認証情報をgspreadのauthorize関数に渡してスプレッドシートの操作権を取得します。
次にスプレッドシートキーを、SPREADSHEET_KEYという変数に代入をします。
スプレッドシートのURLのd/と/editの間の文字列がスプレッドシートキーです。
最後に、共有したスプレッドシートのキーとシート名ををopen_by_key関数に渡すことでシート情報を取得できます。それをworksheetという変数に代入をしましょう。
ではプログラムを実行してみましょう。
これでワークシートの情報が取得できたはずです。
G2セルの値が取得してみましょう。セルの値を取得するにはacell関数を使えばよいです。print関数で表示させてみましょう。
56,000 が出力されました。
Googleスプレッドシートから読み込んだデータの集計
import pandas as pd
スプレッドシートの読み込み方法が分かったところで、今度は読み込んだデータを集計して、集計したデータを別のシートに書き込むということをやってみましょう。
まずは、pandasをインポートしましょう。
次に、発注管理表のシートを読み込みます。
workbook = gs.open_by_key(SPREADSHEET_KEY)
worksheet = workbook.worksheet(“発注管理表”)
この記述は発注管理表を読み取る記述です。先ほど見た読み取るということは同じですが、先ほどと若干記述を変えています。
まず、SPREADSHEET_KEYを使用して、ワークブックを指定して、workbookという変数に代入。
次に、ワークシートを指定しています。
例えば、指定したworkbookには、ドットタイトルをつけるとワークブック名、ドットidをつけるとワークブックのIDを取得できます。print関数で表示させてみましょう。
ワークブック名とIDを取得できました。
また変数worksheetを表示させてみましょう。発注管理表と表示されました。これはワークブックの中の発注管理表を指定しているということになります。
さて、このワークシートのデータをpandasのDataFrameにしてみましょう。
ワークシートのデータをデータフレームにするには、get_all_values関数を使います。
get_all_values関数はシート内の値を多次元リストに変換する関数で、ここで変換したリストをDataFrameに渡しています。実行します。
ワークシートの売上管理表がデータフレームの形で読み取れています。
では次に進みましょう。今度は読み込んだデータフレームを加工します。
df.columns = df.iloc[0]
df = df.drop(df.index[[0]])
df.head()
現在はカラム名が数値になっているので、1行目のデータをカラムに設定しましょう。1行目のデータを取得するにはiloc[0]と記述します。これをdf.columsと書いてカラムに代入します。表示をさせてみます。カラムに設定されています。
1行目のデータをカラムに設定したので、1行目とカラムのデータが同じです。なので1行目のデータを削除しましょう。
df.indexの0行目をdropします。表示をさせます。削除されています。
次に、各カラムのデータ型を確認をするとすべてオブジェクト型になっています。今回は会社名別に発注金額の合計を算出して、それを別のワークシートに書き込んでみたいと思います。そこで発注金額を数値型に変換します。変換の前に発注金額に数字区切りがあるので、それを削除します。replace関数を使って「カンマ」から「何もなし」に変換します。表示させてみます。カンマが消えました。
これをオブジェクト型から数字型に変換しましょう。データ型を変換するにはastypeのintでしたね。実行します。
データ型を再度確認してみましょう。発注金額がint型になっています。
groupbyメソッドを使って会社名ごとに合計を算出しましょう。
実行します。会社ごとに集計ができています。
データフレームをGoogleスプレッドシートに書き出す
pip install gspread_dataframe
それでは書き込むデータの準備ができましたので、新しくシートを作成し、集計したデータを書き出します。
データフレームをGoogleスプレッドシートに書き出す方法はいくつかあるのですが、ここではgspread_dataframeというライブラリを使って書き出します。
そこで、gspread_dataframeのライブラリをインポートしましょう。
from gspread_dataframe import set_with_dataframe
インストールが終わったらインポートをしましょう。
gspread_dataframeの中からset_with_dataframeをインポートします。
set_with_dataframe関数は、その名の通りデータフレームの内容をワークシートにセット、つまり反映させます。
workbook.add_worksheet(title=”会社別売上”, rows=50, cols=10)
それでは新規にワークシートを作成します。
現在のワークシートを確認してみましょう。
新規でワークシートを作成するにはadd_worksheet関数を使います。
titleのところにワークシート名、rowsにはそのワークシートの行数、colsには列数を渡します。今回はワークシート名を会社別売上としましょう。
実行します。
ワークシートを確認してみましょう。
ワークシートができています。
set_with_dataframe(workbook.worksheet(“会社別売上”), df_sum, include_index=True)
では、データフレームをワークシートに書き出す記述をします。
set_with_dataframeと書いて、第一引数には書き出し先のワークシートを記述します。worksheet関数を使い「会社別発注」のシートを指定します。第二引数には書き出すデータフレームを記述します。今回はdf_sumですね。
この関数のデフォルトはインデックスは書き出さない設定になっているので、include_indexという引数にTrueを渡します。こうするとインデックスも書き出してくれます。
それでは実行します。
書き出されているか確認をしてみましょう。
書き出されています。
workbook.add_worksheet(title=”会社別売上”, rows=50, cols=10)
ちなみに、すでにあるワークシート名でadd_worksheet関数を使うとエラーになってしまいます。ワークシートがある状態で
実行します。
エラーになります。
workbook.del_worksheet(workbook.worksheet(“会社別売上”))
すでにあるワークシートを削除するには、del_worksheetでできます。
実行してみます。
ワークシートが削除されています。
Googleスプレッドシートから読み込んだデータを複数集計して自動で新しいシートに書き出す
以上が、PythonによるGoogleスプレッドシートからのデータの読み込み、書き出しの方法についてです。
この方法を使えば一瞬で、かつ、自動で複数の集計をすることができます。
例えば、発注管理表ではなく売上管理表だったとします。
これをデータフレームに読み込みます。別の方法で読み込んでみましょう。
get_all_values関数の実行結果は、リスト型で返ってきます。
インデックスの0番目から4番目を返してみましょう。ちなみに、リストはPython超入門コースのリストで解説しています。
実行します。
インデックスの0番目がカラム名ですね。
また、1番目がデータ部分ですね。
なので、DataFrameのデータ部分をここに記述して、カラムをこのように設定します。
データフレームが一発でできました。
データ型も変更しておきましょう。replaceとastypeを1行で記述してデータ型の変更をします。
Pandas入門コースのpivot_tableで説明しますが、これがPandasでのピボットテーブル の作成方法の記述方法です。
これで支店別の売上金額のピボットテーブル が作成できます。
これで商品別、これで氏名別の売上金額です。
さらに、氏名別で商品分類の売上金額
そして、氏名別、売上日別の商品分類の売上金額です。
これを先ほど説明したスプレッドシートに書き込みます。
一瞬で集計とスプレッドシートの書き出しが終わるはずです。
実行します。
数秒でそして、自動で集計が終わりました。
これを今まで別の動画で説明をしたGmailで送信をするプログラムと組み合わせれば、集計が終わった瞬間に担当者にメールで通知することもできます。
また、Pandas入門コースで説明をしたデータベースと接続するプログラムを活用すれば、発注管理表や売上管理表を自動で作ることができます。つまり、データ取得は自動化できます。
他にも、Pandas入門コースで説明しているようにデータ集計も自動化できます。
また、今回お教えしたGoogleスレッドシートへの書き込みも自動化できました。
したがって、データ取得、データ集計、スプレッドシートへの書き込み、担当者へメールで通知まで、全部自動化することができます。
この作業が毎日2時間がかかっているなら年間で400時間です。新たな技術のインプットや成果を出すためのことに集中できますよね。
ぜひ習得をしてご自身の業務を自動化してみてください。
