GASからSpreadsheetを難しいことを考えないで使う
最近GASを勉強しています。GAS、良いですね。サーバーレスで運用が必要でないお手軽さに、Spreadsheetを使ってステートフルなアプリも作れる。アイデア次第で色々できそうです。今回はそんなGASからSpreadsheetを手軽に扱うメソッド群を紹介します。
追記 2018/04/11
より改良したものを作りました！
こちらも参照してください。
GASをDBとして使いたい
GASからSpreadsheetをDB的に扱おうとした時、以下のような問題が存在します
- 取り出してすぐの状態では2次元配列で扱いにくい
- key-valueな形ではない
- 書き込み範囲を毎回指定しなければならず面倒
- sheetという独自の世界を理解しなければならない
- 他のORMなどを扱った経験が生きない
などなど、他にもIOが遅いなどの問題もありますが、ここでは以上について着目します。これらは、Spreadsheetを見栄えの良い出力先として扱う時には問題になりませんが、ただデータを溜め込み、使いたい時に取り出したいというDB的な扱いをした時に大変困ります。そこで、一定のルールに従って記入することで上記の問題を解決してお手軽にSpreadsheetをDB的に扱えるメソッド群を作成しました。
ルール
これから紹介するメソッド群を使うには以下の制約に従う必要があります。
一行目にはカラム名を定義
二行目以降には一行目のカラムに対応するデータ記入
必ずidカラムを含める
以上3つの制約に従ってください。これは一行目に記入されたカラム名に従ってSpreadsheetを扱いやすい形に変換しているためです。
メソッド紹介
以下を自分のGASに貼り付けて使ってください。GASにはライブラリ公開機能もありますが、まだそこまで触れていないので一旦この形で。
function getSpreadSheet(url){
var spreadsheet = SpreadsheetApp.openByUrl(url);
var sheets = spreadsheet.getSheets();
var sheet = sheets[0];
return sheet
}
function getJson(sheet){
var rowIndex = 1;
var colStartIndex = 1;
var rowNum = 1;
var keys = sheet.getSheetValues(rowIndex, colStartIndex, rowNum, sheet.getLastColumn())[0];
var data = sheet.getRange(rowIndex + 1, colStartIndex, sheet.getLastRow(), sheet.getLastColumn()).getValues();
var tmp = ['','',''];
var tmp1 = [];
var list = [];
data.forEach(function(elm,index){
var template = indexBy(keys);
var member = generate(elm,template);
if(member.id){
list[index] = member;
}
});
if(list.length == 0){
item = generateGenesisItem(keys);
list.push(item);
}
return list;
function generate(elm,obj){
var i = 0;
for(var key in obj){
obj[key] = elm[i];
i++;
}
return obj;
}
function generateGenesisItem(ary){
var obj = {};
for(var i = 0, len = ary.length; i < len; i++){
var key = ary[i];
obj[key] = '';
}
return obj;
}
function indexBy(ary){
var obj = {};
for(var i = 0, len = ary.length; i < len; i++){
var key = ary[i];
obj[key] = key;
}
return obj;
}
}
function getDb(url){
var sheet = getSpreadSheet(url);
return getJson(sheet)
}
function getLastId(db){
id = Math.max.apply(null,db.map(function(o){return o.id;}));
if(id == -Infinity){
id = 0;
}
return id;
}
function findById(db,id){
for(var i in db){
if(db[i].id == id){
return db[i];
}
}
return null;
}
function create(db){
var lastId = getLastId(db)
var date = Utilities.formatDate(new Date() , 'Asia/Tokyo' , 'yyyy/MM/dd HH:mm:ss');
newItemId = lastId + 1;
var item = {};
for (key in db[0]){
if(key === 'id'){
item[key] = newItemId;
continue;
}
if(key === 'datetime'){
item[key] = date;
continue;
}
item[key] = '';
}
return item
}
function save(db,url){
var table = [];
var key_list = [];
for (key in db[0]){
key_list.push(key);
}
table.push(key_list);
for(i in db){
var item_list = [];
for(key in db[i]){
item_list.push(db[i][key])
}
table.push(item_list)
}
var spreadsheet = SpreadsheetApp.openByUrl(url);
var sheets = spreadsheet.getSheets();
var sheet = sheets[0];
var rows = table.length;
var cols = table[0].length;
sheet.getRange(1,1,rows,cols).setValues(table);
}
function update(db,item){
if(db[0].id == ''){
db[0] = item;
return;
}
for(var i in db){
if(db[i].id == item.id){
db[i] = item;
return;
}
}
db.push(item);
}各メソッドの解説をします。
getDb(url)
- url
- Spreadsheetのurl
Spreadsheetのurlを引数として渡します。戻り値として対象のSpreadsheetをjsonの形に変換したものを返します。例えば以下のようなシートの場合
| id | text |
|---|---|
| 1 | foo |
| 2 | bar |
| 3 | baz |
これは
[
{
id:1,
text:foo
},
{
id:2,
text:bar
},
{
id:3,
text:baz
}
]となります。これで2次元配列に触れること無くspreadsheetを扱えます。このメソッドで取得したjson形式のデータは以降のメソッドで使用し、また、以降ではこれを”データベース”と表記します。
create(db)
- db
- getDb(url)で得られたデータベース
getDb(url)で得られたデータベースを引数として渡します。戻り値としてidが最大のものから一つインクリメントされた新しいレコードを返します。この時点ではまだデータベース本体に書き込みはされていません。Spreadsheetにはユニークキー制約なる機能は存在していない(自分が知らないだけかも)ので、このメソッドを通して新しいレコードを生成して、idの重複が起こらないようにしてください。
update(db,item)
- db
- getDb(url)で得られたデータベース
- item
- レコード
データベースとレコードを引数として渡します。レコードの内容がデータベースに反映されます。レコードがデータベース内に既に存在しているものならそれを上書きし、無ければ新たに挿入されます。参照渡しのため、引数に渡したdbに変更が適用されます。
save(db,url)
- db
- getDb(url)で得られたデータベース
- url
- Spreadsheetのurl
データベースとSpreadsheetのurlを引数として渡します。データベースの内容がSpreadsheetに反映されます。saveを行うまではSpreadsheet自体に変更は適用されません。なにか操作を行った時は、上のupdateを行ってデータベースに変更を適用した後にこのメソッドを使ってSpreadsheetに保存してください。
ちなみに、差分だけ変更を書き込むなどの賢い処理はしておらず、jsonの中身を2次元配列の形に変換してすべて書き込んでるので遅いです。今後の課題
findById(db,id)
- db
- getDb(url)で得られたデータベース
- id
- 検索対象のurl
データベースとidを引数として渡します。戻り値としてデータベースから同一のidを持つレコードを返します。idが範囲を超えたときの動作はまだ書いてません。扱う際はそのあたり改良したほうが良いです。
getLastId(db)
- db
- getDb(url)で得られたデータベース
データベースを引数として渡します。戻り値としてデータベースに存在する最大のidを返します。
getJson(sheet)
- sheet
- spreadsheet
spreadsheetを引数として渡します。戻り値としてspreadsheetをjsonの形式に変換したものを返します。
例
function main() {
var url = "SpreadsheetのURL"
var db = getDb(url);
var newRecord = create(db);
var record1 = findById(db,1);
newRecord.text = "foo";
record1.text = "bar";
update(db,newRecord);
update(db,record1);
save(db,url)
}なんだか随所にActiveRecordっぽさがありますが、こんな感じで使えます。2次元配列をそのまま扱ってSheetの作法に習って書き込むよりは楽になったかと思います。
まとめ
突貫で書いたものなので色々中途半端です。GASを使い込んでみて、もう少し発展させた後に更に追記します。