【スケジューラ】タスクを利用した州別ワークシートの自動生成

# はじめに #
私たちはSAPデータモデルを使用して、州単位の顧客別シートを毎月作成し、それをファイルシステムに保存したいと考えています。
- データモデル: SAP Business One
- データベース: SAP Business One デモデータベース (Microsoft SQL データベース)

# 目的 #
テンプレートであるExcelワークブックを異なるパラメータで再計算し、**実行日**と**州名**をファイル名に含む新しいExcelワークブックとして作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/0960efcd-03fb-f3e0-18bf-df6aab2d1dd6.png)
1. テンプレートワークブック  
2. 生成されたワークブック

# 実装 #
## 1. テンプレートワークブックの準備 ##
分割のための基本クエリを準備する必要があります。  
このクエリは、A/R 売上請求書テーブルからすべての顧客の要約情報を返します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/71cde2dd-5922-4eae-4b65-e022fa64d2a5.png)
1. セル D3 にテーブル式が定義されています  
2. クエリビルダーによるテーブル式の定義  
3. フィルター: **BP Type**は顧客のみのため C である必要があります  
4. フィルター: **Customer Name**はセル B3 を参照し、分割のための重要なフィルターです  
5. フィルター: **Document Date**はセル B4 を参照します  
6. フィルター: **State**はセル B5 を参照します

### a. 顧客リスト ###
別のシートに、Sharperlightのテーブル式を使用して顧客リストを準備しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8f647404-ead5-e1c6-3ba2-523359917e70.png)
1. セル C3 にテーブル式が定義されています  
2. クエリビルダーによるテーブル式の定義  
3. フィルター: **State**はセル B2 を参照しています。最終的にはセル B2 の値は、スケジュールされたタスクから渡される値により動的に変化します  
4. **年/月**の値がセル B1 にスケジュールされたタスクから渡されます  

さらに、`Template`シートのセル **B4** に **=Filters!B1** を、セル **B5** に **=Filters!B2** を入力しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/89c3ab0a-38d0-b0c4-01dd-43b886a5f13c.png)


### b. スプリット公式 ###
セル A1 にスプリット公式を定義します。式は以下の形式になります。
```
=@mdSplitSheet("By Customer",AR_Sales_Invoice_Customer_Name[List of Customers],"",$B$3,"Green",0)
```

#### 第1パラメータ ####
スプリッ公式の名前を指定します。

#### 第2パラメータ ####
顧客のリスト。この値を使用して基になるシートが分割されます。別のシートにリストを準備し、式に記載します。

#### 第3パラメータ ####
シート名のリスト。各スプリットシートの名前を指定できます。空白のままにすると、フィルタ値が使用され、この場合は顧客名が使用されます。

#### 第4パラメータ ####
顧客名リストから各顧客名が渡される場所を指定します。

#### 第5パラメータ ####
分割シートの色を指定します。

#### 第6パラメータ ####
スプリット公式はデフォルトで親ワークシートの直系の子として抽出され、階層レベルは0です。レベルを1に設定すると、スプリットシートの式は子から抽出され、レベル2では孫からのみ抽出されます。任意のレベル番号をスプリットシートの式に追加できますが、その階層レベルに達したときのみ抽出されます。

### c. テスト ###
定義が完了したので、式を実行してみます。  
`Filters`シートのセル B2 に **QLD** を入力し、ここで **ワークシート - 再計算** アイコンをクリックして式を実行します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4bd12dbc-0eb2-c6d7-e448-6436fee11dc1.png)
顧客ごとに分割ワークシートが作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4cf7a38f-44ca-e0cb-8249-9e0a0e585018.png)
式が正常に動作することを確認したら、分割ワークシートを削除してテンプレートとして使用する必要があります。 
**ツリー**アイコンをクリックしてツリーペインを開き、それを使ってワークシートを削除できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c2bb2170-aae8-b0a9-13a7-b8cf7fa413f2.png)
分割シートがぶら下がっているノードを選択し、**削除**アイコンをクリックすることで、そのノードを削除できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/437c90b5-7021-e715-c15a-97712adbca2d.png)
分割シートを削除したら、テンプレートワークブックを保存します。

## 2. 毎月、州単位で実行されるタスクの定義 ##
テンプレートワークシートを再計算し、**Report_{State}_{YearMonth}.xlsx** という名前で指定されたフォルダーに保存するスケジュールされたタスクを作成します。  
このタスクは毎月実行され、**州**および**年/月**の値がテンプレートワークブック内のクエリに渡されます。

### a. スケジュールされたタスク ###
Sharperlightアプリケーションメニューから**スケジューラ**を開きます。  
タスクは毎月実行され、ワークブックは州ごとに再計算されます。

#### a.1 クエリの組み合わせ追加とクエリの組み合わせ終了 ####
**クエリの組み合わせ**と**クエリの組み合わせ終了**を選択します。これにより、州ごとにワークブックの再計算を繰り返し実行することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e79056bc-224f-7c13-b6ee-d7bd400f2f7d.png)
**クエリの組み合わせ**にはクエリが必要で、州のリストを返す必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7417a757-c095-6c13-4dd7-4558f7606c9c.png)

クエリ定義
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/9e16e9ef-bb0c-c264-813e-7acb83eccc82.png)

プレビュー結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f51f36e8-0297-c791-8431-e2e51927e453.png)

クエリから返された値は、その**名前**で参照することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/a4897807-9207-da37-b738-cac1bbf3f6c6.png)


#### a.2 州ごとのExcelワークブックの再計算 ####
**クエリの組み合わせ**と**クエリの組み合わせ終了**の間に**Excelワークブック**アクションを追加します。  
Excelワークブックは、Sharperlightで指定されたセキュアフォルダー内に存在する必要があります。これは、Sharperlightの**サイトセットアップ**で行うことができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4f08e2ad-3f15-fe34-4f2a-8a6d8dc7a87f.png)

上記のケースでは、``C:\Temp\``、``C:\QiitaSampleTemp\`` および ``C:\QiitaSampleCopy\`` フォルダがSharperlightからアクセス可能です。  
テンプレートExcelワークブックは、これらのセキュアフォルダーのいずれかに配置しておく必要があります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d3b85c1a-5355-5326-c64a-945836a2303b.png)

##### テンプレートワークブックの指定 #####
話をスケジューラのタスクに戻します。
タスクに使用するテンプレートExcelワークブックを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8322d9fb-c06c-8b54-aeb7-5da02c9baf5d.png)

##### パラメータの設定 #####
テンプレートExcelワークブックに2つのパラメータを渡したいと思います。1つは **{年/月}** の値、もう1つは**州**です。ここで指定できます。  
**{年/月}** の値は`Filters`シートのセル B1 に渡されます。  
**州**は`Filters`シートのセル B2 に渡されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/18996567-66df-24bf-1b0a-04fbff3d2fb8.png)

これは、以下の構文で実現できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d1dd6460-b518-4bde-ca50-d2a0053f663d.png)

最初のものは、Sharperlight式によって生成された現在の**年/月**を`Filters`シートのセル B1 に渡しています。  
2つ目は、**クエリの組み合わせ**で生成された**州**の値を渡しています。

##### 結果の保存 #####
ワークブックを再計算した後、各州ごとにExcelワークブックとして保存します。  
ここで指定することができます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/73d87e59-f945-1aae-6db2-29cff5f7da0a.png)
```
C:\QiitaSampleTemp\Document_{_Date}_{%State}.xlsx
```

##### その他のオプション #####
このオプションを有効にすると、結果が保存される際にすべてのSharperlight式が削除されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2299f629-1c58-10f2-df44-80e7241c3810.png)


#### a.3 トリガーの追加 ####
トリガータブでトリガーを設定します。SharperlightとMicrosoft Excelが同じPC上にある場合、Windowsスケジューラーよりも**サービスタイマー**（Sharperlightサービス）を使用する方が、設定が簡単です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f90c520a-f46f-1f32-6664-d9ed34e7b054.png)


# あとがき #
これは、Sharperlightのスケジュールされたタスクを使用したオフィス自動化の簡単な例です。ただし、Microsoft Officeは自動化のために十分に設計されていないことが多いため、この種の自動化の設定は非常に難しい場合があります。そのため、いくつかの問題を経験しました。それに関するソリューションやワークアラウンドについては、別の投稿で紹介したいと思います。