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

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

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

## Power Appsのデータソースについて
https://docs.microsoft.com/ja-jp/power-apps/maker/canvas-apps/working-with-data-sources

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

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

https://docs.microsoft.com/ja-jp/connectors/connector-reference/

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

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

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

<img src="https://assets.st-note.com/img/1656850855953-lRSamBaP8e.png?width=800" alt="画像"/>

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

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

私の場合は、指定したデータテーブルが下記のデータ型になることを想定していましたが、実際には全ての列がテキスト型として認識されていることで、集計や関数においてエラーが発生しました。
# データソースのデータ型が想定どおりにならなかったときの対処法
## コレクションを活用する
https://docs.microsoft.com/ja-jp/power-apps/maker/canvas-apps/create-update-collection

```PowerFx:コレクションに適用する
ClearCollect(Fact,
    ForAll(record,
        {Date:DateTimeValue(Date),
         Menu:Menu,
         Attachment:Attachment,
         Number:Value(Number),
         Memo:Memo,
         YearMonth:DateValue(YearMonth),
         Day:DateValue(Day)
         }));
```

参照用のコレクションを作り、データソースの中身を**ForAll関数**を使ってデータの型を関数で変えてしまうという方法です。

https://docs.microsoft.com/ja-jp/power-apps/maker/canvas-apps/functions/function-forall

Excel関数と同じように、`DateValue`、`DateTimeValue`、`Value`、`Text`などの関数で、データ型を変換することができます。

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

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

```PowerFx:式の中でデータの型を変換する
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はどうなるでしょうか？
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/482a15c5-9b78-3a78-6384-291c9af51c5f.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/366b03bf-6e4e-96ff-80d8-498fb1428d9d.png)

:::note info
Google Sheet 数値はシングルクォーテーションが追加されています。
:::
### 試しにScriptでデータ型を検証すると

```JavaScript: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); 
}
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/1b6dc186-4aae-114c-3394-5572b6db2ee5.png)

[ object String ]としっかりテキストとして評価されています。
日付に至っては[ '1656851078000 ]という値になっています。UNIX時間として評価されているようです。

https://ja.m.wikipedia.org/wiki/UNIX%E6%99%82%E9%96%93

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/fd2bb300-c52a-ccb8-04e7-7708453c9911.png)

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

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

```JavaScript: 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時間の変換はこちらを参考にさせていただきました。感謝申し上げます

https://qiita.com/yamayafumiteru/items/023733c4149e97727bba

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

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

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

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

Power Apps側で既存のレコードを修正したい場合があると想いますが、この手法で対処できる見込みがあります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/b0cd2177-c1e6-ff34-d363-4fefd2fca128.png)
上手くいきました！
後続のレコードも問題なし！

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

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

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