App Makerでスプレッドシートからデータを取得するクエリスクリプトを丁寧に解説

records

photo credit: Jonas Hellebuyck Vinyl memories via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

App Makerでスプレッドシートをデータベースとしたアプリケーションの作り方をお伝えしています。

前回の記事はこちら。

App Makerでクエリスクリプトでスプレッドシートのデータを取得する方法
App Makerでスプレッドシートをデータベースとしたアプリケーションの作り方をシリーズでお伝えしております。今回はApp Makerでクエリスクリプトでスプレッドシートのデータを取得する方法です。

モデルを作成して、クエリスクリプトを使ってスプレッドシートのデータを取得する方法をお伝えしました。

これで、スプレッドシートのデータをApp Makerに引っ張ってくることはできるようになったのですが、クエリスクリプト…全然解説していなかったので、何をしているかわかりません…汗

ということで、今回はApp Makerでスプレッドシートからデータを取得するクエリスクリプトを丁寧に解説していきますよ。

では、行ってみましょう!

スポンサーリンク

前回のおさらい

では、おさらいからです。

まず、データベースの大元になるスプレッドシートはこちらです。

スプレッドシートの従業員データ

それで、このスプレッドシートをベースにしたフィールド構成+行番号を表すIndexというフィールドを追加した、モデル(App Makerのデータベース)「EmployeeData」を作成しました。

フィールド構成はコチラです。

フィールド データ型 説明
Index Number 行番号
Name String 従業員名
Email String メールアドレス
HireData Date 入社日
Active Boolean 在職かどうか

それで、このモデルのデータソースにデータを取得する方法として、以下のクエリスクリプトを仕込んだのでした。

var ss = SpreadsheetApp.openById('***** スプレッドシート「従業員データ」のID *****');
var sheet = ss.getSheets()[0];
var values = sheet.getDataRange().getValues();

var records = [];

for(var i = 1; i < values.length; i++){
  var record = app.models.EmployeeData.newRecord();
  record.Index = i;
  record.Name = values[i][0];
  record.Email = values[i][1];
  record.HireDate = new Date(values[i][2]);
  record.Active = values[i][3];
  records.push(record);
}

return records;

GASでスプレッドシートを触ったことがある方なら、前半はわかりますけど、後半はお初な感じの部分も多いですかね…?

ということで、今回はこのスクリプトについて解説していきます。

クエリスクリプトとは

まず、そもそもクエリスクリプトとは何かについてお伝えしておきましょう。

今回のシリーズでは、スプレッドシートをデータベースとしたいため、App Makerのモデルの種類としてCalculatedモデルを採用しました。

Calculatedモデルを使用している場合、サーバーサイドスクリプトを実行することでデータソースにレコードの集合を作成することができます。

そして、そのレコードの集合は、配列として生成してリターンする必要があります。

つまり、クエリスクリプトとは、レコードの集合を配列として生成してデータソースへ渡すサーバーサイドスクリプトのことをいいます。

サーバーサイドスクリプトはつまりGASなので、Spreadsheetサービスを使用してスプレッドシートにアクセスできる…つまり

  1. スプレッドシートの各行のデータからレコードを作成する
  2. レコードを配列化してreturnする

この処理を実現できれば、スプレッドシートのデータをApp Makerのデータソースとして引っ張ってこれるというわけです。

スプレッドシートからデータを取得するクエリスクリプト

では、それを踏まえて前述のクエリスクリプトを解説していきましょう。

スプレッドシートのデータを二次元配列に格納する

まず、1~3行目はおなじみの部分ですね。

var ss = SpreadsheetApp.openById('***** スプレッドシート「従業員データ」のID *****');
var sheet = ss.getSheets()[0];
var values = sheet.getDataRange().getValues();

openByIdメソッドで指定したIDのスプレッドシートを開いて、getSheetsメソッド→getDataRangeメソッド→getValuesメソッドにより、該当のスプレッドシートの1番目のシート上にある全てのデータを二次元配列で取得します。

その二次元配列がvaluesという変数に格納されますね。

レコードの配列をreturn

次に、変数recordsに注目しましょう。

var records = [];

for(var i = 1; i < values.length; i++){
  var record = ~;
    
  //recordについての色々な処理
  
  records.push(record);
}

return records;

空の配列を代入していますので、recordsは配列ということがわかります。

for文のループがありますが、スプレッドシートでいうとデータ行の各行について何らかの処理をします。

そのループの最後に、その配列recordsにあれこれしたrecordをプッシュしています。

これが、いわゆるデータソースのレコードのひとつひとつっぽいですね。

なので、スプレッドシートのデータ行の分だけレコードを作成して、配列recordsに追加されるわけですね。

そして、最終的に配列recordsをreturnしています。

つまり、この配列recordsがクエリスクリプトがリターンすべきレコードの配列ということです。

モデルに新しいレコードを追加する

では、forループ内の以下の部分に注目しましょう。

var record = app.models.EmployeeData.newRecord();

あんまり見たことない感じですね…

App Makerのアプリケーションを表すAppオブジェクト

まず「app」ですが、App Makerのアプリケーションを表すAppクラスのオブジェクトです。

app

このオブジェクトから、App Makerのアプリケーションにアクセスできます。

サーバーサイドスクリプトからアクセスできる…ということが画期的ですね。

Modelオブジェクトを取得する

次に、medelsというプロパティがあります。

このプロパティはApp Makerアプリケーションで定義されているすべてのモデルを含むオブジェクトを返します。

app.models

これで返されるオブジェクトは、モデル名をキーとすることで、個々のモデルを表すModelオブジェクトを取り出すことができます。

なので、ドット記法でモデル名「EmployeeData」を指定しているんですね。

モデルに新しいレコードを作成する

そして、最後そのModelオブジェクトに対して、newRecordメソッドです。

Modelオブジェクト.newRecord()

モデルに新しいレコードを作成して返します。

レコードはRecordオブジェクトとして操作をすることができます。

Recordオブジェクトの各フィールドを設定する

では、以下の部分を見ていきましょう。

record.Index = i;
record.Name = values[i][0];
record.Email = values[i][1];
record.HireDate = new Date(values[i][2]);
record.Active = values[i][3];

newRecordメソッドで作成したRecordオブジェクトの操作をしていますね。

もうなんとなくお分かりと思いますが、Recordオブジェクトに続いてピリオド、そしてフィールド名で、そのレコードのフィールドにアクセスできます。

Recordオブジェクト.フィールド名

これで、スプレッドシートの各列の値を、レコードのフィールドにセットしていっているわけです。

まとめ

これで以上ですね。

App Makerでスプレッドシートからデータを取得するクエリスクリプトの解説をしてきました。

App Makerのオブジェクト構造について、モデル周りについては

  1. Appオブジェクト
  2. Modelオブジェクトのオブジェクト(モデル名をキーにして取り出せる)
  3. Recordオブジェクト

という形になっているのがわかりましたね。

次回ですが、フォームウィジェットを使ってUIで操作できるようにしていきたいと思います。

App Makerで新しいページとフォームウィジェットを配置する方法
App Makerでスプレッドシートをデータベースにしたアプリの作り方についてシリーズでお伝えしています。今回はApp Makerで新しいページとフォームウィジェットを配置する方法をお伝えします。

どうぞお楽しみに!

連載目次:App Makerでスプレッドシートをデータベースとして使う

Googleが提供する社内アプリケーションを簡単に構築できるプラットフォームApp Maker。このシリーズでは、スプレッドシートをデータベースとして簡単なアプリケーションを作る方法をお伝えします。
  1. App Makerでスプレッドシートをベースにモデルを作成する方法
  2. App Makerでクエリスクリプトでスプレッドシートのデータを取得する方法
  3. App Makerでスプレッドシートからデータを取得するクエリスクリプトを丁寧に解説
  4. App Makerで新しいページとフォームウィジェットを配置する方法
  5. App Makerでテーブル行をクリックしたらフォームを表示するイベントを設定する方法
  6. App Makerでボタンウィジェットを設置してクリックで動作させる方法
  7. App Makerでボタンクリックで独自のスクリプトを動作させる方法
  8. App Makerでクライアントスクリプトからサーバースクリプトを呼び出す方法
  9. App Makerのボタンクリックでスプレッドシートにアクセスする方法
  10. App MakerでフォームウィジェットでLabelウィジェットを入力可能なウィジェットに差し替える
  11. App Makerでフォーム上のデータを取得してサーバースクリプトに渡す方法
  12. App Makerでフォームからデータベースとしてのスプレッドシートを更新する方法
  13. App Makerで確認用のポップアップダイアログを作成する方法
  14. App Makerでポップアップダイアログの表示の変更とバインディング式について
  15. App Makerでポップアップのボタンクリックによるイベントを設定する
  16. App Makerでポップアップダイアログのボタンで動作するレコード削除のスクリプト
  17. App Makerでデータ追加用のポップアップを作成してその表示の動作確認をする
  18. App Makerでデータベースにレコードを追加するスクリプトを作成する
タイトルとURLをコピーしました