盆栽の管理向上をめざして ８：盆栽カルテできました
はじめに
前回記事にて、データベースの骨子を作成しました。
そして、これらのデータベースを実装し始めています。
次はアウトプットについて考えたい
ここまでデータが揃ってくると、便利な機能が欲しくなってきます。
今考えている感じでは
・keyを選ぶと、その盆栽のカルテが表示される「盆栽カルテ」
・自分の盆栽、アクティビティに関する分析をダッシュボード表示
・今後作成する「盆栽Dock」との連携
・データベースの記入漏れをチェックし、入力を促すチェック機能
などです。
keyを選ぶと、その盆栽のカルテが表示される「盆栽カルテ」 が最初に作りたいです。
どんなものを何を作るか？
Googleスプレッドシートでモックアップを作ってみます。
検索keyを入力すると、その情報が一覧表示されます。
このようにイメージが固まってくると、なんだかすぐにできそうです。
どうやって作るか？
ダッシュボードを作ることはとてもわくわくします。
かっこよく作りたいと思うと同時にできる限り手を抜きたいとも思います。
今の時点でのできるできないではなく、チャレンジしてみたいと思う方法は
・Googleスプレッドシートを用いる
・Google Looker Studio（旧Google Data Portal）を用いる
・Googleドキュメントの「ページ分けなし」機能を用いる
などです。
まずはGoogleスプレッドシートを用いてみます
自分の腑に落ちる範囲の知識で作っていこうと思います。
すぐに思いつく方法は、ダッシュボードである表のシートで、検索のための情報を入力すると、裏方のシートに伝搬され、そこで表のシートに必要な材料を準備した後、その情報を使って、表のシートに情報を表示するという方法です。
今回私が行った方法は少しまわりくどいですし、いくつか危ない橋もわたっていますが、まずは「結果」にこだわり、この方法で進めてまいります。
図式にしてみました
まずは「盆栽カルテ」の構造についてご説明いたします
メイン画面の「盆栽カルテ」には検索keyを選択する欄があります。
ここは、登録されている盆栽のkeyが一覧されています。この中から調べたいkeyを選択します。
この「検索key」の選択からすべての処理が始まります。
「_盆栽管理の参照」、「_アクティビティの参照」、「_画像管理」の各セル内に「検索key」をコピーしている場所があり、各セルはこの情報を元に、決まった場所に情報を抽出します。
そのデータを「盆栽カルテ」の各項目にコピーすることで、ページが完成します。表示する内容は、樹種、樹形、鉢、置場、現在あるか？などの基本情報、直近１０のアクティビティ履歴、最新画像、直近１０枚の画像一覧などです。
当面は、これらの情報があれば、カルテとしての機能を果たします。将来的には、過去の今頃の画像なども見れるとよいと思っています。
次に「_盆栽管理の参照」について説明いたします
「_盆栽管理の参照」のシートでは、まず別ファイル「盆栽管理」の「アイテム」シートの内容を全て転記しています。
A1のセルに
=IMPORTRANGE("ファイル「盆栽管理」のID", "アイテム!A1:G")
と記入することで、A1:Gの範囲にデータが転記されます。
次に、J1のセルに
='盆栽カルテ'!$E$1
と記入することで、「盆栽カルテ」で選択された検索keyが入ります。
次にI4のセルに
=QUERY(A2:G,"select(A),(B),(C),(D),(E),(F),(G)where A="&J1&"")
と記入することで、セルJ1に入った検索keyと同じkeyの行がI4:O4に記入されます。もともと、盆栽の管理をするうえで、keyは固有にするルールでしたので、常に１行になるはずです。
結果が求まりました。この処理をデータの抽出と呼んでいます。
次に「_アクティビティの参照」について説明いたします
「_アクティビティの参照」のシートでは、まず別ファイル「アクティビティ管理」の「アクティビティ」シートの内容を全て転記しています。
A1のセルに
=IMPORTRANGE("ファイル「盆栽管理」のID", "アクティビティ!A1:D")
と記入することで、A1:Dの範囲にデータが転記されます。
次に、G1のセルに
='盆栽カルテ'!$E$1
と記入することで、「盆栽カルテ」で選択された検索keyが入ります。
次にF4のセルに
=QUERY(ARRAYFORMULA(TO_TEXT(A2:D)),"select Col1,Col2,Col3,Col4 where Col2='"&G1&"' Order by Col2 DESC")
と記入することで、A2:Dの範囲を対象として、G1のセルの内容を検索keyとして、検索keyとマッチするものすべてを、F4:Iの範囲で抽出します。とのとき、実施日を降順で並び替えてリストします。
さいごに「_画像管理」について説明いたします
「_画像管理」のシートでは、まず別ファイル「画像管理」の「画像リスト」シートと「LINEより」シートの内容を合算して全て転記しています。
セルA4に
=QUERY({IMPORTRANGE("ファイル「画像管理」のID","画像リスト!$A4:$E");IMPORTRANGE("ファイル「画像管理」のID","LINEより!$A2:$E")},"select Col1,Col2,Col3,Col4,Col5 where not Col1 is null")
と記入します。
{範囲;範囲}
でセルの範囲を立て積みします。
セルB1に
='盆栽カルテ'!$E$1
と記入することで、「盆栽カルテ」で選択された検索keyが入ります。
次に、セルG4に
=QUERY(ARRAYFORMULA(TO_TEXT(A4:E)),"select Col1,Col2,Col3,Col4,Col5 where Col4='"&B1&"' Order by Col2 DESC")
と記入することで、検索keyにマッチする画像のリストが得られます。
以上で下請けシートに検索keyを元にしたデータが集められたことになります。
あとは「盆栽カルテ」にデータをコピーするのみです
同じファイル内の別シートの内容は
='シート名'!セル番地
で取得できます。
基本情報から、どんどん値をコピーしていきます。
画像については、
=IMAGE("url")
で表示されると思っていました。
しかし、画像が表示されません。
mirさん
Googleスプレッドシート 画像にまつわるエトセトラ 2 （あくまでも Image）
他、いろいろな情報に当たりました。結果、分かったことは、
・Googleドライブのurlとスプレッドシートのimage関数に渡すurlは同じではない。ただし変換は可能。
・Googleドライブの共有設定を「リンクを知っている全員」にする。
必要がありました。
何度も何度も試行錯誤をした末、たまたま引っかかった方法で、とにかく前へ進むことにしました。
「_画像管理」シートのL4からのセルを下記のようにしています。
=REGEXREPLACE(I4,"https://drive.google.com/file/d/(.*)/view(.*)","https://drive.google.com/uc?id=$1")
正規表現を使ってセルI4のurlをimage関数で使えるものに変換しています。
さらに、表示側ではクリックすると、画像を大きく表示できるように、
=HYPERLINK('_画像管理'!L4,IMAGE('_画像管理'!L4))
としました。
どこまでも底なし沼です。
これで盆栽カルテの完成です。
ここまで作って動かしてみると、なかなかの出来栄えで、もっともっとデータを入力して蓄積させたいと思うようになりました。
日々のデータの入力は、スマホを使い、画像についてはLINE Botを使い、アクティビティについては直接Googleスプレッドシートに記入しています。
さいごに
一気に駆け抜けました。内部は闇コピペのオンパレードで、ソフトウェアエンジニアとしては恥ずかしすぎますが、趣味の領域であるからこそできる雑なつくり。
これこそが、アマチュアものづくり愛好家として大切な姿勢であると私は思います。
本当はQUERY関数はじっくりと取り組めばとても面白いと思うのですが、少し試していたとき、いじってもいじっても全くエラーが取れず、ネットで調べたサンプルで試すとうまく行くといったことがありました。
昔から何度かSQLの学習をチャレンジしていましたが、例題が成績管理や在庫管理であまりモチベーションが上がらない上に、原因不明のエラーで途中でサンプルが真似できなくなることを何度も経験しています。
このような壁に数回当たると、試すことをしなくなってしまいました。
出来ない理由は必ずあります。
またいつか気持ちに余裕が生まれたら取り組んでみようと思います。
今はこのデータベースをとにかく使いこみたい。という気持ちでいっぱいです。
とはいえ、Googleスプレッドシートのいろいろな面を勉強することができました。
データベースソフトを使わなくても、これぐらいのことはできるんだということも分かり、時代の進化にただただ感動するばかりです。
最後までお読みいただきありがとうございました。
#盆栽カルテ #Googleスプレッドシート #IMAGE #QUERY #IMPORTRANGE #データベース #ダッシュボード #無料 #盆栽管理 #画像管理 #LINEBot
この記事が気に入ったらサポートをしてみませんか？