Monitor air quality data with ONLYOFFICE macro

ONLYOFFICE macros make our life easier. They help us automate our daily routine tasks and operate with huge chunks of data. But what if we take it up a notch and add more functionality to them? In this blog post, we will convert one of the ONLYOFFICE macro samples into a macro that monitors and analyzes the current air quality in the selected region.

Air quality API

ONLYOFFICE macros are JavaScript based. And that’s good news for us because they allow sending API requests, which we will use to retrieve the air quality data.

So our initial step is choosing the suitable API. For convenience, we used the Rapid API platform. It offers various APIs from different developers. Also, every API comes with a code snippet. It’s a very handy and time-efficient feature. We opted for Air Quality API by API-Ninjas. It’s free and has no request cap. And since we need to deal with multiple API requests, the Air Quality API is perfect for our project.

Note! Every API provides you with a unique access key. To maintain stable performance, we strongly recommend you use your own API key.

Building a macro

First, let’s address another issue. We want our macro to display the current air quality data live. And that would require sending the repeated API requests within a certain interval. We implemented a similar solution in the Recalculate worksheet cell values macro, which can be found in the macro samples section of our API documentation. The macro uses the local Api.asc_calculate method to recalculate the entire document within one-second intervals. We will capitalize on this feature to utilize data monitoring.

Now we can build our macro within this recalculating interval. The Air Quality macro also calculates the average Air Quality Index. It is based on the data from three surrounding regions. That would involve sending three API requests. First, we obtain the air quality data from New York. We formed this request using the Rapid API interface. We chose the XMLHttpRequest option because it allows more versatility in terms of tweaking the request parameters:

We just copy-paste the code snippet and make some minor adjustments to it. First, we change the name of the data variable into dataNewYork. It will make our code more readable due to running multiple API requests. Then we set the withCredentials option to false. That will allow us to avoid network issues when running the macro. We also remove the console.log part. We will add our code here instead:

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);

Our macro displays the CO, NO2, O3, SO2, PM2,5, PM10 concentration, and overall AQI. So we need to extract this data from the JSON:

 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;

   }
          });

Then we push these variables into an array:

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

Later on, we will use this array to insert the retrieved air quality values into the spreadsheet cells. But before we do that, let’s label these values. To achieve that, we get the active spreadsheet, then we target the cells and insert text into them by using the SetValue method. And we execute the AutoFit method to adjust the size of the cells:

          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); 

Now we insert the air quality values into the adjoining cells:

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

To save us some time, we did a little trick here. Instead of using the SetValue method for each value, we just iterated through the array. To do that, we added the nRow variable that we used as an index with the GetRangeByNumber method to select the row. We incremented the nRow variable to switch to the next row at each iteration. We iterated through each element in the array, converted it into a string, and used the SetValue method to insert values into the selected rows.

Another nice feature would be adding a chart to the spreadsheet. That would make the data more visible. We tackle it by utilizing the AddChart method:

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);

That wraps up our first API request. However, our goal is to calculate the average AQI in the surrounding area. To address this issue, we will take the Trenton and Hartford API data and apply a formula to find the average. But first, we need to receive this data. So we add two more API requests.

Trenton:

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:

 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);

And finally, we take all three of the OVERALL AQI values and apply a formula to them:

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); 

The entire macro code is the following:

(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);
})();

And now, let`s run our macro and see how it works!

Our macros are Java Script based, which makes them extremely versatile and practical. We kindly encourage you to experiment and hope that you will utilize the concepts outlined in this post to build your own macros. We are open to discussion and cooperation. So don’t hesitate to ask questions and share your ideas or macros with us. Best of luck in your exploratory endeavors!