Create a new snippet from a blank template.

ブラウザのFetch APIで天気情報を取得してみよう.EXCEL.yaml

name: ブラウザのFetch APIで天気情報を取得してみよう
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
  content: >
    $("#run").click(() => tryCatch(run));


    type weather = {
      date: string; //"26日(火)";
      forecast: string; //"曇時々晴";
      mintemp: string; //"29";
      maxtemp: string; //"29";
      poptimes: string; //"-/-/20";
      waves: string; //"０．５メートル";
      winds: string; //"東の風";
      weathers: string; //"くもり　時々　晴れ　所により　夜遅く　雨";
    };


    async function run() {
      await Excel.run(async (context) => {
        const data = await weather();
        let sheet = context.workbook.worksheets.getItemOrNullObject("天気");
        await context.sync();

        if (sheet.isNullObject) {
          sheet = context.workbook.worksheets.add("天気");
        }

        const tokyo = data[319]; // 東京の情報 --- (*3)
        await display(sheet, "天気", tokyo);

        await context.sync();
      });
    }


    /** Default helper for invoking an action and handling errors. */

    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }


    const display = async (sheet: Excel.Worksheet, tableName: string, data:
    weather[]) => {
      let table = sheet.tables.getItemOrNullObject(tableName);
      await sheet.context.sync();

      if (!table.isNullObject) {
        // テーブルの行をすべて削除します
        table.rows.load("items");
        await sheet.context.sync();
        for (let i = table.rows.items.length - 1; i >= 0; i--) {
          table.rows.items[i].delete();
        }
        await sheet.context.sync();
      } else {
        // テーブルが存在しない場合は新しいテーブルを作成します
        table = sheet.tables.add("A1:H1", true);
        table.name = tableName;
        table.getHeaderRowRange().values = [["日", "予報", "最小気温", "最大気温", "予想気温", "風速", "風向き", "天気"]];
      }

      const newData = data.map((item: weather) => [
        item.date,
        item.forecast,
        item.mintemp,
        item.maxtemp,
        item.poptimes,
        item.waves,
        item.winds,
        item.weathers
      ]);

      // 新しいデータをテーブルに追加します
      table.rows.add(null, newData);
      await sheet.getUsedRange().format.autofitColumns();
      await sheet.getUsedRange().format.autofitRows();
    };


    const weather = () => {
      // 天気APIにアクセス --- (*1)
      const api = "https://api.aoikujira.com/tenki/week.php?fmt=json&city=319";
      return fetch(api)
        .then((res) => res.json())
        .then((data) => tenki(data));

      // 結果を表示 --- (*2)
      function tenki(data) {
        let s = "";
        const tokyo = data[319]; // 東京の情報 --- (*3)
        // 1日ずつのデータを表示 --- (*4)
        for (let row of tokyo) {
          console.log(row); // デバッグ表示
          s += `
          <h3>${row["date"]}の天気</h3>
          <ul><li>${row["forecast"]}</li>
          <li>降水確率: ${row["poptimes"]}%</li></ul>
        `;
        }
        document.write(s);

        return data;
      }
    };
  language: typescript
template:
  content: >
    <meta http-equiv="Content-Security-Policy"
    content="upgrade-insecure-requests">

    <button id="run" class="ms-Button">
        <span class="ms-Button-label">Run</span>
    </button>
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }

    section.samples .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