スプレッドシートをGoogleドライブにCSV出力する

はじめに

今回はスプレッドシートをGoogleドライブに定期的に吐き出すプログラムを紹介します。

PythonにはスプレッドシートとGoogleドライブを操作できるライブラリがあるため、これを使ってプログラムにします。

プログラムを動かすまでの基本的な手順とサンプルプログラムを紹介しますので、何かしら参考になれば嬉しく思います。

Google drive APIおよびGoogle Sheets APIの有効化

まずはPythonでスプレッドシートやGoogleドライブを操作するために、APIの有効化から始めます。

こちらのGoogle Cloud Platformにアクセスし、プロジェクトを立ち上げます。

プロジェクトを作成したら、Google Drive APIとGoogle Sheets APIを有効化します。

プロジェクトを作成したら、Google Drive APIとGoogle Sheets APIを有効化します。

これでAPI自体の有効化はできました。次に、プログラムで使用するための認証情報を設定していきます。

APIアクセスのために秘密鍵を生成

PythonなどでAPIを使用するためにはアカウントの認証が必要になります。ここでは、その際に使用する認証情報を設定します。

こちらのAPIとサービス画面の「認証情報」タブの「認証情報の作成」から「サービスアカウント」を選択します。

「サービスアカウント名」と「ロール」をそれぞれ決めてアカウントを作成します。今回はシートの更新をPythonで行いたいので「編集者」ロールを選択します。

その後、先ほどの「認証情報」画面から作成したアカウントをクリックし、「キー」タブにある「鍵を追加」から新しい鍵を作成します。鍵のタイプはJSONとします。

作成すると、JSONファイルが自動でダウンロードされます。このファイルはプログラムで使用するのでとっておきます。これで、GoogleのAPI利用設定は完了しました。

スプレッドシートとGoogleドライブのキーを確認

次に出力元となるスプレッドシートのキーと出力先のGoogleドライブのキーを確認しておきます。

また、さきほど作成したサービスアカウントに共有する設定も必要なので合わせて行います。

まず、出力したいスプレッドシートを開き、URL欄の以下の文字列をメモしておきます。これがスプレッドシートを特定するためのキーとなります。

次にスプレッドシートを作成したサービスアカウントに共有します。サービスアカウントのメールアドレスはこちらから確認できます。

Googleドライブも同様にキーを調べて、サービスアカウントに共有しておきます。

Googleドライブのキーは出力先のフォルダを開いたときのURLにあります。

また、共有は出力先フォルダを右クリックし、「共有」で先ほどと同様にサービスアカウントのメールアドレスを入力して共有します。

以上で、プログラムを動かすための下準備が完了しました。

スプレッドシートをGoogleドライブにCSV出力するプログラム

では、いよいよPythonプログラムです。

あらかじめ、「gspread」、「pydrive」、「ouath2client」をpipでインストールしておいてください。

import gspread
import csv
import json
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from oauth2client.service_account import ServiceAccountCredentials

ACCESS_KEY_JSON = "XXXX" ##先ほどダウンロードしたjson鍵ファイル
SPREAD_SHEET_KEY = "XXXX" ##スプレッドシートキー
GOOGLE_DRIVE_ID = "XXXX" ##Google Driveキー
worksheetname = "シート1" ##出力するシート名
filename = 'XXXX.csv' ##Google Driveに出力するcsvファイル名

def lambda_handler(event, context):

  ## Google SpreadSheet　からCSVをダウンロード
  credentials = ServiceAccountCredentials.from_json_keyfile_name(ACCESS_KEY_JSON, ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'])
  spreadsheet = gspread.authorize(credentials).open_by_key(SPREAD_SHEET_KEY)
  worksheet = spreadsheet.worksheet(worksheetname)

  with open("/tmp/"+filename, mode='w', newline='', encoding='shift_jis') as f:
    writer = csv.writer(f)
    writer.writerows(worksheet.get_all_values())
 
  ## Google Driveへのアップロード
  gauth = GoogleAuth()
  scope = ["https://www.googleapis.com/auth/drive"]
  gauth.credentials = ServiceAccountCredentials.from_json_keyfile_name(ACCESS_KEY_JSON, scope)
  drive = GoogleDrive(gauth)
 
  file = drive.CreateFile({"title": filename , 'mimeType': 'text/csv', "parents": [{"id": GOOGLE_DRIVE_ID}]})
  file.SetContentFile("/tmp/"+filename)
  file.Upload()

任意設定項目は以下になります。

• ACCESS_KEY_JSON：先ほどダウンロードしたjson鍵ファイル名
• SPREAD_SHEET_KEY：スプレッドシートのキー
• GOOGLE_DRIVE_ID：Google Driveのキー
• worksheetname：出力するスプレッドシートのシート名
• filename：Google Driveに出力するcsvファイル名

また、上記プログラムはLinux上で動かす想定で、「/tmp/」に一度csvを出力してからGoogleドライブにアップロードするようになっています。

Windowsで実行する場合は「/tmp/」を適当な「C:\\XXX\\XXX」などに変更して使用してください。

また、csvファイルの書き出し文字コードも、現在は「shift_jis」としていますが、用途に合わせて変更するとよいと思います。

おわりに

PythonでスプレッドシートやGoogleドライブを操作するときの基本的な手順と、サンプルプログラムを紹介しました。

次回はAWS Lambdaを用いてこのプログラムを定期実行する仕組みを紹介します。
AWS Lambdaと組み合わせることで、スプレッドシートの定期バックアップやシステム連携などでも使用できると思いますので、興味があればご覧ください。

Recommended You

【画像付手順】:
AWS Lambdaを使用して、スプレッドシートをGoogleドライブにCSV出力する

本記事のプログラムをAWS Lambdaで動かすことにより、自動で定期実行する仕組みを構築しています。興味があればご覧ください。