Update Google spreadsheets by using python

sheetsapiのライブラリのアップデートにより、この記事のコードは動かなくなっています。最新版の記事を参照してください。 PythonでやるGoogle Sheets API入門その２

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

Sheets API Google Developers

認証情報の取得

まずは認証情報を作ります。Quick Startを開いて、ENABLE THE GOOGLE SHEETS APIをクリックします。

次に適当にprojectを作ります

ダウンロードボタンをおして、ローカルに発行された2つのjsonファイルを保存しておきます。

テスト用のスプレッドシートの作成

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

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

サンプルコードの作成

サンプルコード

from __future__ import print_function
from apiclient.discovery import build
from httplib2 import Http
from oauth2client import file, client, tools

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
store = file.Storage('token.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('credentials.json', SCOPES)
    creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))


spreadsheet_id = 'YOUR_SPREDSHEET_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()
sheetsid=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()

実行してこんな感じになればOKです。

サンプルコード解説

認証部分

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
store = file.Storage('token.json')
creds = store.get()
if not creds or creds.invalid:
    flow = client.flow_from_clientsecrets('credentials.json', SCOPES)
    creds = tools.run_flow(flow, store)
service = build('sheets', 'v4', http=creds.authorize(Http()))

token.jsonがあればそれを利用し、なければブラウザが開き、OKかを聞かれるのでOKを押すとtoken.jsonが生成されます。

シートの作成

spreadsheet_id = 'YOUR_SPREDSHEET_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()
sheetsid=response['replies'][0]['addSheet']['properties']['sheetId']

batchUpdateを使って、spreadsheet_idのテスト用に作ったスプレッドシートにシートを追加します。

セルにデータの追加

#セルに文字列を入れる
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します。

フォーマットの変更

#セルのフォーマットを変更する
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情報も同様に変更可能です。今回は1行目下に黒のボーダー。１行目の文字を左寄せ、文字色を赤に変更しています。

参考リンクなど

公式のSamplesにある、BasicReading, BasicWriting, BasicFormatを読むとだいたい概要が掴めます。

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

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

connvoi.com

PythonでやるGoogle Sheets API入門