【Excel Addin】Execute Scheduled Task from Excel Worksheet

This time, I will try to execute the task defined in the [Sharperlight](https://sharperlight.com/) scheduler from the Excel worksheet.

In some cases, data is acquired from the API with a scheduler task, and synchronous processing is executed, such as saving it to your own database. Of course, you will specify the time, say 5:00 every morning, and have the task run periodically. Reporting is done against that synchronized database. However, for example, in a report created on Excel, even if you recalculate, the data synchronized at 5 o'clock in the morning may be already old, and you want the latest data, and you may want to manually execute the task.
In such a case, it is troublesome to start the scheduler and execute the task for that purpose. I would like to introduce how to deal with such cases.
What to do is very easy! Run by clicking the button:grin:

Furthermore, this time, I will explain in an environment where the Sharperlight client is connected to the Sharperlight server by remote connection. When using Excel in the Sharperlight installation environment, I think this is the case in most cases.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d9000c21-c6b5-3316-439b-1f6e10488f40.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/253f3790-0a5b-617f-71e6-acbd1f0ca909.png)
### Create query to perform tasks ###
Now start Excel. Select the appropriate cell and choose **Table** Formula from the Sharperlight ribbon.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5262c590-badc-79ca-7e2b-64fc42082757.png)
The familiar **Query Builder** will launch.
**System** for `Product`, which of course is the System of the Sharperlight server you are connecting to remotely. Specify **Scheduler Execute Task** for the `Table`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/19e6bfd8-ac8a-f6f5-463b-57417596738d.png)
Specify the **Group** and **Code** of the task to be executed in `Unique Code` in this format: **Group.Code**.
The task you specify is of course on the Sharperlight server side.
I defined such an action for this article with the name `_Test.RunFromExcel`.
##### Definition of the task to be performed #####
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8ba5dcf6-66f6-348e-bade-ff9e2226d4b8.png)
The first `Log Message` action that marks the start of the task, the `Wait` action that waits for 20 seconds to process, and the last `Log Message` action that marks the end of the task.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/fb3d1b5a-26f2-6731-5820-395c410eb8f2.png)
Finally, give execute permission on the `Security` tab. For now, omit the password.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/29891c0c-f8e1-3602-b1f5-95147fba5913.png)
### Creating query to perform tasks (cont.) ###
Specify the task introduced above in the query on the Excel side like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8ce2b9b0-6332-b34c-c650-ee8f241cbd59.png)
Also set the `Mode` to Filter from the selection area and specify **Synchronus** for the filter value.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/57f45b7e-fad8-bf11-b01e-0ec1740d1ea3.png)
The final output column settings should look like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ea4f202a-cf2a-7485-5356-50b60a57722a.png)
From the `Selection` area set the **Unique Code** to the `Outputs` area and change the description to **Tasks Executed**.
Set the **Response** from the `Selection` area to the `Outputs` area. The **Response** will return the time taken to execute the task in seconds. As it is, it will be returned as a `Text` value, so I wanted to treat it as a `Number` in Excel, so I will convert it to a number using the `Expression` defined below. And since this original is not necessary to be displayed, it is hidden (Hidden).

The content of the `Expression`, which converts a text to a number with the Val( ) function.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e7704b80-ea46-9e05-0bc6-979f5b16c846.png)

Next, I used a `Sub Query` to get and display the time when the task started and finished.
The `Sub Query` looks like this. The **Scheduler Task Last Run** table is used to get the `Start Date Time (%StartDT)` and the `Finish Date Time (%EndDT)`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/b99f4622-baa6-13cb-11e4-5c2f1922966b.png)
This is extracted and displayed using an Expression on the parent query side. This figure shows the `Start Date Time`. The same goes for the `Finish Date Time`.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6310004b-dedf-e547-0fd3-3a6550b3bdcd.png)
The `Sub Query` doesn't need to be displayed, so it must be hidden.
Confirm and execute with the `OK` button. Processing is executed for 20 seconds and a table like this is displayed.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/c9847439-4cee-78f5-6e8e-9e2242c97aa9.png)
If the Total row is displayed, turn it off using the Table Design function in Excel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f7dd2f5e-1d46-517e-c75a-5e6bd15c8216.png)
The table formula is defined in cell **D5** because the selected cell was **D5** when designing the query was started.
As it is, this formula will be executed every time something changes in the worksheet, so convert it to a `Delayed Formula`.
Add **DL** after the formula name.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/e65cdb81-0412-1ec4-ca83-bb0cb11dcbd2.png)
After that, on the Excel side, set `Seconds Taken` to 3 decimal places and the table is complete!
### Add button ###
Select cell **B3** and select `Sharperlight XL` -> `Edit Button/Table Bindings` from the right-click menu.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/93b2199e-88e5-815d-3954-e3ab7b125e50.png)
The **Button Table Binding** dialog will appear.
First, add a button by clicking the + (plus) icon. Next, fill in the Query Name, the cell where the formula is defined, and the Table Name, and press the `OK` button.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d4213dd5-dd35-3dac-93fa-092eea47d401.png)
Change the button caption to **Run Task**.
The formula performs the task when you click the button. After binding the formula to the button and confirming its execution, the formula is no longer needed on the worksheet, so delete it from cell **D5**.

Now I can run the task from Excel.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6880a0aa-75cc-7606-eb00-8dd55dca3c90.png)

Thank you for reading this article.