search
LoginSignup
88

More than 1 year has passed since last update.

posted at

updated at

Google Spread Sheets に Pythonを用いてアクセスしてみた

はじめに

人は生まれながらにGoogle Spread Sheetsにpythonでアクセスしてみたいはず。
そんな欲望を簡単に満たせる記事です。

目次

  • 1. Google Spread SheetsにPythonを用いてアクセスするための下準備
    • 1-1. 新規プロジェクトを作成する
    • 1-2. Google Drive APIを有効にする
    • 1-3. Google Spread Sheets APIを有効にする
    • 1-4. 認証情報を設定する
    • 1-5. 秘密鍵を生成する
  • 2. Google Spread SheetsにPythonを用いてアクセスする
    • 2-1. Google Spread Sheetの共有設定をする
    • 2-2. Google Spread Sheetのkeyを取得
    • 2-3. プログラム実行!

1. Google Spread SheetsにPythonからアクセスするための下準備

これが一番めんどくさい。

Google Spread Sheetsに外部からアクセスするためには、
Google Cloud Platformを利用するのが一番よさそう。

  • Google Cloud Platformでの初期設定の手順は以下の5ステップ
    • 1-1. 新規プロジェクトを作成する
    • 1-2. Google Drive APIを有効にする
    • 1-3. Google Spread Sheets APIを有効にする
    • 1-4. 認証情報を設定する
    • 1-5. 秘密鍵を生成する

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

  • まずはAPIを取得するための、プロジェクトを用意する必要がある。
  • Google Cloud PlatformのAPIライブラリにアクセス
  • プロジェクトの選択をクリック

  • 新しいプロジェクトをクリック

  • 適当なプロジェクト名を入力してを作成

  • おしまい

1-2. Google Drive APIを有効にする

  • Google DriveのAPIを有効にしないと、Google Spread SheetsにPythonなど、外部からアクセスするのは不可能とのこと。
  • 仕方がないので、ぽちぽちしながら有効にします。
  • Google Drive APIを探してクリック

  • 有効をクリック

  • 1-1で作成ししたプロジェクトを選択

  • おしまい

1-3. Google Spread Sheets APIを有効にする

  • 1-2と同様にGoogle Spread SheetsのAPIを有効にします。

1-4. 認証情報を設定する

  • あと少しで下準備はおしまい。
  • ここでは外部(Python)からアクセスする際に、認証に用いられる情報を取得します。
  • 認証情報へ移動し、認証情報を作成をクリックし、サービスアカウントを選択。

  • サービスアカウント名を適当に記入し、作成をクリック。

    • サービスアカウント名はどういったことをするのかを書くといいらしい
  • ロール(役割)では、プロジェクトオーナーを選択。

  • 完了をクリックして終了。

1-5. 秘密鍵を生成する

  • 1-4で作成した認証情報から、秘密鍵を生成します。

    • 秘密鍵とは?
    • つまり、ここで作成した秘密鍵は誰にも渡さず大切に保管しておきましょう
  • 1-4で作成したサービスアカウントをクリック

  • キー新しい鍵をクリック

  • キータイプはjsonを選択し、作成をクリック。

  • これでjsonファイルが保存される。

2. Google Spread SheetsにPythonを用いてアクセスする

Google Spread SheetsにPythonからアクセスする方法はいくつかあるらしい。
今回は、一番簡単そうだった、gspreadを用いた方法を紹介。

手順は以下の3ステップ
2-1. Google Spread Sheetの共有設定をする
2-2. Google Spread Sheetのkeyを取得
2-3. プログラム実行!

2-1. Google Spread Sheetの共有設定をする

まずはGoogle Spread Sheetsを作成し、共有設定を行います。

  • 1-5でdownloadしたjsonを開き、"client_email"の横に書かれているアドレスをコピーする。

    • XXXXXX[at]gspread-sheets-python.YY.gserviceaccount.com みたいなアドレスになってるはず。
  • 右上の「共有」をクリック

  • コピーしたアドレスをユーザーやグループに追加に記入。

これで、Google Spread Sheetsの共有設定はおしまい。

2-2. Google Spread Sheetsのkeyを取得

  • 2-1で用意したGoogle Spread Sheetsのリンクからkeyを取得できます。
https://docs.google.com/spreadsheets/d/aaaaaaaaaaaaaa/edit#gid=0

のaaaaaaaaaaaaaaがkeyになります。
2-3で必要になるので、控えておきましょう。

2-3. プログラム実行!

  • する前に環境を整えます。
  • Pythonの環境が自分のPCの環境にない方はこちらを参考にしてください(投稿準備中)

  • 必要なモジュールは以下の3つ

    • json
    • gspread
    • oauth2client

jsonはPython標準のモジュールなので、その他2つをpipで取得します。

pip install gspread
pip install oauth2client
  • いよいよプログラムを実行。
  • 実行するとこんな感じで値が書き込まれます。 image.png

参考コード

gspread_simple.py
import gspread
import json
from oauth2client.service_account import ServiceAccountCredentials

# (1) Google Spread Sheetsにアクセス
def connect_gspread(jsonf,key):
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonf, scope)
    gc = gspread.authorize(credentials)
    SPREADSHEET_KEY = key
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    return worksheet

# ここでjsonfile名と2-2で用意したkeyを入力
jsonf = "~~~~~~~.json"
spread_sheet_key = "aaaaaaaaaaaaaa"
ws = connect_gspread(jsonf,spread_sheet_key)

#(2) Google Spread Sheets上の値を更新
#(2−1)あるセルの値を更新(行と列を指定)
ws.update_cell(1,1,"test1")
ws.update_cell(2,1,1)
ws.update_cell(3,1,2)

#(2−2)あるセルの値を更新(ラベルを指定)
ws.update_acell('C1','test2')
ws.update_acell('C2',1)
ws.update_acell('C3',2)

#(2-3)ある範囲のセルの値を更新
ds= ws.range('E1:G3')
ds[0].value = 1
ds[1].value = 2
ds[2].value = 3
ds[3].value = 4
ds[4].value = 5
ds[5].value = 6
ds[6].value = 7
ds[7].value = 8
ds[8].value = 9
ws.update_cells(ds)

参考コード解説

(1) Google Spread Sheetsにアクセス

def connect_gspread(jsonf,key):
    #spreadsheetsとdriveの2つのAPIを指定する
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    #認証情報を設定する
    credentials = ServiceAccountCredentials.from_json_keyfile_name(jsonf, scope)
    gc = gspread.authorize(credentials)
    #スプレッドシートキーを用いて、sheet1にアクセスする
    SPREADSHEET_KEY = key
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    return worksheet

# jsonfile名を指定
jsonf = "~~~~~~~.json"
# 共有設定したスプレットシートキーを指定
spread_sheet_key = "aaaaaaaaaaaaaa"
ws = connect_gspread(jsonf,spread_sheet_key)

(2−1) あるセルの値を更新(行と列を指定)

ws.update_cell(,,)

となっている。つまり、

ws.update_cell(2,4,100)

とすれば、2行目の4列目(D列)に100が書き込まれます。

(2−2) あるセルの値を更新(ラベルを指定)

ws.update_acell(ラベル,)

となっている。つまり、

ws.update_acell("E4",200)

とすれば、4行目の5列目(E列)に200が書き込まれます。

(2−3) ある範囲のセルの値を更新

  • 多数のセルに書き込みたい場合はこちらがおすすめ。

  • ちなみに、Google Spread Sheetsへのアクセスは100秒に100回までと制限されておtり、これを超えてアクセスするとエラーが出てしまいます。

  • この方法を使えば、アクセス数は2回で済みます。

#セルの範囲を指定して、一次元配列に格納。
ds = ws.range('A1:C3') #アクセス発生
#各セルの値を指定 
ds[0].value = 1
ds[1].value = 2
ds[2].value = 3
ds[3].value = 4
ds[4].value = 5
ds[5].value = 6
ds[6].value = 7
ds[7].value = 8
ds[8].value = 9
#値を更新 
ws.update_cells(ds) #アクセス発生
  • A1:C3と指定した場合、一次元配列に格納される順番は以下の通りです。
A B C
1
2
3

参考サイト

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
88