[Python]CSVファイルをGoogleスプレッドシートにアップロードする

quiver

2018.06.26

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

他のチーム・メンバーとのインターフェースがGoogleスプレッドシートな皆様にとって、いかに楽にGoogleスプレッドシートを更新できるか、というのは頑張りどころです。

今回は、ローカルのCSVファイルをプログラマブルに新規Googleスプレッドシートに変換する方法を紹介します。

もちろん、スプレッドシートをブラウザから新規作成し、CSVファイルをアップロードすることもできますが、もっと楽をしたい怠惰なプログラマーにおすすめです。

2種類のアプローチ

以下の2種類のアプローチがあります。

Google Drive API を使い、CSV ファイルとして更新する方法
Google Sheets API を使い、スプレッドシートの追加、シートの更新をする方法

前者は、ローカルに CSV ファイルが有るケース、後者は、DB に SQL を投げたり、S3 のようなオブジェクトストレージからデータを読み込んで、CSV データを作成するケースで有用です。

順に試してみましょう。

使用環境

Linux/Mac
Python: 2.7

担当システムの制約から Python2.7 を利用しましたが、Python3 系でも軽微な修正で動作するはずです。

事前インストール

必要なライブラリを先にインストールしておきます

$ pip install -U oauth2client google-api-python-client

Google Drive API を使い、CSV ファイルとして更新

Google Drive API を使って、ローカルの CSV ファイルをもとに Google スプレッドシートを作成します。ローカルに CSV ファイルが有るケースなどで有用です。

プロジェクトの作成

Google Drive API を利用するために、Google APIキーコンソールに、アプリケーションを登録し、シークレットキーをダウンロードします。

手順は次のブログ記事の 「OAuthを使用する」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

注意点としては、ブログ記事は Google Sheets API を利用していますが、今回は Drive API を利用するため、アプリケーションの登録用URLが異なります(id パラメーター)。

ブラウザから下記のURLを開いてください。

https://console.developers.google.com/start/api?id=drive.googleapis.com

スクリプト

CSV ファイルをGoogleスプレッドシートにアップロードするスクリプトは以下です。

add_spreadsheet_with_drive_api.py

#!/usr/bin/env python
# coding:utf-8

from apiclient.discovery import build
from apiclient.http import MediaFileUpload
import oauth2client
import httplib2

CLIENT_SECRET_FILE = 'drive_client_secret.json'
CREDENTIAL_FILE = 'drive_credential.json'
APPLICATION_NAME = 'YOUR_APP_NAME'

SCOPES = 'https://www.googleapis.com/auth/drive' # Quickstarts と スコープを変える

store = oauth2client.file.Storage(CREDENTIAL_FILE)
creds = store.get()
if not creds or creds.invalid:
    flow = oauth2client.client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
    flow.user_agent = APPLICATION_NAME
    creds = oauth2client.tools.run_flow(flow, store)
drive_service = build('drive', 'v3', http=creds.authorize(httplib2.Http())) # Setup the Drive v3 API

file_metadata = {
    'name': 'My Report',
    'mimeType': 'application/vnd.google-apps.spreadsheet'
}
media = MediaFileUpload('files/report.csv',
                        mimetype='text/csv',
                        resumable=True)

file = drive_service.files().create(body=file_metadata,
                                    media_body=media,
                                    fields='id').execute()
print 'File ID: %s' % file.get('id')

重要なところをかいつまんで紹介します。

スコープ

Google Drive APIs のQuickstarts ではスコープに "https://www.googleapis.com/auth/drive.metadata.readonly" を利用しています。

今回は、更新作業をおこなうため、OAuth の認可スコープは以下のいずれかを利用してください。

https://www.googleapis.com/auth/drive
https://www.googleapis.com/auth/drive.file
https://www.googleapis.com/auth/spreadsheets

また、 readonly で作成したクレデンシャルは、今回のスクリプトでは利用できませんので、再作成してください。

Google Drive のメタデータ

24-25 行目の file_metadata で Google Drive で作成するドキュメントのメタデータを設定しています。

name はファイル名です。
mimeType が spreadsheet になっていることに注意してください。

アップロードするファイルの定義

27-29 行目の media でアップロードするファイルを定義しています。

MediaFileUpload の第1引数は CSV ファイルのパスです。

ファイルの ID

アップロードが無事成功すると、34行目でファイルの ID が出力されます。このIDはスプレッドシートのIDです。

ブラウザでスプレッドシートで開いたときの URL は以下のスキーマをしています。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

スクリプト実行時に生成されたIDと Google スプレッドシートから確認できるIDが一致していることを確認してください。

認証を通す

最初の実行で、認証が必要です。

以下のコマンドを実行します。

$ python add_spreadsheet_with_drive_api.py --auth_host_name localhost --noauth_local_webserver

Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?scope=https...

Enter verification code: <ここにブラウザに表示されたコードを入力>

具体的な手順は次のブログ記事の 「認証を通す」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

CSVファイルをもとにスプレッドシートを作成する

以上で準備が整いましたので、 CSVデータを送ってみましょう。

$ cat files/report.csv
1,2,3
"a,b",c,d

$ python add_spreadsheet_with_drive_api.py
File ID: XXX

Googleドライブに移動すると、「My Report」という名前のGoogle スプレッドシートファイルが作成されているはずです。

Google Sheets API を使い、スプレッドシートの追加、シートの更新

次に Google Sheets API を使って Google スプレッドシートを作成します。

DB に SQL を投げたり、S3 のようなオブジェクトストレージからデータを読み込んで、オンメモリで CSV データを作成し、そこからスプレッドシートを作成するようなケースで有用です。

スクリプト

今回はスクリプトを単純にするために、スプレッドシート、及び、シートの作成は省き、CSVデータを更新したいスプレッドシート、及び、シートはすでに存在するものとします。

CSV データをGoogleスプレッドシートにアップロードするスクリプトは以下です。

スクリプトでは

スプレッドシートID − シートID

を利用しています。

ブラウザでスプレッドシートで開いたときの URL は以下のスキーマをしています。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

スクリプト実行時は、この URL から実際の ID に置換ください。

update_sheet_with_csv_data.py

#!/usr/bin/env python
# coding:utf-8

from apiclient import discovery
import oauth2client
import httplib2
import argparse

SPREADSHEET_ID = 'XXX' # 実環境に合わせる
SHEET_ID = 'XXX'  # 実環境にあわせる
CLIENT_SECRET_FILE = 'client_secret.json'
CREDENTIAL_FILE = 'credential.json'
APPLICATION_NAME = 'XXX'

store = oauth2client.file.Storage(CREDENTIAL_FILE)
credentials = store.get()
if not credentials or credentials.invalid:
    SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
    flow = oauth2client.client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
    flow.user_agent = APPLICATION_NAME
    credentials = oauth2client.tools.run_flow(flow, store)

http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?' 'version=v4')
service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)

requests = {
    'pasteData': {
        # coordinate https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridCoordinate
        'coordinate':{
          'sheetId': SHEET_ID,
          'rowIndex': 0,
          'columnIndex': 0
        },
        'data':'1,2,3\n"a,b",c,d\n',
        # PasteType https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#PasteType
        'type':'PASTE_VALUES',
        'delimiter': ',',
    }
}

body = {
    'requests': requests
}

response = service.spreadsheets().batchUpdate(
  spreadsheetId=SPREADSHEET_ID,
  body=body).execute()

27-44行目が肝です。重要なところをかいつまんで紹介します。

データ更新はpasteData API を利用

28行目にあるように pasteData API を利用します。名前どおり、データを特定のシートの特定の位置にペーストします。

この位置は30行目からの coordinate で指定します。

詳細は次のドキュメントを参照ください。

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridCoordinate

この例では、指定したシートの1行目A列を起点にデータをペーストします。

ペーストスタイルは 37 行目の type 引数で指定します。今回は値だけをペーストするため、PASTE_VALUES を指定しています。

詳細は次のドキュメントを参照ください。

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#PasteType

更新データ

更新データは35行目の data 引数で指定します。

38 行目の delimiter から CSV データであることを表しています。

delimiter と html の引数を同時にすると、以下のエラーが発生します。エラーメッセージ通り、片方しか指定できないため、お気をつけください。

Union field kind . How to interpret the data, exactly one value must be set. kind can be only one of the following: "Invalid value at 'requests.paste_data' (oneof), oneoffield 'kind' is already set. Cannot set 'html'"

事前準備

スクリプトの実行に関して、最初の実行で、認証が必要です。

$ python update_sheet_with_csv.py  --auth_host_name localhost --noauth_local_webserver

Go to the following link in your browser:
 
    https://accounts.google.com/o/oauth2/auth?scope=https...
 
Enter verification code: <ここにブラウザに表示されたコードを入力>

具体的な手順は次のブログ記事の 「OAuthを使用する」と「認証を通す」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

CSVファイルをもとにスプレッドシートを作成する

以上で準備が整いましたので、 CSVデータを送ってみましょう。

$ python update_sheet_with_csv_data.py

Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?scope=https...

Enter verification code: <ここにブラウザに表示されたコードを入力>

Googleドライブに移動すると、指定したGoogle スプレッドシートファイルの指定したシートが更新されているはずです。

まとめ

CSV ファイル・データをもとにGoogleスプレッドシートをプログラマブルに更新する方法を2種類紹介しました。

作成ずみの CSV ファイルをアップロードしたいときは Google Drive API
DB の処理結果のようにオンメモリにデータがある場合は Google Spreadsheet API

を使うと便利です。

lazy で impatient なプログラマーのお役に立てれば幸いです。

類似実装

Google SpreadSheetにコマンドラインからCSVをアペンドする

[Python]MySQLへのクエリ結果をGoogleスプレッドシートに反映

参照

https://stackoverflow.com/a/42367466
https://developers.google.com/drive/api/v3/manage-uploads#importing_to_google_docs_types_wzxhzdk18wzxhzdk19
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#pastedatarequest
OAuth スコープ https://developers.google.com/sheets/api/guides/authorizing

[Python]CSVファイルをGoogleスプレッドシートにアップロードする

2種類のアプローチ

使用環境

事前インストール

Google Drive API を使い、CSV ファイルとして更新

プロジェクトの作成

スクリプト

スコープ

Google Drive のメタデータ

アップロードするファイルの定義

ファイルの ID

認証を通す

CSVファイルをもとにスプレッドシートを作成する

Google Sheets API を使い、スプレッドシートの追加、シートの更新

スクリプト

データ更新はpasteData API を利用

更新データ

事前準備

CSVファイルをもとにスプレッドシートを作成する

まとめ

類似実装

参照

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩

[Python]CSVファイルをGoogleスプレッドシートにアップロードする

2種類のアプローチ

使用環境

事前インストール

Google Drive API を使い、CSV ファイルとして更新

プロジェクトの作成

スクリプト

スコープ

Google Drive のメタデータ

アップロードするファイルの定義

ファイルの ID

認証を通す

CSVファイルをもとにスプレッドシートを作成する

Google Sheets API を使い、スプレッドシートの追加、シートの更新

スクリプト

データ更新はpasteData API を利用

更新データ

事前準備

CSVファイルをもとにスプレッドシートを作成する

まとめ

類似実装

参照

イベント

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜AWS / システムアーキテクト案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/9（木）】組織的なクラウド統制 はじめの一歩

関連記事

How to Fetch Latest Objects from AWS S3 and Store Locally using Python

WindowsマシンでPython環境構築をpoetry + pyenvで再度やってみた

[Amazon Bedrock][LangChain] チャット会話履歴をセッション毎に記憶・保存する方法

Exploring OpenAI Whisper for Speech to Text Generation

EVENT【4/23リモート】クラスメソッドのフリーランスエンジニア会社説明会〜AWS / システムアーキテクト案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【5/9（木）】組織的なクラウド統制はじめの一歩