Find company logos with ONLYOFFICE macro
Given the many logos out there, discovering logo references can be a bit tricky these days, potentially leading to confusion. Yet, with ONLYOFFICE macros, you can make this process automatic. In this blog post we will show you how to create a macro that can simultaneously retrieve several logotypes from an external API and insert them into your spreadsheet.
Accessing the API
In this tutorial, we will utilize the Logo API offered by API Ninjas, an online platform that provides a range of resources and insights about APIs. This platform also offers a collection of free APIs that developers can include in their projects. Specifically, the Logo API allows access to logotype images for a wide variety of companies.
Building the macro
First we target the active sheet:
// Get the active worksheet
var oWorksheet = Api.GetActiveSheet();
Then we get the selection on the document. The values from the cells within this selection will be used in the API request:
var selectedRange = Api.GetSelection()
After that we add logic that would set titles for the data that is to be inserted in the document. We start by creating an array that contains the field names (“name”, “ticker”, “image”) and the column letters:
// Define a mapping of values to insert into specific columns
var valuesToInsert = {
"name": "B",
"ticker": "C",
"image": "D"
};
Upon mapping columns and values we, loop through each field name in the valuesToInsert object. For each field, we fetch the corresponding cell and check if the cell is empty. If the cell is empty, the predefined value associated with the field is inserted into the cell by using the oCell.SetValue() method:
// Loop through the values to insert and populate empty cells
for (var value in valuesToInsert) {
var oCell = oWorksheet.GetRange(valuesToInsert[value] + "1");
if (!oCell.GetValue()) oCell.SetValue(value);
}
Then we use the selectedRange.ForEach() function to iterate through each cell in the selected range. For each cell, it fetches the cell’s value. If the value exists, an AJAX request is made to the API. Then the response data is processed in the success callback:
// Iterate through each cell in the selected range
selectedRange.ForEach(function (cell) {
var value = cell.GetValue();
if (value) {
// Make an AJAX request to an API to retrieve data based on the cell value
$.ajax({
method: 'GET',
url: 'https://api.api-ninjas.com/v1/logo?name=' + value,
headers: { 'X-Api-Key': 'yourAPIkey' },
contentType: 'application/json',
success: function(result) {
}
Within the AJAX success callback, we retrieve the API response. The code then iterates through the response data and populates the retrieved data into specific columns of the worksheet. The currentRow variable determines the row to populate, and the corresponding columns are filled with data. The AutoFit function is used to adjust column widths based on the populated data:
// Iterate through the API response and populate data into specific columns
for (var i = 0; i < result.length; i++) {
var data = result[i];
var currentRow = cell.GetRow() + i - 1;
// Populate data into specific columns and adjust column width
oWorksheet.GetRangeByNumber(currentRow, 1).SetValue(data.name);
oWorksheet.GetRangeByNumber(currentRow, 1).AutoFit(false, true);
oWorksheet.GetRangeByNumber(currentRow, 2).SetValue(data.ticker);
oWorksheet.GetRangeByNumber(currentRow, 2).AutoFit(false, true);
oWorksheet.GetRangeByNumber(currentRow, 3).SetValue(data.image);
oWorksheet.GetRangeByNumber(currentRow, 3).AutoFit(false, true);
}
The entire macro code is the following:
(function() {
// Get the active worksheet
var oWorksheet = Api.GetActiveSheet();
// Get the currently selected range of cells
var selectedRange = Api.GetSelection();
// Define a mapping of values to insert into specific columns
var valuesToInsert = {
"name": "B",
"ticker": "C",
"image": "D"
};
// Loop through the values to insert and populate empty cells
for (var value in valuesToInsert) {
var oCell = oWorksheet.GetRange(valuesToInsert[value] + "1");
if (!oCell.GetValue()) oCell.SetValue(value);
}
// Iterate through each cell in the selected range
selectedRange.ForEach(function (cell) {
var value = cell.GetValue();
if (value) {
// Make an AJAX request to an API to retrieve data based on the cell value
$.ajax({
method: 'GET',
url: 'https://api.api-ninjas.com/v1/logo?name=' + value,
headers: { 'X-Api-Key': 'yourAPIkey' },
contentType: 'application/json',
success: function(result) {
console.log(result);
// Iterate through the API response and populate data into specific columns
for (var i = 0; i < result.length; i++) {
var data = result[i];
var currentRow = cell.GetRow() + i - 1;
// Populate data into specific columns and adjust column width
oWorksheet.GetRangeByNumber(currentRow, 1).SetValue(data.name);
oWorksheet.GetRangeByNumber(currentRow, 1).AutoFit(false, true);
oWorksheet.GetRangeByNumber(currentRow, 2).SetValue(data.ticker);
oWorksheet.GetRangeByNumber(currentRow, 2).AutoFit(false, true);
oWorksheet.GetRangeByNumber(currentRow, 3).SetValue(data.image);
oWorksheet.GetRangeByNumber(currentRow, 3).AutoFit(false, true);
}
},
error: function ajaxError(jqXHR) {
console.error('Error: ', jqXHR.responseText);
}
});
}
});
})();
Now, let’s run our macro and see how it works!
We hope that this macro will quickly become a valuable asset in your arsenal. The ONLYOFFICE API’s versatility and capabilities create significant openings for customizing and automating your tasks.
While you delve into macro crafting, don’t overlook the possibilities that the ONLYOFFICE API offers. If you ever have questions or innovative ideas, we encourage you to share them through the comments or by getting in touch with us. Your insights are valuable, and we’re excited about the potential for collaboration. Best of luck in your exploratory endeavors!
