2024年12月4日 / 最終更新日時 : 2024年12月4日 officeの杜 Google

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

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

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

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

※2024年12月4日、Drive API v3による変換方法に書き換えています。

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

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

実際に使う場合には、メニューより「作業実行」より、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;
  }
}

//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;
}

////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だけ利用させていただきました。

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

変換されたシートから自分のシートに対してデータを取得して書き込むルーチンです。トリガーなどを利用したり、ボタン一発で指定フォルダ内の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("変換しデータを取得しました。");

}

//変換した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("変換しデータを取得しました。");

}

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

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

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

UrlFetchAppで変換する方法

ソースコード

解説

Drive APIで変換する方法

Drive APIを有効にする

ソースコード

解説

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

関連リンク

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

AppSheetでタイムレコーダーを作成する新着!!

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

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

UrlFetchAppで変換する方法

ソースコード

解説

Drive APIで変換する方法

Drive APIを有効にする

ソースコード

解説

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

関連リンク

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

AppSheetでタイムレコーダーを作成する新着!!

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