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

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

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

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

準備するもの

1. 今回使用するスプレッドシート
2. 今回使用するcsvファイル
3. DriveAppクラスのgetDataAsString等
4. UtilitiesクラスのnewBlobメソッドやsetDataFromStringメソッド
5. UtilitiesクラスのparseCsvメソッド

CSVの文字コードを確認する

世の中には大きくわけて、2つのCSVファイルが存在します。それは文字コードがUTF-8のCSVと、Shift-JISのCSV。両者は見た目は同じでも、コンピュータで扱う場合、文字化けなどが発生する原因となるので、取り込む場合には事前にそのCSVの文字コードをよく考慮する必要があります。（古いオフコンだとEUCなんてものもあったりしますが）

文字コードの確認方法ですが、Windowsの場合はメモ帳で開き確認できます。名前を付けて保存をすると、文字コードが下に表示されています。これがANSIならば通常はShift-JISであろうと思われます。EUCなどの他の文字コードの場合は扱いにくいので、できればGASで取り込む前に、UTF-8に変換しておいたほうがトラブルは少ないと思いますし、後述のgetDataAsStringを使わずに済むので、楽です。

図：ANSIならばShift-JISであろう

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("Shift_JIS"); 
    
    //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で指定しています。ただし、Excel2016くらいから標準がUTF-8になってたりするので、CSVの文字コードをよく確認して設定しましょう。
• 指定フォルダ内ファイルだけを取り込む為、DriveApp.searchFilesを使ってファイルを取り出す場合には、DriveApp.searchFiles("'"+csvFolderkey+"' in parents")を使います。ただこのコードだと指定フォルダにcsv以外のファイルがあっても取り込もうとしてしまうので注意！！
• CSVだけをきっちり取り出す場合には、MIMETYPE指定でDriveApp.getFolderById(フォルダID).getFilesByType(MimeType.CSV);を使うとフィルタして取り出してくれます。

V8のバグ回避の為のコード

今現在もV8移行で動かない、Google側が修正していない為バグのまま取り残されてるケースが散見されます。そのうちの１つに、CSVの区切り文字のトラブルがあります。この場合parseCsvを使うには以下のようにコードを変更する必要があります。

//これまでのコード(V8では動かない）
var data = Utilities.parseCsv(data, delimiter);

//バグ対応版コード
var data = Utilities.parseCsv(data, delimiter.charCodeAt(0));

もしくは

var data = Utilities.parseCsv(data, Utilities.newBlob(delimiter).getBytes());

• delimiterが区切り文字。通常はカンマなので、指定しなくても良いのですが
• この区切り文字がセミコロンなど特殊なケースでは今回のような工夫が必要になります。

CSVToArray関数について

かつて利用していたCSVToArray関数は以下のようなコード。テキストデータが配列になって返ってくる非常に便利なコードでした。V8でparseCsvを使った場合、区切り文字指定で取り込めないバグが出ているケースでは、この関数に置き換えると取り込めるので、現在もバグ回避の手段としても利用出来ます。

//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()) {
    //UTF-8にて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データの特定の列にある値がある場合には取り込み対象から除外したいといったような、条件をつけてCSVをインポートしたいケースはループの中で検証して別に用意した配列にレコード単位でpushし、その配列を書き込み対象にすればOKです。

今回、22-xという文字列が入ってるレコードは除外という条件でコードを作りました。使用したファイルはこちらです。

//特定列だけ取得
function importcsv2(){
  //事前準備
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  //書き込み先
  var sheetman = sheet.getSheetByName('選択取得');

  //ファイルの指定
  var csv3 = "ここに取り込みたいCSVファイルのID";
  var csvFile = DriveApp.getFileById(csv3).getBlob().getDataAsString("UTF-8");

  //CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)
  var csvData = Utilities.parseCsv(csvFile);

  //特定文字列がある場合は排除する
  var array = [];
  for(var i = 0; i<csvData.length; i++){
    //レコードを一個取り出す
    var rec = csvData[i];

    //indexOfで「22-x」があったら取り込まない
    var ret = rec.indexOf("22-x");

    //-1の場合は入っていないので取り込み対象
    if(ret == -1){
      //配列にpushする
      array.push(rec);
    }else{
      continue;
    }
  }

  //書込みをする
  var endrow = Number(sheetman.getLastRow()) + 1;
  var lastColumn = array[0].length;　　//カラムの数を取得する
  var lastRow = array.length;  　　　　//行の数を取得する
  sheetman.getRange(endrow,1,lastRow,lastColumn).setValues(array);

  //完了通知
  ui.alert("取り込み完了");

}

• 取り込みたいCSVファイルのIDを指定します。
• レコードを1行ずつ取り出し、indexOfで入ってるかどうかをチェック。入っていない場合は取り込み対象となる（-1が返ってくる）
• 取り出したレコードを別に用意した、arrayにpushする
• 最後にarrayをシートに書き込む

必要な列だけをスプレッドシートに書き込みたい

CSVデータ取り込みについて、必ずしも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()) {
    //UTF-8にてCSVをそのままで取り込む
    csvFile = files.next().getBlob().getDataAsString("UTF-8"); 
    
    //CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)
    csvData = Utilities.parseCsv(csvFile);

    //スプレッドシート書き込み用の配列
    var array = [];

    //ループで必要な列だけをtempArrayに取り込み
    for(var i = 0;i<csv.length;i++){
      //一時処理用の配列を用意
      var tempArray = [];

      //2,4,7列だけ配列に取り込む
      tempArray.push(csvData[i][1]);
      tempArray.push(csvData[i][3]);
      tempArray.push(csvData[i][6]);

      //書き込み用配列に押し込む
      array.push(tempArray);
    }
    
    //書込みをする
    var endrow = Number(sheetman.getLastRow()) + 1;
    var lastColumn = array[0].length;　　//カラムの数を取得する
    var lastRow = array.length;  　　　　//行の数を取得する

    //arrayを書き込む
    sheetman.getRange(endrow,1,lastRow,lastColumn).setValues(array);
    
  }
  
  ui.alert("CSVデータのインポートが完了しました。"); 
}

• 書き込み用にarrayという配列を用意する
• また、一時処理用のtempArrayという配列も用意する。用意する位置に注意。
• 配列は1列目は0から始まるので要注意。3ならば4列目となります。
• csvDataではなくarrayを書き込むようにする

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

• 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側コード（UTF8-BOM有り）

現在最新のExcelは、Shift-JISのCSVだけでなく「UTF8のBOM付き」であれば、ダブルクリックでも読み込めるようになっています。また、ウェブサービスの多くはUTF8でなければ読み込めないものが多いです（多くが海外のサービスでありShift-JIS未対応な所が多い）。

そこで、Google Apps ScriptからのエクスポートするCSVに対して.setDataFromStringを付けなければ、通常のUTF8となりますが、これにBOMを付けてエクスポートする場合はとても簡単。前述のコードに対して以下のようなコードの修正を加えてエクスポートするだけ。これでBOM付きUTF8のCSVになります。

//BOMを付ける
var utf8bom = "\ufeff" + csvData;
var blob = Utilities.newBlob("", "text/comma-separated-values", filename + ".csv").setDataFromString(utf8bom, "UTF8");

このほうが、ウェブサービスでもExcelでも余計な変換の手間を掛けずにCSVを扱えるようになるので、システム間連携する場合は、BOM付きUTF8にして扱うと良いでしょう。

図：きちんとUTF8のBOM付きになった

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ファイルのダウンロード');
}

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

HTML側コード

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

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

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

実行と結果

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

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

ポイント

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

関連リンク

• google apps scriptでspreadshhetにローカルCSVファイルをインポート(SJIS対応＆高速版)
• GASで文字コード指定してファイルを書き出す
• ExcelでUTF-8エンコーディングされたCSVファイルを開く方法
• Google Apps ScriptでShift_JIS変換したcsvをメール添付送信
• Getting CSV data into Google Spreadsheets Automatically
• How to delete/overwrite CSV file using google apps script?
• Google Apps Scriptでのファイル入出力
• 守破離でいこう!!- Google Apps Script ってすごいね
• Google Apps Script Documentation – まだ執拗にデータの取得・格納・挿入-2
• csv to array v2.1
• Enum MimeType