目次
Google Apps Script と Spread Sheet
Google Apps Script を使って SpreadSheetにアクセスします。
Google Apps Script は Google Drive 上で、無料で使える開発・実行環境です。
ここでは、Google Spread Sheet にただアクセスするだけでなく、より実践的なコードも紹介します。
具体的には
selectAll: すべてのデータを取得し、連想配列に入れます。
insert: データを挿入します。
update: データを更新します。
これらのメソッドの実装例を紹介していきます。
Spread Sheet のデータを取得する基本の方法
Spread Sheet の指定が必要です。
どのシートからデータを取得するかIDを取得する必要があります。
これには、Spread Sheet の URL をみてみます。
https://docs.google.com/spreadsheets/d/{ここがIDです}/
Google Drive の中で対象のIDといったら、通常ここの文字列のことです。
では、まずこのSpread Sheet を普通に開き、中のデータを取得してみます。
コード.gs に以下のように記述します。
|
1 2 3 |
var wholeSheet = SpreadsheetApp.openById(sheetId); var targetSheet = wholeSheet.getSheets()[0] var data = targetSheet.getDataRange().getValues() |
一行目で全体を取得し、
二行目で一枚目のシートを指定して取得し、
三行目でデータがある範囲を取得して、その中身を取得します。
このコードを例えば以下のように記述します。
|
1 2 3 4 5 6 7 |
function test(){ let sheetId = "{IDを入れます}" let wholeSheet = SpreadsheetApp.openById(sheetId); let targetSheet = wholeSheet.getSheets()[0] let data = targetSheet.getDataRange().getValues() Logger.log(data) } |
そしてメニューバーの「関数を選択」のドロップボックスから、「testSheet」を選択し、実行ボタン(再生ボタンの形です)を押します。
その後、Ctrl + Enter を押すと、Logger.log に渡されたデータを見る事が出来ます。
通常この動作で関数ごとのデバックを行えるのですが、今回のコードは初回実行時に、許可を出す必要があります。
このアプリ内で、「SpreadSheetApp」のライブラリを使う許可を、自分のアカウントで付与しましょう。
また、参照しようとしているSpreadSheetが、自分のアカウントで閲覧可能である必要もあります。
Class を使ってリファクタリングする
可読性の高いコードに変更しながら、実践的なコードの記述をしていきましょう。
selectAll、insert、updateを実装していきます。
まずは sheetIdを受け取ってインスタンスを返すようにコードを変更します。
外部可変変数を出来るだけ動作本体から分離します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
// クラスの作成 class MySheetEditor { constructor(sheetId){ // プロパティの設定 this.sheetId = sheetId // 同時に取得 this.wholeSheet = SpreadsheetApp.openById(sheetId); this.targetSheet = this.wholeSheet.getSheets()[0] } } function test(){ let sheetId = "{IDを入れます}" let = editor = new MySheetEditor(sheetId) } |
ECMAScript 2015(ES6) からjavascriptではclassが導入されています。
Google Apps Script ではES6が動作しますので、classを用いて、実装の分離を行いつつ、selectAll等を実装していきます。
データ構造を定義する
SelectAll、Insert、Updateを実装するために、ここで、データ構造の定義が必要になります。
さらに先に進む前に、DBとして扱うSpreadSheetのデータ構造を決定しましょう。
今回は、SpreadSheetからデータ構造を読み込むタイプで作成します。
別途定義情報をGAS内部に定義する方法もあり、一長一短です。
| id | name | label | level | note |
| 2 | cris | クリス | 13 | 町人 |
| 1 | anna | アンナ | 22 | 村娘 |
ここでポイントは二点です。
- 一行目は各カラムの名称をつける行にする
- 一カラム目はid(通し番号。上に行くほど新しい)
すべてのデータベースはこの構造を持つことを前提した上で、コードを書いていきます。
上に行くほど新しいのは、このシートが書き込み可能なシートであり、新しいデータを上に挿入していくからです。
注意点として、この定義が壊れてしまうと、プログラムが壊れてしまいます。
SelectAll を実装する
SellectAll の設計をしていきます。
上記のように記述されたSpreadSheet を SelectAll したとき、
[ {カラム名称 => 内容, カラム名2 => 内容} ,…(次のデータ)]
の連想配列にして取得するコードです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
// クラスの作成 class MySheetEditor { constructor(sheetId){ // プロパティの設定 this.sheetId = sheetId // 同時に取得 this.wholeSheet = SpreadsheetApp.openById(sheetId); this.targetSheet = this.wholeSheet.getSheets()[0] } selectAll() { let data = this.targetSheet.getDataRange().getValues() let titles = data.shift() let ret = [] for( let i = 0; i < data.length; i++ ){ let row = {} for( let v = 0; v < titles.length; v++ ){ row[titles[v]] = data[i][v] } ret.push( row ) } return ret } } function test(){ sheetId = "{IDを入れます}" let = editor = new MySheetEditor(sheetId) let data = editor.selectAll() Logger.log(data) } |
Insert を実装する
insertの実装も行っていきましょう。
新しいデータを、どんどん追加できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
insert (registerData){ // キーと最終IDを取得します。 let numCols = this.targetSheet.getDataRange().getNumColumns() let data = this.targetSheet.getRange(1,1,2,numCols).getValues() let titles = data.shift() // 1カラム目はidと決めています。 //もし既にデータがあれば、+1してlastIdとします。 let lastId = 1 if( data[0][0] != "" ){ lastId = data[0][0] + 1 } // 受け付けたデータから、インサートデータを作成します。 let insertData = [ lastId ] for( let i = 1; i < titles.length; i++ ){ if( typeof registerData[titles[i]] != "undefined" ){ insertData.push( registerData[titles[i]] ) }else{ insertData.push("") } } // insert を行います。 this.targetSheet.insertRowBefore(2).getRange(2, 1, 1, numCols).setValues([insertData]); } |
このinsertを用いるときは、以下のようになります。
|
1 2 3 4 5 6 7 8 9 10 |
function test(){ sheetId = "{IDを入れます}" let = editor = new MySheetEditor(sheetId) editor.insert( { name: "cris", level: 12 } ) } |
データに抜けがあっても、keyにしたがって、新しいIDを付与してデータをインサートします。
Update を実装する
Updateは該当のIDを検索し、keyにしたがって値を変更します。
その都合上、どうしても全体を取得せざるを得ないと思っています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
update( registerData ){ // idが設定されていないデータは受け付けません if( typeof registerData["id"] == "undefined" ){ return } let id = registerData.id // すべてを取得し、該当IDのindexを検索する必要があります // idの重複は禁則事項としています。考慮しません // 手動で更新しない限りありえないためです let rows = this.selectAll() let targetIndex = 0 let targetRow = null for( let i = 0; i < rows.length ; i++ ){ if( rows[i].id == id ){ targetRow = rows[i] // SpreadSheetの連番は上から「1」で始まり、 // 一番上はkey行なので、+2します targetIndex = i + 2 break } } // idが見つからなければ、ここで終わります if( targetIndex == 0 ){ return } // 該当データの該当キーを受付データで上書きしながら、updateDataを作成します let updateData = [] let keys = Object.keys(targetRow) for( let i = 0; i < keys.length; i++ ){ let key = keys[i] if( typeof registerData[key] != "undefined" ){ updateData.push( registerData[key] ) }else{ updateData.push( targetRow[key] ) } } // indexを指定して上書きします this.targetSheet.getRange(targetIndex, 1, 1, keys.length).setValues([updateData]) } |
使ってみましょう！
|
1 2 3 4 5 6 7 8 9 10 11 |
function test(){ sheetId = "{IDを入れる}" let = editor = new MySheetEditor(sheetId) editor.update( { id: 2, name: "taro", level: 12 } ) } |
insertで既に入っているデータを、keyにしたがって上書きします。
より堅牢なプログラミング方法について
実装方法にはいくつもの選択肢があります。
上記した、「DBのSpreadSheetの定義内容をGAS内部に記述する」方法です。
例えば今回の実装では、誰かがSpreadSheetのkey行をちょっと変更すると、プログラムが壊れます。
正確なInsertやUpdateが出来なくなるからです。
それを防ぐために、GAS内部に定義するほうが、より堅牢な設計だといえます。
今回はひとつのメソッドにすべての要素に入れるために、1メソッドで簡潔する実装です。
参考にしていただき、リファクタリングしてみましょう！
