Guides

Googleスプレッドシートでのアプリの作成

Googleスプレッドシートのデータにクエリーを実行します

Retoolを使用すると、Googleスプレッドシートを他のAPIに接続することを含め、Googleスプレッドシートのデータでアプリを容易に作成できるようになります。Retoolは、Googleスプレッドシートからのデータの読み取りおよび書き込みをサポートしており、ユーザーはSQL経由のクエリーJSONで、Googleスプレッドシートと他のデータ・ソースを容易に組み合わせることができます。

クラウドの設定

  1. Retoolダッシュボードにログインし、上部のメニュー・バーでResourcesをクリックします。
1600
  1. Create newをクリックします。
1596
  1. リソース・タイプのリストからGoogle Sheetsを選択します。
1596
  1. エディターでリソースにクエリーを実行するときに使用する、リソースの名前を入力します(例: David’s Google Sheets)。次に、Retoolに付与するアクセス権をRead and writeにするのか、Read onlyにするのかを決定する必要があります。Read and writeをお勧めします。シートの値を読み取るほかに、アプリでは、行の更新、行の追加、シートのコピー、新しいスプレッドシートの作成なども実行できるようになります。
1596
  1. Connect to Google Sheetsをクリックして、Googleの承認フローを開始します。
1596
  1. 承認フローが完了すると、接続が成功したことを示すメッセージが表示されます。そのメッセージ内のCreate a new app nowリンクをクリックして、ブランクのRetoolアプリを作成し、Googleスプレッドシートでの作成を開始します。
1596

オンプレミスの設定

オンプレミス設定プロセスを使用してGoogleスプレッドシートを設定するには、Googleスプレッドシートの統合の手順( English )を参照してください。

Googleスプレッドシートでの最初のアプリケーションの作成

新製品のベータ・テスターになるためにサインアップしたユーザーに招待状を送信するRetoolアプリを作成するとしましょう。ユーザーの連絡先情報は、新規リソースとして設定したGoogleドライブのGoogleスプレッドシートに格納されています。アプリの作成を開始するには、スプレッドシートの形式が正しいことを確認しましょう。

スプレッドシートの要件

Retoolでは、データへのクエリーの実行元であるGoogleスプレッドシートのシートを指定できます。そのシートの先頭行には列名が含まれていて、その下の行にリストされたデータにマップされている必要があります。以下に、正しい形式のシートの例を示します。

1596

上記の例のように、Retoolでクエリーを実行するシートには、次の項目以外に追加のデータを含めないでください。

  1. 列名(先頭行)
  2. データ行

列名の間に空の列を含めないでください。列名を容易に参照するには、次の名前付け規則をお勧めします。

  • 英数字を使用する
  • 小文字を使用する
  • 単語の区切りにスペースではなく、アンダースコアを使用する

シートに追加のデータ(例: 右にサマリー統計)を含める場合、A1表記オプションを使用できます。その場合、もう少し設定作業が必要です。このチュートリアル用に、必要なデータを参照する新規シートを作成して上記のように新規シートの形式を設定することをお勧めします。

Retoolでは、デフォルトで、A:Zの最大26個の列にクエリーを実行します。データに26個を超える列がある場合、A1表記を使用する必要があります。(例えば、28列ある場合、A1の表記はA:ABになります。)

テーブル・コンポーネントへのデータの読み込み

シートからのデータの読み取りに使用する新規クエリーをアプリで作成します。

1596

ResourceドロップダウンからGoogleスプレッドシートのリソースを選択します。この例では、「David’s Google Sheets」です。次に、必要なスプレッドシートを選択します。ここでは、「Beta signups」です。(これをブランクのままにすると、スプレッドシート内の最初のシートを使用します。)

1596

📘

IDによるスプレッドシートの選択

ドロップダウン・メニューからスプレッドシート名を選択する方法の他に、URLで指定するようにスプレッドシートIDを入力することもできます。例えば、GoogleスプレッドシートのURLがhttps://docs.google.com/spreadsheets/d/1L2h7HMrHjcpU_-vpT8pGVKNlfBaxh8FUEN-UHXt--mU/edit#gid=0である場合、IDは1L2h7HMrHjcpU_-vpT8pGVKNlfBaxh8FUEN-UHXt--mUになります。

Previewをクリックして、Retoolでデータを表示します。

1596

保存するには、Save & Runをクリックします。次に、テーブル・コンポーネントをキャンバスにドラッグします。

1596

デフォルトで、Retoolのテーブル・コンポーネントには、作成した最初のクエリー(例: query1)の.dataが自動的に移入されます。Retoolで、{{query1.data}}はJSONオブジェクトの配列で、次のような構造の行ごとに1つです。

{
  "name":"Ines",
  "phone_number":"303-555-0132",
  "interested_in_beta_testing":"FALSE",
  "beta_invite_sent":"FALSE",
}

読み取りクエリーのオプション

Retoolには、Googleスプレッドシートでの読み取りクエリーにいくつかのオプションが用意されています。

Limitでは、シートから返される行(列名の行を除く)に対する整数の制限を設定します。

1596

以下のサンプル・シートの場合、

1596

制限が2に設定された読み取りクエリーは、InesとOliverの行を返し、Priya、Asia、Isaacの行を除外します。次のJSONが返されます。

[
  {
    "name":"Ines",
    "phone_number":"303-555-0132",
    "interested_in_beta_testing":"FALSE",
    "beta_invite_sent":"FALSE",
  },
  {
    "name":"Oliver",
    "phone_number":"907-555-0146",
    "interested_in_beta_testing":"TRUE",
    "beta_invite_sent":"FALSE",
  }
]

Offsetでは、シートから返される行(列名の行を除く)に対する整数のオフセットを設定します。

1596

以下のサンプル・シートの場合、

1596

オフセットが1に設定された読み取りクエリーは、Oliver、Priya、Asia、Isaacの行を返し、Inesの行を除外します。次のJSONが返されます。

[
  {
    "name":"Oliver",
    "phone_number":"907-555-0146",
    "interested_in_beta_testing":"TRUE",
    "beta_invite_sent":"FALSE",
  },
  {
    "name":"Priya",
    "phone_number":"312-555-0188",
    "interested_in_beta_testing":"FALSE",
    "beta_invite_sent":"FALSE",
  },
  {
    "name":"Asia",
    "phone_number":"818-555-0173",
    "interested_in_beta_testing":"TRUE",
    "beta_invite_sent":"FALSE",
  },
  {
    "name":"Isaac",
    "phone_number":"415-555-1045",
    "interested_in_beta_testing":"TRUE",
    "beta_invite_sent":"FALSE",
  },
]

A1表記

シートの特定のセクションを選択する必要がある場合、A1表記をRetoolで使用できます。このサンプル・スプレッドシートでは、行のデータは列Bから始まり、右側にサマリー統計があります。

1596

このシートからデータを選択するには、A1表記を記述して、B1:E6のセルの該当のデータのみを取得するか、将来追加されるB:Eの行を取得します。A1表記を使用するには、use A1 notationをクリックします。

1596

A1表記(ここでは、B:E)を追加すると、データはJSONオブジェクトの配列として返されます。

1596

データへの行の追加

アプリのユーザーが新規ベータ・テスターをテーブルに追加して、データ・ソースであるスプレッドシートにこれを伝播できるようにする必要があるとしましょう。

最初に、データをスプレッドシートに追加するappend_rowという名前の新規クエリーを作成します。Action TypeドロップダウンからAppend data to a spreadsheetを選択します。次に、table1をクリックして、右側にあるInspectorタブでOn row addドロップダウンを新規append_rowクエリーになるように変更します。

1596

Values to appendフィールドに何も表示されていないことを確認してください。最初に、行の追加を許可するようにtable1コンポーネントを設定してから、append_rowクエリーの接続に戻ります。

行を追加するためのテーブルの設定

最初に、キャンバスのtable1をクリックし、テーブルのInspectorプロパティで各列を1つずつ編集可能にします。

1596

アプリのユーザーはtable1の+ボタンをクリックして、新しい行を追加します。

1596

これをテストして、Retoolでこの新しく追加された行がどのように格納されるか確認しましょう。新規ベータ・テスター(この場合、Hui-lan)の情報を入力してこれをアプリに追加できます。

1596

これを実行すると、table1のnewRowフィールドには新しい行のJSONオブジェクトが移入されます。これをエディターの左側のパネルで確認できます。

1596 1596

Googleスプレッドシートへの行の追加

最後のステップとして、append_rowクエリーとtable1コンポーネントに入力したデータを接続する必要があります。追加アクションでは、行ごとのJSONオブジェクトの配列の追加をサポートしている[ {{ table1.newRow }} ]を使用します。

1596

行のJSONオブジェクトの配列が機能します。例えば、次のリテラル・オブジェクトの配列です。

[
  {
    "name":"Thara",
    "phone_number":"212-555-4435",
    "interested_in_beta_testing":"TRUE",
    "beta_invite_sent":"FALSE",
  },
  {
    "name":"Vanessa",
    "phone_number":"503-555-9742",
    "interested_in_beta_testing":"FALSE",
    "beta_invite_sent":"FALSE",
  }
]

必要となるのは、JSONオブジェクトのキーがスプレッドシートの最初の行(列名)と一致するということのみです。

新しい行が行データの末尾に追加され、Googleで自動的に検出されます。最後に、追加クエリーを作成するときは常に、読み取りクエリーをon success triggerとしてトリガーすることが必要になります。これにより、テーブルのデータが再読み込みされ、追加された新しい行が反映されます。

1596

ここで、テーブルでSave changesボタンを押します。これによって、append_rowクエリーがトリガーされて実行され、新しい行がスプレッドシートに追加されます。

1596

行の更新

次に、「Send invite text」ボタンを設定します。Actionボタン列をテーブルに追加する必要があります。table1コンポーネントをクリックして選択してから、InspectorプロパティでNew actionをクリックします。

1596

「Actions」という名前の新しい列がテーブルに表示され、各行にボタンが設定されます。リストでAction 1をクリックして、Action button textを「Send invite text」に変更することで、ボタンの名前を変更できます。

1599

Twilio APIのようなものを使用して、このボタンが押されたときにテキスト・メッセージを送信することが必要になります。ただし、この例では、テキスト・メッセージを正常に送信したようにスプレッドシートを更新することに焦点を当てます。

次に、新しい列invite_text_sentをスプレッドシートに追加することが必要になります。

1596

別の新規クエリーupdate_invite_sentを作成し、Update a spreadsheetアクションを選択します。Retoolでは更新する行を認識することが必要です。そのためには、Retoolが更新する1行を検出できるように、一意の列および対応する値を指定する必要があります。

1600

一意の列の候補として適しているのはIDなどの主キーになりますが、この場合は、ベータ・テスターごとに一意のphone_number列を使用できます。「Send invite text」ボタンがクリックされた行の電話番号を照合する必要があります。Filter by列をphone_numberに設定し、値を{{ table1.selectedRow.data.phone_number }}に設定します。次に新しい列の値をTRUEにして、update valueフィールドに{ "invite_text_sent": "TRUE" }を追加します。

1600

次に、新規update_invitation_sentクエリーをテーブルのアクション・ボタンにつなげる必要があります。テーブルを選択して、InspectorタブでActionボタンをクリックし、Action queryを新規update_invitation_sentクエリーに設定します。

1600

最後のステップとして、再度、読み取りクエリーをこの更新クエリー用にon success triggerとして設定します。これにより、テーブル・データが常にGoogleスプレッドシート内のデータと一致するようになります。

1600

これで終了です。「Send Invite Text」ボタンをクリックすると、「invite_text_column」がFALSEからTRUEに更新されるようになりました。

値の書式設定

デフォルトで、RetoolはGoogleスプレッドシートから書式設定済み文字列としてデータをフェッチします。例えば、スプレッドシートのセルにUSDに書式設定された値1.23が含まれている場合、GoogleスプレッドシートのRetoolのクエリーは"$1.23"を返します。

ベータ招待サンプル・スプレッドシートを確認しましょう(num_usersという列を追加したので、数値を操作できます)。

1276

クエリーの返された結果の上にカーソルを置いてチェックすると、書式設定済みの文字列が表示されます。

1464

"10"と10に注意します

書式設定された文字列は、次の場合に正しく動作します。

  • Googleスプレッドシートで表示されるときとまったく同じようにテーブル内にデータを表示する場合。Retoolのテーブルのソート機能または書式設定機能を使用する必要はありません。
  • RetoolでサポートされていないがGoogleスプレッドシートでサポートされている列の書式設定を使用する場合。書式設定タイプに関するご要望がある場合は、[email protected]までご連絡ください。

Retoolの列の書式設定を適用しようとすると、書式設定された文字列は正しく動作しません。例えば、Googleスプレッドシートの書式設定されたブール型は"TRUE"または"FALSE"で、両方の値がtrueに評価されると、常にチェックボックスがチェックされます。

1386

さらに、列のソートは文字列ベースになり、スプレッドシート内では数値で、Retoolでは書式設定された文字列である列をソートすると、想定したものと異なります。

1600

表示されているとおり、Retoolのテーブルの機能のうち多数の機能は追加設定せずに、書式設定された文字列で正しく機能しないので、最近、Googleスプレッドシートの書式設定されていない型の値で読み取る機能が作成されました。

書式設定されていない型の値

現在、Googleスプレッドシートにブール型や数値など、複数のデータ型がある場合、クエリー・エディターの読み取りクエリーのData Typesセクションで「Read data as typed values」を選択すると、それらの型の値で読み取るようにすることができます。デフォルトでは、元の動作(書式設定された文字列)に設定されています。

1600

しかし、書式設定されていない型の値を読み取るように切り替えることができます。

1600

以下に、書式設定されていない型の値にクエリーを変更した例を示します。Retoolでは、自動的に列の書式が検出され、他の書式を正しく適用することができます。また、ソートは期待どおりに動作します。

1777

書式設定されていない値でのテーブルの更新

このチュートリアルの最初の部分で、Retoolテーブル内の変更によるスプレッドシートのデータの更新方法を説明しました。「Filter by」の値が{{table1.selectedRow.data.id}}などのテーブルから移入されていて、書式設定されていない型の値を含むクエリーからそのテーブルが読み取る場合、Googleスプレッドシートの行を正しく照合できるように、更新クエリーで同じように区別する必要があります。

デフォルトでは、「Compare formatted spreadsheet cell values」がチェックされますが、以下のスクリーンショットのように、このチェックを解除して、書式設定されていない型の値を使用して比較します。

1600

行の一括更新

新機能: 主キーでのスプレッドシートの一括更新

以前はテーブルからのスプレッドシートの一括更新にRetoolの多数のカスタムJSが必要だったため、このためのアクションが作成されました。

最初に、Googleスプレッドシートのデータ・セットに主キー列があることを確認します。行ごとに一意の値を含む列(一意のIDなど)が適しています。

サンプル・スプレッドシートでは、主キーとしてphone_numberを使用します。

1186

データ・セットに主キーがない場合、行を識別するための行番号を追加できます。

1400

では、Googleスプレッドシートからテーブルにデータを読み取るクエリーを設定したとしましょう。

1600

変更が必要な列が編集可能であることを確認します。Retoolのテーブル・コンポーネントの詳細は、Tableの利用を参照してください。

1304

ここで、一括更新クエリーを作成して、テーブルを編集したときにトリガーされるように設定する必要があります。

  1. アクション・リストからBulk update a spreadsheet by primary keyを選択します。
  2. 主キー(例: phone_number)を追加します。
  3. テーブルに行った編集を取得するように、{{ your_table_name.recordUpdates }}をArray of rows to updateとして追加します。
  4. 右側のパネルのBulk update actionセクションで、テーブルに対する一括更新アクションとして、このクエリーを選択します。
1600

最後に、忘れずに、読み取りクエリーを再実行して、テーブルに更新した値を移入します。

1600

これで、一度に複数の行を編集できるようになりました。Save Changesをクリックして、Googleスプレッドシートでこれらの更新を確認します。

1777

JSループによる一括更新

前述の主キーでスプレッドシートを一括更新するアクションが動作しない場合は、[email protected]までご連絡ください。次の一連の手順を使用して、テーブルの行への変更ごとにループさせて、変更ごとに1行の更新クエリーをトリガーすることもできます。

すべての列を編集可能にした後のテーブルを、以下に示します。

1600

セルを編集すると、Retoolでは更新した行がtable1.recordUpdates配列に追加されます。また、左側のパネルでクリックして、recordUpdatesの値をいつでも表示することができます。Priyaのinterested_in_beta_testingステータスを「FALSE」から「TRUE」に変更してみてください。

1600

recordUpdatesの1つのエントリーにマップする行の更新クエリーを作成することができます。先ほどと同様に、Retoolに更新する行を指示する必要があります。phone_numberは行ごとに一意の列値であるため、再度これにフィルターを使用します。更新値を{{ table1.recordUpdates[i] }}に設定します。

i変数は、デフォルトで0として評価され、これをtable1.recordUpdates[0].phone_numberでフィルターされる行への1回の更新にします。table1.recordUpdates[0]である値がGoogleスプレッドシートに書き込まれ、実際に行全体がRetoolで更新した値に置き換わります。

次に、recordUpdates配列の各項目にupdate_rowクエリーをトリガーする新規Run JS Codeクエリーを作成します。名前をbulk_updateに変更します。

ここで、.trigger() JSメソッドのadditionalScope objectオプションでインスタンスごとにiを定義します。このi値はデフォルト値を上書きします。項目が最後の更新である場合、読み取りクエリーquery1をトリガーしてテーブルのすべての変更を再読み込みして表示します。

1600

前述のコードは次のとおりです。

const updates =  table1.recordUpdates.map((d,i) => {
  update_row.trigger({
    additionalScope: {"i":i},
    onSuccess: function(data) {
      if (i == table1.recordUpdates.length-1) {
        query1.trigger();
      }
    }
  }); 
});
    
return Promise.all(updates);

最後のステップです。Inspectorタブで、テーブル設定の「Table Edit Queries」セクションの「Bulk update action」設定としてこのJSクエリーを使用するようにテーブルを設定します。これにより、ユーザーが「Save Changes」ボタンをクリックすると、テーブルでbulk_updatesクエリーがトリガーされます。

1600

Updated over 3 years ago