2018年5月6日 / 最終更新日時 : 2023年3月28日 officeの杜 Google

Google Apps ScriptにてExcelファイルを変換して取り込んでみる【GAS】

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

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

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

1 今回使用するスプレッドシート
2 直接編集が可能になりました
3 UrlFetchAppで変換する方法
- 3.1 ソースコード
- 3.2 解説
4 Drive APIで変換する方法
5 変換シートからデータを集約する
6 関連リンク

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

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

実際に使う場合には、メニューより「作業実行」より、ファイル選択、ファイル選択その２を選びます。それぞれ、この後で説明する「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にて公開されているものを利用させていただきました。

ソースコード

//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン
function xls2sheet(fileid) { 
  var xlsFile = DriveApp.getFileById(fileid); 
  var xlsBlob = xlsFile.getBlob(); 
  var xlsFilename = xlsFile.getName(); 
  var destFolders = xlsparents(fileid); // xlsxファイルがあるカレントフォルダを指定する
  var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);
  
  //変換したファイルからデータを取得する
  getSheetData(ss.getId());
}

//UrlFetchAppでAPIを叩いて変換する
function convertExcel2Sheets(excelFile, filename, targetfolder) {
  //アクセストークンを取得
  var token = ScriptApp.getOAuthToken();
  
  //ファイル変換パラメータ
  var uploadParams = {
    method:'post',
    contentType: 'application/vnd.ms-excel',
    contentLength: excelFile.getBytes().length,
    headers: {'Authorization': 'Bearer ' + token},
    payload: excelFile.getBytes()
  };
  
  // マイドライブへの変換リクエスト
  var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);
  var fileDataResponse = JSON.parse(uploadResponse.getContentText());

  // 指定したフォルダへファイルを変換する際のパラメータ
  var payloadData = {
    title: filename, 
    parents: [{id: targetfolder}]
  };

  // ファイル名と格納先パラメータ
  var updateParams = {
    method:'put',
    headers: {'Authorization': 'Bearer ' + token},
    contentType: 'application/json',
    payload: JSON.stringify(payloadData)
  };
  
  // 変換したファイルを受け取る
  UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);
  
  //変換後のスプレッドシートのファイルIDを取得して返す。
  return SpreadsheetApp.openById(fileDataResponse.id);
}

//xlsファイルのあるカレントフォルダのIDを返す関数
function xlsparents(id){
  var parentsfolder = DriveApp.getFileById(id).getParents();
  while (parentsfolder.hasNext()) {
   var folder = parentsfolder.next();
   var idval = folder.getId();
   return idval;
 }
}

//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン

function xls2sheet(fileid) {

var xlsFile = DriveApp.getFileById(fileid);

var xlsBlob = xlsFile.getBlob();

var xlsFilename = xlsFile.getName();

var destFolders = xlsparents(fileid); // xlsxファイルがあるカレントフォルダを指定する

var ss = convertExcel2Sheets(xlsBlob, xlsFilename, destFolders);

//変換したファイルからデータを取得する

getSheetData(ss.getId());

}

//UrlFetchAppでAPIを叩いて変換する

function convertExcel2Sheets(excelFile, filename, targetfolder) {

//アクセストークンを取得

var token = ScriptApp.getOAuthToken();

//ファイル変換パラメータ

var uploadParams = {

method:'post',

contentType: 'application/vnd.ms-excel',

contentLength: excelFile.getBytes().length,

headers: {'Authorization': 'Bearer ' + token},

payload: excelFile.getBytes()

};

// マイドライブへの変換リクエスト

var uploadResponse = UrlFetchApp.fetch('https://www.googleapis.com/upload/drive/v2/files/?uploadType=media&convert=true', uploadParams);

var fileDataResponse = JSON.parse(uploadResponse.getContentText());

// 指定したフォルダへファイルを変換する際のパラメータ

var payloadData = {

title: filename,

parents: [{id: targetfolder}]

};

// ファイル名と格納先パラメータ

var updateParams = {

method:'put',

headers: {'Authorization': 'Bearer ' + token},

contentType: 'application/json',

payload: JSON.stringify(payloadData)

};

// 変換したファイルを受け取る

UrlFetchApp.fetch('https://www.googleapis.com/drive/v2/files/'+fileDataResponse.id, updateParams);

//変換後のスプレッドシートのファイルIDを取得して返す。

return SpreadsheetApp.openById(fileDataResponse.id);

}

//xlsファイルのあるカレントフォルダのIDを返す関数

function xlsparents(id){

var parentsfolder = DriveApp.getFileById(id).getParents();

while (parentsfolder.hasNext()) {

var folder = parentsfolder.next();

var idval = folder.getId();

return idval;

}

解説

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プロジェクト）上で作成されたプロジェクトでなければならないということです。詳細はこちらのページを見てください。

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

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

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

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

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

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

Drive APIを有効にする

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

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

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

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

図：APIコンソールの画面

ソースコード

//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン(Drive API)
function xls2sheetApi(fileid) { 
  var xlsFile = DriveApp.getFileById(fileid); 
  var xlsBlob = xlsFile.getBlob(); 
  var xlsFilename = xlsFile.getName(); 
  var destFolders = xlsparents(fileid); // xlsxファイルがあるカレントフォルダを指定する
  var ss = convertExcel2SheetApi(xlsBlob, xlsFilename, destFolders);
  
  //変換したファイルからデータを取得する
  getSheetData(ss);
}

//Drive.Files.insetを使った変換方法
function convertExcel2SheetApi(excelFile, filename, targetfolder){
  //変換情報を作成する
  var files = {
      title: filename,
      mimeType: MimeType.GOOGLE_SHEETS,
      parents: [{id: targetfolder}],
  };
  
  //Drive APIで変換
  var res = Drive.Files.insert(files, excelFile);
  
  //変換シートのIDを返す
  return res.id;
}

//xlsファイルのあるカレントフォルダのIDを返す関数
function xlsparents(id){
  var parentsfolder = DriveApp.getFileById(id).getParents();
  while (parentsfolder.hasNext()) {
   var folder = parentsfolder.next();
   var idval = folder.getId();
   return idval;
 }
}

//ExcelをGoogleスプレッドシートに変換するメソッドを呼び出すルーチン(Drive API)

function xls2sheetApi(fileid) {

var xlsFile = DriveApp.getFileById(fileid);

var xlsBlob = xlsFile.getBlob();

var xlsFilename = xlsFile.getName();

var destFolders = xlsparents(fileid); // xlsxファイルがあるカレントフォルダを指定する

var ss = convertExcel2SheetApi(xlsBlob, xlsFilename, destFolders);

//変換したファイルからデータを取得する

getSheetData(ss);

}

//Drive.Files.insetを使った変換方法

function convertExcel2SheetApi(excelFile, filename, targetfolder){

//変換情報を作成する

var files = {

title: filename,

mimeType: MimeType.GOOGLE_SHEETS,

parents: [{id: targetfolder}],

};

//Drive APIで変換

var res = Drive.Files.insert(files, excelFile);

//変換シートのIDを返す

return res.id;

}

//xlsファイルのあるカレントフォルダのIDを返す関数

function xlsparents(id){

var parentsfolder = DriveApp.getFileById(id).getParents();

while (parentsfolder.hasNext()) {

var folder = parentsfolder.next();

var idval = folder.getId();

return idval;

}

解説

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

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

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

//変換したexcelファイルからの情報をスプレッドシートに書き出す
function getSheetData(id){
  var ui = SpreadsheetApp.getUi();
  
  //変換したスプレッドシートへ接続
  var sheet = SpreadsheetApp.openById(id).getSheetByName("売上データ");
  var ss = sheet.getRange("A2:G").getValues();
  var slength = ss.length;

  //書き込み用配列
  var array = [];
  
  //データあるものだけ配列へpush
  for(var i = 0;i<slength;i++){
    if(ss[i][0] == ""){
      continue;
    }else{
      array.push(ss[i]);
    } 
  }
  
  //書き込み先スプレッドシートを取得
  var editSheet = SpreadsheetApp.getActiveSpreadsheet();
  var ess = editSheet.getSheetByName("シート1");
  
  //スプレッドシートに配列データを最終行以降に書き込み
  var endRow = ess.getLastRow() + 1;
  var lastColumn = array[0].length;　　//カラムの数を取得する
  var lastRow = array.length;  　　　　//行の数を取得する
  ess.getRange(endRow,1,lastRow,lastColumn).setValues(array);
  
  //終了メッセージ
  ui.alert("変換しデータを取得しました。");

}

//変換したexcelファイルからの情報をスプレッドシートに書き出す

function getSheetData(id){

var ui = SpreadsheetApp.getUi();

//変換したスプレッドシートへ接続

var sheet = SpreadsheetApp.openById(id).getSheetByName("売上データ");

var ss = sheet.getRange("A2:G").getValues();

var slength = ss.length;

//書き込み用配列

var array = [];

//データあるものだけ配列へpush

for(var i = 0;i<slength;i++){

if(ss[i][0] == ""){

continue;

}else{

array.push(ss[i]);

}

//書き込み先スプレッドシートを取得

var editSheet = SpreadsheetApp.getActiveSpreadsheet();

var ess = editSheet.getSheetByName("シート1");

//スプレッドシートに配列データを最終行以降に書き込み

var endRow = ess.getLastRow() + 1;

var lastColumn = array[0].length;　　//カラムの数を取得する

var lastRow = array.length; 　　　　//行の数を取得する

ess.getRange(endRow,1,lastRow,lastColumn).setValues(array);

//終了メッセージ

ui.alert("変換しデータを取得しました。");

}

Google Apps ScriptにてExcelファイルを変換して取り込んでみる【GAS】

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

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

UrlFetchAppで変換する方法

ソースコード

解説

Drive APIで変換する方法

プロジェクトを移動

Drive APIを有効にする

ソースコード

解説

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

関連リンク

コメントを残すコメントをキャンセル

Google Apps ScriptでWebAPIや外部サイトを取得する指南書【GAS】

人工知能音声AIはAlexaが良いと思う

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

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

UrlFetchAppで変換する方法

ソースコード

解説

Drive APIで変換する方法

プロジェクトを移動

Drive APIを有効にする

ソースコード

解説

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

関連リンク

コメントを残す コメントをキャンセル

Google Apps ScriptでWebAPIや外部サイトを取得する指南書【GAS】

人工知能音声AIはAlexaが良いと思う

コメントを残すコメントをキャンセル