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](https://console.cloud.google.com/)を利用するのが一番よさそう。

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

## 1-1. 新規プロジェクトを作成する
- まずはAPIを取得するための、プロジェクトを用意する必要がある。
- [Google Cloud PlatformのAPIライブラリ](https://console.cloud.google.com/apis/dashboard)にアクセス
-  **プロジェクトの選択**をクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/539a3b40-1aea-929c-ab87-7cb6fadfbf80.png" width="800">

- **新しいプロジェクト**をクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/faafed99-2016-596b-3220-f80635381362.png" width="800">

- 適当なプロジェクト名を入力してを作成
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/cc5e0fec-ba28-11f0-3b9e-cbdf9131e23c.png" width="400">

- おしまい

## 1-2. Google Drive APIを有効にする
- Google DriveのAPIを有効にしないと、Google Spread SheetsにPythonなど、外部からアクセスするのは不可能とのこと。
- 仕方がないので、ぽちぽちしながら有効にします。
- Google Drive APIを探してクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/fe593c31-978b-3071-42cc-ff64fce58345.png" width="800">

- 有効をクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/3db0cd35-6b8c-7c64-3218-59642bb54f54.png" width="800">

- 1-1で作成ししたプロジェクトを選択
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/039f38ab-a9ce-db48-90a7-957230623f11.png" width="800">

- おしまい

## 1-3. Google Spread Sheets APIを有効にする
- 1-2と同様にGoogle Spread SheetsのAPIを有効にします。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/056d04e4-3a95-683e-33ff-db4d502dc11a.png" width="800">


## 1-4. 認証情報を設定する
- あと少しで下準備はおしまい。
- ここでは外部(Python)からアクセスする際に、認証に用いられる情報を取得します。
- 認証情報へ移動し、**認証情報を作成**をクリックし、サービスアカウントを選択。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/cbd605de-9c51-102d-8b49-ddbebefba896.png" width="800">

- サービスアカウント名を適当に記入し、**作成**をクリック。
    - サービスアカウント名はどういったことをするのかを書くといいらしい
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/fa5223af-1bca-3b54-d1d0-ccbe51fe72f4.png" width="800">

- ロール(役割)では、**プロジェクト**→**オーナー**を選択。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/84e7225c-dfae-e7fd-fddf-a9669498b290.png" width="800">

- 完了をクリックして終了。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/efe968b5-2562-9986-4d6c-c0483051028d.png" width="800">


## 1-5. 秘密鍵を生成する
- 1-4で作成した認証情報から、秘密鍵を生成します。
    - [秘密鍵とは?](http://e-words.jp/w/%E7%A7%98%E5%AF%86%E9%8D%B5.html) 
    - つまり、ここで作成した秘密鍵は誰にも渡さず大切に保管しておきましょう


- 1-4で作成したサービスアカウントをクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/2be4e955-ac78-981d-f9e4-ffb3a7bfa542.png" width="800">

- **キー**の**新しい鍵**をクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/8d312bdd-c1c5-22f4-898b-5c72cae3325d.png" width="800">

- キータイプは**json**を選択し、作成をクリック。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/bc4bbb67-45aa-eadd-fc55-65ea31c70ae4.png" width="800">

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

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

Google Spread SheetsにPythonからアクセスする方法はいくつかあるらしい。
今回は、一番簡単そうだった、[gspread](https://gspread.readthedocs.io/en/latest/)を用いた方法を紹介。

手順は以下の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 みたいなアドレスになってるはず。

- 右上の「共有」をクリック
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/7730ea07-f3e4-2a9f-1f18-4a0769b23b59.png" width="800">


- コピーしたアドレスをユーザーやグループに追加に記入。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/2102197e-8998-4027-3efa-9860b99fae77.png" width="400">


これで、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](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/582061/feadd56c-9f48-c2ba-7da1-88466b32c4e1.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上の値を更新
#(２−１)あるセルの値を更新（行と列を指定）
ws.update_cell(1,1,"test1")
ws.update_cell(2,1,1)
ws.update_cell(3,1,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にアクセス
```.py
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)
```

### (２−１) あるセルの値を更新（行と列を指定）

```.py
ws.update_cell(行,列,値)
```
となっている。つまり、

```.py
ws.update_cell(2,4,100)
```
とすれば、2行目の４列目(D列)に100が書き込まれます。

### (２−２) あるセルの値を更新（ラベルを指定）
```.py
ws.update_acell(ラベル,値)
```
となっている。つまり、

```.py
ws.update_acell("E4",200)
```
とすれば、4行目の5列目(E列)に200が書き込まれます。

### (２−３) ある範囲のセルの値を更新
- 多数のセルに書き込みたい場合はこちらがおすすめ。

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

```.py
#セルの範囲を指定して、一次元配列に格納。
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**| ⑦ | ⑧ | ⑨ |



# 参考サイト
- [【もう迷わない】Pythonでスプレッドシートに読み書きする初期設定まとめ](https://tanuhack.com/operate-spreadsheet/)
- [gspreadライブラリの使い方まとめ！Pythonでスプレッドシートを操作する](https://tanuhack.com/library-gspread/#i-9)