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

こんにちは、色んな効率化が大好きなたぬ（@tanuhack）です！

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

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

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

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

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

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

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

[Mac]未経験者向け！Pythonの『導入・初期設定』から『実行』まで紹介

この記事の著者

データサイエンティスト / ブロガー

たぬ

tanu

詳しいプロフィール

プロフィール

福岡在住のデータサイエンティスト。Pythonでルーチンワークを瞬殺する仕組みを作ることやデータを視覚化することが得意です。

Google Cloud Platformの設定

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

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

STEP1：プロジェクトを選択

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

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

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

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

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

Google Drive APIを有効にする

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

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

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

STEP3：Google Drive APIを有効に設定

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

Google Sheets APIを有効にする

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

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

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

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

STEP2：Google Sheets APIを有効に設定

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

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

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

認証情報を設定する

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

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

STEP2：認証情報を作成

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

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

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

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

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

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

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

STEP5：秘密鍵（JSONデータ）を保存

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

スプレッドシートの設定

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

下準備

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

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

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

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

シートの共有設定をする

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

STEP1：共有を選択

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

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

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

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

プログラムを実行する

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

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

環境構築

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

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

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

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

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

プログラムを作成する（完成形）

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)

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

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

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

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

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

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

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

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

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

（例：https://docs.google.com/spreadsheets/d/××××/edit#gid=0）

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

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

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

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

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

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

さいごに

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

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

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

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

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

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

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

それでは

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

気になる方はどうぞ！

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

入門Ｐｙｔｈｏｎ　３ /オライリ-・ジャパン/ビル・ルバノビック

posted with カエレバ

楽天市場

Amazon

退屈なことはＰｙｔｈｏｎにやらせよう ノンプログラマーにもできる自動化処理プログラミング /オライリ-・ジャパン/アル・スウェイガート

posted with カエレバ

楽天市場

Amazon