#PowerAutomate でExcel表をメール本文に貼り付けて送付する方法2つ

# 概要

SharePointライブラリに保存されたExcelからテーブルデータを読み取ってメール送信する方法の覚え書きです。
以下の2通りの方法があります。

- ①HTMLテーブルとして送付する方法(Power Automateの機能)
- ②画像として送付する方法(Office Scriptとの組み合わせ)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/2889c2ad-cc77-97c6-c200-dd343729792b.png)

# シナリオ
１．日付や商品、売上が記録されたExcelテーブルがあり、[画像左]
２．指定した日付(メール送信の前日など)の行にフィルターし[画像右]
３．表をメールに貼り付けて送付する<br>
といったシナリオを想定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/e895732a-b274-6ed8-8253-cc31dc6fb895.png)

今回、フィルターする日付(フィルター値)は以下のようなExcelシート内に入力した日付を使用します。
※日付はPower Automate側で生成してもOKです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/3b6461d1-dd9a-751a-3924-18945436843b.png)


# 方法1 HTMLテーブルとして送付する

Power Automateのメール送信アクションでHTMLテーブルを本文に貼り付けて送付する方法です。  

完成形  
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/5fa16475-93cd-5922-476a-21eb75d682fb.png)

## 元テーブル
以下のようなテーブルです。
数値や日付の列はPower Automate側で別途書式設定が必要になるため、
表示用_** の列を作成し、慣れたExcel関数(Text関数)で先に書式設定しておけば後が簡単です。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/69dce645-7c98-4452-1cf3-30d11fe6503f.png)


## フロー全体
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/7721d918-146c-ea18-8f2f-87e2362043b9.png)

※単純化するためインスタントフローとしています。

## ポイント解説

### 行の取得
報告日(フィルターする値)を設定情報テーブルから単一行データとして読み込みます。
※今回のHTMLテーブルには直接関係ありません。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/6b60eb62-75a9-8d32-83f6-928bb1f1de36.png)

ここで取得する情報は、以下のようなExcelテーブルの1行目です。
「フィルタークエリ文字列」は次の関数でISO日付が表示されるように設定しています。
```=TEXT([@報告日],"yyyy-mm-dd")```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/3b6461d1-dd9a-751a-3924-18945436843b.png)

### 表内に存在する行の取得
売上テーブルから表データを取得します(複数行データ)。

フィルタークエリを使用し、日付列が特定の日付である行に絞り込みます。
式：```列名 eq 'yyyy-MM-dd'```
日付部分は上記の「フィルタークエリ文字列」をセットするだけなので簡単です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/7e19ad77-f625-ca47-119c-490a93336e68.png)

:::note info
フィルタークエリについて、Excel Onlineでは'eq', 'ne', 'contains', 'startswith' or 'endswith'のみがサポートされています。
leやgeなどの比較演算子を使用したい場合は、セルの値が1 or 0などの値となるようにExcel関数側で工夫が必要です。
:::

:::note alert
公式テンプレートでは一旦表データを取得してからフィルター処理を行うものがありますが、元データの行数が多い場合、既定では先頭の256行しか取得されないため注意が必要です。
フィルタークエリを使ってExcel Online側でフィルターしましょう。
:::

### HTMLテーブルの作成
先ほどの表データ(オブジェクト配列)をメールに表示するため、HTMLテーブルに変換します。
必要な列のみ選択したい場合は、詳細オプション→列でカスタムを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/31ae92e7-505f-7818-703e-9b4342839ab7.png)

すると、以下のように設定できるようになります。
売上金額や日付を任意の書式で表示させたい場合は、Text関数を使ってExcel側で表示用列を作成しておくと簡単です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/625e320e-4c9c-d150-547b-dbba579ab76e.png)  
  

表内に存在する行の取得 のオプションでDateTime形式：ISO8601に設定したうえで、
formatDateTime()関数などで書式を設定してもOKです。


### 作成：CSS
上記のHTMLテーブルには装飾がありませんので、styleタグにてCSSを追加してあげます。

今回はこちらから頂きました。
https://ryanmaclean365.com/2020/01/29/power-automate-html-table-styling/

以下のようなテーブルCSS作成ツールもあるよです。
https://divtable.com/table-styler/


作成アクションに以下を貼り付けます。

```css
<style>
Table {
  background-color: #EEEEEE;
  border-collapse: collapse;
}

Table td, Table th {
  border: 1px solid #ddd;
  padding: 5px 5px;
}

Table th {
  font-size: 15px;
  font-weight: bold;
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #1C6EA4;
  color: white;
}
</style>
```



### メールの送信
CSS→本文の順で埋め込みます。
これで装飾されたHTMLテーブルが送信されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/c499f83f-2cf9-0490-592f-7c58416c6294.png)  

<br>



# 方法2 テーブルを画像として送る
上記のHTML＋CSSでは単純なデザインのテーブルでした。
こちらは、ExcelのセルをコピーしてOutlookに貼り付けるように見たままのデザインで表にする方法です。

テーブルを画像として貼り付けます。
画像の生成には、Excel OnlineのOffice Scriptsを使用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/2889c2ad-cc77-97c6-c200-dd343729792b.png)

## 元テーブル
以下のようなテーブルです。
①の方法と同じく日付でフィルターし、必要な列のみ表示する必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/68f8b9b6-c6bc-c7ca-4196-b88f18917318.png)


## 準備

Excel Onlineの自動化タブより、以下のOfficeScriptを登録します。

```TypeScript:
function main(workbook: ExcelScript.Workbook, tableName:string, 
  keyColumn: string, valueOrIsoDate: string, hiddenColumns?:string[]):string
{

  // テーブルの取得
  const table = workbook.getTable(tableName);
  
  // フィルターコンテキストを定義
  let filterCtx: string | ExcelScript.FilterDatetime;
  if (valueOrIsoDate.match(/^\d{4}-[01]\d-[0-3]\d$/) != null){
    filterCtx  = {
      date: valueOrIsoDate,
      specificity: ExcelScript.FilterDatetimeSpecificity.day
    };
  }else{
    filterCtx = valueOrIsoDate;
  }

  // 不要な列を非表示
  SetColumnsVisibility(table, hiddenColumns,false);

  // 列にフィルターを適用
  const column = table.getColumnByName(keyColumn);
  column.getFilter().applyValuesFilter([filterCtx]);

  // テーブルの画像を取得
  const tableImage = table.getRange().getVisibleView().getRange().getImage();

  // フィルターを解除
  column.getFilter().clear();

  // 列の再表示
  SetColumnsVisibility(table, hiddenColumns, true);

  return tableImage;

}


function SetColumnsVisibility(table: ExcelScript.Table, columnNames:string[]=[], visibility:boolean){
  columnNames.map(
    (name:string) => table.getColumnByName(name).getRange().setColumnHidden(!visibility)
  );
}

```

## 簡単な解説

**指定列の行の値でフィルターする**
getColumnByNameにより列名で列オブジェクトを取得し、
applyValuesFilter([])で配列形式でフィルター値を設定します。

```TypeScript
const column = table.getColumnByName(keyColumn);
column.getFilter().applyValuesFilter([filterCtx]);
```

**行のフィルターコンテキスト**
フィルター値は、string | ExcelScript.FilterDatetime のUnion形式なのでどちらかを指定します。
今回のコードでは、指定された値がyyyy-MM-dd形式の日付か判定し、filterCtxの型を判別しています。
ExcelScript.FilterDatetimeの詳細
https://docs.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.filterdatetime?view=office-scripts

**列の非表示**
不要な列を非表示にするには、以下のメソッドを使います。
setColumnHidden(true)で列が非表示になります。
Power Automateでテーブルを取得するとき、非表示列があるとエラーが発生しますので、
非表示にした後は必ず再表示しておきます。

```TypeScript
table.getColumnByName(name).getRange().setColumnHidden(!visibility)
```

**テーブルの表示されているセルの画像を取得**
getVisibleView()で表示状態のセルを取得し、getImage()でbase64形式の画像を取得します。
```TypeScript
const tableImage = table.getRange().getVisibleView().getRange().getImage();
```

getImage()でグラフの画像なども取得できるようです。
https://docs.microsoft.com/ja-jp/office/dev/scripts/resources/samples/email-images-chart-table

## Power Automateのフロー全体
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/ff3f631b-0b9b-3f7d-5acb-6e732488bab3.png)

## ポイント解説

### スクリプトの実行
以下のアクションで先ほどのスクリプトを実行します。



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/a3ea1568-3f33-f22c-acb2-f653f29e0697.png)

入力パラメータは4つです。
```
tableName：
    テーブル名を指定します。
keyColumn：
    フィルター対象となる列名を指定します。
valueOrIsoDate：
    フィルターする列がテキストの場合はその値を、
    日付の場合はyyyy-MM-ddの形式で指定します。
hiddenColumns：
    テーブルのうち非表示にする列名を配列形式で指定します。
    非表示にしない場合は空白でOKです。
```

### imageタグの作成

スクリプトの出力はbase64なのでimageタグ及びdatauriに変換します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/bdb6ba18-4d00-78ab-b481-c08fdee636ae.png)
```
<img src="data:image/png;base64,@{outputs('スクリプトの実行')?['body/result']}"/>
```

### メール送信
後はメール本文に埋め込むだけ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/293667/39db9d42-5fe0-d674-374a-d3b25ff3ad98.png)


# あとがき
よく利用されそうなシナリオですが、意外と情報が見つからなかったので記事にしてみました。
間違い等ありましたらご指摘いただけると幸いです。