はじめに

初投稿です。Power Platformの学習を進めている出戻りガツオと申します。
Power Appsについてデータソースの選択肢は大変豊富で素晴らしいですが、
今回「Googleスプレッドシート」をデータソースとして選定したことにより、
トラブルに直面したので回避策をシェアします。

※今回に至ってはほぼ同様の内容をnoteにも記載しておりますがご了承ください。

Power Appsのデータソースについて

Power AppsもといPower Platformの強みですが、
コネクタによりMicrosoftサービスのみならず、様々なサービスを、Power Platformのサービスのデータソースに指定することができます。

Google Sheet（Googleスプレッドシートですが以下、Google Sheetに略）もその一環で、シートをテーブルを認識し、キャンバスアプリのデータソースにすることができました。

Google Sheetをデータソースにした場合の注意点

私が陥った今回のトラブルは
「Google Sheetをデータソースにしたときにテーブルにおける列のデータ型が想定したとおりにならない」
ということです。

データテーブルにおいて、レコードのデータ型はとても大切です。
テキストであったり、数字であったり、日時であったり、そのデータの型に応じて、使える関数や処理が限定されていきます。

Power Appsのデータソースのデータ型は、接続先を指定した時点でアプリ側で自然にデータ型を評価するように感じました。

警告
私の環境のみの可能性がありますが、すべてTEXTと評価されました。Power Automateも同様です。

もとのサービスの設定に依存する部分があると思いますが、
PowerApps側もといコネクタの機能によってデータ型を評価した結果を返す*と思われます。

私の場合は、指定したデータテーブルが下記のデータ型になることを想定していましたが、実際には全ての列がテキスト型として認識されていることで、集計や関数においてエラーが発生しました。

データソースのデータ型が想定どおりにならなかったときの対処法

コレクションを活用する

コレクションに適用する

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などの関数で、データ型を変換することができます。

元のTextの場合	変換する関数の例
Date	DateValue()
DateTime	DateTimeValue()
Int64	Value()

データソースに対して処理を行う場合

上記のコレクションの対処と同様に、
データ利用時に関数でテキスト型からデータ型を変更する方法も
対策として挙げられます。

式の中でデータの型を変換する

ClearCollect(_data_table,
    Filter(record,
           DateValue(Day)>=Today()
           )
        )

[ Power Apps ] ⇨ [ Google Sheet ]へのデータ連携に課題

データの編集、追加の時、データソースに対してPower Apps側からデータを送るときに、
Power Apps側で解決する方法が見つけることができませんでした。

Power Apps側の認識とデータソース側の認識がズレていることから、Form機能でデータに操作を加えるとき

Power Appsはデータソースの列のデータ型を全てテキスト型と評価している
Formの変更、追加が送信された際のデータも全てテキスト型に変換されてしまう

データソース側で予め列で定義しているデータ型と齟齬が起きてしまいます。
幸いデータソースはGoogle Sheetだったため、解決をGoogleに譲ることにしました。

データソース[ Google Sheet ]側でデータ型を変換する

テキスト型でデータが送られるとGoogle Sheetはどうなるでしょうか？

Google Sheet 行331が今回登録されたレコードです。

Google Sheet 数値はシングルクォーテーションが追加されています。

試しにScriptでデータ型を検証すると

GoogleAppsScriptでデータ型を検証

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時間として評価されているようです。

Google Apps Scriptで値を修正する

データの編集をトリガーにしたGoogle Apps Scriptで問題解決に取り組みます。

Power AppsからGoogle Sheetがデータを受け取ったとき、イベントトリガーが正常に動くことは確認できました。

データソース全体のデータを取得し、UNIX時間(文字列)とシングルクォーテーションが入ることで文字列と評価されている数字を変換します。

UNIX TIMESTAMPを型変換

function setPowerAppsDataFormat(){
    const sheet   = sheetApp.getActiveSpreadsheet().getSheetByName("*シート名*");
    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時間の変換はこちらを参考にさせていただきました。感謝申し上げます

スクリプトで実施していることは
　1. 値を配列に格納
　2. UNIXタイムのテキストを数字に変換
　3. 「yyyy/MM/dd HH: mm:ss」の形式に変換

またテキストとして評価されている数字は、配列に格納して、スプレッドシートに戻す一連の動作で
特に加工を加えることなく数値として評価され直します。

変換後の二次元配列を同じ範囲にそっくりそのまま戻すことで、希望していたデータ型でデータテーブルが整いました。

ExcelやGoogle Sheetの編集時の値によってデータ型を評価する機能を活かしています。

Power Apps側で既存のレコードを修正したい場合があると想いますが、この手法で対処できる見込みがあります。

上手くいきました！
後続のレコードも問題なし！

コネクタが豊富な分柔軟な対応を！

コネクタが豊富な分、このような想定外が今後も起きるかもしれません。
その都度対応は柔軟に考えなければなりません。

今回はGoogle Apps Scriptの活用によって解決を試みました。
Power Apps側で全て解決するとなると更に深い理解が必要になるかもしれません。

これからも開発を通じて、面白い挙動や解決策の例は、Power Appsの対処ケースとして、
今後もシェアしていきたいと思いますのでお付き合いください。

【Power Apps】キャンバスアプリのデータソースをGoogleスプレッドシートにするときの注意点