希望するディレクトリにGoogle スプレッドシートを作成する

前回のエントリーのなんとなく続きのエントリーになります。

uepon.hatenadiary.com

前回は意図したGoogleDriveのディレクトリにスプレッドシートを作成することができませんでした。作成できるのはルート（マイドライブ）の直下のみの制限付き。（gspreadの制限という感じですが…）

今回は、直接GoogleDriveの指定したパスに対してファイル（スプレッドシート）を作成し、以後そのスプレッドシートのKey（id）またはURLを別途取得してファイルを更新をするにはということを考えたいと思います。

使用するもの

今回はgspread以外にPyDriveというパッケージを使用することにしました。

pypi.python.org

github.com

PyDriveは以下のような機能を持っています。

Simplifies OAuth2.0 into just few lines with flexible settings.
Wraps Google Drive API into classes of each resource to make your program more object-oriented.
Helps common operations else than API calls, such as content fetching and pagination control.

簡単にいうとGoogleDriveの操作用のラッパーパッケージになります。こちらもわざわざGoogleDriveをマウントしなくてもファイル群を使えるので便利です。マウント作業とはいったい何だったのか！

`PyDrive`モジュールのインストール

PyDriveのインストールは以下のコマンドを実行すればOKです。

!pip install PyDrive

f:id:ueponx:20180406161707p:plain

無事にインストールできました。

基本的にはこれまでと同様に認証してからファイルへのアクセスを行うことになります。ドキュメントをみると、以下のようなコードを実行すれば認証ができるということだったのですが、

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive

gauth = GoogleAuth()
gauth.LocalWebserverAuth()

drive = GoogleDrive(gauth)

ちょっと書き換えをして以下の様にしてみました。

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from google.colab import auth
from oauth2client.client import GoogleCredentials

# auth.authenticate_user()
gauth = GoogleAuth()
gauth.credentials = GoogleCredentials.get_application_default()
drive = GoogleDrive(gauth)

print('--- PyDrive Auth OK ---')

コードを実行してみるとこんな感じになります。

f:id:ueponx:20180408004235p:plain

恒例の認証処理を行っていきます。基本はクリック程度の作業で、一度行ってしまえばインスタンスが有効である間は認証の2回目以降省略されます。

まずは使用するアカウントを選択します。

f:id:ueponx:20180406183813j:plain

アカウントを選択したらGoogleDriveのアクセスへの許可を行います。【許可】ボタンをクリックします。すると以下の様な認証コードが表示されるので

f:id:ueponx:20180406183918j:plain

コードをコピーして

f:id:ueponx:20180407183436p:plain

Notebookのタブに戻って入力ボックスにこのコードをペーストして

f:id:ueponx:20180408004323p:plain

Enterキーを押します。

f:id:ueponx:20180407182758p:plain

これで認証は完了です。自由にGoogleDriveへアクセスができるようになっています。以下のようにすればファイルの作成ができるようになります。

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from google.colab import auth
from oauth2client.client import GoogleCredentials

auth.authenticate_user()
gauth = GoogleAuth()
gauth.credentials = GoogleCredentials.get_application_default()
drive = GoogleDrive(gauth)

print('--- PyDrive Auth OK ---')

file1 = drive.CreateFile({'title': 'Hello.txt'})
file1.SetContentString('Hello')
file1.Upload()

print('--- PyDrive CreateFile OK ---')

実行すると以下のようになります。

f:id:ueponx:20180407190133p:plain

正常に実行できました。あとはGoogleDriveにファイルが生成されているかを確認します。

f:id:ueponx:20180407184423p:plain

Hello.txtのファイルが無事にできていると思います。内容にも'''Hello```と入っているかなと思います。内容を簡単に説明するとファイルオブジェクトを作成しているのがCreateFile()、ファイルの内容のセットがSetContentString()、具体的なGoogleDriveファイルの生成処理がUpload()になっています。

うまく行ったかなと思うのですが…ちょっと気になる点があります。この処理を2回実行すると同名のファイルが2つ作成されてしました。画像としては以下の様になります。

f:id:ueponx:20180407190447p:plain

そのままCreateFile()を行うとファイル名は同じですがKeyは異なるファイルが生成されるようです。別途上書きをするメソッドもなさそうです。つまり、2回目以降のアクセスに関しては名前からファイルを作成するのではなく一意に振られるKeyやURLを使用する必要があります。

重複したファイルが生成されないようにするには？

いろいろ考えてみました。

簡単に考えると以下のような処理になるかなと。

ファイルの所在を確認
ファイルが所在していなければファイルを作成
ファイルのKey（id）を取得
Key（id）を使用してgspreadなどでスプレッドシートのファイルを開く
スプレッドシートの編集

ファイルの所在の確認処理は、PyDriveを使って各ディレクトリを走査し、ファイル一覧を取得していくことになります。また、ファイルの作成は、GoogleDriveがディレクトリ構成を書くファイルの属性値として持っている構成なので、その中のparent_id属性に親フォルダのkey（id）を与えることで指定したディレクトリの下にファイルを作成することになります。

例えば/aaa/bbb/c.sheetなるファイルを処理する場合にはこうなるのかなと思います。

マイドライブのファイルリストからaaaというディレクトリ名のkeyを取り出す
前の手順で取得したkeyをもとにマイドライブ/aaaのファイルリストからbbbというディレクトリ名のkey（id）を取り出す
前の手順で取得したkeyをもとにマイドライブ/aaa/bbbのファイルリストの中にc.sheetというファイルがあるか調べる
c.sheetというファイルがなければファイルを作成する。（作成時にKey（id）が取得できるようになる）
c.sheetというファイルがあればKey（id）が取得する
取得したスプレッドシートファイルのkey（id）を使ってgspreadを使用して処理を行う

もう少しロジックを考えてみた

とはいっても、GoogleDriveはフラットな構成なのでリスト取得は一回やればいいのかなと思うので以下の様に修正してみました。

マイドライブのファイルリストを取得する
ファイルリスト中にparentがマイドライブ（root）でありaaaという名前のディレクトリがあるか調べる
ない場合にはフォルダを作成する
aaaディレクトリのkeyを取得する
ファイルリスト中にparentがkeyでありbbbという名前のディレクトリがあるか調べる
ない場合にはフォルダを作成する
bbbディレクトリのkeyを取得する
ファイルリスト中にparentがkeyでありc.sheetという名前のファイルがあるか調べる
c.sheetというファイルがなければファイルを作成する
c.sheetのkeyを取得する
取得したスプレッドシートファイルのkeyを使ってgspreadを使用して処理を行う

処理が増えたような気がしますが単純になったこととリストを何回も取得しなくて良くなった点はよいのかなと思います。

このロジックから以下のようなidの取得までのコードをかいてみました。（途中のパス（ディレクトリ）でエラーが発生した場合の対応できてません）

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from google.colab import auth
from oauth2client.client import GoogleCredentials

auth.authenticate_user()
gauth = GoogleAuth()
gauth.credentials = GoogleCredentials.get_application_default()
drive = GoogleDrive(gauth)

print('--- PyDrive Auth OK ---')

def get_id(path_str):

  fileDict = {}

  file_list = drive.ListFile({'q': "trashed=false"}).GetList()
  for f in file_list:
    if(len(f['parents']) != 0): # 他のユーザからの共有ファイルはparents属性がない。
      # print('title: %s, id: %s, parent_id %s' % (f['title'], f['id'], f['parents'][0]['id']))
      tmp = {'title':f['title'], 'ID':f['id'], 'parentsID':f['parents'][0]['id']}
      fileDict[f['title']] = tmp

  #for key, value in fileDict.items():
  #  print(key, value)

  path_str_list = path_str.strip('/').split('/')

  print('-> pathList:')
  for i,p in enumerate(path_str_list):
    if(p in fileDict):
      print(fileDict[p])
    else:
      print('ファイル新規作成')
      nf = drive.CreateFile({'title': p, 'mimeType': 'application/vnd.google-apps.spreadsheet', 'parents': [{'kind': 'drive#parentReference', 'id': fileDict[path_str_list[i-1]]['ID']}]})
      nf.Upload()
      return nf['id']
  else:
    return fileDict[p]['ID']
  
path_string = "/Colab Notebooks/sample/SpreadSample"
d = get_id(path_string)
print('id = %s' % d)

注意点としてはファイルの作成時にMime-Typeを指定していないとスプレッドシートファイルを作成できない点でしょうか。具体的に抜き出すとこの処理になります。

nf = drive.CreateFile({'title': p, 'mimeType': 'application/vnd.google-apps.spreadsheet', 'parents': [{'kind': 'drive#parentReference', 'id': fileDict[path_str_list[i-1]]['ID']}]})

ここ実行するとこんな感じになります。

f:id:ueponx:20180421143800p:plain

スプレッドシートもできています。

f:id:ueponx:20180421144103p:plain

ちなみに2回目の実行をおこなってみたのですが、新規作成は行われませんでした。

f:id:ueponx:20180421145246p:plain

f:id:ueponx:20180421145428p:plain

一応、無事に動作できたようです。

終わりにというか、追記

今回はこんなロジックでつくってみたのですが、GoogleDriveのファイル数多くある場合にはかなり遅くなります。そのため、最初に作成したロジックのものも作ってみました。ファイル数が多い場合にはこちらのほうが有効かもしれません。

from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
from google.colab import auth
from oauth2client.client import GoogleCredentials

auth.authenticate_user()
gauth = GoogleAuth()
gauth.credentials = GoogleCredentials.get_application_default()
drive = GoogleDrive(gauth)

print('--- PyDrive Auth OK ---')

# ファイルのPathからIDを検索する
def get_id_list(path_str):

  # parentsのidで検索してフォルダ、ファイル名が一致するオブジェクトのidを返す
  def find_path_id(parent_id, name):
    file_list = drive.ListFile({'q': "'%s' in parents and trashed=false" % parent_id}).GetList()
    for f in file_list:
      if (f['title']==name):
        return f['id']
  
  path_str_list = path_str.strip('/').split('/')
  parent_id_dict = {}
  filename = path_str_list[-1]
      
  print('---Process---')
  parent_id = 'root'
  for p in path_str_list:
    current = p
    # print('parent_id: %s current: %s' % (parent_id, current))
    parent_id_dict[current] = parent_id
    parent_id = find_path_id(parent_id, current)
  print('<-- parent_id -->: %s <-- current -->: %s' % (parent_id , current))
  if(parent_id == None):
    print('ファイル新規作成')
    print('<-- parent_id -->: %s <-- filename -->: %s' % ((parent_id_dict[filename]), filename))
    f = drive.CreateFile({'title': filename, 'mimeType': 'application/vnd.google-apps.spreadsheet', 'parents': [{'kind': 'drive#parentReference', 'id': parent_id_dict[filename]}]})
    f.Upload()
    # print(f['id'])
    return f['id']
  else:
    id = find_path_id(parent_id_dict[filename], filename)
    # print(id)
    return id
  
path_string = "/Colab Notebooks/sample/SpreadSample"
try:
  d = get_id_list(path_string)
  print('id = %s' % d)
except:
  print('raise exception')

1回目の実行 f:id:ueponx:20180421153831p:plain

2回目の実行 f:id:ueponx:20180421153910p:plain

あとはこのkey（id）を使用して、以下のようなコードを追記してあげると

import gspread

gc = gspread.authorize(GoogleCredentials.get_application_default())
sh = gc.open_by_key(key)
worksheet = sh.get_worksheet(0)

# set value
worksheet.update_acell('A1', 'foo')

# set values
cell_list = worksheet.range('A2:C3')
for cell in cell_list:
    cell.value = 'bar'

worksheet.update_cells(cell_list)

# get value
val = worksheet.acell('A1').value
print(val)

# get value
val = worksheet.cell(2, 2).value # B2 cell
print(val)