【Power Apps】キャンバスアプリのデータソースをGoogleスプレッドシートにするときの注意点
こんにちは！出戻りガツオ🐟です！
絶賛Power Appsの学習を進めていますが、
作成中のアプリのデータソースを、
「Excel Online」から「Google Sheet」に変更しました。
（理由は割愛・・）
データソースの選択肢の豊富さもPower Appsの魅力！
ですが、今回私が直面したトラブル
『データソースを「Google Sheet」にした際の不具合に対処した方法』
をシェアさせていただきます。
Power Appsのデータソースについて
Power AppsもといPower Platformの強みですが、
コネクタによりMicrosoftサービスのみならず、様々なサービスを、Power Platformのサービスのデータソースに指定することができます。
Google Sheet（Googleスプレッドシートですが以下、Google Sheetに略）
もその一環で、シートをテーブルを認識し、キャンバスアプリのデータソースにすることができました。
コネクタ一覧の参考はこちら↓
Google Sheetをデータソースにした場合の注意点
私が陥った今回のトラブルは
「Google Sheetをデータソースにしたときに、
テーブルにおける列のデータ型が想定したとおりにならない」
ということでした。
データテーブルにおいて、レコードのデータ型はとても大切です。
テキストであったり、数字であったり、日時であったり、
そのデータの型に応じて、使える関数や処理が限定されていきます。
Power Appsのデータソースのデータ型は、接続先を指定した時点でアプリ側で自然にデータ型を評価するように感じました。
もとのサービスの設定に依存する部分があると思いますが、
Power Apps側もといコネクタの機能によって
データ型を評価した結果を返すものと思われます。
私の場合は、指定したデータテーブルが下記のデータ型になることを想定していましたが、実際には全ての列がテキスト型として認識されていることで、集計や関数においてエラーが発生しました。
データソースのデータ型が想定どおりにならなかったときの対処法
コレクションを活用する
データの参照先という観点では、
「読み込んだデータソースからコレクションを作成する対処法」
が挙げられます。
サンプルのコードです。
ClearCollect(Fact,
ForAll(record,
{Date:DateTimeValue(Date),Menu:Menu,Attachment:Attachment,Number:Value(Number),Memo:Memo,YearMonth:DateValue(YearMonth),Day:DateValue(Day)}));参照用のコレクションを作り、
データソースの中身をForAll 関数を使い、データの型を関数で変えてしまうという方法です。
Excel関数と同じように、DateValue、DateTimeValue、Value、Textなどの関数で変換することができます。
このコレクションを参照することが一つの解決策になります。
データソースに対して処理を行う場合
上記のコレクションの対処と同様に、
都度テキスト型から変換したいデータ型に変更する関数を使用する方法も
対策として挙げられます。
事実それでも参照時に関数を書くだけなので、
そこまで大変な処理ではありません。
[ Power Apps ] ⇨ [ Google Sheet ]へのデータ連携に課題
「データの編集、追加の時」
データソースに対して
Power Apps側から処理を行うことが課題になります。
Power Apps側の認識とデータソース側の認識がズレていることから、
Form機能でデータに操作を加えるとき、
Appsはデータソースの列のデータ型を全てテキスト型と解釈している
Formの変更、追加が送信された際のデータも全てテキスト型に変換されてしまう。
データソース側では、予め列で定義しているデータ型と齟齬が起きてしまいます。大きな問題です。
Apps側でも参照したときに全然違う値が出てくる課題が発生します。関数対処も難しいかもしれません。
幸いデータソースはGoogle Sheetだったため、解決をGoogleに譲ることにしました。
データソース[ Google Sheet ]側でデータ型を変換する
テキスト型でデータが送られるとGoogle Sheetはどうなるでしょうか？
画像の通り全てテキスト型で値が渡されています。
数値はシングルクォーテーション[ ' ]が前に入ることで、
テキストと解釈されています。
試しにScriptでデータ型を検証すると
A331とD331の検証用コード
function myFunction() {
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("record");
const date = Object.prototype.toString.call(SHEET.getRange("A331").getValue());
const num = Object.prototype.toString.call(SHEET.getRange("D331").getValue());
const result = "Date列の評価：" + date + " Number列の評価：" + num;
Logger.log(result);
}
[ object String ]としっかりテキストとして評価されています。
困りました。
日付に至っては[ '1656851078000 ]という値になっています。
スプレッドシートでよく見られるシリアル値とも違います。
調べたところUNIX時間として評価されているようです。
※筆者はUNIX時間を知りませんでした…
何にせよ時間を出しているものであるということは変わりません。
光が見えてきました。演算で時間に変換する方法が見込めます。
データベース側( Google Sheet )の機能で解決してみましょう。
Google Apps Scriptで値を修正する
データの編集を
トリガーにしたGoogle Apps Scriptを作成してこの問題解決に取り組みます。
Power AppsからGoogle Sheetがデータを受け取ったとき、
イベントトリガーが正常に動くことは確認済みです。
今回はデータソース全体のデータを取得し、UNIX時間(文字列)とシングルクォーテーションが入ることで文字列と評価されている数字を変換します。
コードはこちら
// UNIX TIMESTAMPを型変換
function setPowerAppsDataFormat(){
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("record");
const lastRow = SHEET.getLastRow();
const range = SHEET.getRange(1,1,lastRow, 4);
let values = range.getValues();
for(let i=1; i < values.length; i++){
if(Object.prototype.toString.call(values[i][0]) == '[object String]'){
values[i][0] = Utilities.formatDate(new Date(Number(values[i][0])),"Asia/Tokyo","yyyy/MM/dd HH:mm:ss")
}
}
range.setValues(values);
}UNIX時間の変換はこちらを参考にさせていただきました。
やまやふみてる @yamayafumiteru 様に感謝申し上げます
スクリプトで実施していることは値を配列に格納し、
UNIXタイムのテキストを数字に変換し、日付型としてシートで解釈できるよう、「yyyy/MM/dd HH:mm:ss」の形式に値を変換しています。
またテキストとして評価されている数字は、
配列に格納して、スプレッドシートに戻す一連の動作で
特に加工を加えることなく数値として評価され直します。
変換後の二次元配列を同じ範囲にそっくりそのまま戻すことで、
希望していたデータ型でデータテーブルが整いました。
ExcelやGoogle Sheetの編集時の値によって
データ型を評価する機能を活かしています。
Power Apps側で既存のレコードを修正したい場合があると想いますが、この手法で対処できる見込みがあります。
コスパがいいコードですね🐟
※後続のレコードも問題なしです！
それぞれのツールを活かす
コネクタが豊富な分、このような想定外が今後も起きるかもしれません。
その都度対応は柔軟に考えなければなりません。
今回はGoogle Apps Scriptの活用によって解決を試みました。
Power Apps側で全て解決する、となると更に深い理解が必要になるかもしれません。
ですが色々なサービスと接続できるPower Platformの利点を振り返ると、
単一のサービスで全て何とかするのではなく、
いろいろ繋げて長所を活かすことで開発のしやすさも変わってきそうですね。
これからも開発を通じて、面白い挙動や解決策の例は、Power Appsの対処ケースとして、今後もシェアしていきたいと思いますのでお付き合いください。
またお知恵も拝借させていただくこともあると思いますので今後ともよろしくお願いいたします🙇
お読みいただきありがとうございました🐟🐟
最後に宣伝
お読みいただいた方はぜひTwitterもフォローしてくださると大変嬉しいです！！
リプ、いいね👍、RT大歓迎です！
強く求めてます🐟😂🐟
業務改善フレンズ大歓迎！！切磋琢磨しましょ～♪♪
それではまた今度！ばいば〜い！🐟🐟