GoogleAppsScript

google apps scriptでspreadsheetにローカルCSVファイルをインポート(SJIS対応&高速版)

More than 3 years have passed since last update.


はじめに

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()で1セルずつ書き込むと、メチャクチャ遅いです。

大きなCSVファイルだと、1ファイルをインポートするのに10分以上かかることもザラでした。

でも、setValues()でまとめて書き込むと劇的に速くなります。

しかし、setValues()には色々クセがあって結構扱いがメンドウなんです。

あらかじめシートのサイズをデータの行列サイズよりも広げておかないと落ちるとか、

書き込みセル数が251?以上だと落ちるとか。 詳しくは参考ページへ。

ということで、そういった制限を考慮しながら1行ずつsetValues()する事で高速書き込み。


ソース抜粋

全ソースはgithub https://github.com/prog-mitsu/importCsv に上げてありますので、

ご興味ある方は持って行って下さい。


importCsv.gs

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

/**
* 対象シートに対象カンマ区切りテキストを1セルずつ書き込む
* @param {Object} spreadsheetObj 対象になるスプレッドシートオブジェクト
* @param {Object} sheetObj 対象になるシートオブジェクト
* @param {String} readData 書き込む文字列
* @returns {Number} startRow 書き込む最初の行数
* @returns {Number} startColumn 書き込む最初の列数
*
* setValueで1セルずつ書き込むと超遅いので、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;

// 対象シートに対象カンマ区切りテキストを1セルずつ書き込む
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