Office Scriptsを使ってCopilot風のサンプルデータ作成機能をExcelに搭載する

# はじめに

`Power Apps`には、`プロンプト`から`Dataverse`の`テーブル`を作成するという[非常にCoolな機能が追加されています。](https://learn.microsoft.com/ja-jp/power-apps/maker/canvas-apps/ai-conversations-create-app)

https://learn.microsoft.com/ja-jp/power-apps/maker/canvas-apps/ai-conversations-create-app

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/35925895-2d2e-8e87-1493-d9bd5440227b.png)

サンプルデータを作成してくれることは、ありがたい・・・。
**なんだかんだこういったサンプルデータを活用するシーンって多いな**と感じています。

少なくとも自分で一から考えるのは大変です。

# GPTを活用すればいいのでは？
もちろん`ChatGPT`を使えば一発で解決する話です。
サイトにアクセスしてプロンプトを打てばOK!!

*ChatGPTにアクセスして、プロンプトさえ渡せば*解決するんです。
ただそれがもう手間。

表データの取り扱いは、*Excel*でなんだかんだ調整するシーンが多いため、この機能を*Excel*にも追加したい！

そこで`GPT-4o`の*API*をたたく際に、JSON フォーマットを指定すれば、うまい具合にパースできるのでは🧐という仮説を立てたため、今回検証してみました🐟

:::note info
この記事は2024.06.16時点の記事です。`Copilot for Microsoft 365`にありそうな機能ですが、使えない状況の個人利用を想定しています。
標準で配置されそうな機能ですが、もう今必要なもの！ということでアウトプットします！
:::

# Excelからのシナリオを考える
[先日のQiitaの記事](https://qiita.com/DEmodoriGatsuO/items/b1cc2cece525810e8430#excel%E3%81%A7power-automate%E3%82%92%E5%91%BC%E3%81%B3%E5%87%BA%E3%81%99)でも紹介させていただきましたが、Excelから`デスクトップアプリ`、`ブラウザ`問わず、`Power Automate`を実行できます。

https://qiita.com/DEmodoriGatsuO/items/b1cc2cece525810e8430#excel%E3%81%A7power-automate%E3%82%92%E5%91%BC%E3%81%B3%E5%87%BA%E3%81%99

https://learn.microsoft.com/ja-jp/power-automate/flows-excel

> [ 自動化 ]タブの[ 作業の自動化 ]、[ このブック内のフロー ]から、`Power Automate`が起動できます。
>> [ExcelからOffice Scripts経由で、Outlookの連絡先を追加する（with Graph API）](https://qiita.com/DEmodoriGatsuO/items/b1cc2cece525810e8430#excel%E3%81%A7power-automate%E3%82%92%E5%91%BC%E3%81%B3%E5%87%BA%E3%81%99)

1. `Excel`からサンプルデータが欲しいテーマを指定し
1. `Power Automate`でプロンプトを作成、`GPT-4o`からサンプルデータである`JSON`を取得
1. `Office Scripts`で`JSON`を解析し、ブックに追加

上記のアプローチで進めてみましょう。

# Excelの準備
前述のとおり`[ 自動化 ]タブの[ 作業の自動化 ]、[ このブック内のフロー ]から、Power Automateが起動できます。`

テンプレートをクリックし、Excelから呼び出す`Power Automateフローの準備をします`

## 例
1. テンプレートのうち`Send Bulk Emails`を選択、作成をクリック
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/108ebada-7e36-4b0a-dce1-53a1cc9a74ca.png)
1. `このブック内のフロー`に追加された場合、`Power Automate`の画面でフローを編集できます
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/15892d19-df5f-2aff-1168-38557a5df40f.png)

:::note info
`Office Scripts`の解説は後述します
:::

# Power Automateの準備
前述で追加した`Power Automate`フローを全部消去します。

## フローの流れ
1. フローを手動でトリガーする - `Excel`の画面から作成したいデータのテーマを受け取る
1. 作成 - `JSON`配列入力のスキーマを`GPT-4o`で作成するためのプロンプトを作成
1. シークレットの取得[Azure Key Vault] - `Azure Key Vault`に格納した`OpenAI`の`APIキー`を取得
1. `HTTP 要求`で`GPT-4o`の`API`を使用し、サンプルデータの`JSON Schema`を作成
1. 作成 - サンプルデータを取得するためのプロンプトを作成
1. `HTTP 要求`で`GPT-4o`の`API`を使用し、サンプルデータを作成
1. `Office Scripts`で`Excel`にシートを追加し、サンプルデータを追加

**GPT-4oのAPIを2回使用しています。**
様々なデータのスキーマの定義も`GPT-4o`にやってもらおう！という思想の元です。
ここもできてしまうわけですから、`GPT-4o`はとんでもないなと思います。

### 1. フローを手動でトリガーする - `Excel`の画面から作成したいデータのテーマを受け取る
文字列の入力を追加するのみです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/2b990fd9-88de-5037-162b-8f278c9a5c28.png)

### 2. 作成 - `JSON`配列入力のスキーマを`GPT-4o`で作成するためのプロンプトを作成
`GPT-4o`で利用するためのプロンプトです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/fc1f6870-a6cc-c072-1c9f-56b1434782f2.png)

`JSON Schema {\"type\":\"string\"}}`と指定することにより、サンプルデータを作成するときのための`JSON Schema`がエスケープ文字込みの状態で、うまい具合に作られます。

```json:JSONスキーマを定義するためのプロンプト
{
  "model": "gpt-4o",
  "messages": [
    {
      "role": "system",
      "content": "与えられたプロンプトに対して、ダミーデータをJSONで作成します。配列入力のスキーマを提供してください。"
    },
    {
      "role": "user",
      "content": "@{起動時に受け取るデータのテーマ}のダミーデータのスキーマを作成してください。 ## JSON Schema {\"type\":\"string\"}}"
    }
  ],
  "temperature": 0.8,
  "max_tokens": 2000,
  "response_format": {
    "type": "json_object"
  }
}
```

### 3. シークレットの取得 [Azure Key Vault] - `Azure Key Vault`に格納した`OpenAI`の`APIキー`を取得

:::note info
Azure Key Vaultの取得は以前から[ブログ](https://qiita.com/DEmodoriGatsuO/items/720e417efc1032ff7649)で複数回取り上げているため、割愛します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/389a1dfc-1591-2b40-57a4-5fe8bbf9fc1b.png)

:::

https://qiita.com/DEmodoriGatsuO/items/720e417efc1032ff7649

https://qiita.com/DEmodoriGatsuO/items/2fae27220017f5f205ea

### 4. `HTTP 要求`で`GPT-4o`の`API`を使用し、サンプルデータの`JSON Schema`を作成
[私のQiita記事では最高に反響があった記事](https://qiita.com/DEmodoriGatsuO/items/4c4734b78973970e0d3d)のアプローチと同様です。

https://qiita.com/DEmodoriGatsuO/items/4c4734b78973970e0d3d

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/4f7dd3c9-8aff-8ad5-1faf-69b6802ec74d.png)

■ HTTP

| キー | 値 |
|---|---|
| URI | https://api.openai.com/v1/chat/completions |
| Method | POST |

ヘッダー

| キー | 値 |
|---|---|
| Content-Type | application/json |
| Authorization | Bearer `@{outputs('シークレットの取得')?['body/value']}` |

`Body`は前述の(2)`作成`アクションで作成した`JSON`

```plaintext:body
@{outputs('Prompt')}
```

### 5. 作成 - サンプルデータを取得するためのプロンプトを作成
(4)で`GPT-4o`から取得した`JSON Schema`は`body('CreateSchema')?['choices'][0]?['message']?['content']`に格納されます。
※ `CreateSchema`は私の設定した`JSONスキーマ`を作成するためのアクション名です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/365fb235-0810-04d8-852b-ebaff4e591a5.png)

こちらを活用して、あらためてプロンプトを作成!!

```json:プロンプト
{
  "model": "gpt-4o",
  "messages": [
    {
      "role": "system",
      "content": "与えられたプロンプトに対して、ダミーデータを提供してください。日本の情報をベースに作成してください。"
    },
    {
      "role": "user",
      "content": "JSON配列入力形式で@{outputs('InputData')}のダミーデータを作成してください。配列のキー値はvaluesで統一してください。 ## JSON Schema {\"type\":\"array\",\"items\":@{body('CreateSchema')?['choices'][0]?['message']?['content']}]}}"
    }
  ],
  "temperature": 0.8,
  "max_tokens": 2000,
  "response_format": {
    "type": "json_object"
  }
}
```

### 6. `HTTP 要求`で`GPT-4o`の`API`を使用し、サンプルデータを作成
(4)と一緒ですね。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/bd777c90-e3fa-8e30-13ec-0468b10c39ce.png)

■ HTTP

| キー | 値 |
|---|---|
| URI | https://api.openai.com/v1/chat/completions |
| Method | POST |

ヘッダー

| キー | 値 |
|---|---|
| Content-Type | application/json |
| Authorization | Bearer `@{outputs('シークレットの取得')?['body/value']}` |

`Body`は前述の(2)`作成`アクションで作成した`JSON`

```plaintext:body
@{outputs('CreateDataPrompt')}
```

このアクションの戻り値`body('HTTP-CreateDummyData')?['choices'][0]?['message']?['content']`を`Office Scripts`に渡して完了です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/5f781278-c7d1-6ca7-7957-b0fa6b5cb9c2.png)

# Office Scripts
まずは手っ取り早くコードを載せてしまいます。
GPT様様で作成しています。

```typescript:Office Scripts
function main(workbook: ExcelScript.Workbook, jsonInput: string) {
  // ネストされたオブジェクトをフラット化するヘルパー関数
  function flattenObject(obj: Record<string, unknown>): Record<string, string | number> {
    const toReturn: Record<string, string | number> = {};

    for (const key in obj) {
      // プロトタイプチェーン上のプロパティは無視
      if (!Object.prototype.hasOwnProperty.call(obj, key)) continue;

      const value = obj[key];

      // プロパティがオブジェクトの場合,フラット化
      if (typeof value === 'object' && value !== null) {
        const flatObject = flattenObject(value as Record<string, unknown>);
        for (const nestedKey in flatObject) {
          if (!Object.prototype.hasOwnProperty.call(flatObject, nestedKey)) continue;

          // ネストされたプロパティを結合して新しいキーを作成
          toReturn[`${key}.${nestedKey}`] = flatObject[nestedKey];
        }
      } else {
        // プロパティがオブジェクトでない場合、追加
        toReturn[key] = value as string | number;
      }
    }
    return toReturn;
  }

  // 新しいシート名を生成するヘルパー関数
  function generateUniqueSheetName(baseName: string, workbook: ExcelScript.Workbook): string {
    let newSheetName = baseName;
    let counter = 1;
    while (workbook.getWorksheet(newSheetName)) {
      newSheetName = `${baseName} (${counter})`;
      counter++;
    }
    return newSheetName;
  }

  try {
    // 1. JSON文字列をオブジェクトに変換
    const parsedJson = JSON.parse(jsonInput) as { values: Record<string, unknown>[] };
    // 配列のキーはプロンプトで`values`に固定
    const data = parsedJson.values;

    if (!Array.isArray(data) || data.length === 0) {
      throw new Error("Invalid or empty data array");
    }

    // 2. 新しいシート名を生成し、追加
    const newSheetName = generateUniqueSheetName("Parsed Data", workbook);
    const newSheet = workbook.addWorksheet(newSheetName);

    // 3-1. 最初のデータオブジェクトをフラット化して、ヘッダー行を設定
    const headers = Object.keys(flattenObject(data[0]));
    newSheet.getRangeByIndexes(0, 0, 1, headers.length).setValues([headers]);

    // 4. 各データオブジェクトをフラット化してシートに設定
    const values = data.map(item => {
      const flattenedItem = flattenObject(item);
      return headers.map(header => flattenedItem[header]);
    });
    newSheet.getRangeByIndexes(1, 0, values.length, headers.length).setValues(values);

  } catch (error) {
    console.log("Error processing JSON input: ", error.message);
    const errorSheetName = generateUniqueSheetName("Error", workbook);
    const errorSheet = workbook.addWorksheet(errorSheetName);
    errorSheet.getRange("A1").setValue(`Error processing JSON input: ${error.message}`);
  }
}
```

1. 受け取った`JSON`を配列に変換
1. シート名重複時対策に、シート追加の関数を追加
1. 二次元配列に固定化するためのフラット化の関数を実行
1. 新しいシートにサンプルデータを作成

上記のことを実施しています。

スクリプトの中でも`parsedJson.values`は、GPTが二次元配列のキーを`values`に設定してくれなければ上手くいきません。

```typescript
const data = parsedJson.values;
```

# 実際の挙動は・・・！
このアクションは`Web 用 Excel`ではないと、コンフリクトを起こし、失敗します。
実験するときは`Web 用 Excel `でやりましょう。

`フローを実行`すると、`プロンプト`の入力画面がでます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/8d7adf75-6bc3-2378-eeb7-57b5749283e4.png)

`人事`と打って、実行します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/9445834c-a0d9-f433-c153-711a3f707f2b.png)

おお！次は`営業活動`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/5630bc63-2af4-865e-dcf8-fd4fd9f88d62.png)

`経費精算`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/7f59e378-279d-bb8c-ed17-7397e41e0f1d.png)

`製品`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/40478057-d968-2038-816b-5cbd2eb465f7.png)

なんだかんだ上手くいくものですね。
エラーも想定外に発生しません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2734332/e3f0710d-42f6-c8bc-2d28-396689814f10.png)

やっぱり`GPT-4o`は凄すぎる！！

今回も読んでくださり、ありがとうございました。🐟✨