法人向けの有料Gmail等々サービスとして、G Suiteというものがあり、従業員一人年間8,160~36,000円で、会社名ドメインのメールアドレスが得られます。要は、みんな無料のGmailに慣れてるから会社でも使いたいけど、gmail.co.jpは格好悪いからお金を払う。これにはCalendarやDriveやChat、スプレッドシートなどのGoogle Appsがサービスで付いてくるので、連携させて業務効率化して元を取ってくれ、という小言なのです。
実際かなり強力で、業種によってはロートル一掃できる程の力があります。しかし工数削減の手柄は上司に持っていかれることは諦めましょう。
個人が無料で使用することももちろん可能で、簡単なECサイトやメール自動配信機能を構築できます。
私も初めて業務で使ってみて、便利さに感銘する一方、独特の癖やコツを覚えるのに苦労しました。これからはExcel + Access VBAのスキルだけでは業務効率化できないことは現実です。OpenOffice(LibreOffice) BasicはまともなIDEも用意できずに消えていく予感…。
今回は初心者の壁になるであろう、高速化とスプレッドシートの二次元配列について、解説と便利なクラスを紹介します。コピペで利用できます。
改良点がありましたら、容赦なくコメントで指摘していただいて構いません。ご相談・ご依頼も受け付けます。
GoogleスプレッドシートをGASで操作する際の課題点
スプレッドシートにスクリプトを組み込むには、メニューバーの「ツール→スクリプトエディタ」から。シンプルなIDEが開きます。色々試してみると分かりますが、実行速度はかなり遅く、配列化等の工夫が必須です。- 高速化の為には内容を二次元配列に格納→処理→貼り付けが必要
- しかし配列だとインデックスが行・列とズレて、分かりにくい
- データベースのように項目名で値を検索したい（項目の追加削除に対応）
- ついでにMoveNextとEOFを使って、Do Loopで回したい
Google Database(NoSQL) Appなんてのがあれば事足りそうですが…ありません。Excel VBAのRangeやFindと同等の関数をループで使うと、すぐに実行時間が数分になってしまいます。GASは5分を超えるスクリプトの実行ができません。そこで、スプレッドシートのデータを配列に格納する処理が必要になります。
スプレッドシートを二次元配列に格納
シートをgetRange().getValues()で二次元配列にする
シートの内容を配列に取り込むには、範囲を指定する方法もありますが、全体を取り込むのもシンプルなコードでできます。var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName); var values = sheet.getDataRange().getValues();
ここで、sheetIdはスプレッドシートのURL「https://docs.google.com/spreadsheets/d/xxxxxxxxxxx/edit#gid=0」の赤字x部分の文字列をコピーして利用します。ファイル毎に自動で付与されます。アカウント＆ファイル名で指定できないのはちょっと不便ですね。
シートはgetSheetByNames(sheetName)で取得できます。ひとつのシートしかない場合、getActiveSheet()も使えます。
シートのデータを二次元配列で返してくれるメソッドは、getRange().getValues()で、Excel VBAでのWorksheets(1).UsedRangeのように、データのある最大行・列を判別してくれます。
例えば、以下のようなテーブルを配列に格納すると…？
配列にはどのように格納された？
変数valuesには、以下のような二次元配列が格納されます。[[品番, 都市名, 日付], [xcom-12, Morioka, 3/4], [ybs-4000, Sendai, 2/5]]
values[0] = [品番, 都市名, 日付] //「0」列目に項目名が入る values[1][0] = xcom-12 //「1」列目から各値が入る
最大行・列（配列の最大インデックス値）を取得するには？
// [col1, col2, ...]のまとまりがいくつある=最大行 this.maxRow = values.length - 1; // [col1, col2, ...]の中に要素はいくつある=最大列 this.maxColumn = values[0].length - 1; //lengthは要素数なので、最大インデックス値を得るには-1。
データ処理
二次元配列の処理を分かりやすく！
ここからは、- 1行目の都市名は？
- 2行目の品番は？
- 都市名=Sendaiの行を検索
といった処理が行われると思いますが、都市名のインデックス値は？列挿入したら？と考えると鬱になりますね。また、検索で使われる array.indexOf は一次元配列の検索（この場合は１行ごと）しかできないので、Excel VBAでの Columns.Find は使えません。
列項目名をkeyとする連想配列を作成（項目名 -> 列データ）
そこで列単位で検索できるように、カラム型（列指向）データベース（と言うと格好いいね）を目指して配列を変換します。下記コードのようにゴリゴリ変換せずとも、行列変換のライブラリを利用しても良いと思います。// 列項目名->列毎データの連想配列を作成
this.columns = {};
for(var c = 0; c <= this.maxColumn; c++){
this.columns[values[0][c]] = [];
for(var r = 0; r <= this.maxRow; r++){
this.columns[values[0][c]].push(values[r][c]);
};
}
columnsには key:都市名、配列[Morioka, Sendai] といった連想配列が、項目の数だけ作成されます。
表操作用functions
columnsを検索するfunctionをprototypeとして追加します。その他、配列をデータベースっぽく操作するために、moveNext, moveFirst, EOFがあると便利です。
// 表操作用functions
SheetWrapper.prototype = {
getByItem : function(itemName){
return this.columns[itemName][this.currentRow];
},
searchItem : function(itemName, val){
return this.columns[itemName].indexOf(val);
},
moveNext : function(){this.currentRow++;},
moveFirst : function(){this.currentRow = 1;},
EOF : function(){
return this.currentRow > this.maxRow;
}
};
まとめ：ここまでのコードと使用例（コピペで使用可能）
上記のコードをSheetWrapperクラスとして、スプレッドシートごと格納すれば、二次元配列を意識せずにシート操作ができます。ここからシート書き込みやChat/Mail送信など機能を付け加えていく予定ですが、次回の記事にて！下記コードでは、クラスの宣言から、元スプレッドシートからマイスプレッドシートに無いデータを検索し、ログ出力しています。
function getNew() {
///// SheetWrapper Class /////
// sheetId:スプレッドシートのURLからIdを抽出する
// sheetName:スプレッドシートのシート名
// tableTopRow:表が始まる行を指定（1列目が項目、2列目がデータの場合、1を指定）
var SheetWrapper = function(sheetId, sheetName, tableTopRow){
// スプレッドシートのデータを二次元配列に格納。以降高速化の為、GoogleAppsScriptのAPI関数は使わない
var values = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName).getDataRange().getValues();
// 1行目が項目となっていない表の場合、shiftで余分な頭を削る
for(var r = 0; r < tableTopRow - 1; r++){
values.shift();
}
//最大インデックス値は(要素数-1)
this.maxRow = values.length - 1;
this.maxColumn = values[0].length - 1;
// 列項目名->列毎データの連想配列を作成
this.columns = {};
for(var c = 0; c <= this.maxColumn; c++){
this.columns[values[0][c]] = [];
for(var r = 0; r <= this.maxRow; r++){
this.columns[values[0][c]].push(values[r][c]);
};
}
//0行目は項目名なので、行数の初期値は1
this.currentRow = 1;
};
// 表操作用functions
SheetWrapper.prototype = {
getByItem : function(itemName){
return this.columns[itemName][this.currentRow];
},
searchItem : function(itemName, val){
return this.columns[itemName].indexOf(val);
},
moveNext : function(){this.currentRow++;},
moveFirst : function(){this.currentRow = 1;},
EOF : function(){
return this.currentRow > this.maxRow;
}
};
///// SheetWrapper Class /////
///// Main Process /////
// 元スプレッドシートの全データを配列として取得
var sourceSheet = new SheetWrapper('dummyid1', 'シート1', 1);
// マイスプレッドシートの全データを配列として取得
var mySheet = new SheetWrapper('dummyid2', 'シート1', 1);
// 元スプレッドシートからマイスプレッドシートに無い都市名の行を抽出
newData = [];
while(!sourceSheet.EOF()){
if(mySheet.searchItem('都市名', sourceSheet.getByItem('都市名')) < 0){
var newRow = [];
newRow.push(Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd'),
sourceSheet.getByItem('都市名'));
newData.push(newRow);
}
sourceSheet.moveNext();
}
Logger.log(newData);
}
0 件のコメント:
コメントを投稿