Go to Qiita Advent Calendar 2024 Top
LoginSignup
65
69

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@connvoi_tyou(k y)

PythonでやるGoogle Sheets API入門

Last updated at Posted at 2018-12-13

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

Sheets API Google Developers

認証情報の取得

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

スクリーンショット 2018-12-14 0.18.26

次に適当にprojectを作ります

スクリーンショット 2018-12-14 0.18.53

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

スクリーンショット 2018-12-14 0.19.31

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

ブラウザで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

サンプルコード

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を利用する許可を求められるので許可をします。

$ ls
credentials.json        example.py

$ python example.py
Input SheetsId:
YOUR_SHEETS_ID

スクリーンショット 2018-12-14 3.00.04
実行してこんな感じになればOKです。

サンプルコード解説

認証部分

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に保存します。

シートの作成

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を使って、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とかに落としてコピペとかを無くしたい。

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 にコードを上げましたのでご参考になれば。
65
69
2

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
  3. You can use dark theme
What you can do with signing up
Sign upLogin
65
69

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?