Google Apps ScriptにてExcelファイルを変換して取り込んでみる【GAS】
Google DriveではExcelファイルをスプレッドシートで開くと自動的に変換して開く機能がついています。しかし、Google Apps Scriptには直接これを変換するようなメソッドは搭載されていません。現場では普通にExcelも併用されているでしょうし、これではちょっと不便です。
変換する手法が２つありますが、定期的に自動的にExcelファイルでアップロードされてるデータから変換し、指定のスプレッドシートにデータを集約する事が自動化させる事が可能です。
今回は、UrlFetchAppクラスと、拡張機能であるDrive APIを利用した２つの手法について記述します。
※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クラスを利用します。
ソースコード
//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン(UrlfetchAppを利用するパターン)
function xls2sheet3() {
//変換するファイルのIDを指定する
let fileid = "ここに変換するExcelファイルのIDを入れる"
//Excelファイルを取得する
let xlsxfile = DriveApp.getFileById(fileid);
let filename = xlsxfile.getName();
//ExcelファイルがあるカレントディレクトリのIDを取得する
let destFolders = xlsparents(fileid); // xlsxファイルがあるカレントフォルダを指定する
//ファイル変換を実行する
let ssid = convertExcel2Sheets(fileid, filename, destFolders);
//変換したファイルのIDを取得する
console.log(ssid)
}
//UrlfetchAppでDrive API v3を叩いて変換
function convertExcel2Sheets(fileid, filename, targetfolder) {
//アクセストークンを取得
let token = ScriptApp.getOAuthToken();
//リクエストURLを構築する
//テンプレートリテラルを使ってfileidを含めてみたパターン
const url = `https://www.googleapis.com/drive/v3/files/${fileid}/copy`;
//リクエストオプション
const options = {
'method': 'post',
'headers': {
'Authorization': 'Bearer ' + token,
'Content-Type': 'application/json'
},
'payload': JSON.stringify({
'mimeType': MimeType.GOOGLE_SHEETS,
'convert': true,
'title': filename,
'parents': [{id: targetfolder}]
})
};
//レスポンスを取得する
let response = UrlFetchApp.fetch(url, options);
//レスポンス内容を取得する
let res = JSON.parse(response.getContentText());
//ファイルIDを返す
return res.id;
}
//xlsファイルのあるカレントフォルダのIDを返す関数
function xlsparents(id){
let parentsfolder = DriveApp.getFileById(id).getParents();
while (parentsfolder.hasNext()) {
let folder = parentsfolder.next();
let idval = folder.getId();
return idval;
}
}
解説
- 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のスイッチをオン
ソースコード
//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン(Drive API v3を利用するパターン)
function xls2sheetApi3(){
//変換するファイルのIDを指定する
let fileid = "ここに変換するExcelファイルのIDを入れる"
//Excelファイルを取得する
let xlsxfile = DriveApp.getFileById(fileid);
let filename = xlsxfile.getName();
//ExcelファイルがあるカレントディレクトリのIDを取得する
let destFolders = xlsparents(fileid); // xlsxファイルがあるカレントフォルダを指定する
//ファイル変換を実行する
let ssid = convertExcel2SheetApi(fileid, filename, destFolders);
//変換したファイルのIDを取得する
console.log(ssid)
}
//Drive.Files.insetを使った変換方法
function convertExcel2SheetApi(fileid, filename, targetfolder){
//変換情報を作成する
var files = {
title: filename,
mimeType: MimeType.GOOGLE_SHEETS,
parents: [{id: targetfolder}],
};
//Drive APIで変換
var res = Drive.Files.copy(files, fileid, {convert: true});
//変換シートのIDを返す
return res.id;
}
解説
- UrlFetchAppと異なりルーチンがスッキリです。
- v2の時はDrive.Files.insertでしたが、v3ではDrive.Files.copyにメソッド名が変わっているので注意。
- xls2sheetApi3を叩いて、指定のExcelファイルのIDを元に変換を掛けます。実際に変換するのがconvertExcel2SheetApi関数になります。
- Drive APIでの変換データからの返り値はJSONデータです。様々な情報がつまっていますが、今回はファイルのIDだけ利用させていただきました。
- 上記のコードはマイドライブ上で正しく動作しますが、共有ドライブ上だと上手く動作しません。その場合には、convert: trueの場所に追加で「supportsAllDrives」をtrueで追加が必要です。「API call to drive.files.copy failed with error: File not found」を解消できます。
var res = Drive.Files.copy(files, fileid, {convert: true,supportsAllDrives: true});
変換シートからデータを集約する
変換されたシートから自分のシートに対してデータを取得して書き込むルーチンです。トリガーなどを利用したり、ボタン一発で指定フォルダ内のxlsxファイルを変換して取り込むといったような改造をすると尚良いでしょう。
//変換したexcelファイルからの情報をスプレッドシートに書き出す
function getSheetData(id){
let ui = SpreadsheetApp.getUi();
//変換したスプレッドシートへ接続
let sheet = SpreadsheetApp.openById(id).getSheetByName("売上データ");
let ss = sheet.getRange("A2:G").getValues();
let slength = ss.length;
//書き込み用配列
let array = [];
//データあるものだけ配列へpush
for(let i = 0;i<slength;i++){
if(ss[i][0] == ""){
continue;
}else{
array.push(ss[i]);
}
}
//書き込み先スプレッドシートを取得
let editSheet = SpreadsheetApp.getActiveSpreadsheet();
let ess = editSheet.getSheetByName("シート1");
//スプレッドシートに配列データを最終行以降に書き込み
let endRow = ess.getLastRow() + 1;
let lastColumn = array[0].length; //カラムの数を取得する
let lastRow = array.length; //行の数を取得する
ess.getRange(endRow,1,lastRow,lastColumn).setValues(array);
//終了メッセージ
ui.alert("変換しデータを取得しました。");
}
関連リンク
- 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
