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

はじめに

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

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

GoogleスプレッドシートのQuery関数

Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。

QUERY(データ, クエリ, [見出し])

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

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

どんなシーンで活用するのか

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

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

例えば

• 昨日時点の社員名簿
• 本日時点の社員名簿

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

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

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

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

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

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

---

人事情報を本日時点で抽出するQuery関数

下記にデータを思い切って挙げました。
個人情報はダミーデータです。

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

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

上記のデータの中で

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

QUERY関数

=QUERY(mst!$A:$I,"SELECT * WHERE I IS NULL OR I >= date '" & TEXT(TODAY(),"yyyy-MM-dd") & "'")

関数を活用して退職日が本日時点より前のデータだけにフィルタする内容です。
QUERY関数によって、日付による更新少なくとも退職者は計算で除外されるデータソースが作成できました。

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

Power Appsを繋げて活用するケース

私はTeamsに下記の様なアプリを導入し、活用しています。

• 従業員名簿アプリ - 繰り返しますがデータはダミーです
  

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

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

HTMLテキスト機能を使い、

• メール送信

• 電話をかける
  といった活用も可能、Androidのスマホであれば、Windowsとリンクできるのでさらに便利です。

• 詳細情報
  

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

でも実はこれが

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

Power Automateと連携すれば、この画面に表示されているアカウントに

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

---

このアプリの課題

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

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

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

データの増加は問題なし

外部要因によってデータが増えたときは、どうなるでしょうか？
これはPower Appsの機能によって、起動時やReflash関数実行時に、データの再読み込みの過程で、自動的に「__PowerAppsId__」は追加記入されるため問題になりません。

• Google SpreadSheet - マスタ側
  

• Power Apps
  

課題はデータの減少

QUERY関数を利用して、現在の日付より前に退職した人のデータは表示しないといったQUERY式を設定していると、この方法は成り立たなくなります。
図の通り、レコードが空のIDが残る。

• Power Apps側は・・・
  

空のレコードがギャラリーに出てしまっています。

残念な感じがしますね。

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

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

---

Google Apps Scriptを活用する

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させていくという単純なコードです。

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

自動的に__PowerAppsId__列をFitさせていく方法

GASの強みは、Power Automateと同様にトリガーが存在することです。
トリガーをうまく使うことで、__PowerAppsId__列をキレイにし続けていく仕組みを作ってみます。

トリガーの紹介

1. Google Apps Script の統合開発環境（IDE）に入り、メニューバーの中に「トリガー」の設定メニューがあります。
   

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

トリガーによる解決方法

時間ベースのトリガーで解決する

文字通り時間主導で定期的にクリーニングする方法です。
sampleのコードでは下記が条件となり、定期的にスクリプトが実行されます。

取得した二次元配列の、`二次元要素[0]のデータが、空文字列``※nullではないこと

一応解決の手段にはできそう・・・？🧐
1時間おきにクリーニングされるので、アプリ使う時ドンピシャで！は確約できませんが、確率高く運用はできそうです。

変更時のトリガーで解決する

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

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

実験してみましょう！

1. 【Google Sheet】作業シートを用意する
   
   workという作業シートを用意します

2. 【Power Automate】作業シートを作る

   1. Power AutomateでGoogle Sheetを操作するにあたり、行IDが必要になります。
      行IDを作るために、インスタントフローを作りました。
      
      カラム「column1」に対する__PowerAppsId__を作るために下記のフローをとりあえず起動します。
      終わったら下記の画面で戻ってきたJSONを確認します。
      

   このフローを実行することで、内部IDが附番されました。
   長い平文JSONが出てきますがフォーマットすると、ItemInternalIdが見れます。これが行IDのみたいですね！
   

3. 【Power Automate】行IDを使ったテキトーなアクションを作る
   
   ItemInternalIdがわかったので、Power AutomateからGoogle Sheetを編集するフローが一応できました。

   • このフローの中のcolumn1の値を、フロー実行時に変わる値utcNow()関数にします！
     

   1. Power Automateの実行履歴
      

   2. Google Apps Script IDE
      

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

しかし・・・

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

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

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

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

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

これが！新しい出戻りガツオ🐟のスタイルや🐟🐟

登壇を含め、活動をこのスタイルで増やすから期待してくれよな！

Power Platform中心に非ITからのDX推進のマスコット目指すぜ🐟‼️#出戻りガツオ #新しいプロフィール画像 pic.twitter.com/wXDkhkkpuS

— 出戻りガツオ🐟 (@DemodoriGatsuo) July 31, 2022