Googleスプレッドシートを使用していてぶつかる厄介な問題がCSVファイルの取り扱いに関する問題。主に、GoogleスプレッドシートからエクスポートしたCSVファイルをExcelで読み込めない問題や、逆に大量のCSVファイルをインポートを読み込みたい(しかもローカルPCから)となると、非常に融通の効かないアプリケーションだなと今でも思います。
これらの問題は、前者はGoogleスプレッドシートからエクスポートされるテキストがUTF-8でエンコードされたもので、取り分け日本やEXCELで普通に扱うShift-JISでエンコードされたCSVファイルではないから起き、後者はそもそも標準機能だと1個ずつしか読み込めないなどの仕様があるからです。後者の複数ファイルを読み込む件に関しては、Google Picker APIを使ったドライブから複数ファイルの読み込みや、HTML5を利用したローカルPCから複数読み込む手法を使えば行けます。
※一部のコードで不具合が出ていたので、修正するとともに、行列入替えインポートのコードを追加しました。
難易度:
目次
準備するもの
- 今回使用するスプレッドシート
- 今回使用するcsvファイル
- DriveAppクラスのgetDataAsString等
- UtilitiesクラスのnewBlobメソッドやsetDataFromStringメソッド
- UtilitiesクラスのparseCsvメソッド
CSVファイルをインポートするコード
普通にCSVファイルをインポートする
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
//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で指定しています。
- 指定フォルダ内ファイルだけを取り込む為、DriveApp.searchFilesを使ってファイルを取り出す場合には、DriveApp.searchFiles(“‘”+csvFolderkey+”‘ in parents”)を使います。ただこのコードだと指定フォルダにcsv以外のファイルがあっても取り込もうとしてしまうので注意!!
- CSVだけをきっちり取り出す場合には、MIMETYPE指定でDriveApp.getFolderById(フォルダID).getFilesByType(MimeType.CSV);を使うとフィルタして取り出してくれます。
CSVToArray関数について
かつて利用していたCSVToArray関数は以下のようなコード。テキストデータが配列になって返ってくる非常に便利なコードでした。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
//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関数などがありますね。しかし、大量にインポートする場合、普通にインポートを終えてから手動でこれらの作業を行うのは不便です。そこで、インポート時にそもそも行列入替えて貼り付けられたら、手間がなくなります。
今回のコードはそれを実現するコードです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
//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の全データを必要としてるケースは少ないと思います。既存のシートに用意された列に必要な列のデータだけを書き込みたいことはままあります。そういった場合、CSV取り込みで取得したデータをそのまま書き込むわけにはいかないので、配列操作で必要な列だけで新たな配列を用意し、それを書き込むようにします。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
//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側コード(通常版)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
//ダイアログ用のグローバル変数 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側コード(変換版)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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側コード
1 2 3 4 5 |
<div align="center"> <? 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なら自動で日付型と認識してくれるようになります。
関連リンク
関連記事
- 投稿タグ
- csv, google apps script, shift-jis, インポート, エクスポート
コメントを残す