Run a Openapi V3 query test for new partner

OpenApiV3 with metadata.EXCEL.yaml

name: OpenApiV3 with metadata
description: 'Run a Openapi V3 query test for new partner '
host: EXCEL
api_set: {}
script:
  content: |
    interface PartnerDic {
      [partnerId: string]: PartnerInfo;
    }

    interface PartnerInfo {
      PartnerId: string;
      PartnerName: string;
      Character: string;
      ProdUrl: string;
      ProdId: string;
      PPEUrl: string;
      PPEId: string;
      AppKey: string;
    }

    let PARTNERS: PartnerDic = {};

    tryCatch(setup);

    $("#run").click(() => tryCatch(run));

    console.log(_.VERSION);

    async function run() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Queries");
        let uRange = sheet.getUsedRange();
        uRange.load("address");
        uRange.load("rowCount");
        await context.sync();

        let count = uRange.rowCount;
        if (count > 1500) {
          count = 1500;
        }

        let qRanges = sheet.getRange("C2:C" + count.toString()).load("values");

        await context.sync();
        let queries = qRanges.values;

        let responseTimes = [];

        for (let i = 0; i < queries.length; i++) {
          if (queries[i] == undefined || queries[i][0].toString() == "") {
            break;
          }

          let startTime = Date.now();
          let response = await getApiQuery(queries[i][0])
            .then((res) => {
              return res.json();
            })
            .catch((error) => console.log(error));

          let t = Date.now() - startTime;
          let bRanges = sheet.getRange("D" + (2 + i).toString() + ":F" + (2 + i).toString());
          let p = Math.floor((i / queries.length) * 100 + 2);
          if (p > 100) {
            p = 100;
          }
          if (i == queries.length - 1) {
            p = 100;
          }

          if (p < 100) {
            $("#run").prop("disabled", true);
          } else {
            $("#run").prop("disabled", false);
          }

          $("#Progress").text("进度 " + p.toFixed(0) + "%");

          if (response == undefined || response.length == 0) {
            bRanges.values = [["No Reply", t, null]];
          } else {
            bRanges.values = [[response[0].content.text, t, JSON.stringify(response[0].content.metadata)]];
          }

          context.sync();
        }
      });
    }

    async function getApiQuery(msg) {
      let pid = $("#PartnerId")
        .find(":selected")
        .val();

      let p = PARTNERS[pid.toString()];

      let isSingleTurn = $("#SingleTurn").is(":checked");

      let env = $("#Env")
        .find(":selected")
        .val();

      let url = "";
      let subscriptionKey = "";
      switch (env) {
        case "prod":
          subscriptionKey = p.ProdId;
          url = p.ProdUrl;
          break;
        case "PPE":
          subscriptionKey = p.PPEId;
          url = p.PPEUrl;
          break;
        default:
          break;
      }
      if (url.startsWith("http")) {
        url = url.replace("http://", "http://localhost:3000/");
      } else {
        url = "http://localhost:3000/" + url + "";
      }
      console.log(url);
      let uuid =
        Date.now().toString(36) +
        Math.random()
          .toString(36)
          .substring(2);

      if (!isSingleTurn) {
        uuid = "Test-User-Id-From-Excel";
      }

      let data = {
        senderId: uuid,
        msgId: uuid,
        content: { metadata: {}, text: msg },
        timestamp: Date.now()
      };

      if (p.Character) {
        data.content.metadata["Character"] = p.Character;
      }
      let ts = getTime();
      let body = JSON.stringify(data);
      let signature = calcuSignature(p.AppKey, ts, body);
      return fetch(url, {
        headers: new Headers({
          "Content-Type": "application/json",
          Accept: "application/json",
          "subscription-key": subscriptionKey,
          Signature: signature,
          timestamp: ts.toString()
        }),
        method: "POST",
        body: JSON.stringify(data)
      });
    }

    function getTime() {
      let timestamp = new Date().getTime();
      return parseInt(timestamp.toString().substr(0, 10));
    }

    function calcuSignature(appkey, timestamp, body) {
      let bodyStr = body;
      let stringToCheckSum = bodyStr + appkey + timestamp;
      let signature = CryptoJS.SHA512(stringToCheckSum).toString(CryptoJS.enc.Hex);
      return signature;
    }

    async function setup() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Partners");
        let uRange = sheet.getUsedRange();
        uRange.load("address");
        uRange.load("rowCount");
        await context.sync();

        let count = uRange.rowCount;
        const partners = sheet.getRange("A2:H" + count.toString());
        partners.load("values");
        await sheet.context.sync();

        let c = context.workbook.getActiveCell();

        $("#PartnerId")
          .children()
          .remove()
          .end();

        partners.values.forEach(function(item) {
          $("#PartnerId").append("<option>" + item[0] + "</option>");
          PARTNERS["" + item[0]] = {
            PartnerId: item[0],
            PartnerName: item[1],
            Character: item[2],
            ProdUrl: item[3].toLowerCase(),
            ProdId: item[4],
            PPEUrl: item[5].toLowerCase(),
            PPEId: item[6],
            AppKey: item[7]
          };
        });

        $("#PartnerId").on("change", function() {
          let pid = $(this)
            .find(":selected")
            .val();

          let p = PARTNERS[pid.toString()];
          $("#PartnerName").text(p.PartnerName);
          $("#Character").text(p.Character);
          $("#ProdUrl").text(p.ProdUrl);
          $("#ProdId").text(p.ProdId);
          $("#PPEUrl").text(p.PPEUrl);
          $("#PPEId").text(p.PPEId);
          $("#AppKey").text(p.AppKey);
        });

        await context.sync();
      });
    }

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        console.log(error);
        $("Error").text(error);
      }
    }
  language: typescript
template:
  content: "<section class=\"xiaoice ms-font-m\">\n\t<h3>Xiaoice OpenAPI Api Tester</h3>\n\t<p><b>使用说明:</b></p>\n\t<p>\n\t\t1. 把需要跑的数据复制到C列，点击PartnerId的下拉列表，选择一个Partner。<br />\n        2. 默认是prod的环境，如果需要切换，选择环境下拉菜单选项<br />\n        3. 默认是单轮的，如果需要多轮对话，取消单轮的选项<br />\n\n    </p>\n\t\t<p>\t\t\n        PartnerName: <span id=\"PartnerName\"></span><br>\n        Character: <span id=\"Character\"></span><br>\n        ProdUrl: <span id=\"ProdUrl\"></span><br>\n        ProdId: <span id=\"ProdId\"></span> <br>\n        PPEUrl: <span id=\"PPEUrl\"></span><br>\n        PPEId: <span id=\"PPEId\"></span><br>\n        AppKey: <span id=\"AppKey\"></span><br>\n\t\t\t<p> Partner:\n\t\t\t\t<select id=\"PartnerId\" Size=\"1\"></select> <br /><br />\n\n        单轮聊天: <input type=\"checkbox\" id=\"SingleTurn\" checked=\"checked\" title=\"单轮\" /> <br />subscriptionKey<br />:\n\t\t\t\t<select id=\"Env\" Size=\"1\">\n            <option value=\"PPE\">PPEId</option>\n            <option value=\"prod\" selected=\"true\">ProdId</option>\n        </select>\n\n\n\t\t\t</p>\n\t\t\t<p><span id=\"Progress\">进度 0%</span></p>\n\t\t\t<!--\t\t<button id=\"setup\" class=\"ms-Button\">\n\t\t\t        <span class=\"ms-Button-label\">初始化</span>\n\t\t\t    </button>-->\n\t\t\t<button id=\"run\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">开始</span>\n    </button>\n\n\t\t\t<p>\n\t\t\t\t<p id=\"Error\"></p>\n</section>"
  language: html
style:
  content: |
    section.xiaoice {
        margin-top: 20px;
    }

    section.xiaoice .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |-
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1

  moment@2.29.1
  @types/moment

  lodash@4.17.21
  @types/lodash

  https://unpkg.com/crypto-js@4.0.0/crypto-js.js
  @types/crypto-js