Insert Google Search results into a spreadsheet with ONLYOFFICE macro

28 July 2022 By Serge 0 77
Share

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.

Insert Google Search results into a spreadsheet with ONLYOFFICE macro

 

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.

Insert Google Search results into a spreadsheet with Onlyoffice macro

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 APIRequest 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.”

Insert Google Search results into a spreadsheet with ONLYOFFICE macro

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.

Insert Google Search results into a spreadsheet with Onlyoffice macro

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 Snippetswe select JavaScript > XML/HttpRequest.

Insert Google Search results into a spreadsheet with Onlyoffice macro

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.

Insert Google Search results into a spreadsheet with Onlyoffice macro

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:

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

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:

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:

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:

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

Insert Google Search results into a spreadsheet with Onlyoffice macro

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:

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

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

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:

Now, let’s run our macro:

Insert Google Search results into a spreadsheet with Onlyoffice 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!

 

Share

Add a comment