Google DriveではExcelファイルをスプレッドシートで開くと自動的に変換して開く機能がついています。しかし、Google Apps Scriptには直接これを変換するようなメソッドは搭載されていません。現場では普通にExcelも併用されているでしょうし、これではちょっと不便です。

そこで、GoogleのAPIを利用して変換する2つの手法があります。これを利用する事で、定期的に自動的にExcelファイルでアップロードされてるデータから変換し、指定のスプレッドシートにデータを集約する事が自動化させる事が可能です。

今回は、UrlFetchAppクラスと、拡張機能であるDrive APIを利用した2つの手法について記述します。

今回使用するスプレッドシート

今回のファイルはファイル選択にGoogle Pickerを使用している為、事前にAPIキーを取得し登録しておく必要があります。こちらのエントリーよりAPIキーを取得して下さい。取得したAPIキーは、メニューより「作業実行」⇒「APIキーの登録」より入力してください。

実際に使う場合には、メニューより「作業実行」より、ファイル選択、ファイル選択その2を選びます。それぞれ、この後で説明する「UrlFetchAppで変換する方法」「Drive APIで変換する方法」に対応しています。

UrlFetchAppで変換する方法

こちらの手法は、Googleの拡張機能を使用せず、Google APIに対して情報をPOSTする形でExcelファイルをGoogleスプレッドシートへ変換します。UrlFetchAppクラスを利用します。元になってるスクリプトはGitHubにて公開されているものを利用させていただきました。

ソースコード

解説

  • Google Pickerで取得したファイル情報のうち、IDをxls2sheet関数に渡してあげています。最期にgetSheetDataにて、変換したシートからデータを取得して集約させています。
  • 取得したIDより指定のフォルダに対してスプレッドシートに変換する際は、PUTで送信し変換済みファイルのIDを取得して返しています。
  • ファイルをアップロードする時は、POSTで送信しています。

Drive APIで変換する方法

こちらの手法は事前にGoogleの拡張サービスより、Drive APIを有効にしておく必要があります。

Drive APIを有効にする

スクリプトエディタから作業を開始します。

  1. メニューより「リソース」⇒「Googleの拡張サービス」を開く
  2. Drive APIのスイッチをオンにする
  3. Google APIコンソールのリンクをクリックする
  4. ダッシュボードでGoogle Drive APIが有効になっていればOK
  5. なっていなかった場合は、APIとサービスの有効化をクリックする
  6. 検索窓で、driveと検索し、Google Drive APIを開く
  7. 有効化ボタンを押す。
  8. スクリプトエディタに戻って、OKボタンを押す

これで、Drive APIが直接Google Apps Scriptから利用できるようになりました。

図:Drive APIのスイッチをオン

図:APIコンソールの画面

ソースコード

解説

  • UrlFetchAppと異なりルーチンがスッキリです。
  • Google Pickerで取得したファイル情報のうち、IDをxls2sheetApi関数に渡してあげています。最期にgetSheetDataにて、変換したシートからデータを取得して集約させています。
  • Drive APIでの変換データからの返り値はJSONデータです。様々な情報がつまっていますが、今回はIDだけ利用させていただきました。

変換シートからデータを集約する

変換されたシートから自分のシートに対してデータを取得して書き込むルーチンです。トリガーなどを利用したり、ボタン一発で指定フォルダ内のxlsxファイルを変換して取り込むといったような改造をすると尚良いでしょう。

関連リンク