ファイルサーバーやSharePointサイトの現状把握？とりまExcelのPower Query使お！

# はじめに
ファイルの全量の現状把握やSharePointの運用管理といったシーン、まずは`どのようなファイルが保存されているのか`、といったことが必要になります。

このような事態は、プログラミングスキルがなくても、ExcelのPower Queryで実は解決できてしまうんですよね。

今回は、データソースから集計の自動化に役立つPower Queryを、
 1. **ファイルサーバーのファイル コンテンツ・パスの把握**
 1. **SharePoint フォルダのファイル コンテンツ・パスの把握**
 1. **SharePoint ListsからPower Automateで利用できる`ID`の把握**

といった活用を目的に記事を書いていきます！

Power Queryは`Excel`を想定しています。

:::note info
バージョンは、`Excel for Microsoft 365`を想定
:::

> Twitterの友人より
＞バージョンは、Excel for Microsoft 365を想定
とありますが、
ExcelからSharePointを参照するUIが表示されるのは Microsoft 365 Apps for Enterpriseの場合で、
Microsoft 365 Apps for businessの場合はUIが表示されないです。Power Query Editorでコーディングすればアクセスできるのですが...
>> [ちゅん🐤さん](https://twitter.com/KotorinChunChun)

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">＞バージョンは、Excel for Microsoft 365を想定<br>とありますが、<br>ExcelからSharePointを参照するUIが表示されるのは Microsoft 365 Apps for Enterpriseの場合で、<br>Microsoft 365 Apps for businessの場合はUIが表示されないです。Power Query Editorでコーディングすればアクセスできるのですが...</p>&mdash; ちゅん🐤 (@KotorinChunChun) <a href="https://twitter.com/KotorinChunChun/status/1782010608403263510?ref_src=twsrc%5Etfw">April 21, 2024</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">Microsoft365 Apps for businessとEnterpriseの比較<br>やっぱり表示されるメニューが違う。<br><br>ちなみに無料のPowerBI Desktopなら、Enterprise持ってない人でも、GUI操作によってSharePointの取得クエリ書ける。それをExcelにコピーするのもありかも。 <a href="https://t.co/M8T534KNsq">pic.twitter.com/M8T534KNsq</a></p>&mdash; ちゅん🐤 (@KotorinChunChun) <a href="https://twitter.com/KotorinChunChun/status/1497961454590255113?ref_src=twsrc%5Etfw">February 27, 2022</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

:::note warn
バージョンについては`Excel for Microsoft 365`でも差異があるようです！
ご指摘感謝です！
:::


[Power Query](https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE-power-query-%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a)とは何ぞや、の部分は下記をご参照ください。

https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE-power-query-%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a

今回ご紹介するテクニックは、
 - 転職して新しい環境に移った
 - 新しいプロジェクトにアサインされた

といった環境の変化に対し、**非常に有益なテクニック**です。

# 1. ファイルサーバーを調査する
フォルダの中のファイルを調べるには、Excelの`[データ]タブ` > `[データの取得]` > `[ファイルから]` > `[フォルダーから]`と選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/194f4f16-dd6a-d4b4-2457-c266f12b920d.png)

外付けHDDを丸ごと選択してみると、こんな雰囲気で一覧が表示されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/f7944ca9-4e0c-5b61-2f33-9165b45ceefd.png)

ここで[データの変換]をクリックすると、これだけでドライブの配下のファイルが漏れなく表示されるわけです。
たったこれだけ・・・というので驚きを隠せません。

読み込める情報は以下のものです。

|列名|内容|内容の説明|
|---|---|---|
|Content|内容|ファイルのバイナリデータ|
|Name|名前|ファイルの名前|
|Extension|拡張子|ファイルの拡張子|
|Date accessed|アクセス日時|ファイルに最後にアクセスされた日時|
|Date modified|変更日時|ファイルが最後に変更された日時|
|Date created|作成日時|ファイルが作成された日時|
|Attributes|属性|ファイルの属性（例：隠しファイル、読み取り専用など）|
|Folder Path|フォルダーパス|ファイルが保存されているフォルダーのパス|

## ファイルの一覧を作成する
上記からファイルの一覧を作成するために必要な列は、`Name`と`Folder Path`、
拡張子別の集計がしたい場合は`Extension`を使いましょう。

:::note info
「Excelのデータをフォルダーから集計する」といったシーンには`Content`から、データをひとまとめにすることができます。
:::

**`Folder Path`と`Name`の結合でファイルのフルパスも取得できるので、非常に便利です。**

:::note warn
ファイル サーバー丸ごと指定して実行すると、思わぬ量が出力されてしまい、大変なことになるので絞り込みをしっかりしましょう。
:::


## ファイル種別を拡張子から算出するカスタム関数
`Extension`列で拡張子が抽出されているので、`Power Query`でファイルを分類することも可能です。
下記のカスタム関数を用いて、ざっくりとファイル種別を判別することができます。

```plaintext:カスタム関数 GetCategoryByExtension
let
    GetCategoryByExtension = (extension as text) as text =>
    let
        ext = Text.Lower(Text.AfterDelimiter(extension,".")),
        mapping = [
            doc = "Word",
            docx = "Word",
            xls = "Excel",
            xlsx = "Excel",
            xlsm = "Excel",
            ppt = "PowerPoint",
            pptx = "PowerPoint",
            accdb = "Access",
            mdb = "Access",
            eml = "Outlook",
            msg = "Outlook",
            pdf = "PDF",
            txt = "テキストファイル",
            jpg = "画像ファイル",
            jpeg = "画像ファイル",
            png = "画像ファイル",
            gif = "画像ファイル",
            mp4 = "動画ファイル",
            avi = "動画ファイル",
            mov = "動画ファイル",
            mp3 = "音声ファイル",
            wav = "音声ファイル",
            html = "ウェブファイル",
            css = "ウェブファイル",
            js = "JavaScript ファイル",
            java = "Java ソースコード",
            py = "Python ソースコード",
            cpp = "C/C++ ソースコード", c = "C/C++ ソースコード"
        ],
        category = Record.FieldOrDefault(mapping, ext, "その他")
    in
        category
in
    GetCategoryByExtension
```

使い方は
1. 空のクエリの追加
1. 詳細エディターに上記の関数を貼り付け

こちらにて使用することができます。
関数で実行していることは、`マッピングを使用してファイル種別を取得`しています。
`mapping = []`を調整すれば分類を変えることが可能。

 - どこに何のファイルがあるのか
 - どういったファイルが多いのか
 - どこのフォルダに格納されているのか

一瞬で可視化できることが魅力ですね。
**ここから`PowerShell`や`VBA`でファイルのリネームなど、良い具合に整理することも魅力的です✨**


# 2. SharePoint フォルダを調査する
SharePoint フォルダを調べるするのは、Excelの`[データ]タブ` > `[データの取得]` > `[ファイルから]` > `[SharePoint フォルダーから]`。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/45e91e63-27ce-79f6-170a-f652eaea1c1e.png)


① サイトパスを指定

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/26cdb632-78f8-b8a1-9a51-247a43885b8b.png)

② `Microsoft アカウント`で`サインイン`してから接続します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/c029bb81-c9a8-a931-3cbe-f878df4ed570.png)

**結果は`1. ファイルサーバーを調査する`と同様に結果が出力されます！**
    ファイルパスがURLで可視化できるので、Power Automateの[パスを使用してファイル コンテンツを取得](https://learn.microsoft.com/ja-jp/connectors/sharepointonline/#%E3%83%91%E3%82%B9%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB-%E3%82%B3%E3%83%B3%E3%83%86%E3%83%B3%E3%83%84%E3%82%92%E5%8F%96%E5%BE%97)や[パスを使用してファイル メタデータを取得](https://learn.microsoft.com/ja-jp/connectors/sharepointonline/#%E3%83%91%E3%82%B9%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB-%E3%83%A1%E3%82%BF%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E5%8F%96%E5%BE%97)で活用できます。

**ファイル**だけではなく、`フォルダ`の構成も一発で可視化することができるため、非常にお勧めです。

https://learn.microsoft.com/ja-jp/connectors/sharepointonline/#%E3%83%91%E3%82%B9%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB-%E3%82%B3%E3%83%B3%E3%83%86%E3%83%B3%E3%83%84%E3%82%92%E5%8F%96%E5%BE%97


[公式は下記のサイト](https://learn.microsoft.com/ja-jp/power-query/connectors/sharepoint-folder)

https://learn.microsoft.com/ja-jp/power-query/connectors/sharepoint-folder

# 3. SharePoint Lists
フォルダの中のファイルを調べるには、Excelの`[データ]タブ` > `[データの取得]` > `[オンライン サービスから]` > `[SharePoint Online リストから]`と選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/992ec680-aa53-a09a-98d0-f24861a6117d.png)

① サイト URLを指定します
 - 実装の 2.0を選択すると詳細が選択できます。ビューによる表示する列の設定が可能。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/2bb437b9-c121-7ad9-41bf-63cfe656371e.png)

② 読み込むリストを指定
 - ナビゲーターにて読み込むリストを指定します
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/735203c1-12f8-ba19-aa90-453535d6927d.png)

:::note info
**SharePointのドキュメントライブラリも指定することができます。**
ドキュメントライブラリはフォルダでは🧐と思いますが、**こちらから指定することによって、ライブラリに追加された列が可視化されます。**
:::

### 実装 2.0

SharePoint Listsのビューに沿って表示されることがポイントです。

|列名|説明|
|---|---|
|タイトル|項目のタイトル|
|色タグ|項目に関連付けられた色タグ|
|コンプライアンス資産ID|コンプライアンスのための資産識別ID|
|**ID**|**項目の一意識別子**|
|コンテンツタイプ|項目のコンテンツタイプ|
|更新日時|項目が最後に更新された日時|
|登録日時|項目が登録された日時|
|登録者|項目を登録したユーザー|
|更新者|項目を最後に更新したユーザー|
|バージョン|項目のバージョン情報|
|添付ファイル|項目に添付ファイルが存在するかどうか|
|編集|編集するためのリンク|
|種類|項目の種類|
|子アイテムの数|項目に含まれる子アイテムの数|
|子フォルダーの数|項目に含まれる子フォルダーの数|
|ラベルの設定|項目に設定されているラベル|
|保持ラベル|項目に適用されている保持ラベル|
|保持ラベルを適用済み|保持ラベルが適用されているかどうか|
|ラベルの適用者|ラベルを適用したユーザー|
|レコードとして登録されているアイテム|アイテムがレコードとして登録されているかどうか|
|アプリの作成者|アプリを作成したユーザー|
|アプリの変更者|アプリを最後に変更したユーザー|

### 実装 1.0

出力される列数が多いです。


|列名|説明|
|----------------------------------|-----------------------------------------------------|
|FileSystemObjectType|ファイルシステムオブジェクトのタイプ（ファイル、フォルダなど）|
|Id|ID|
|ServerRedirectedEmbedUri|サーバーがリダイレクトした組み込みURI|
|ServerRedirectedEmbedUrl|サーバーがリダイレクトした組み込みURL|
|ContentTypeId|コンテンツタイプID|
|Title|タイトル|
|OData__ColorTag|項目に関連付けられた色タグ|
|ComplianceAssetId|コンプライアンス資産ID|
|ID.1|項目の別のID（二重登録などの場合）|
|Modified|更新日時|
|Created|登録日時|
|AuthorId|登録者ID|
|EditorId|項目を最後に編集したユーザーのID|
|OData__UIVersionString|項目のUIバージョン文字列|
|Attachments|項目に添付ファイルがあるかどうか|
|GUID|グローバル一意識別子|
|FirstUniqueAncestorSecurableObject|最初の一意な最上位のセキュリティオブジェクト|
|RoleAssignments|ロール割り当て アクセス許可レベル情報|
|AttachmentFiles|添付ファイルのリスト|
|ContentType|コンテンツタイプ情報|
|GetDlpPolicyTip|データ損失防止ポリシーヒントを取得するための関数|
|FieldValuesAsHtml|HTML形式フィールド値|
|FieldValuesAsText|テキスト形式フィールド値|
|FieldValuesForEdit|編集のためのフィールド値|
|File|ファイル関連（存在する場合）|
|Folder|フォルダー関連（存在する場合）|
|LikedByInformation|「いいね！」|
|ParentList|親リスト|
|Properties|プロパティ|
|Versions|バージョン|
|Author|登録者|
|Editor|更新者|

アクセス許可レベルも出るとは凄い情報量・・・！
抽出時にクエリの列の評価は内部名で表示されるため、どの列に該当するのか、見易さに劣ります。

## この方法について・・・！

**ID列を可視化できるのは、とっても嬉しい!**
そのままPower Automateの[項目の更新](https://learn.microsoft.com/ja-jp/connectors/sharepointonline/#%E9%A0%85%E7%9B%AE%E3%81%AE%E6%9B%B4%E6%96%B0)アクションで使えるので、非常に便利です！

https://learn.microsoft.com/ja-jp/connectors/sharepointonline/#%E9%A0%85%E7%9B%AE%E3%81%AE%E6%9B%B4%E6%96%B0

ほかにも項目の操作のIDとして利用できます。

ユーザー列や選択肢は、レコードとしてPower Query内で表現されるので、Power Query内でドリルダウンしていく列になります。

想像以上に沢山のデータが、こんなに手軽に取得できるのか！！と驚きを隠せない操作です！

# おわりに
今回の紹介は`Power Query`になりました。
この操作、技術的な仕事をしている人だけではなく、コンサルタントやバックオフィスの方々にも広く知っていただきたいと思います。

Power Queryはホントにハンパないです！
Salesforceの中身ぶち抜いたりとかもできたりもできます😀
使いこなしてみてください！