PythonでやるGoogle Sheets API入門

Python+ GoogleSheets APIを使ってGoogle Spreadsheetsを編集していく方法まとめていきます。

[Sheets API  Google Developers](https://developers.google.com/sheets/api/?hl=ja)

<!--more-->

### 認証情報の取得


#### まずは認証情報を作ります。[Quick Start](https://developers.google.com/sheets/api/quickstart/python?hl=ja)を開いて、ENABLE THE GOOGLE SHEETS APIをクリックします。
<a data-flickr-embed="true"  href="https://www.flickr.com/photos/kyagyao/46251480332/in/album-72157676327477732/" title="スクリーンショット 2018-12-14 0.18.26"><img src="https://farm5.staticflickr.com/4828/46251480332_c58d973fc3.jpg" width="500" height="185" alt="スクリーンショット 2018-12-14 0.18.26"></a><script async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8"></script>    



#### 次に適当にprojectを作ります
<a data-flickr-embed="true"  href="https://www.flickr.com/photos/kyagyao/31362815557/in/album-72157676327477732/" title="スクリーンショット 2018-12-14 0.18.53"><img src="https://farm5.staticflickr.com/4892/31362815557_37bfc2fae2.jpg" width="500" height="198" alt="スクリーンショット 2018-12-14 0.18.53"></a><script async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8"></script>  

#### ダウンロードボタンをおして、ローカルに発行された2つのjsonファイルを保存しておきます。
<a data-flickr-embed="true"  href="https://www.flickr.com/photos/kyagyao/44484798390/in/album-72157676327477732/" title="スクリーンショット 2018-12-14 0.19.31"><img src="https://farm5.staticflickr.com/4866/44484798390_bc44808ac3.jpg" width="500" height="345" alt="スクリーンショット 2018-12-14 0.19.31"></a><script async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8"></script>  


### テスト用のスプレッドシートの作成
ブラウザでgoogle driveを開き、スプレッドシートを新規作成します。作成したスプレッドシートのurlの一部がそのままIDになるのでコピーしておきます。

```
https://docs.google.com/spreadsheets/d/{YOUR_SPEADSHEET_ID}/edit#gid=0
```

### サンプルコードの作成

#### 環境の準備

googleのoauth系のライブラリをpipでインストールします。

```
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
```

#### サンプルコード

```python
from __future__ import print_function
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'

print('Input SheetsId:')
sheet_id = input()

creds=None

if os.path.exists('token.pickle'):
    with open('token.pickle', 'rb') as token:
        creds = pickle.load(token)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open('token.pickle', 'wb') as token:
        pickle.dump(creds, token)

service = build('sheets', 'v4', credentials=creds)


spreadsheet_id = sheet_id 
sheetname='スプレッドシートのテスト' range_ = sheetname+"!A1:B10"

# シートの作成
requests=[]
requests.append({
    'addSheet':{
        "properties":{
            "title": sheetname,
            "index": "0",
            }

        }
    })

body={'requests':requests}
response=service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
sheetid=response['replies'][0]['addSheet']['properties']['sheetId']

#セルに文字列を入れる
range_ = sheetname+"!A1:B10"
v={}
v['range']=range_
v['majorDimension']="ROWS"
v['values']=[
        [1,  2],
        [3,  4],
        [4,  5],
        [5,  6],
        [6,  7],
        [7,  8],
        [8,  9],
        [10, 11],
        [12, 13],
        ['test', 'スプレッドシートのテストですよ'],
        ]
value_input_option = 'USER_ENTERED'
insert_data_option='OVERWRITE'
result = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=v).execute()

#セルのフォーマットを変更する
requests = []
requests.append({
    "updateBorders":{
        "range": {
            "sheetId": sheetid,
            "startRowIndex": 0,
            "endRowIndex": 1,
            "startColumnIndex": 0,
            "endColumnIndex": 2,
            },
        "bottom": {
            "style": "SOLID",
            "width": "1",
            "color": { "red": 0, "green":0, "blue":0 },
            },
        },
    })

requests.append({
    "repeatCell": {
        "range": {
            "sheetId": sheetid,
            "startRowIndex": 0,
            "endRowIndex": 1,
            "startColumnIndex": 0,
            "endColumnIndex": 2,
            },
        "cell": {
            "userEnteredFormat": {
                "horizontalAlignment" : "LEFT",
                "textFormat": {
                    "fontSize": 11,
                    "bold": True,
                    "foregroundColor": {
                        "red": 1.0,
                        },
                    }
                }
            },
        "fields": "userEnteredFormat(textFormat,horizontalAlignment)"
        },
    })
body = { 'requests': requests }
response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()


```

#### 実行方法

example.pyと同じディレクトリにcredentials.jsonがある状態でexample.pyを実行します。初回にブラウザに飛んでSheetsAPIを利用する許可を求められるので許可をします。

``` shell
$ ls
credentials.json        example.py

$ python example.py
Input SheetsId:
YOUR_SHEETS_ID
```

<a data-flickr-embed="true"  href="https://www.flickr.com/photos/kyagyao/46302650631/in/dateposted/" title="スクリーンショット 2018-12-14 3.00.04"><img src="https://farm5.staticflickr.com/4868/46302650631_3839884787.jpg" width="500" height="461" alt="スクリーンショット 2018-12-14 3.00.04"></a><script async src="//embedr.flickr.com/assets/client-code.js" charset="utf-8"></script>  
実行してこんな感じになればOKです。

### サンプルコード解説

#### 認証部分
``` python
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'

print('Input SheetsId:')
sheet_id = input()

creds=None

if os.path.exists('token.pickle'):
    with open('token.pickle', 'rb') as token:
        creds = pickle.load(token)
# If there are no (valid) credentials available, let the user log in.
if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file('credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open('token.pickle', 'wb') as token:
        pickle.dump(creds, token)

service = build('sheets', 'v4', credentials=creds)

```

token.pickleがあればそれを利用し、なければ認証情報を取得、token.pickleに保存します。


#### シートの作成
``` python
spreadsheet_id = sheet_id 
sheetname='スプレッドシートのテスト' range_ = sheetname+"!A1:B10"

# シートの作成
requests=[]
requests.append({
    'addSheet':{
        "properties":{
            "title": sheetname,
            "index": "0",
            }

        }
    })

body={'requests':requests}
response=service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

```

[batchUpdate](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate)を使って、spreadsheet_idのテスト用に作ったスプレッドシートにシートを追加します。

#### セルにデータの追加

``` python
#セルに文字列を入れる
range_ = sheetname+"!A1:B10"
v={}
v['range']=range_
v['majorDimension']="ROWS"
v['values']=[
        [1,  2],
        [3,  4],
        [4,  5],
        [5,  6],
        [6,  7],
        [7,  8],
        [8,  9],
        [10, 11],
        [12, 13],
        ['test', 'スプレッドシートのテストですよ'],
        ]
value_input_option = 'USER_ENTERED'
insert_data_option='OVERWRITE'
result = service.spreadsheets().values().update( spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=v).execute()
```

range_にシートと書き込む範囲を設定して、データをリストで用意して、[update](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update)します。


#### フォーマットの変更
``` python
#セルのフォーマットを変更する
requests = []
requests.append({
    "updateBorders":{
        "range": {
            "sheetId": sheetid,
            "startRowIndex": 0,
            "endRowIndex": 1,
            "startColumnIndex": 0,
            "endColumnIndex": 2,
            },
        "bottom": {
            "style": "SOLID",
            "width": "1",
            "color": { "red": 0, "green":0, "blue":0 },
            },
        },
    })

requests.append({
    "repeatCell": {
        "range": {
            "sheetId": sheetid,
            "startRowIndex": 0,
            "endRowIndex": 1,
            "startColumnIndex": 0,
            "endColumnIndex": 2,
            },
        "cell": {
            "userEnteredFormat": {
                "horizontalAlignment" : "LEFT",
                "textFormat": {
                    "fontSize": 11,
                    "bold": True,
                    "foregroundColor": {
                        "red": 1.0,
                        },
                    }
                }
            },
        "fields": "userEnteredFormat(textFormat,horizontalAlignment)"
        },
    })
body = { 'requests': requests }
response = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
```

フォーマット[BasicFormat](https://developers.google.com/sheets/api/samples/formatting)情報も同様に変更可能です。今回は1行目下に黒のボーダー。１行目の文字を左寄せ、文字色を赤に変更しています。

### 参考リンクなど
公式の[Samples](https://developers.google.com/sheets/api/samples/)にある、[BasicReading](https://developers.google.com/sheets/api/samples/reading), [BasicWriting](https://developers.google.com/sheets/api/samples/writing), [BasicFormat](https://developers.google.com/sheets/api/samples/formatting)を読むとだいたい概要が掴めます。

基本的には、ディクショナリ、リストでデータや、設定値を作成して、update,batchUpdateを利用して更新していく形になります。
一旦なれてしまえば、使いやすくて良いですね。

これで、データをcsvとかに落としてコピペとかを無くしたい。

### 2020/02/08 追記

sheetsapiのSDKにアップデートがかかったので修正しました。google独自のライブラリを使うようになったことと、token情報は今まではcredencial.jsonに付加して保存するのが、token.pickleというpickle形式のファイルを作成する形に変更されました。
詳しくはこちら
https://developers.google.com/sheets/api/quickstart/python

### 2020/02/28 追記

- typoを複数修正しました。
- コマンドラインからYOUR_SHEETS_IDを取得するように修正
- [connvoi/sheets-example](https://github.com/connvoi/sheets-example) にコードを上げましたのでご参考になれば。