ONLYOFFICEマクロで大気環境データをモニタリングする方法

[ONLYOFFICE](https://www.onlyoffice.com/ja/)マクロは、私たちの生活をより快適にしています。日々のルーティンワークを自動化し、膨大なデータの塊を操作するのに役立っています。しかし、もし私たちがそれをさらに一歩進めて、より多くの機能を追加したらどうでしょうか？このブログ記事では、ONLYOFFICEマクロのサンプルの1つを、選択した地域の現在の大気質を監視・分析するマクロに変換します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2655664/820f79cb-182f-9897-c5af-7de2b0016a7b.png)

## 空気質API

ONLYOFFICEマクロはJavaScriptベースです。そしてそれは私たちにとって良いニュースです。なぜなら、JavaScriptベースのマクロはAPIリクエストを送ることができ、私たちはそれを使って空気質データを取得することができるからです。

そこで最初のステップは、適切なAPIを選択することです。便宜上、Rapid API プラットフォームを使用しました。このプラットフォームは、さまざまな開発者によるさまざまなAPIを提供しています。また、すべてのAPIにコード・スニペットが付属しています。これは非常に便利で時間効率の良い機能でしょう。私たちはAPI-NinjasのAir Quality APIを選択しました。これは無料で、リクエストの上限もない、そして、私たちは複数のAPIリクエストを処理する必要があるので、Air Quality APIは私たちのプロジェクトに最適です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2655664/51520ba6-b42a-7c96-6e45-ef3c10733c14.png)

**ご注意**！各APIには、固有のアクセスキーが用意されています。安定したパフォーマンスを維持するために、独自のAPIキーを使用することをお勧めします。

## マクロの構築

まず、別の問題を取り上げましょう。このマクロでは、現在の大気質データをライブで表示させたいとのことです。そのためには、一定時間ごとにAPIリクエストを繰り返し送信する必要があります。私たちは、APIドキュメントのマクロサンプルセクションにあるワークシートセル値の再計算マクロで、同様の解決策を実装しました。このマクロは、ローカルの**Api.asc_calculate**メソッドを使用して、1秒間隔でドキュメント全体の再計算を行います。この機能を活かして、データ監視を活用する予定です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2655664/a6c17a73-e2a3-b408-02b3-0ff6ee3878cd.png)

これで、この再計算の間隔内にマクロを構築することができます。Air Qualityマクロは、平均的なAir Quality Indexも計算します。これは、周辺3地域のデータを元に算出します。そのためには、3つのAPIリクエストを送ることになります。まず、ニューヨークの大気質データを取得します。このリクエストは、Rapid API インターフェースを使用して形成しました。*XMLHttpRequest*オプションを選択したのは、リクエストパラメーターの調整という点でより多用途に使えるからです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2655664/a0201819-5f30-1434-9842-28d1eddd0f60.png)

このコード・スニペットをコピー・ペーストし、若干の調整を加えるだけです。まず、データ変数の名前を「*dataNewYork*」に変更します。これは、複数のAPIリクエストを実行することによって、コードをより読みやすくするためです。次に、*withCredentials*オプションを「false」に設定します。これにより、マクロを実行する際にネットワークの問題を回避することができます。また、*console.log*の部分を削除しています。代わりにここにコードを追加します。

```javascript
let timerId = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);
  const dataNewYork = null;
  const xhrNewYork = new XMLHttpRequest();
            xhrNewYork.withCredentials = false;

          xhrNewYork.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
   }
          });
xhrNewYork.open("GET", "https://air-quality-by-api-ninjas.p.rapidapi.com/v1/airquality?city=New%20York");
xhrNewYork.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b")
xhrNewYork.setRequestHeader("X-RapidAPI-Host", "air-quality-by-api-ninjas.p.rapidapi.com");
xhrNewYork.send(dataNewYork);

  }, 10000);
```

マクロでは、CO, NO2, O3, SO2, PM2,5, PM10 の濃度、および全体の AQI を表示します。そこで、JSONからこのデータを抽出する必要があります。

```javascript
 xhrNewYork.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
          const oData = JSON.parse(xhrNewYork.responseText);
          const co = oData.CO.concentration;
          const no2 = oData.NO2.concentration;
          const O3 = oData.O3.concentration;
          const so2 = oData.SO2.concentration;
          const pm25 = oData['PM2.5'].concentration;
          const pm10 = oData.PM10.concentration;
          const overall_aqi = oData.overall_aqi;

   }
          });
```

そして、これらの変数を配列に押し込みます。

```javascript
const arr = [];
              arr.push(co, no2, O3, so2, pm25, pm10, overall_aqi);
```

後ほど、この配列を使って、取得した大気質の値をスプレッドシートのセルに挿入する予定です。しかし、その前に、これらの値にラベルを付けておきましょう。そのためには、アクティブなスプレッドシートを取得し、セルをターゲットにして、**SetValue**メソッドを使用してテキストを挿入します。そして、**AutoFit**メソッドを実行して、セルのサイズを調整します。

```javascript
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("A1").SetValue("CO:");
              oWorksheet.GetRange("A2").SetValue("NO2:");
              oWorksheet.GetRange("A3").SetValue("O3:");
              oWorksheet.GetRange("A4").SetValue("SO2:");
              oWorksheet.GetRange("A5").SetValue("PM2.5:");
              oWorksheet.GetRange("A6").SetValue("PM10:");
              oWorksheet.GetRange("A7").SetValue("OVERALL AQI:"); 
              oWorksheet.GetRange("A1:A7").AutoFit(false, true); 
```

ここで、隣接するセルに大気質の値を挿入します。

```javascript
  let nRow = 0;
              for (let i = 0; i < arr.length; i++) {
          const text = JSON.stringify(arr[i]);   
              oWorksheet.GetRangeByNumber(nRow, 1).SetValue(text);
          nRow++;
              }
```

時間を節約するために、ここでちょっとしたトリックを行いました。各値に対して *SetValue* メソッドを使用する代わりに、配列を繰り返し処理するようにしたのです。そのために、*GetRangeByNumber*メソッドでインデックスとして使用する*nRow*変数を追加して、行を選択するようにしました。反復処理ごとに *nRow* 変数をインクリメントして、次の行に切り替えました。配列の各要素を繰り返し、文字列に変換し、SetValueメソッドで選択された行に値を挿入しています。

もうひとつの素晴らしい機能は、スプレッドシートにグラフを追加することです。そうすれば、データをより見やすくすることができます。私たちは*AddChart*メソッドを利用してそれに取り組みます。

```javascript
const oChart = oWorksheet.AddChart("'Sheet1'!$S$1:$T$7", true, "bar3D", 2, 100 * 36000, 70 * 36000, 20, 2 * 36000, 0, 3 * 36000);
              oChart.SetTitle("Hartford AQI", 13);
              oChart.ApplyChartStyle(5);
```

これで最初のAPIリクエストは終了です。しかし、我々の目標は周辺地域の平均AQIを計算することです。この問題に対処するため、TrentonとHartfordのAPIデータを取得し、数式を適用して平均値を求めることにします。しかし、まず、このデータを受け取る必要があります。そこで、さらに2つのAPIリクエストを追加します。

Trenton

```javascript
const dataTrenton = null;
          const xhrTrenton = new XMLHttpRequest();
          xhrTrenton.withCredentials = false;
          
          xhrTrenton.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
          const oData = JSON.parse(xhrTrenton.responseText);
          const co = oData.CO.concentration;
          const no2 = oData.NO2.concentration;
          const O3 = oData.O3.concentration;
          const so2 = oData.SO2.concentration;
          const pm25 = oData['PM2.5'].concentration;
          const pm10 = oData.PM10.concentration;
          const overall_aqi = oData.overall_aqi;
          const arr = [];
              arr.push(co, no2, O3, so2, pm25, pm10, overall_aqi);
          
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("J1").SetValue("CO:");
              oWorksheet.GetRange("J2").SetValue("NO2:");
              oWorksheet.GetRange("J3").SetValue("O3:");
              oWorksheet.GetRange("J4").SetValue("SO2:");
              oWorksheet.GetRange("J5").SetValue("PM2.5:");
              oWorksheet.GetRange("J6").SetValue("PM10:");
              oWorksheet.GetRange("J7").SetValue("OVERALL AQI:"); 
              oWorksheet.GetRange("J1:K7").AutoFit(false, true); 
              
          let nRow = 0;
              for (let i = 0; i < arr.length; i++) {
          const text = JSON.stringify(arr[i]);   
              oWorksheet.GetRangeByNumber(nRow, 10).SetValue(text);
          nRow++;
              }
          
          const oChart = oWorksheet.AddChart("'Sheet1'!$J$1:$K$7", true, "bar3D", 2, 100 * 36000, 70 * 36000, 11, 2 * 36000, 0, 3 * 36000);
              oChart.SetTitle("Trenton AQI", 13);
              oChart.ApplyChartStyle(5);
            }
          });
```

Hartford:

```javascript
 const dataHartdord = null;
          const xhrHartford = new XMLHttpRequest();
          xhrHartford.withCredentials = false;
          
          xhrHartford.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
          const oData = JSON.parse(xhrHartford.responseText);
          const co = oData.CO.concentration;
          const no2 = oData.NO2.concentration;
          const O3 = oData.O3.concentration;
          const so2 = oData.SO2.concentration;
          const pm25 = oData['PM2.5'].concentration;
          const pm10 = oData.PM10.concentration;
          const overall_aqi = oData.overall_aqi;
          const arr = [];
              arr.push(co, no2, O3, so2, pm25, pm10, overall_aqi);
          
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("S1").SetValue("CO:");
              oWorksheet.GetRange("S2").SetValue("NO2:");
              oWorksheet.GetRange("S3").SetValue("O3:");
              oWorksheet.GetRange("S4").SetValue("SO2:");
              oWorksheet.GetRange("S5").SetValue("PM2.5:");
              oWorksheet.GetRange("S6").SetValue("PM10:");
              oWorksheet.GetRange("S7").SetValue("OVERALL AQI:"); 
              oWorksheet.GetRange("S1:T7").AutoFit(false, true); 
              
          let nRow = 0;
              for (let i = 0; i < arr.length; i++) {
          const text = JSON.stringify(arr[i]);   
              oWorksheet.GetRangeByNumber(nRow, 19).SetValue(text);
          nRow++;
              }
          
          const oChart = oWorksheet.AddChart("'Sheet1'!$S$1:$T$7", true, "bar3D", 2, 100 * 36000, 70 * 36000, 20, 2 * 36000, 0, 3 * 36000);
              oChart.SetTitle("Hartford AQI", 13);
              oChart.ApplyChartStyle(5);
            }
          });
          
          xhrHartford.open("GET", "https://air-quality-by-api-ninjas.p.rapidapi.com/v1/airquality?city=Hartford");
          xhrHartford.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
          xhrHartford.setRequestHeader("X-RapidAPI-Host", "air-quality-by-api-ninjas.p.rapidapi.com");
          xhrHartford.send(dataHartdord);
```

そして最後に、3つの*OVERALL AQI*値をすべて取り出して、計算式に当てはめるのです。

```javascript
const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("N18").SetValue("Average AQI:");
              oWorksheet.GetRange("O18").SetValue("=AVERAGE(B7:K7:U7)");
              oWorksheet.GetRange("N18:O18").AutoFit(false, true); 
```

マクロのコード全体は以下の通りです。

```javascript
(function()
{
    let timerId = setInterval(function(){
            Api.asc_calculate(Asc.c_oAscCalculateType.All);

    // New York air quality API request:
          const dataNewYork = null;
          const xhrNewYork = new XMLHttpRequest();
            xhrNewYork.withCredentials = false;
          
          xhrNewYork.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
          const oData = JSON.parse(xhrNewYork.responseText);
          const co = oData.CO.concentration;
          const no2 = oData.NO2.concentration;
          const O3 = oData.O3.concentration;
          const so2 = oData.SO2.concentration;
          const pm25 = oData['PM2.5'].concentration;
          const pm10 = oData.PM10.concentration;
          const overall_aqi = oData.overall_aqi;
          const arr = [];
              arr.push(co, no2, O3, so2, pm25, pm10, overall_aqi);
          
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("A1").SetValue("CO:");
              oWorksheet.GetRange("A2").SetValue("NO2:");
              oWorksheet.GetRange("A3").SetValue("O3:");
              oWorksheet.GetRange("A4").SetValue("SO2:");
              oWorksheet.GetRange("A5").SetValue("PM2.5:");
              oWorksheet.GetRange("A6").SetValue("PM10:");
              oWorksheet.GetRange("A7").SetValue("OVERALL AQI:"); 
              oWorksheet.GetRange("A1:A7").AutoFit(false, true); 
              
          let nRow = 0;
              for (let i = 0; i < arr.length; i++) {
          const text = JSON.stringify(arr[i]);   
              oWorksheet.GetRangeByNumber(nRow, 1).SetValue(text);
          nRow++;
              }
          
          const oChart = oWorksheet.AddChart("'Sheet1'!$A$1:$B$7", true, "bar3D", 2, 100 * 36000, 70 * 36000, 2, 2 * 36000, 0, 3 * 36000);
              oChart.SetTitle("New York AQI", 13);
              oChart.ApplyChartStyle(5);
            }
          });
          
          xhrNewYork.open("GET", "https://air-quality-by-api-ninjas.p.rapidapi.com/v1/airquality?city=New%20York");
          xhrNewYork.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
          xhrNewYork.setRequestHeader("X-RapidAPI-Host", "air-quality-by-api-ninjas.p.rapidapi.com");
          xhrNewYork.send(dataNewYork);

    //Trenton air quality API request: 
          const dataTrenton = null;
          const xhrTrenton = new XMLHttpRequest();
           xhrTrenton.withCredentials = false;
          
          xhrTrenton.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
          const oData = JSON.parse(xhrTrenton.responseText);
          const co = oData.CO.concentration;
          const no2 = oData.NO2.concentration;
          const O3 = oData.O3.concentration;
          const so2 = oData.SO2.concentration;
          const pm25 = oData['PM2.5'].concentration;
          const pm10 = oData.PM10.concentration;
          const overall_aqi = oData.overall_aqi;
          const arr = [];
              arr.push(co, no2, O3, so2, pm25, pm10, overall_aqi);
          
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("J1").SetValue("CO:");
              oWorksheet.GetRange("J2").SetValue("NO2:");
              oWorksheet.GetRange("J3").SetValue("O3:");
              oWorksheet.GetRange("J4").SetValue("SO2:");
              oWorksheet.GetRange("J5").SetValue("PM2.5:");
              oWorksheet.GetRange("J6").SetValue("PM10:");
              oWorksheet.GetRange("J7").SetValue("OVERALL AQI:"); 
              oWorksheet.GetRange("J1:K7").AutoFit(false, true); 
              
          let nRow = 0;
              for (let i = 0; i < arr.length; i++) {
          const text = JSON.stringify(arr[i]);   
              oWorksheet.GetRangeByNumber(nRow, 10).SetValue(text);
          nRow++;
              }
          
          const oChart = oWorksheet.AddChart("'Sheet1'!$J$1:$K$7", true, "bar3D", 2, 100 * 36000, 70 * 36000, 11, 2 * 36000, 0, 3 * 36000);
              oChart.SetTitle("Trenton AQI", 13);
              oChart.ApplyChartStyle(5);
            }
          });
          
          xhrTrenton.open("GET", "https://air-quality-by-api-ninjas.p.rapidapi.com/v1/airquality?city=Trenton");
          xhrTrenton.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
          xhrTrenton.setRequestHeader("X-RapidAPI-Host", "air-quality-by-api-ninjas.p.rapidapi.com");
          xhrTrenton.send(dataTrenton);

    //Hartford air quality API request:
          const dataHartdord = null;
          const xhrHartford = new XMLHttpRequest();
          xhrHartford.withCredentials = false;
          
          xhrHartford.addEventListener("readystatechange", function () {
            if (this.readyState === this.DONE) {
          const oData = JSON.parse(xhrHartford.responseText);
          const co = oData.CO.concentration;
          const no2 = oData.NO2.concentration;
          const O3 = oData.O3.concentration;
          const so2 = oData.SO2.concentration;
          const pm25 = oData['PM2.5'].concentration;
          const pm10 = oData.PM10.concentration;
          const overall_aqi = oData.overall_aqi;
          const arr = [];
              arr.push(co, no2, O3, so2, pm25, pm10, overall_aqi);
          
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("S1").SetValue("CO:");
              oWorksheet.GetRange("S2").SetValue("NO2:");
              oWorksheet.GetRange("S3").SetValue("O3:");
              oWorksheet.GetRange("S4").SetValue("SO2:");
              oWorksheet.GetRange("S5").SetValue("PM2.5:");
              oWorksheet.GetRange("S6").SetValue("PM10:");
              oWorksheet.GetRange("S7").SetValue("OVERALL AQI:"); 
              oWorksheet.GetRange("S1:T7").AutoFit(false, true); 
              
          let nRow = 0;
              for (let i = 0; i < arr.length; i++) {
          const text = JSON.stringify(arr[i]);   
              oWorksheet.GetRangeByNumber(nRow, 19).SetValue(text);
          nRow++;
              }
          
          const oChart = oWorksheet.AddChart("'Sheet1'!$S$1:$T$7", true, "bar3D", 2, 100 * 36000, 70 * 36000, 20, 2 * 36000, 0, 3 * 36000);
              oChart.SetTitle("Hartford AQI", 13);
              oChart.ApplyChartStyle(5);
            }
          });
          
          xhrHartford.open("GET", "https://air-quality-by-api-ninjas.p.rapidapi.com/v1/airquality?city=Hartford");
          xhrHartford.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
          xhrHartford.setRequestHeader("X-RapidAPI-Host", "air-quality-by-api-ninjas.p.rapidapi.com");
          xhrHartford.send(dataHartdord);
          
          const oWorksheet = Api.GetActiveSheet();
              oWorksheet.GetRange("N18").SetValue("Average AQI:");
              oWorksheet.GetRange("O18").SetValue("=AVERAGE(B7:K7:U7)");
              oWorksheet.GetRange("N18:O18").AutoFit(false, true); 
              
      
              }, 1000);
})();
```

それでは、マクロを実行し、その動きを見てみましょう。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2655664/4706596c-b156-502d-9843-7f4b700590a8.png)

当社のマクロはJava Scriptをベースにしており、非常に汎用的で実用的なものとなっています。この投稿で説明したコンセプトを活用して、独自のマクロを構築していただければと思います。私たちは、議論と協力にオープンです。あなたのアイデアやマクロを私たちと共有しませんか？あなたの探求的な試みが成功することを祈っています。