Power AppsとGoogle SheetのQuery関数を使ったアプリのすゝめ

# はじめに
今回は、Power Appsのキャンバスアプリで、データソースを「Googleスプレッドシート」にしたときに、「意外とコレ、いいじゃん！」と思った使い方をご紹介します。

そもそもGoogleスプレッドシートを採用した場合、Google APIが動いているので読み込み時のエラーが散見する印象を受けますが、その
デメリットを理解したうえで、「あ！こんなことできるんだ！」ということに貢献出来たら幸いです。

# GoogleスプレッドシートのQuery関数
> Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。
```
QUERY(データ, クエリ, [見出し])
```
https://support.google.com/docs/answer/3093343?hl=ja

指定した配列に対して、 __クエリ言語__ が書けます。
特定の列に対して条件を適用したり、ソートの形式を変えたり、配列を整形するうえで大変強力な機能です。

また __IMPORTRANGE関数__ という他のスプレッドシートからリアルタイムにデータを取得する関数と組み合わせることで、
__マスタとなる元データ__ と __使いたいデータソース__ を分離して使うことが出来ます。

https://support.google.com/docs/answer/3093340?hl=ja

## どんなシーンで活用するのか
私は人事の仕事をしています。
仕事柄、その日に応じて在籍していたり、退職していたり、いろいろなステータスが変動するデータを扱っています。

__日付に応じた動的なデータ__ を参照するうえで、データソースの加工が都度発生する職務です。

例えば
 - 昨日時点の社員名簿
 - 本日時点の社員名簿

これらは一致しないケースがあります。日々変化するものです。
しかしその都度データ作成をするということは非常に手間、また人事情報活用のシーンは度々発生します。

__人事システムを毎回開き、データを参照する__ こういったことも手間。
機微な情報の集大成なので開きっぱなしもNGです。

__セキュリティーポリシーを理解したうえで、Googleスプレッドシートをマスタとして活用し、
クエリ文を適用することで動的に変化する人事情報マスタを作り、活用する__

入社日と退職日だけでもクエリ式を反映し、社員名簿が自動生成されれば、
__Power Appsを中心にデータを活用した業務改善__ ができます。

:::note warn
注意
個人情報は大変管理が重要なものであり、漏洩はあってはなりません。
クラウドにアップされたデータのセキュリティは疑いようのないものの、閲覧権限の付与などは簡単にできてしまいます。
十分に注意して取り扱いましょう。
:::

前置きが長くなりましたが、上記の人事情報のケースで情報を紹介させてください。

***
# 人事情報を本日時点で抽出するQuery関数
下記にデータを思い切って挙げました。
個人情報はダミーデータです。

株式会社ユーザーローカル様の個人情報テストデータジェネレーターを使用しています。
https://testdata.userlocal.jp/

入社日、退職日は`RANDBETWEEN関数`でランダムに作成しています。

https://docs.google.com/spreadsheets/d/1YMIBfMuI-HpqE2Op5dBh9HM6Crq37iauzFuwJoN3iWg/edit?usp=sharing

上記のデータの中で
 - Sheet「mst」 - マスタデータ、入社日・退職日関係なく記載された全てのデータ
 - Sheet「list」 - QUERY関数で抽出した本日時点のデータ

```function:QUERY関数
=QUERY(mst!$A:$I,"SELECT * WHERE I IS NULL OR I >= date '" & TEXT(TODAY(),"yyyy-MM-dd") & "'")
```
関数を活用して退職日が本日時点より前のデータだけにフィルタする内容です。
QUERY関数によって、日付による更新`少なくとも退職者は計算で除外される`データソースが作成できました。

このデータをPower Appsのデータソースにします。

# Power Appsを繋げて活用するケース
私はTeamsに下記の様なアプリを導入し、活用しています。

 - 従業員名簿アプリ - __繰り返しますがデータはダミーです__
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/1990b2d5-f128-38b0-b084-b1dd1bf76cb7.png)

メールアドレスは調べずとも補完機能で簡単に出すことができますが、
電話番号は電話帳のセッティングをしたり、他の名簿をみなければならないケースもあります。

:::note warn
Office365、Googleのアカウントに所属情報なども設定できると思いますが、そこの部分は扱えない想定です。
:::

HTMLテキスト機能を使い、
 - メール送信
 - 電話をかける
といった活用も可能、Androidのスマホであれば、Windowsとリンクできるのでさらに便利です。

 - 詳細情報
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/99321bd6-7c09-6f9e-7967-09336a8b8c61.png)

ギャラリーメニューから選択して個人の詳細情報を呼び出しています。
このアプリの生成は、Power Appsのデータソースの指定 + データの出力定義を多少修正した
30分もかからないほどの時間で作れる簡易アプリです。

でも実はこれが

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/7c3e1ae5-14a9-5224-1f87-4360bed7f052.png)

- 和暦と西暦を変換
- 社員番号をリサーチ
と __小技__ が利いたりします。こういうの __手間を駆逐していくPower__ ですね。

Power Automateと連携すれば、この画面に表示されているアカウントに
- Teamsのチャットの投稿
- 提携メールの送信
といった __過去のメール検索してコピペしてやっている__ 作業を駆逐できるんですよねー。
仕組化することで上記の情報で置換する仕組みも作ればミスも無くなりますので、コスパ◎な機能だと思ってます！

***

# このアプリの課題
さて、お気づきの方が多いと思いますが、このアプリのデータソースは`QUERY関数で出力された配列`になります。
データソースの`編集`は一切できません。

**閲覧とPower Automateを利用した作業にとどまります。**

そしてデータソースが日付によって変わるので・・・、QUERY関数で制御していない`__PowerAppsId__列`を
どう対応させていくかが課題になります。

# データの増加は問題なし
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/79f33f01-827c-59e9-cab1-4afad9179ea9.png)
外部要因によってデータが増えたときは、どうなるでしょうか？
これはPower Appsの機能によって、`起動時`や`Reflash関数実行時`に、**データの再読み込みの過程**で、自動的に「\_\_PowerAppsId\_\_」は追加記入されるため問題になりません。

 - Google SpreadSheet - マスタ側
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/d111cb80-5276-e3cf-e665-629c879f5cfa.png)

 - Power Apps
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/5b4eb474-1c6b-f4e4-e4f3-9f85415073dd.png)

# 課題はデータの減少
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/d34618fc-d389-0e26-e7e2-7c2081bcbc18.png)
`QUERY関数`を利用して、**現在の日付より前に退職した人のデータは表示しない**といったQUERY式を設定していると、この方法は成り立たなくなります。
図の通り、レコードが空のIDが残る。

 - Power Apps側は・・・
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/b8fc28f1-b4d0-515c-b4ed-ad461e8b1aa3.png)
```
空のレコードがギャラリーに出てしまっています。
```
残念な感じがしますね。

データソースの行数にフィットさせるには、\_\_PowerAppsId\_\_列が、
**レコードの数と合致していることがMUST**になります。

いちいち手でやるのも面倒です。
**Google Apps Script**で解決してみましょう！

***
# Google Apps Scriptを活用する

```javascript:sample.gs
// column_id　列番号
const column_id = 10;
function sample() {
  // 1. 扱うシートを定義する
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("list");
  const range = sheet.getDataRange();
  const mst = range.getValues();

  // 2. 空配列を宣言
  let ids = [];

  // 3. __PowerAppsId__の値を配列でpushする
  for(let i=1; i < mst.length; i++){
    if(mst[i][0] != ''){
      ids.push([mst[i][column_id - 1]])
    }
  }

  // 4. データをクリアして代入する
  sheet.getRange(2, column_id, mst.length).clearContent();
  sheet.getRange(2, column_id, ids.length).setValues(ids);
}

```

かなり短いコードですが、これで要件は達成できます。
空の配列に、「\_\_PowerAppsId\_\_」をFitさせていくという単純なコードです。

:::note warn
お願い
筆者自身、ブラッシュアップの余地が大きいと思っておりますので、ご助言あればWelcomeです！
:::

## 自動的に\_\_PowerAppsId\_\_列をFitさせていく方法
GASの強みは、Power Automateと同様にトリガーが存在することです。
トリガーをうまく使うことで、\_\_PowerAppsId\_\_列をキレイにし続けていく仕組みを作ってみます。

## トリガーの紹介
 1. Google Apps Script の統合開発環境（IDE）に入り、メニューバーの中に「**トリガー**」の設定メニューがあります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/795046b2-eff1-04f1-9edf-34e99841128f.png)

 **スタンドアロン型スクリプト**と、スプレッドシートやドキュメントに付帯する**コンテナバインド型スクリプト**によって、
トリガーの内容が異なります。
今回はスプレッドシートに付帯した**コンテナバインド型**の例で解決策を考えてみます。

## トリガーによる解決方法
### **時間ベースのトリガーで解決する**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/f16ce4b2-69f9-6592-ebb6-be575e550c11.png)
文字通り時間主導で定期的にクリーニングする方法です。
sampleのコードでは下記が条件となり、定期的にスクリプトが実行されます。
```
取得した二次元配列の、`二次元要素[0]のデータが、空文字列``※nullではないこと
```
    
一応解決の手段にはできそう・・・？🧐
1時間おきにクリーニングされるので、`アプリ使う時ドンピシャで！`は確約できませんが、確率高く運用はできそうです。

### 変更時のトリガーで解決する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/b4d6737f-bba0-a368-9e1d-d3b9eaf731aa.png)

こちらはスプレッドシートに`何らかしらの変更処理`を実行したときに、起動するトリガーです。
とてもガテン系なやり方になります。

例えば
```Power AppsでPower Automateを使い、Google Sheetの「行を更新する」```
このアクション用の、作業シートがあれば使える見込みがあります。

実験してみましょう！

1. 【Google Sheet】作業シートを用意する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/be70dd87-7547-1270-b133-1735c82a6ab7.png)
workという作業シートを用意します

1. 【Power Automate】作業シートを作る
    1. Power AutomateでGoogle Sheetを操作するにあたり、`行ID`が必要になります。
        行IDを作るために、インスタントフローを作りました。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/c108091e-b342-cbd1-11a7-6f9c135940f3.png)
    カラム「column1」に対する\_\_PowerAppsId\_\_を作るために下記のフローをとりあえず起動します。
    終わったら下記の画面で戻ってきたJSONを確認します。
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/d0725afc-1d47-058f-87e5-669f48a12d25.png)
    
    このフローを実行することで、内部IDが附番されました。
    長い平文JSONが出てきますがフォーマットすると、`ItemInternalId`が見れます。これが行IDのみたいですね！
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/60c75bac-bec4-8cde-5ce8-0c49975dddd2.png)

1. 【Power Automate】行IDを使ったテキトーなアクションを作る
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/fd987015-f0c0-dc1f-303d-4a845a04ccba.png)
    `ItemInternalId`がわかったので、`Power AutomateからGoogle Sheetを編集するフロー`が一応できました。

     - このフローの中の`column1`の値を、フロー実行時に変わる値`utcNow()`関数にします！
    ![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/a4d62ba3-e444-b3c1-7784-1f9346696aa6.png)
    1. Power Automateの実行履歴
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/ce7a9c6c-f766-c394-7c5b-0df866601127.png)

    1. Google Apps Script IDE
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/1efceb7b-5d5b-e8f0-8716-10a0f826545a.png)

Power Automateから無理やりですがGoogle Apps Scriptは起動できそう。かなり・・・ですが（苦笑）
言わずもがな __非同期__ です。またGoogle API失敗の確率もあるため、確実な処理としては惜しい部分もあります。

__しかし・・・__

# 連携は強い！良いとこ取りをしていこう！

Microsoft 365でもグレードに応じたライセンスがあり、Google Workspaceと並行で使っていらっしゃる企業のご担当者様もいるのではないかと思います！
そういった方にお役立て出来ればと思い、記事を書かせていただきました！

小さな改善のために使えるものは使う！手段の一つとして貢献できれば幸いです！

__お読みいただきありがとうございました🙇🙇__

良ければTwitter  Followしてください！ Microsoft MVPを非IT、バックオフィス職から目指すアカウントです！

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">これが！新しい出戻りガツオ🐟のスタイルや🐟🐟<br><br>登壇を含め、活動をこのスタイルで増やすから期待してくれよな！<br><br>Power Platform中心に非ITからのDX推進のマスコット目指すぜ🐟‼️<a href="https://twitter.com/hashtag/%E5%87%BA%E6%88%BB%E3%82%8A%E3%82%AC%E3%83%84%E3%82%AA?src=hash&amp;ref_src=twsrc%5Etfw">#出戻りガツオ</a> <a href="https://twitter.com/hashtag/%E6%96%B0%E3%81%97%E3%81%84%E3%83%97%E3%83%AD%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E7%94%BB%E5%83%8F?src=hash&amp;ref_src=twsrc%5Etfw">#新しいプロフィール画像</a> <a href="https://t.co/wXDkhkkpuS">pic.twitter.com/wXDkhkkpuS</a></p>&mdash; 出戻りガツオ🐟 (@DemodoriGatsuo) <a href="https://twitter.com/DemodoriGatsuo/status/1553610805718831106?ref_src=twsrc%5Etfw">July 31, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>