Google Apps ScriptにてExcelファイルを変換して取り込んでみる【GAS】
Google DriveではExcelファイルをスプレッドシートで開くと自動的に変換して開く機能がついています。しかし、Google Apps Scriptには直接これを変換するようなメソッドは搭載されていません。現場では普通にExcelも併用されているでしょうし、これではちょっと不便です。
変換する手法が2つありますが、定期的に自動的にExcelファイルでアップロードされてるデータから変換し、指定のスプレッドシートにデータを集約する事が自動化させる事が可能です。
今回は、UrlFetchAppクラスと、拡張機能であるDrive APIを利用した2つの手法について記述します。
※2024年12月4日、Drive API v3による変換方法に書き換えています。
目次 [隠す]
今回使用するスプレッドシート
実際に使う場合には、メニューより「作業実行」より、Urlfetchで変換する、Drive APIで変換するのどちらかを選びます。それぞれ、この後で説明する「UrlFetchAppで変換する方法」「Drive APIで変換する方法」に対応しています。
直接編集が可能になりました
2019年5月15日、Google WorkspaceじゃないGoogleアカウントのほうで、この機能がロールアウトされてるのを確認しました。何も特別なことをせず、サポートしてるフォーマットをダブルクリック⇒Googleスプレッドシート等のアプリで開くと、そのまま直接編集と保存が可能です。ファイル名の横に「XLSX」などの表記が出ているのが特徴です。
試しにxlsxファイルを開いても、Googleスプレッドシートに変換される事なく、保存も編集も可能。Googleスプレッドシートにしたい場合には、「ファイル」⇒「Googleスプレッドシートとして保存」を選ぶだけです。
図:officeフォーマットがそのまま利用できる
図:もちろん変換保存も可能
UrlFetchAppで変換する方法
こちらの手法は、Googleの拡張機能を使用せず、Google APIに対して情報をPOSTする形でExcelファイルをGoogleスプレッドシートへ変換します。UrlFetchAppクラスを利用します。
ソースコード
解説
- Google Pickerで取得したファイル情報のうち、IDをxls2sheet3関数に渡してあげています。最期にgetSheetData関数を使えば、変換したシートからデータを取得して集約させることが可能です。
- 取得したIDより指定のフォルダに対してスプレッドシートに変換する際は、POSTで送信し変換済みファイルのIDを取得して返しています。
- xlsparents関数はExcelファイルが存在するカレントディレクトリのIDを取得して返します。
- payloadとしてmimeTypeに「MimeType.GOOGLE_SHEETS」を指定することで、スプシに変換することが可能です。この関数はDrive APIで変換する方法でも利用しています。
Drive APIで変換する方法
こちらの手法は事前にGoogleの拡張サービスより、Drive APIを有効にしておく必要があります。以下の手順で設定する必要性があります。こちらのほうがコードがシンプルでスッキリします。
Drive APIを有効にする
スクリプトエディタから作業を開始します。
- 左サイドバーのサービスの隣にある+をクリックする
- Drive APIを見つけてクリックする
- バージョンはv3を選択します。
- 追加をクリックする
これで、Drive API v3が直接Google Apps Scriptから利用できるようになりました。
図:Drive APIのスイッチをオン
ソースコード
解説
- UrlFetchAppと異なりルーチンがスッキリです。
- v2の時はDrive.Files.insertでしたが、v3ではDrive.Files.copyにメソッド名が変わっているので注意。
- xls2sheetApi3を叩いて、指定のExcelファイルのIDを元に変換を掛けます。実際に変換するのがconvertExcel2SheetApi関数になります。
- Drive APIでの変換データからの返り値はJSONデータです。様々な情報がつまっていますが、今回はファイルのIDだけ利用させていただきました。
変換シートからデータを集約する
変換されたシートから自分のシートに対してデータを取得して書き込むルーチンです。トリガーなどを利用したり、ボタン一発で指定フォルダ内のxlsxファイルを変換して取り込むといったような改造をすると尚良いでしょう。
関連リンク
- Script to convert .XLSX to Google Sheet and move converted file
- File Upload and Download with File Convert For curl using Drive API
- Failing to get a file in Google Apps Script using the Drive API
- スクリプトで「GoogleJsonResponseException: 次のエラーが発生し、drive.files.copy の呼び出しに失敗しました: User rate limit exceed」
- drive.files.copy failed with error
- How to Copy Google Drive Files Using Apps Script
- Automatically convert Excel spreadsheets to Google Sheets using Apps Script
- Google Apps Scriptを使ってエクセルファイルをスプレッドシートに変換する
- 初心者向けガイド:Google Apps Scriptを使ってExcelファイルをGoogleスプレッドシートに変換する方法
- Method: files.copy - Drive API v3