どうも、nippa です。
前回に続き、Google スプレッドシートの操作についてまとめておきます。
今回は GoogleSheetAPI を利用して、スプレッドシートのセルに書き込みをしていきたいと思います。
GoogleSheetsAPI の詳細については、こちらを参考にしてください。
また、スプレッドシートの情報取得に関しては以前の記事を参考にしてください。
GoogleSheetsAPI の有効化については、以前の記事同様の内容です。
環境
Google Sheets API を有効化
すでに有効化している場合は読み飛ばしてください。
新しくプロジェクトを作成
既存のプロジェクトを利用する場合は、作成不要です。
複数 Google アカウントログインしている場合は、ユーザを選んでから行なってください。
Cloud Console のダッシュボードにアクセスします。
プロジェクトを選択 -> 「新しいプロジェクトの作成」 -> プロジェクトを作成
Google Sheet API を有効化
利用するプロジェクトを選択後に、
で無効化することもできます。
認証情報の作成と保存
有効可したあとに、認証情報を作成します。
認証情報には 2 種類あります。
- OAuth クライアント ID: ユーザが Google スプレッドシートにアクセスする認証情報
- サービスアカウント: サービス、アプリ用の認証情報
必要に応じて、使い分けてください。
「認証情報」 -> 「認証情報を作成」 -> 認証情報を選択して作成
作成した認証情報を保存します。
この認証情報を使って python で google スプレッドシートにアクセスします。
認証情報は、重要な情報なので取り扱いに注意してください。
Git でサーバ上にアップロードなどしないように。
必要なライブラリのインストール
pip でインストールする場合は、以下です。
pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib
conda でインストールする場合は、以下です。
conda install google-api-python-client google-auth-httplib2 google-auth-oauthlib
Python で Google スプレッドシートのセルに値を書き込む
スプレッドシートの ID と シート名
ブラウザからスプレッドシートを開いていただき、URL から確認することができます。
https://docs.google.com/spreadsheets/d/[スプレッドシートのID]/edit#gid=0
ブラウザ下部に表示されるシート名をメモしておいてください。
スプレッドシートの ID がわかっている場合は、API からシートの情報を取得できます。
下記は、スプレッドシートの ID の引数に与えるとすべてのシート名を順に表示する python のコードになっています。
import sys import pickle import os.path from google.auth.transport.requests import Request from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request from googleapiclient.discovery import build from googleapiclient.errors import HttpError as googleHttpError scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'] if len(sys.argv) == 2: spreadsheet_id = sys.argv[1] else: sys.exit(f'invalid arguments. \n Usage: {sys.argv[0]} [Spreadsheet ID]') creds = None if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) 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) with open('token.pickle', 'wb') as token: pickle.dump(creds, token) service = build('sheets', 'v4', credentials=creds) sheet = service.spreadsheets() try: result = sheet.get(spreadsheetId=spreadsheet_id).execute() except googleHttpError as e: print(e) for sheet in result['sheets']: print('Index %s: Sheet name = %s' % ( sheet['properties']['index'], sheet['properties']['title']) )
コードを sample1.py とした場合、実行結果の出力結果は以下のようになります。
python sample1.py [スプレッドシートのID]
# 出力結果
Title [シートのタイトル]: Sheet ID = [シートのID]
このとき、認証情報としてcredentials.jsonまたは Token (token.pickle)を読み込んでいますので、同じディレクトリの中に保存しておいてください。
特定のシートのセルに値を書き入れる
セルへの書き込みにはspreadsheets().values().updateを利用します。
いくつかオプションがありますが、基本的には body で以下を指定すれば、書き込み可能です。
body = {
'range': '[シート名]![セルの範囲],
'majorDimension': 'ROWS',
'values': [値のリスト]
}
service = build('sheets', 'v4', credentials=creds)
sheet = service.spreadsheets()
result = sheet.values().update(spreadsheetId=[スプレッドシートのID],
range='[シート名]![セルの範囲]',
body=body).execute()
配列の並びとスプレッドシートの関係はmajorDimensionで指定します。
'majorDimension': 'ROWS'のときは、行方向に値を書き入れていきます。
RANGE = '[シート名]!A1:B5' valuse = [['test0', '0'], ['test1', '1'], ['test2', '2'], ['test3', '3'], ['test4', '4']]
の python の配列がスプレッドシートに以下の表の用に書き込まれます。
| A | B | |
|---|---|---|
| 1 | test0 | 0 |
| 2 | test1 | 1 |
| 3 | test2 | 2 |
| 4 | test3 | 3 |
| 5 | test4 | 4 |
'majorDimension': 'COLUMNS'のときは、列方向に値を書き入れていきます。
RANGE = '[シート名]!A1:E2' valuse = [['test0', '0'], ['test1', '1'], ['test2', '2'], ['test3', '3'], ['test4', '4']]
の python の配列がスプレッドシートに以下の表の用に書き込まれます。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| １ | test0 | test1 | test2 | test3 | test4 |
| 2 | 0 | 1 | 2 | 3 | 4 |
書き込み用のサンプルプログラム
引数にスプレッドシートの ID とシート名を与えます。
指定したスプレッドシートのシートの A1:B5 までに以下のような書き込みを行い、値を取得します。
| A | B | |
|---|---|---|
| 1 | test0 | 0 |
| 2 | test1 | 1 |
| 3 | test2 | 2 |
| 4 | test3 | 3 |
| 5 | test4 | 4 |
import init import sys import pickle import os.path from google.auth.transport.requests import Request from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request from googleapiclient.discovery import build from googleapiclient.errors import HttpError as googleHttpError scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'] if len(sys.argv) == 3: spreadsheet_id = sys.argv[1] sheet_name = sys.argv[2] else: sys.exit(f'invalid arguments. \n Usage: {sys.argv[0]} [Spreadsheet ID]') creds = None if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) 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) with open('token.pickle', 'wb') as token: pickle.dump(creds, token) service = build('sheets', 'v4', credentials=creds) sheet = service.spreadsheets() # Config range_ = sheet_name + '!A1:B5' # Check values on sheet try: result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_).execute() print(result.get('values', [])) except googleHttpError as e: print(e) # Preprocess values = [] for i in range(10): values += [[f'test{i}', i]] value_input_option = 'RAW' body = { 'range': range_, 'majorDimension': 'ROWS', 'values': values } # Write values to sheet try: result = sheet.values().update(spreadsheetId=spreadsheet_id, range=range_, valueInputOption=value_input_option, body=body).execute() except googleHttpError as e: print(e) # Check values on sheet try: result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range_).execute() print(result.get('values', [])) except googleHttpError as e: print(e)
感想
python から GoogleSheetsAPI を使ってスプレッドシートの特定のシートに値を書き入れることができました。
セルの読み書きがこれでできるようになったので、スプレッドシートの簡単な操作が python からできるようになりました。
他にもグリッドの操作、色の操作などを GoogleSheetsAPI を使って行うことができます。
これらの操作については、今後必要に合わせてまとめていきたいと思っています。
仕事の効率化に使ってみてはいかがでしょうか？
ではでは、また次回。
