Power Automate: SharePoint フォルダで 最新ファイルを抽出する

# 背景
会社にて、定期的に特定フォルダのファイルを印刷する方法が欲しいって話があった

Automate で印刷ファイル情報を取得して、Teams/Mail で通知すればいいんじゃない？ってことで
Filter Query と OrderBy で最新ファイルの抽出方法の例を準備した記録

# 概要
1. （省略）定期トリガー
1. 特定の拡張子でフィルター
1. 更新日を降順で
1. 一点のみ取得
1. （省略）Mail や Teams で通知・・・で、まぁ、手動印刷とかでいいんじゃね？と。PAD よりは

# フロー
トリガーと通知アクションは、省略
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/120072/6b1397ee-0eb3-2ac9-5196-ae906aeeb14f.png)

```ribbon:例
{
    "id": "15a1e53e-5ed6-4c96-b558-e9f3-abf509b0",
    "brandColor": "#8C3900",
    "connectionReferences": {
        "shared_office365groups": {
            "connection": {
                "id": "/providers/Microsoft.PowerApps/apis/shared_office365groups/connections/5e96053ddc264f998e74dbe3ccf3ef9c"
            }
        },
        "shared_sharepointonline": {
            "connection": {
                "id": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-1c8b887c-61a2-4d83-ae95-966e8760de31"
            }
        }
    },
    "connectorDisplayName": "Control",
    "icon": "data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=",
    "isTrigger": false,
    "operationName": "Scope",
    "operationDefinition": {
        "type": "Scope",
        "actions": {
            "Get_files_(properties_only)": {
                "type": "OpenApiConnection",
                "inputs": {
                    "host": {
                        "connectionName": "shared_sharepointonline",
                        "operationId": "GetFileItems",
                        "apiId": "/providers/Microsoft.PowerApps/apis/shared_sharepointonline"
                    },
                    "parameters": {
                        "dataset": "https://{site url}/sites",
                        "table": "77cbc9b8-4538-4e2b-b0d7-4eee344e8317",
                        "folderPath": "/Shared Documents",
                        "viewScopeOption": "RecursiveAll",
                        "$filter": "substringof('.xlsx',FileLeafRef)",
                        "$orderby": "Modified desc",
                        "$top": 1
                    },
                    "authentication": {
                        "type": "Raw",
                        "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
                    }
                },
                "runAfter": {}
            }
        },
        "runAfter": {}
    }
}
```

## filter query
以下に Filter Query についての詳細説明があります

https://sharepains.com/2018/11/12/sharepoint-get-items-odata-filter-query/


InternalName の基本定義

https://docs.microsoft.com/ja-jp/dotnet/api/microsoft.sharepoint.spbuiltinfieldid?view=sharepoint-server

Filter Query で利用する InternalName は以下の fields API で確認可能
> API https://{site_url}/_api/web/lists(guid'{list_guid}')/Fields

例としては、 
> _api/web/GetFolderByServerRelativeUrl('/sites/testforflow/Shared Documents')

https://docs.microsoft.com/ja-jp/sharepoint/dev/sp-add-ins/working-with-lists-and-list-items-with-rest

```filterQuery:filterQuery
substringof('.xlsx',FileLeafRef)
```
## order by
昇順にしたければ、省略するか、desc → asc へ

```orderBy:orderBy
Modified desc
```

# 参考
https://social.technet.microsoft.com/wiki/contents/articles/35796.sharepoint-2013-using-rest-api-for-selecting-filtering-sorting-and-pagination-in-sharepoint-list.aspx

# keyword
how to retreive newest file in sharepoint folder