【Click_NoCode】訳あってSteinというスプレッドシートAPIを使ってみた
（なぜこれを書いたか？）
最近アプリ作るときにAdaloでなくClickという国産NoCodeアプリを使う機会が多くなりました。ただ困ったことにAdaloほど高機能でないので出来ないことが結構あります。そういうときには外部APIに接続して足りない機能を補うようにしています。
例えばClickからスプレッドシートを読み込む場合、スプレッドシートAPIが必要になってきます。今まではSheetDBを使ってやってきましたが、無料で使える範囲がアプリ2個までなので新しいアプリを作ろうとすると既存のアプリを消さなければなりません（それも嫌です）
そこで今回はアプリ数無制限という太っ腹のSteinというスプレッドシートAPIを使うことにしました。ただ、ClickからSteinに接続するとうまく動作しない部分がいくつかあり、以前は使うのをあきらめていたのですが、いろいろいじっていたら解決策を見つけることが出来たので記録にとどめておくことにしました。
多分、日本でStein使っている人なんて聞いたこと無いんで自分だけの備忘録として書くつもりです。
１．スプレッドシートAPIのSteinとは？
スプレッドシートAPIとはNoCodeアプリとスプレッドシートを接続するときに使うAPIです。（ClickもAdaloもスプレッドシートを直接読むことは出来ないのでスプレッドシートAPIという機能を使ってデータの読み込みを行っています）
何故今回Stein（シュタイン）を使ったかというと作れるアプリの数に制限が無いんです。いつも使っているSheetDBというAPIは2個までしか作れません。その他のスプレッドシートAPIも似たり寄ったりで作成アプリ無制限というのはほとんどないのが現状です。
無料なのでもちろん制限はあります。スプレッドシートの行数は200行まで、1か月のアクセス数は5000回となっています。趣味でプロトタイプのアプリを作っている私からすると十分な内容です。
２．SteinとClickは相性があまりよくない（？）
私がSteinを知ったのは昨年（2021年）の事で、こんな太っ腹なAPIがあるならずっとこれを使っていこうと思い、使い始めたのですが、いろいろとうまく行かないことが多く途中で投げ出してしまいました。
一番困ったのはDELETEの機能がうまく使えないことです。最初はAdaloでアプリを作り、Steinと連携していたのですが、DELETEで削除すると、その行が削除されず、空白行として残ってしまい格好悪いんです。
それではClickではどうかと思い、設定をしてみるとClickではそもそも動作しませんでした。これはStein独自の仕様が影響していて、データをDELETEする場合、他のAPIではJSONでデータを送らないんですが、SteinはJSONでどの行のどのデータを削除するか指定しているのです。(下記の-dの部分)
（削除参考例）curlコマンドの場合
curl "https://api.steinhq.com/v1/storages/5cc158079ec99a2f484dcb40/Sheet1"
-X DELETE
-H "Content-Type: application/json"
-d '{"condition": {"name": "Taro Yamada"}}'
いろいろいじっていたら解決出来たのでやり方は後述します。まずは簡単なデータベースをスプレッドシートで作りそれをClickから削除する手順を説明していきます。
３．データベースの読み込み設定
以下のような簡単なデータベースをスプレッドシートに作りました。これをClickから削除するやり方を説明します。
これを読み込むためにClickのデータベースの設定画面で「外部データベースを追加」をクリックします。（下図）
（General API設定画面）
APIのURLに接続するための設定画面が出てきます。
「テーブル名」と「接続先URL」を入力します。
①「テーブル名」・・・「Stein 削除アプリ」にしました。（任意）
②「接続先URL」:
SteinのAPIのURLは以下のような構造になっています。
「https://api.steinhq.com/v1/storages/{API-Key}/{使用するシート名}」
「API-Key」:
SteinのWebサイトにアクセスしDashboardでスプレッドシートのURLを入力するとAPI-Keyが発行されます。
（注意点）
APIのURLにはAPI-Keyの後ろに使用するシート名（タブ）を必ず追加して下さい。（最初は「シート１」になっていますが、日本語にするとトラブルの可能性があるのでsheet1等に変更しておきましょう）Steinを初めて使うとこの部分は忘れやすいので注意が必要です。
この2カ所を記入したら画面右下の「プレビュー＆エラーチェック」ボタンを押すとデータを読み込み始めます。うまく行ったら「保存ボタン」を押してClickと接続させます。
（Click側の読み込み設定）
スプレッドシートがClickの外部データベースとして読み込まれたらカスタムリストにデータとして表示させましょう。
ホーム画面にカスタムリストを配置し、「Stein削除アプリ」と紐づけします。titleとsubtitleにはスプレッドシートのidとfoodというタイトルを設定します。
（プレビューで表示したところ）
スプレッドシートから読み込んだデータはClickで以下のように表示されます。
４．データベースの削除設定
ここからが本題です。読み込んだスプレッドシートのデータをClickで削除できるように設定します。
現在はホーム画面にカスタムリストが配置されていますが、削除したい行をクリックするとそのデータが別画面（Edit）に転送され、そこでボタンを押すとその行データが削除されるという流れにします。（下図）
（削除ボタンの設定）
Editという編集画面にボタンを設け、これを押すと指定した行のデータが消えるようにします。外部データを削除する設定なので通常のClickFlowではなく、カスタムClickFlowを選択します。（下図）
（カスタムClickFlowの設定）
カスタムClickFlowを選択するとGeneralAPIの設定画面が表示されます。ここでDELETEの設定を行います。
「APIの名称」と「API接続URL」の２つを入力して、API接続の種類はDELETEを選択します。
（大きな問題発生！）
ここで困ったことが発生します。SteinのDELETE設定ではJSONでどの列のどのデータを削除するか指定する仕様なのですが、DELETEを選んでしまうと「INPUTデータ」部分が灰色になってしまい、JSONが書き込めないのです。（下図）
仕方ないのでDELETE以外のAPI接続を選んで一時的に「INPUTデータ」の部分が記入可能な状態にします。（下図）
「INPUTデータ」部分にJSONを書き込んだらAPI接続の種類をDELETEに切り替えます。「INPUTデータ」の部分はこの時点でまた灰色になってしまうのですがこれで機能しますので大丈夫です。
５．データを削除してみる
一応設定はこれで出来たのでどんな具合にデータが削除されるのか見ていきます。最初にホーム画面にデータベースの一覧が出てきます。
この中で「c カレーパン」の行を削除しましょう。このカレーパンの行をクリックするとEditという編集画面に遷移します。
カレーパンのデータがEditという編集画面に転送されました。ここでDELETEボタンを押してデータを削除します。
（またまた問題発生！）
「c カレーパン」のデータは確かに削除はされたのですが、行自体は残っており空白行となってしまいました。
スプレッドシートを見るとやはり空白行が残っている状態です。（下図）
（空白行の対策）
空白行はどうしても消せなかったのでカスタムフィルターの設定でリストには表示しないようにしました。下図のような設定をしてidが空白でないデータだけを表示するようにしました。
（今度はうまく行くでしょうか？）
また削除してみます。今度は「d メロンパン」の行を削除してみます。（メロンパンの行をクリックします）
Edit編集画面に「d メロンパン」のデータが転送されてきました。DELETEボタンを押します。
「d メロンパン」のデータは消え、空白行も表示されないようになりました。DELETEに関する問題は一応設定を工夫することで何とか解決することが出来ました。
６．その他のStein不具合
SteinはDELETEの他にもう一つ不具合があります。データ更新（PUT/PATCH）をするときにある列のデータをすべて一括変更するような場合、最後の行が更新されないのです。
（例）
例えば人名データベースがあって現在10人のデータがあるとします。各人がTRUE/FALSEのデータを持っていて、最初は全員がFALSEの状態で、一括更新（PUT）でTRUEに変更するとします。この操作をすると9人目までがTRUEに変更され、10人目の人は相変わらずFALSEのままなのです。
ただ、これもTRUE,FALSEが混在するようなデータの場合は問題無いんです。全部がTRUE（またはFALSE）でそれが反転するようなデータ更新の場合にだけ最後の行のデータが更新されないという現象になります。
（対応）
SteinはUpdateの場合はPATCHでなくPUTを使うようになっています。（PATCHを設定するとエラーになる）JSONの記入部分でオプションで更新するデータの数の上限を設定することが出来ます。（下図のlimitの部分）
ここを少し多めの数を指定してやると問題なく全部のデータが更新されます。（理由はよく分からない。）
curlコマンドの設定例で示します。
①FALSE to TRUE
$ curl "https://api.steinhq.com/v1/storages/632cc8017bccea08c1199a72/sheet1"
-X PUT
-H "Content-Type: application/json"
-d '{"condition":{"status":"FALSE"},"set":{"status":"TRUE"},"limit":50}'
いろいろあって疲れますが、Steinは分かり易く、使いやすいのでDELETEとPUT/PATCHの問題点を把握しておけば応用範囲が広いAPIと思います。
(過去の投稿)