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

準備するもの

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 = "";
while (files.hasNext()) {
//Shift_JISにてCSVをそのままで取り込む
csvFile = csvFile + files.next().getBlob().getDataAsString("Shift_JIS"); 
}
//CSVToArrayにて成形してシートに記述(書き込み先シートはtestという名称)
var csvData = Utilities.parseCsv(csvFile);
var sheetman = sheet.getSheetByName('書き込み先');
//CSVToArrayで配列となったデータをsetValuesで一気に書き込み
for (var i = 0; i < csvData.length; i++) {
sheetman.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
}
ui.alert("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 = "";

while (files.hasNext()) {

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

csvFile = csvFile + files.next().getBlob().getDataAsString("Shift_JIS");

}

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

var csvData = Utilities.parseCsv(csvFile);

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

//CSVToArrayで配列となったデータをsetValuesで一気に書き込み

for (var i = 0; i < csvData.length; i++) {

sheetman.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));

}

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

}

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

ちなみにかつて利用していた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 );

}

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ファイルが入手出来る

ポイント

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秒程度でした。結構早いですね。
書き込み自体は１行ずつ書き込みをしている。csvFileには全CSVデータを連結する形で一気に流し込んでいます。
エクスポートする場合なのですが、日付のデータは日付型で出力してしまうと、相手側で取り込むのがやっかいです。予め、スプレッドシート上で書式なしテキストにしておきましょう。Excelなら自動で日付型と認識してくれるようになります。

MENU

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

準備するもの

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

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

GAS側コード（通常版）

GAS側コード（変換版）

HTML側コード

ポイント

関連リンク

関連記事

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

最近の投稿

人気の投稿とページ

おすすめ

MENU

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

準備するもの

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

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

GAS側コード（通常版）

GAS側コード（変換版）

HTML側コード

ポイント

関連リンク

関連記事

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

最近の投稿

人気の投稿とページ

おすすめ

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