Insert Google Search results into a spreadsheet with ONLYOFFICE macro

Web search has become an essential part of our life. We do it on a daily basis. But what if we need to store the search results somewhere? In a spreadsheet for instance? It is a very convenient way to operate with your data. In this blog post, we will show you how to create an ONLYOFFICE macro that inserts Google Search results into a spreadsheet.

 

Choosing Google Web Search API

API stands for Application Programming Interface, and it is a cornerstone of the internet these days. It is everywhere. Be it a weather forecast app or a messenger on your phone. API is a protocol that a web application uses to communicate with a server. Needless to say that retrieving Google Search results requires API as well.

For convenience, we suggest obtaining Google Web Search API through the Rapid API platform. It is a marketplace used by over one million developers to discover and connect to thousands of APIs. It offers free, freemium, and paid plans. Every API comes with a code snippet which saves you a lot of time.

Upon a successful registration at Rapid API, we look for the Google Web Search API. At this moment, there are several options available.

To test our macro, we chose this API. It offers a freemium plan with 300 requests a month. This plan will be enough to see what our macro is capable of. In case you need more requests, you can choose a different API or subscribe to a paid plan.

Note! Every API provides you with a unique access key. Your macro will work only with the APIs that you subscribed to.

 

Setting API parameters

On the API page, we can see what parameters can be applied to our search request. We will leave the Rapid API, Request URL, and Header Parameters intact. That’s what our API needs to interact with the server. The Required Parameters contain our search request. Here we put “ONLYOFFICE editors.”

Also, you can add some optional parameters to your request. In our case, we can specify what pages we want to retrieve, the number of results ( the maximum is 100, and the default value is 20), the locale of the search request, the time filter, and the country. We are leaving these parameters default. So the retrieved data will contain 20 results in English, starting from the first page with no time filter applied.

Now we need to choose a language in which we will be making our API request. We will be making an XML/HTTP request in JavaScript. So we go to the right panel, and under the Code Snippets, we select JavaScript > XML/HttpRequest.

Before we proceed to our macro, let’s see what data comes back. We can do it right on this page. For that, we need to click the Test Endpoint button. The results will be displayed in the right panel.

The API request returned 2 items. What we are looking for is the results array. It contains the search results that we are going to be pasting into our spreadsheet. If we take a closer look at it, we will see that every search result array has the title, link, and snippet elements. Later on, we will fetch only the title and link elements and insert them into our spreadsheet. But now we go back to the Code Snippets and copy our XML/HTTP request.

 

Insert two lines in one column

In this variation of our macro, we will combine the title and the link values and insert them into one column together.

So, we open ONLYOFICE Editors, create a new macro and paste the XML/HTTP request that we copied above. We go to the if statement that starts after the readystatechange event and we remove the line with the console.log method. We don’t need it. We will add our own code here.

First, we need to convert the data that we retrieve from the request into a JSON format. To achieve that, we use the JSON.parse method and store the result in the oData variable:

(function()
{
   const data = null;

  const xhr = new XMLHttpRequest();
  xhr.withCredentials = true;

  xhr.addEventListener("readystatechange", function () {
    if (this.readyState === this.DONE) {
      const oData = JSON.parse(xhr.responseText);

Then we start filtering the data. First, we fetch the results array and store it in a separate variable:

const { results } =  oData;

Now we can access the title and the link elements in this array. In our next step, we use the map method to combine the title and the link values and store them in the dataComb variable. This way they will form one line and insert it into the spreadsheet cells later on:

 const dataComb = results.map((item) => ({
      title: item.title,
      link: item.url,
    }));

So we have the dataComb variable that contains elements. Each element includes the title and the link value. Our goal now is to insert them into cells. To do that we create a for loop. It will iterate through each element in the variable, convert it into a string and then insert it into a corresponding cell:

 let nRow = 0;
      for (let i = 0; i < dataComb.length; i++) {
       const text = JSON.stringify(dataComb[i]); 
        var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRow, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRow, 0).AutoFit(false, true);
  nRow++;
      }

First, we created the nRow variable. Later on, we used it 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 dataComb variable, converted it into a string, and used the SetValue method to insert values into the selected rows. To achieve that, we took the i variable that we had declared in the parameters and used it as an index. Also, we used the AutoFit method to adjust the cell size.

The full macro code is the following:

(function()
{
   const data = null;

  const xhr = new XMLHttpRequest();
  xhr.withCredentials = true;

  xhr.addEventListener("readystatechange", function () {
    if (this.readyState === this.DONE) {
      const oData = JSON.parse(xhr.responseText);
      const { results } =  oData;
      const dataComb = results.map((item) => ({
      title: item.title,
      link: item.url,
    }));
    let nRow = 0;
      for (let i = 0; i < dataComb.length; i++) {
       const text = JSON.stringify(dataComb[i]); 
        var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRow, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRow, 0).AutoFit(false, true);
  nRow++;
      }
    }
  });

  xhr.open("GET", "https://google-search26.p.rapidapi.com/search?q=Onlyoffice%20editors&hl=en&tbs=qdr%3Aa");
  xhr.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
  xhr.setRequestHeader("X-RapidAPI-Host", "google-search26.p.rapidapi.com");

  xhr.send(data);
})();

Now let’s run our macro and see how it works:

Insert lines into separate columns

In this variation of our macro, we will insert the title and the link values into two separate columns.

To achieve that, we use the map method to save the title and the link values into two variables:

 const { results } =  oData;
    
      const title = results.map((item) => item.title);
      const link = results.map((item) => item.link);

Now we create two for loops. One will iterate through the title variable and insert the values into the first column:

   let nRowTitle = 0;
      for (let i = 0; i < title.length; i++) {
       const text = JSON.stringify(title[i]); 
          var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRowTitle, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowTitle, 0).AutoFit(false, true);
  nRowTitle++;
      }

Another will iterate through the link variable and insert the values into the second column:

 let nRowLink = 0;
       for (let i = 0; i < link.length; i++) {
       const text = JSON.stringify(link[i]); 
  oWorksheet.GetRangeByNumber(nRowLink, 1).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowLink, 1).AutoFit(false, true);
  nRowLink++;
       }

Note that we changed the second parameter of the GetRangeByNumber method to 1. This parameter corresponds to the position of the columns. This way, the link values will be inserted into the second column.

The full macro code is the following:

(function()
{
    const data = null;

  const xhr = new XMLHttpRequest();
  xhr.withCredentials = true;

  xhr.addEventListener("readystatechange", function () {
    if (this.readyState === this.DONE) {
      const oData = JSON.parse(xhr.responseText);
      const { results } =  oData;
      const title = results.map((item) => item.title);
      const link = results.map((item) => item.link);
    let nRowTitle = 0;
      for (let i = 0; i < title.length; i++) {
       const text = JSON.stringify(title[i]); 
          var oWorksheet = Api.GetActiveSheet();
  oWorksheet.GetRangeByNumber(nRowTitle, 0).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowTitle, 0).AutoFit(false, true);
  nRowTitle++;
      }
     let nRowLink = 0;
       for (let i = 0; i < link.length; i++) {
       const text = JSON.stringify(link[i]); 
  oWorksheet.GetRangeByNumber(nRowLink, 1).SetValue(text);
  oWorksheet.GetRangeByNumber(nRowLink, 1).AutoFit(false, true);
  nRowLink++;
       }
    }
  });

xhr.open("GET", "https://google-search26.p.rapidapi.com/search?q=Onlyoffice%20editors&hl=en&tbs=qdr%3Aa");
xhr.setRequestHeader("X-RapidAPI-Key", "3a059ddf9bmshc40ba13a409d0abp12b76bjsn8fd6f316f49b");
xhr.setRequestHeader("X-RapidAPI-Host", "google-search26.p.rapidapi.com");

xhr.send(data);
})();

Now, let’s run our macro:

We hope that this macro will help automate your routine tasks and structure data in a more convenient way. It is just a fraction of what ONLYOFFICE macros can do.

Our macros are JavaScript based, which makes them extremely versatile and easy to tailor to your needs. We kindly encourage you to experiment and create your own macros. Don’t hesitate to ask questions and share your ideas or macros with us. We are open to discussion and cooperation. Best of luck in your exploratory endeavors!