【もう迷わない】Pythonでスプレッドシートに読み書きする初期設定まとめ

こんにちは、色んな効率化が大好きなたぬ(@tanuhack)です!

Pythonで得たデータは、MySQLMongoDBなどのデータベースに格納するのが普通だと思いますが、僕は異を唱えます。

実際、仕事でスクレイピングをする場合、そのデータを非エンジニアの人でも簡単に編集したり操作したりできなかったら意味ないんと思うんですよね。

そこで、Pythonから得たデータを上記のデータベースに格納するのではなく、誰でも簡単にアクセスしたり編集できたりする『スプレッドシート』に格納することができたら、会社全体で数字に強くなると確信しています。

普段、仕事で数字に関わることがない部署や人にデータを共有することで、新しい発見が生まれたり、より今の仕事が加速すると思いませんか?

そう思って、Pythonからスプレッドシートに読み書きする方法がないか探してた訳なんですけど、その設定方法が想像した5倍以上大変で、時間でいうと10時間くらいかかってしまいました。

今回の記事は自分に対する備忘録を含めて、Pythonからスプレッドシートに読み書きすることができる初期設定の方法を紹介します!!

[voice icon="https://tanuhack.com/wp-content/uploads/character-icons/urochan.jpg" name="ウロ教授" type="l"]

ちゃんと設定方法を理解すれば、最初の設定なんて5分くらいで終わるウロ。

1つ1つ丁寧に紹介するからしっかり着いてこいウロ。

[/voice] [aside]Pythonのインストールがまだ終わってない人は、こちらを参考にインストールしてみて下さい!
[Mac]未経験者向け!Pythonの『導入・初期設定』から『実行』まで紹介 [/aside]

Google Cloud Platformの設定

operate-spreadsheet1
Google Cloud Platform

プロジェクトを新規作成する

まずAPIを取得するプロジェクトを作成しないと何も始まらないので、プロジェクトを作成します。

STEP1:プロジェクトを選択

operate-spreadsheet2

STEP2:新しいプロジェクトを選択

operate-spreadsheet3

STEP3:プロジェクト名を入力

プロジェクトの名前を入力します。

今回はわかりやすく『spreadsheet-test』としました。

operate-spreadsheet4

これでプロジェクトの作成が終了しましたので、次にGoogle DriveGoogle SheetsのAPIを有効にします。

Google Drive APIを有効にする

外部(※今回はPython)からスプレッドシートを操作する場合は、Google DriveのAPIも有効にしなければいけません。

STEP1:サイドバーのライブラリを選択

operate-spreadsheet5

STEP2:検索フォームでdriveと入力

operate-spreadsheet6

operate-spreadsheet10

STEP3:Google Drive APIを有効に設定

operate-spreadsheet11

以下の画面に遷移したら成功です。

operate-spreadsheet12

Google Sheets APIを有効にする

次に、Google Driveと同様に、Google Sheets APIを有効にします。

STEP1:検索フォームでsheetsと入力

サイドバーのライブラリを選択し、検索フォームでsheetsと入力します。

注意点は、spreadと入力しないことです。

operate-spreadsheet7

STEP2:Google Sheets APIを有効に設定

operate-spreadsheet8

以下の画面に遷移したら成功です。

operate-spreadsheet9

STEP3:2つのAPIが有効になっているか確認

ここまでの設定が終了したら、サイドバーのダッシュボードを選択して、これらの2つのAPIが有効になっているか確認してみましょう。

operate-spreadsheet13

認証情報を設定する

Google Cloud Platformで行う最後の設定は、外部アプリケーションからスプレッドシートにアクセスするために必要なクレデンシャル情報を作成することです。

STEP1:サイドバーの認証情報を選択

operate-spreadsheet21

STEP2:認証情報を作成

operate-spreadsheet14

STEP3:サービスアカウントキーを選択

operate-spreadsheet15

STEP4:サービスアカウントキーを作成

サービスアカウントを選択。

operate-spreadsheet16

次に、サービスアカウント名と役割を設定します。

アカウント名には、そのアプリケーションがどういう動きをするのか記述します。(今回は『Pythonから読み書きする』にしました。)

役割には、全リソースへの編集アクセス権が与えられる編集者にしました。

operate-spreadsheet17

保存するデータがJSONファイルであることを確認して、作成をおします。

operate-spreadsheet18

STEP5:秘密鍵(JSONデータ)を保存

operate-spreadsheet19

画面が下図のようになっていれば、認証情報は正しく設定されています。

operate-spreadsheet20

スプレッドシートの設定

次にスプレッドシートの設定をします。

下準備

先程保存したJSONファイルをエディタで開いて、『client_email』を探します。

operate-spreadsheet22

その後ろに記述されているアドレスをコピーしておきます。

この例だと『python@spreadsheet-test-XXXX.com』の部分が該当します。

このJSONファイルの情報は超重要なので、決して他人に見せないようにしましょう。

シートの共有設定をする

Pythonのプログラムから編集することができるように、共有設定から先程コピーしたアドレスを『編集者』として認証します。

STEP1:共有を選択

operate-spreadsheet23

STEP2:フォームにアドレスをペースト

operate-spreadsheet24

STEP3:編集者に設定し、送信

operate-spreadsheet25

operate-spreadsheet26

これで、スプレッドシート側の設定は完了です。

【必見】スプレッドシートを他人に共有する方法を紹介!

プログラムを実行する

あとはPythonのプログラムを書いて、プログラムを実行させるだけです。

まず、プログラムを実行するための環境を構築します。

環境構築

動作環境
  • OS:MacOS High Sierra
  • Python:3.6.5

Pythonは3.x系で使用してます。特に理由がなければ、3.x系を使いましょう。

必要なライブラリをPCにインストールします。

ライブラリをインストール
$ pip install gspread
$ pip install oauth2client
  • gspread:Python3.X系でもスプレッドシートを操作できるライブラリ
  • oauth2client:Googleの各種APIへアクセスできるようにするライブラリ

Pythonのプログラムを実行する

例題として、スプレッドシートのA1セルの値を受け取って、その値に100加算した値をB1セルに出力するプログラムを書きます。

operate-spreadsheet27

プログラムを作成する(完成形)

import gspread
import json

#ServiceAccountCredentials:Googleの各サービスへアクセスできるservice変数を生成します。
from oauth2client.service_account import ServiceAccountCredentials 

#2つのAPIを記述しないとリフレッシュトークンを3600秒毎に発行し続けなければならない
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

#認証情報設定
#ダウンロードしたjsonファイル名をクレデンシャル変数に設定(秘密鍵、Pythonファイルから読み込みしやすい位置に置く)
credentials = ServiceAccountCredentials.from_json_keyfile_name('ダウンロードしたJSONファイル名.json', scope)

#OAuth2の資格情報を使用してGoogle APIにログインします。
gc = gspread.authorize(credentials)

#共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。
SPREADSHEET_KEY = 'スプレッドシートキー'

#共有設定したスプレッドシートのシート1を開く
worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1

#A1セルの値を受け取る
import_value = int(worksheet.acell('A1').value)

#A1セルの値に100加算した値をB1セルに表示させる
export_value = import_value+100
worksheet.update_cell(1,2, export_value)

アルゴリズムは次のような感じです。

  1. 必要なライブラリ(gspread,json,oath2client)をインポートする
  2. クレデンシャル情報に必要な、scopeを作成する
  3. 認証情報(サービスアカウントキー)を設定し、GoogleAPIにログインする
  4. 操作するスプレッドシートのスプレッドシートキーを変数に設定する
  5. スプレッドシートキーとシート名を選択し、シートを開く
  6. A1セルの値を受け取り、100加算した値をB1セルに出力する

複雑そうに見えますが①〜④までの設定は毎回ほぼ一緒なので、自分がやりたいことに対して⑤と⑥を適宜変更する感じですね。

ほぼコピペだけでOKですが、以下3点に注意して使用して下さい。

注意点1:ダウンロードしたJSONファイル名を入力する

12行目のJSONファイルは、自分でダウンロードして付けた名前を入力して下さい。

特にこだわりがなければ、PythonファイルとJSONファイルを同じディレクトリに入れることを推奨します。

#認証情報設定
#ダウンロードしたjsonファイル名をクレデンシャル変数に設定(秘密鍵、Pythonファイルから読み込みしやすい位置に置く)
credentials = ServiceAccountCredentials.from_json_keyfile_name('ダウンロードしたJSONファイル名.json', scope)

注意点2:スプレッドシートキーを設定する

※スプレッドシートキーとは、スプレッドシートのURLを指した名称で、以下の例でいう「××××」の部分のことです。

(例:https://docs.google.com/spreadsheets/d/××××/edit#gid=0)

#共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する。
SPREADSHEET_KEY = 'スプレッドシートキー'

注意点3:読み書きの制限は100秒間で100リクエスト

今回のプログラムでは、A1セルの値を取得してB1セルにその値+100の値を返しています。

つまり、2リクエストです。

詳しく制限を見てみると…。

注意
注意

  1. ユーザーごとに100秒あたり100件のリクエスト
  2. 1回のプログラムで設定できる最大値は1,000件まで
  3. さらに1秒あたり10件まで

上記3点に注意してプログラムを書きましょう。

さいごに

今回は、Pythonでスプレッドシートに読み書きする一番最初の設定方法を紹介しました。

ここを乗り越えたら、あとはもう好き勝手にPythonからスプレッドシートを更新することができます。

[voice icon="https://tanuhack.com/wp-content/uploads/character-icons/urochan.jpg" name="ウロ教授" type="l"]

データサイエンティストの役割は、数字に弱い人達にスクレイピングや高度な計算・機械学習等で得たデータをわかりやすく伝えることウロ。

[/voice]

スプレッドシートにはExcelと違って、強力な関数群がたくさん用意されています。

知っていたらドヤれる!スプレッドシート独自の関数を10個まとめてみた

冒頭でも述べましたが、MySQLやMongoDBなどのプログラマ御用達のデータベースにデータを格納するのではなく、誰でも簡単に扱えるスプレッドシートに高度な計算で抽出したデータを送り込むことで、より発想が広がりプラスの相乗効果がもたらされると確信しています。

この記事をきっかけに、スプレッドシートを簡易データベースとして使ってくれる人が増えてくれると嬉しいですね!

それでは

[aside]

今回紹介したgspreadライブラリの詳細はこちらで7000字くらいでまとめてます。

気になる方はどうぞ!

gspreadライブラリの使い方まとめ!Pythonでスプレッドシートを操作する [/aside]

1 COMMENT

現在コメントは受け付けておりません。