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

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

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

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

前回の記事はこちら。

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サービスを使用してスプレッドシートにアクセスできる…つまり

スプレッドシートの各行のデータからレコードを作成する
レコードを配列化して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のオブジェクト構造について、モデル周りについては

Appオブジェクト
Modelオブジェクトのオブジェクト（モデル名をキーにして取り出せる）
Recordオブジェクト

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

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

App Makerで新しいページとフォームウィジェットを配置する方法

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

どうぞお楽しみに！

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

Googleが提供する社内アプリケーションを簡単に構築できるプラットフォームApp Maker。このシリーズでは、スプレッドシートをデータベースとして簡単なアプリケーションを作る方法をお伝えします。