はじめに
google apps scriptの情報はそもそも少ないと思っていますが、その中でも
ローカルのCSVファイルを読み込んで、シートに書き込む事例はかなり少なかったのでまとめてみました。
（googleドライブ上のCSVファイルをインポートする例はまぁまぁあるんですけどね）
各種事例の中でネックだったのは、SJISのCSVがNGだった事と、大きなサイズのCSVファイルだと遅すぎて
実用性の面で難ありだったことです。
ですので、これらを解決しました。
CSVファイルの文字コードについて
巷にあるgoogle apps scriptでCSVインポートする例は、CSVファイルがsjisだと文字化けするorフリーズしちゃいました。
読み込んだテキストをsplitする前に、文字コード変換したり色々考えましたが、
よくよくAPIのドキュメントとにらめっこすると、
Class Blob - Google Apps Script - Google Developers :
https://developers.google.com/apps-script/reference/base/blob#getDataAsString()
あれ、getDataAsString() charset引数受け付けてるやん。
ということで、"Shift_JIS" 渡すだけでスンナリ通りました。(引数無しだとutf-8)
setValueによるセル書き込みが超遅いことについて
getRange() → setValue()で１セルずつ書き込むと、メチャクチャ遅いです。
大きなCSVファイルだと、1ファイルをインポートするのに10分以上かかることもザラでした。
でも、setValues()でまとめて書き込むと劇的に速くなります。
しかし、setValues()には色々クセがあって結構扱いがメンドウなんです。
あらかじめシートのサイズをデータの行列サイズよりも広げておかないと落ちるとか、
書き込みセル数が251?以上だと落ちるとか。 詳しくは参考ページへ。
ということで、そういった制限を考慮しながら１行ずつsetValues()する事で高速書き込み。
ソース抜粋
全ソースはgithub https://github.com/prog-mitsu/importCsv に上げてありますので、
ご興味ある方は持って行って下さい。
var importCsv = {
/**
* ファイルから読み込んだバイナリをテキストデータにして返す
* @param {Object} fileBlob fileBlobオブジェクト
* @returns {String} stringCode 文字コード sjisなら "Shift_JIS"
*/
fileToTextData: function(fileBlob, stringCode){
"use strict";
var readData = null;
// 文字コード指定読み込みgetDataAsString
readData = fileBlob.getDataAsString(stringCode);
if(!readData){
Logger.log("ERROR:データがありません");
return null;
}
return readData;
},
/**
* 対象シートに対象カンマ区切りテキストを１セルずつ書き込む
* @param {Object} spreadsheetObj 対象になるスプレッドシートオブジェクト
* @param {Object} sheetObj 対象になるシートオブジェクト
* @param {String} readData 書き込む文字列
* @returns {Number} startRow 書き込む最初の行数
* @returns {Number} startColumn 書き込む最初の列数
*
* setValueで１セルずつ書き込むと超遅いので、setValuesで書き込みます
*/
writeSheet: function(spreadsheetObj, sheetObj, readData, startRow, startColumn){
"use strict";
var csvData = readData.split('\n'); // 行単位分割
var csvSplit = csvData[0].split(','); // 1行目をカンマ単位で分割
var maxColumn = csvSplit.length; // 列数
var maxRow = csvData.length; // 行数
var i, j, iLength, jLength;
var logStr = "";
var lineWriteArray = [];
var srcRowArray = null;
var range = null;
var PROGRESS_BODER = 250; // 進捗表示用
sheetObj.clear(); // シートの中身を全クリア
// シートのサイズが小さい場合問題があるので、拡張しておきます
// setValuesする場合、書き込み先が小さいと落ちる
utilities.expansionSheetSize(sheetObj, maxColumn + (startColumn-1), maxRow + (startRow-1));
iLength = csvData.length;
for (i = 0; i < iLength; i++) {
// 1行文字列を区切り分割して配列化
srcRowArray = utilities.csvSplit(csvData[i]);
// 区切った配列を列データとして収集
jLength = srcRowArray.length;
for (j = 0; j < jLength; j++) {
lineWriteArray.push(srcRowArray[j]);
}
// setValuesのセル数限界が251?らしいので、超えない程度で(1行単位で)書き込む
// [lineWriteArray] → setValuesは二次元配列受付のため
range = sheetObj.getRange(startRow+i, startColumn, 1, lineWriteArray.length);
range.setValues( [lineWriteArray] );
lineWriteArray = [];
// 進捗表示
if( 0 === (i%PROGRESS_BODER) ){
logStr = " progress " + i + " / " + iLength;
Logger.log(logStr);
spreadsheetObj.toast(logStr);
}
}
return true;
},
/**
* インポートするファイル選択用ダイアログUI表示
* @param {spreadSheetObj} 対象のスプレッドシート
* @param {String} titleStr ダイアログのタイトル表示文字列
* @param {String} descriptionStr ダイアログの説明文字列
* @param {String} butonStr ダイアログのボタン表示文字列
*
* @example showUiDialog("csvファイルインポート", "インポートするファイルを選んで下さい", "インポート実行");
*
*/ showUiDialog: function (spreadsheetObj, titleStr, descriptionStr, buttonStr) {
"use strict";
var app = UiApp.createApplication().setTitle(titleStr);
var form = app.createFormPanel().setId('frm').setEncoding('multipart/form-data');
var formContent = app.createVerticalPanel();
var label1 = app.createLabel(" ");
var label2 = app.createLabel(descriptionStr);
var file = app.createFileUpload().setName('csvFileName');
var button = app.createSubmitButton(buttonStr);
form.add(formContent);
formContent.add(label1);
formContent.add(label2);
formContent.add(file);
formContent.add(button);
app.add(form);
spreadsheetObj.show(app);
return true;
},
/**
* インポート実行
* @param {spreadSheetObj} 対象のスプレッドシート
* @param {Object} fileBlob fileBlobオブジェクト
* @param {String} stringCode 文字コード sjisなら "Shift_JIS"
* @param {String} インポート e対象になるシート名
* @param {Number} startRow 書き込む最初の行数(1 origin)
* @param {Number} startColumn 書き込む最初の列数(1 origin)
*/ run: function (spreadSheetObj, fileBlob, stringCode, sheetNameStr, startRow, startColumn) {"use strict";
var readTextData = null;
if( !fileBlob ){
var FILE_NAME = "csv4.txt";
var forceOnMemory = false; // シート書き込み強制OFFか？
readTextData = this.getTextFile(FILE_NAME);
}
else{
// バイナリ → テキスト変換
readTextData = this.fileToTextData(fileBlob, stringCode);
if (!readTextData)
return false;
}
// 対象シートを開く
var sheetObj = utilities.openSheet(spreadSheetObj, sheetNameStr);
if (!sheetObj)
return false;
// 対象シートに対象カンマ区切りテキストを１セルずつ書き込む
return this.writeSheet(spreadSheetObj, sheetObj, readTextData, startRow, startColumn);
}
};
最後に
ローカルファイル選択アップロード
SJIS対応
高速書き込み
が実現できたので、滅EXCEL、google spreadsheet推進への野望が一歩前進しました。
参考ページ
Google Apps Script ってすごいね :
http://moblogger.r-stone.net/blogs/9016404448327222924/posts/1382923398397652155
守破離でいこう!! :
http://ishikawa.r-stone.net/
[GoogleAppsScript]setValuesではまったところ : minoawのブログ : http://blog.livedoor.jp/minoaw/archives/1523932.html
[GAS][スプレッドシート]処理速度を向上するには : 逆引きGoogle Apps Script : http://www.bmoo.net/archives/2012/04/313959.html