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 関連リンク

準備するもの

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側コード

実行と結果

ポイント

関連リンク

関連記事

最近の投稿

人気の投稿とページ

おすすめ