Google Apps ScriptでCSVファイルを取り扱う ★🌴 officeの杜

Googleスプレッドシートを使用していてぶつかる厄介な問題がCSVファイルの取り扱いに関する問題。主に、GoogleスプレッドシートからエクスポートしたCSVファイルをExcelで読み込めない問題や、逆に大量のCSVファイルをインポートを読み込みたい（しかもローカルPCから）となると、非常に融通の効かないアプリケーションだなと今でも思います。

これらの問題は、前者はGoogleスプレッドシートからエクスポートされるテキストがUTF-8でエンコードされたもので、取り分け日本やEXCELで普通に扱うShift-JISでエンコードされたCSVファイルではないから起き、後者はそもそも標準機能だと１個ずつしか読み込めないなどの仕様があるからです。後者の複数ファイルを読み込む件に関しては、Google Picker APIを使ったドライブから複数ファイルの読み込みや、HTML5を利用したローカルPCから複数読み込む手法を使えば行けます。

※一部のコードで不具合が出ていたので、修正するとともに、行列入替えインポートのコードを追加しました。

1 準備するもの
2 CSVファイルをインポートするコード
3 CSVファイルをエクスポートするコード
4 実行と結果
5 ポイント
6 関連リンク
- 6.1 関連記事

準備するもの

CSVファイルをインポートするコード

普通にCSVファイルをインポートする

//csvファイルをインポートする
function importcsv(){
  //事前準備
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  //CSVファイル群が入っているフォルダのIDを取得
  var csvFolderkey = "ここにフォルダのIDを指定する";
  
  //特定のフォルダ内のCSVファイルを取得
  var files = DriveApp.getFolderById(csvFolderkey).getFilesByType(MimeType.CSV);
  var csvFile = "";
  var csvData = "";
  var sheetman = sheet.getSheetByName('書き込み先');
  
  while (files.hasNext()) {
    //Shift_JISにてCSVをそのままで取り込む
    csvFile = files.next().getBlob().getDataAsString("UTF-8"); 
    
    //CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)
    csvData = Utilities.parseCsv(csvFile);
    
    //書込みをする
    var endrow = Number(sheetman.getLastRow()) + 1;
    var lastColumn = csvData[0].length;　　//カラムの数を取得する
    var lastRow = csvData.length;  　　　　//行の数を取得する
    sheetman.getRange(endrow,1,lastRow,lastColumn).setValues(csvData);
    
  }
  
  ui.alert("CSVデータのインポートが完了しました。"); 
}

//csvファイルをインポートする

function importcsv(){

//事前準備

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var ui = SpreadsheetApp.getUi();

//CSVファイル群が入っているフォルダのIDを取得

var csvFolderkey = "ここにフォルダのIDを指定する";

//特定のフォルダ内のCSVファイルを取得

var files = DriveApp.getFolderById(csvFolderkey).getFilesByType(MimeType.CSV);

var csvFile = "";

var csvData = "";

var sheetman = sheet.getSheetByName('書き込み先');

while (files.hasNext()) {

//Shift_JISにてCSVをそのままで取り込む

csvFile = files.next().getBlob().getDataAsString("UTF-8");

//CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)

csvData = Utilities.parseCsv(csvFile);

//書込みをする

var endrow = Number(sheetman.getLastRow()) + 1;

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

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

sheetman.getRange(endrow,1,lastRow,lastColumn).setValues(csvData);

}

ui.alert("CSVデータのインポートが完了しました。");

}

以前は、Google Developerで公開していた「CSVToArray関数」を取り込んで変換していましたが、現在は、Utilities.parseCsvで処理が出来るようになっています。
今回のコードは指定フォルダ内のCSVをDriveAppで全て拾って、取り込むコードになっています。まとめて、CSVファイルを取り込む事が可能です。
Excel標準のCSVはShift-JISなので、getDataAsStringにてShift-JISを指定して取り込みます。今回のファイルはUTF-8なので、UTF-8で指定しています。
指定フォルダ内ファイルだけを取り込む為、DriveApp.searchFilesを使ってファイルを取り出す場合には、DriveApp.searchFiles(“‘”+csvFolderkey+”‘ in parents”)を使います。ただこのコードだと指定フォルダにcsv以外のファイルがあっても取り込もうとしてしまうので注意！！
CSVだけをきっちり取り出す場合には、MIMETYPE指定でDriveApp.getFolderById(フォルダID).getFilesByType(MimeType.CSV);を使うとフィルタして取り出してくれます。

CSVToArray関数について

かつて利用していたCSVToArray関数は以下のようなコード。テキストデータが配列になって返ってくる非常に便利なコードでした。

//CSVデータを整形して配列で返す関数（特に変更していません）
function CSVToArray( strData, strDelimiter ){
  // Check to see if the delimiter is defined. If not,
  // then default to comma.
  strDelimiter = (strDelimiter || ",");

  // Create a regular expression to parse the CSV values.
  var objPattern = new RegExp(
    (
      // Delimiters.
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

      // Quoted fields.
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

      // Standard fields.
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );


  // Create an array to hold our data. Give the array
  // a default empty first row.
  var arrData = [[]];

  // Create an array to hold our individual pattern
  // matching groups.
  var arrMatches = null;


  // Keep looping over the regular expression matches
  // until we can no longer find a match.
  while (arrMatches = objPattern.exec( strData )){

    // Get the delimiter that was found.
    var strMatchedDelimiter = arrMatches[ 1 ];

    // Check to see if the given delimiter has a length
    // (is not the start of string) and if it matches
    // field delimiter. If id does not, then we know
    // that this delimiter is a row delimiter.
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){

      // Since we have reached a new row of data,
      // add an empty row to our data array.
      arrData.push( [] );

    }


    // Now that we have our delimiter out of the way,
    // let's check to see which kind of value we
    // captured (quoted or unquoted).
    if (arrMatches[ 2 ]){

      // We found a quoted value. When we capture
      // this value, unescape any double quotes.
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );

    } else {

      // We found a non-quoted value.
      var strMatchedValue = arrMatches[ 3 ];

    }

    // Now that we have our value string, let's add
    // it to the data array.
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }

  // Return the parsed data.
  return( arrData );
}

//CSVデータを整形して配列で返す関数（特に変更していません）

function CSVToArray( strData, strDelimiter ){

// Check to see if the delimiter is defined. If not,

// then default to comma.

strDelimiter = (strDelimiter || ",");

// Create a regular expression to parse the CSV values.

var objPattern = new RegExp(

(

// Delimiters.

"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

// Quoted fields.

"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

// Standard fields.

"([^\"\\" + strDelimiter + "\\r\\n]*))"

"gi"

);

// Create an array to hold our data. Give the array

// a default empty first row.

var arrData = [[]];

// Create an array to hold our individual pattern

// matching groups.

var arrMatches = null;

// Keep looping over the regular expression matches

// until we can no longer find a match.

while (arrMatches = objPattern.exec( strData )){

// Get the delimiter that was found.

var strMatchedDelimiter = arrMatches[ 1 ];

// Check to see if the given delimiter has a length

// (is not the start of string) and if it matches

// field delimiter. If id does not, then we know

// that this delimiter is a row delimiter.

if (

strMatchedDelimiter.length &&

(strMatchedDelimiter != strDelimiter)

){

// Since we have reached a new row of data,

// add an empty row to our data array.

arrData.push( [] );

}

// Now that we have our delimiter out of the way,

// let's check to see which kind of value we

// captured (quoted or unquoted).

if (arrMatches[ 2 ]){

// We found a quoted value. When we capture

// this value, unescape any double quotes.

var strMatchedValue = arrMatches[ 2 ].replace(

new RegExp( "\"\"", "g" ),

"\""

);

} else {

// We found a non-quoted value.

var strMatchedValue = arrMatches[ 3 ];

}

// Now that we have our value string, let's add

// it to the data array.

arrData[ arrData.length - 1 ].push( strMatchedValue );

}

// Return the parsed data.

return( arrData );

}

データの行列を入替えてインポートする

エクセルでもよく、データの行列を入替えて貼り付けを行います。簡単に行う関数として、Transpose関数などがありますね。しかし、大量にインポートする場合、普通にインポートを終えてから手動でこれらの作業を行うのは不便です。そこで、インポート時にそもそも行列入替えて貼り付けられたら、手間がなくなります。

今回のコードはそれを実現するコードです。

//csvファイルを行列転置してインポート
function transposeacsv(){
  //事前準備
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  //CSVファイル群が入っているフォルダのIDを取得
  var csvFolderkey = "ここにフォルダのIDを入れる";
  
  //特定のフォルダ内のCSVファイルを取得
  var files = DriveApp.getFolderById(csvFolderkey).getFilesByType(MimeType.CSV);
  var csvFile = "";
  var csvData = "";
  var sheetman = sheet.getSheetByName('転置して取り込み');
  
  while (files.hasNext()) {
    //Shift_JISにてCSVをそのままで取り込む
    csvFile = files.next().getBlob().getDataAsString("UTF-8"); 
    
    //CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)
    csvData = Utilities.parseCsv(csvFile);
    
    //配列となったcsvデータを行列入替えながらシートに追記
    var endrow = Number(sheetman.getLastRow()) + 1;
    var lastrow = csvData[0].length;
    var lastColumn = csvData.length;
    sheetman.getRange(endrow,1,lastrow,lastColumn)
            .setValues(Object.keys(csvData[0]).map ( function (colnum) {
            return csvData.map( function (row) {
              return row[colnum];
            });
    }));  
  }

  ui.alert("CSVデータを行列入替えしてインポートが完了しました。"); 
}

//csvファイルを行列転置してインポート

function transposeacsv(){

//事前準備

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var ui = SpreadsheetApp.getUi();

//CSVファイル群が入っているフォルダのIDを取得

var csvFolderkey = "ここにフォルダのIDを入れる";

//特定のフォルダ内のCSVファイルを取得

var files = DriveApp.getFolderById(csvFolderkey).getFilesByType(MimeType.CSV);

var csvFile = "";

var csvData = "";

var sheetman = sheet.getSheetByName('転置して取り込み');

while (files.hasNext()) {

//Shift_JISにてCSVをそのままで取り込む

csvFile = files.next().getBlob().getDataAsString("UTF-8");

//CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)

csvData = Utilities.parseCsv(csvFile);

//配列となったcsvデータを行列入替えながらシートに追記

var endrow = Number(sheetman.getLastRow()) + 1;

var lastrow = csvData[0].length;

var lastColumn = csvData.length;

sheetman.getRange(endrow,1,lastrow,lastColumn)

.setValues(Object.keys(csvData[0]).map ( function (colnum) {

return csvData.map( function (row) {

return row[colnum];

});

}));

}

ui.alert("CSVデータを行列入替えしてインポートが完了しました。");

}

途中までは通常インポートと同じコードですが、最後の書き込み時の部分で、配列の行列を入替えて貼り付けています。

CSVファイルをエクスポートするコード

GAS側コード（通常版）

//ダイアログ用のグローバル変数
var url = "";

//CSVエクスポートするルーチン
function exportcsv(){
  //データを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var range = sheet.getSheetByName("エクスポート対象").getDataRange().getValues();
  var targetFolder = "ここにエクスポート先フォルダのIDを入力";
  
  if(range.length >= 1){
  }else{
    ui.alert("データがありません。");
    return 0;
  }
  
  //csvデータをshif-jisに変換
  var csvData = csvchange(range);
  var filename = sheet.getSheetByName("エクスポート対象").getSheetName();
  var blob = Utilities.newBlob("", "text/comma-separated-values", filename + ".csv").setDataFromString(csvData, "Shift_JIS");

  //blobデータをcsvファイルとしてドライブに保存
  var fileid = DriveApp.getFolderById(targetFolder).createFile(blob).getId();
  
  //ダウンロードリンクを生成
  url = "https://drive.google.com/uc?export=download&id=" + fileid;
  
  //ダウンロードリンクのダイアログを生成
  var output = HtmlService.createTemplateFromFile('download');
  var html = output.evaluate().setHeight(150).setWidth(300);
 
  ui.showModalDialog(html, 'csvファイルのダウンロード');

}

//CSVデータ形式に整える関数
function csvchange(data){
  var rowlength = data.length;
  var columnlength = data[0].length;
  var csvdata = "";
  var csv = "";
  
  for(var i = 0;i<rowlength;i++){
    if (i < rowlength-1) {
      csvdata += data[i].join(",") + "\r\n";
    }else{
      csvdata += data[i];
    }
  }

  return csvdata;
}

//ダイアログ用のグローバル変数

var url = "";

//CSVエクスポートするルーチン

function exportcsv(){

//データを取得

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var ui = SpreadsheetApp.getUi();

var range = sheet.getSheetByName("エクスポート対象").getDataRange().getValues();

var targetFolder = "ここにエクスポート先フォルダのIDを入力";

if(range.length >= 1){

}else{

ui.alert("データがありません。");

return 0;

}

//csvデータをshif-jisに変換

var csvData = csvchange(range);

var filename = sheet.getSheetByName("エクスポート対象").getSheetName();

var blob = Utilities.newBlob("", "text/comma-separated-values", filename + ".csv").setDataFromString(csvData, "Shift_JIS");

//blobデータをcsvファイルとしてドライブに保存

var fileid = DriveApp.getFolderById(targetFolder).createFile(blob).getId();

//ダウンロードリンクを生成

url = "https://drive.google.com/uc?export=download&id=" + fileid;

//ダウンロードリンクのダイアログを生成

var output = HtmlService.createTemplateFromFile('download');

var html = output.evaluate().setHeight(150).setWidth(300);

ui.showModalDialog(html, 'csvファイルのダウンロード');

}

//CSVデータ形式に整える関数

function csvchange(data){

var rowlength = data.length;

var columnlength = data[0].length;

var csvdata = "";

var csv = "";

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

if (i < rowlength-1) {

csvdata += data[i].join(",") + "\r\n";

}else{

csvdata += data[i];

}

return csvdata;

}

csvchange関数はCSVToArray付属の関数を使いました。
Excel標準はShift-JISですので、エクスポートする時も文字コードとしてsetDataFromStringにてShift_JISを指定します。
Utilities.newBlobにて出力するデータのMIMETYPEとしてtext/comma-separated-valuesを指定します。
Google Driveからのファイル直ダウンロードは、https://drive.google.com/uc?export=download&id=に続けてファイルIDをつなげれば簡単に作れます。

GAS側コード（変換版）

var url = "";

//Access Tokenを取得して返す関数
function getOAuthToken() {
  DriveApp.getRootFolder()
  return ScriptApp.getOAuthT;oken();
}

//シートをUrlfetchAppでCSVに変換する
function convertcsv(){
  //csv化するシートのIDを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssid = ss.getId();
  var filename = ss.getSheetByName("エクスポート対象").getSheetName();
  var ui = SpreadsheetApp.getUi();
  var token = ScriptApp.getOAuthToken();
  var newFileId = ssid;
  var targetFolder = "ここに出力先フォルダーのIDを入力";
  
  //UrlFetchで渡す変換URLを組み立てる
  var csvurl = "https://docs.google.com/spreadsheets/d/" + newFileId + "/export?format=csv"
  
  //PDF生成するURLをfetchする
  var csv = UrlFetchApp.fetch(csvurl, {headers: {'Authorization': 'Bearer ' +  token}}).getBlob().setName(filename + ".csv");

  //blobデータをcsvファイルとしてドライブに保存
  var fileid = DriveApp.getFolderById(targetFolder).createFile(csv).getId();
  
  //ダウンロードリンクを生成
  url = "https://drive.google.com/uc?export=download&id=" + fileid;
  
  //ダウンロードリンクのダイアログを生成
  var output = HtmlService.createTemplateFromFile('download');
  var html = output.evaluate().setHeight(150).setWidth(300);
 
  ui.showModalDialog(html, 'csvファイルのダウンロード');
}

var url = "";

//Access Tokenを取得して返す関数

function getOAuthToken() {

DriveApp.getRootFolder()

return ScriptApp.getOAuthT;oken();

}

//シートをUrlfetchAppでCSVに変換する

function convertcsv(){

//csv化するシートのIDを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet();

var ssid = ss.getId();

var filename = ss.getSheetByName("エクスポート対象").getSheetName();

var ui = SpreadsheetApp.getUi();

var token = ScriptApp.getOAuthToken();

var newFileId = ssid;

var targetFolder = "ここに出力先フォルダーのIDを入力";

//UrlFetchで渡す変換URLを組み立てる

var csvurl = "https://docs.google.com/spreadsheets/d/" + newFileId + "/export?format=csv"

//PDF生成するURLをfetchする

var csv = UrlFetchApp.fetch(csvurl, {headers: {'Authorization': 'Bearer ' + token}}).getBlob().setName(filename + ".csv");

//blobデータをcsvファイルとしてドライブに保存

var fileid = DriveApp.getFolderById(targetFolder).createFile(csv).getId();

//ダウンロードリンクを生成

url = "https://drive.google.com/uc?export=download&id=" + fileid;

//ダウンロードリンクのダイアログを生成

var output = HtmlService.createTemplateFromFile('download');

var html = output.evaluate().setHeight(150).setWidth(300);

ui.showModalDialog(html, 'csvファイルのダウンロード');

}

こちらのコードは、PDF変換などで使うGoogleスプレッドシート標準機能を利用して変換するコードです。
そのままだとUTF-8形式のCSVが出力されます。
Access Tokenが必要なので、getOAuthToken関数を利用しています。

HTML側コード

<div align="center">
<?
output.append("<a href='" +  url + "' target='_blank'>ダウンロード</a>");
?>
</div>

output.append("<a href='" + url + "' target='_blank'>ダウンロード</a>");

</div>

ダイレクトに生成したCSVをダウンロードが出来ないので、HTML Serviceでダイアログを作り、ダウンロードさせるようにしています。取得データはドライブに格納されており、生成されたリンクをクリックするとダウンロードが始まります。

図：ダウンロードをクリックするとCSVファイルが入手出来る

実行と結果

今回のスプレッドシートには4つのコードが入っています。スプレッドシートのメニューより、「CSVメニュー」より実行します。GAS内で指定してるフォルダのIDを元に一括取り込みとエクスポートを実装しています。

エクスポート – 通常のCSVファイル生成をしてエクスポートしています（Shif-JIS形式）
エクスポートその2 – PDF生成のときにも利用してるUrlfetchAppを利用して出力（UTF-8形式）
インポート – 通常のCSVファイルをUtilities.parseCSVを使って一括取り込み
行列入替インポート – インポート時に行列を入替えて一括インポートします。

ポイント

getContentAsString()は使用しない。UTF-8であるというならばそのまま使えば結構ですが。encode_utf8()にてエンコードしているような例もありますが、今回は使いません。
Shift_JISなCSVの多い日本では、インポート時はgetDataAsString(“Shift_JIS”)を使用するのが良いでしょう。
逆にエクスポートする時は、.setDataFromStringにてcsvデータをshift_jisを使用するのが良いでしょう。
DriveAppを今回は使用しているので、帰ってきた値は変数filesで受けて、files.next().getBlob()にて取得すること。
80個の平均5KBのCSV（合計2,800レコード/31列）読み込み完了まで、50秒程度でした。結構早いですね。
書き込み自体は1ファイルずつ書込をしています。
エクスポートする場合なのですが、日付のデータは日付型で出力してしまうと、相手側で取り込むのがやっかいです。予め、スプレッドシート上で書式なしテキストにしておきましょう。Excelなら自動で日付型と認識してくれるようになります。

MENU

Google Apps ScriptでCSVファイルを取り扱う

準備するもの

CSVファイルをインポートするコード

普通にCSVファイルをインポートする

CSVToArray関数について

データの行列を入替えてインポートする

CSVファイルをエクスポートするコード

GAS側コード（通常版）

GAS側コード（変換版）

HTML側コード

実行と結果

ポイント

関連リンク

関連記事

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

G Suite加入用プロモコード

最近の投稿

表示数

おすすめ

G Suite関連リンク

目次

MENU

Google Apps ScriptでCSVファイルを取り扱う

準備するもの

CSVファイルをインポートするコード

普通にCSVファイルをインポートする

CSVToArray関数について

データの行列を入替えてインポートする

CSVファイルをエクスポートするコード

GAS側コード（通常版）

GAS側コード（変換版）

HTML側コード

実行と結果

ポイント

関連リンク

関連記事

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

G Suite加入用プロモコード

最近の投稿

表示数

おすすめ

G Suite関連リンク

目次

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