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で変換する方法」に対応しています。

直接編集が可能になりました

2019年5月15日、G SuiteじゃないGoogleアカウントのほうで、この機能がロールアウトされてるのを確認しました。何も特別なことをせず、サポートしてるフォーマットをダブルクリック⇒Googleスプレッドシート等のアプリで開くと、そのまま直接編集と保存が可能です。ファイル名の横に「XLSX」などの表記が出ているのが特徴です。

試しにxlsxファイルを開いても、Googleスプレッドシートに変換される事なく、保存も編集も可能。Googleスプレッドシートにしたい場合には、「ファイル」⇒「Googleスプレッドシートとして保存」を選ぶだけです。

GASからは変換せずとも直接XLSXファイルからデータを取り出せるかもしれませんね(未確認)。

 

図:officeフォーマットがそのまま利用できる

図:もちろん変換保存も可能

UrlFetchAppで変換する方法

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

ソースコード

解説

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

Drive APIで変換する方法

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

プロジェクトを移動

今回の発表直前の2019年4月8日より、Google Apps ScriptからCloud Platform Projectへ直接アクセスが出来なくなりました。これまでにデプロイしてるものについては、これまで通り「リソース」⇒「Google Cloud Platform API ダッシュボード」からアクセスが可能です。

今回の変更はスプレッドシート上で動かすスクリプトやGoogleの拡張サービスを利用しないタイプのスクリプトであれば特に問題はありませんが、「Apps Script API」や「Google Picker API」、「Cloud SQL接続」などGCP上のAPIを利用する場合には以下の手順を踏んで、Google Apps Scriptにプロジェクトを連結する必要があります。これまでは、自動的にGCP上にGoogle Apps Script用のプロジェクトが生成されていたのですが、今後は自分の組織(もしくはGCPプロジェクト)上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。

連結する手順は以下の通り

  1. Google Cloud Consoleを開く
  2. 左上にある▼をクリックする
  3. ダイアログが出てくるので、新規プロジェクトを作るか?既存のプロジェクトを選択する。この時、G Suiteであれば選択元は「自分のドメイン」を選択する必要があります。
  4. プロジェクト情報パネルから「プロジェクト番号」をコピーする
  5. 対象のGoogle Apps Scriptのスクリプトエディタを開く
  6. 「リソース」⇒「Cloud Platform プロジェクト」を開く
  7. 4.で入手した番号をプロジェクトを変更のテキストボックスに入れて、プロジェクトを設定ボタンをクリックする
  8. 無事に移動が完了すればメッセージが表示されます。
  9. この時、元の自動作成されたプロジェクトはシャットダウンされて消えます。これで設定完了です。

今回のこの変更だと1つ作ったプロジェクトに集約する必要があるので、クォータについてプロジェクト毎のカウントだったので問題なかったものが、集約されることで、クォータに引っ掛かる可能性があります。

図:プロジェクト番号をコピーしておきます

図:プロジェクトを他のプロジェクトに紐付けしました。

図:GCPの拡張サービスを使うには手順が必要になった

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ファイルを変換して取り込むといったような改造をすると尚良いでしょう。

共有してみる: