Retrieve website details with ONLYOFFICE macro
In our previous post, we built a fully functioning ONLYOFFICE macro from a VBA reference sent to us by one of our users. Today we would like to take it up a notch and add some Whois API functionality to it.
About Whois
Whois, short for “Who is?” is an essential internet protocol that enables users to access comprehensive information about domain names and IP addresses. It serves as a valuable resource for retrieving details related to the registered owner, administrative and technical contacts, registration and expiration dates, domain name servers, and even the organization responsible for domain registration.
Accessing the API
In order to get an access to the Whois API, we will use a solution provided by the API Ninijas.
The API Ninjas website is an online platform that offers a variety of resources and insights about APIs. It also provides a selection of free APIs that developers can use in their projects. This website aims to be a helpful destination for developers looking for information, tools, and practical examples to improve their API development skills.
The platform offers code snippets that assist in making HHTP requests:
var domain = 'example.com'
$.ajax({
method: 'GET',
url: 'https://api.api-ninjas.com/v1/whois?domain=' + domain,
headers: { 'X-Api-Key': 'your_API_key'},
contentType: 'application/json',
success: function(result) {
console.log(result);
},
error: function ajaxError(jqXHR) {
console.error('Error: ', jqXHR.responseText);
}
});
Building the macro
Now let’s incorporate this .ajax request into our macro! The first few lines of our code remain intact. Except for the part where we retrieved the value of cell A4 . This time the value of cell A2 is sufficient:
var sheet = Api.GetActiveSheet();
var domainValue = sheet.GetRange("A2").GetValue();
var dateRange = sheet.GetRange("D1:D10");
Then, we initiate a GET request to the API endpoint specified in the apiUrl variable:
$.ajax({
method: 'GET',
url: apiUrl,
headers: { 'X-Api-Key': 'your_API_key' },
contentType: 'application/json',
success: function(result) {
console.log(result);
Upon a successful API response, we proceed to processing the data. We convert the UNIX timestamps for creation date, expiration date, and updated date into human-readable formats using JavaScript’s Date object. These formatted dates are stored in variables like formattedCreationDate, formattedExpirationDate, and formattedUpdatedDate:
// Convert creation_date to a more readable format
var unixTimestamp = result.creation_date;
var creationDate = new Date(unixTimestamp * 1000);
var formattedCreationDate = creationDate.toDateString();
// Convert expiration_date to a more readable format
var expirationTimestamp = result.expiration_date;
var expirationDate = new Date(expirationTimestamp * 1000);
var formattedExpirationDate = expirationDate.toDateString();
// Convert updated_date to a more readable format
var updatedTimestamp = result.updated_date;
var updatedDate = new Date(updatedTimestamp * 1000);
var formattedUpdatedDate = updatedDate.toDateString();
To populate the spreadsheet with the retrieved domain information, we use a forEach loop on the dateRange variable. This loop iterates through each cell in the specified range (D1:D10). Inside the loop, we retrieve the current row using range.GetRow() and store it in the row variable:
var oRange = sheet.GetRange("E" + row);
The current value of the cell is obtained using range.GetValue() and stored in the currentValue variable:
var currentValue = range.GetValue();
Based on the value of currentValue, we use a switch statement to determine the appropriate action. For example, if the currentValue is “domain name,” we set the value of the result.domain_name to the corresponding cell using oRange.SetValue(result.domain_name):
switch (currentValue) {
case "domain name":
oRange.SetValue(result.domain_name);
break;
Similarly, we handle other cases such as registrar, whois server, updated date, creation date, expiration date, name servers, and dnssec:
oRange.SetValue(result.registrar);
break;
case "whois server":
oRange.SetValue(result.whois_server);
break;
case "updated date":
oRange.SetValue(formattedUpdatedDate);
break;
case "creation date":
oRange.SetValue(formattedCreationDate);
break;
case "expiration date":
oRange.SetValue(formattedExpirationDate);
break;
case "name servers":
oRange.SetValue(result.name_servers.join(", "));
break;
case "dnssec":
oRange.SetValue(result.dnssec);
break;
default:
// Handle other cases if needed
break;
}
});
Finally, we define a reload function that invokes the Api.asc_calculate(Asc.c_oAscCalculateType.All) method to recalculate the spreadsheet after the loop is completed. This ensures that the newly populated values are reflected in the spreadsheet:
reload(); // Invoke reload function after the loop is completed
},
error: function ajaxError(jqXHR) {
console.error('Error: ', jqXHR.responseText);
}
});
function reload() {
let reloadInterval = setInterval(function() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
}, 100);
}
The entire macro code is the following:
(function()
{
var sheet = Api.GetActiveSheet();
var domainValue = sheet.GetRange("A2").GetValue();
var dateRange = sheet.GetRange("D1:D10");
var apiUrl = 'https://api.api-ninjas.com/v1/whois?domain=' + domainValue;
$.ajax({
method: 'GET',
url: apiUrl,
headers: { 'X-Api-Key': 'your_API_key' },
contentType: 'application/json',
success: function(result) {
console.log(result);
// Convert creation_date to a more readable format
var unixTimestamp = result.creation_date;
var creationDate = new Date(unixTimestamp * 1000);
var formattedCreationDate = creationDate.toDateString();
// Convert expiration_date to a more readable format
var expirationTimestamp = result.expiration_date;
var expirationDate = new Date(expirationTimestamp * 1000);
var formattedExpirationDate = expirationDate.toDateString();
// Convert updated_date to a more readable format
var updatedTimestamp = result.updated_date;
var updatedDate = new Date(updatedTimestamp * 1000);
var formattedUpdatedDate = updatedDate.toDateString();
dateRange.ForEach(function(range) {
var row = range.GetRow();
var oRange = sheet.GetRange("E" + row);
var currentValue = range.GetValue();
switch (currentValue) {
case "domain name":
oRange.SetValue(result.domain_name);
break;
case "registrar":
oRange.SetValue(result.registrar);
break;
case "whois server":
oRange.SetValue(result.whois_server);
break;
case "updated date":
oRange.SetValue(formattedUpdatedDate);
break;
case "creation date":
oRange.SetValue(formattedCreationDate);
break;
case "expiration date":
oRange.SetValue(formattedExpirationDate);
break;
case "name servers":
oRange.SetValue(result.name_servers.join(", "));
break;
case "dnssec":
oRange.SetValue(result.dnssec);
break;
default:
// Handle other cases if needed
break;
}
});
reload(); // Invoke reload function after the loop is completed
},
error: function ajaxError(jqXHR) {
console.error('Error: ', jqXHR.responseText);
}
});
function reload() {
let reloadInterval = setInterval(function() {
Api.asc_calculate(Asc.c_oAscCalculateType.All);
});
}
})();Now, let’s run our macro and see how it works!
We hope that this macro proves to be a useful tool for your projects, allowing you to efficiently retrieve and display important domain details. The flexibility and functionality offered by the ONLYOFFICE API opens vast possibilities for customization and automation of your tasks.
We encourage you to explore the potential of the ONLYOFFICE API and unleash your creativity by building your own macros. If you have any questions or ideas to share, feel free to leave comments or contact us. We are open to hearing your ideas and are eager to collaborate with you. Best of luck in your exploratory endeavors!
