Integrate external APIs

Hello everyone! We continue our conversation about the use of spreadsheets in the work of OSINT analysts. first part In our series of articles we talked about automating the generation of dorks (advanced search query operators). Today we will touch on the issue of integrating data from external APIs into tables.

What is API?

API or Application Programming Interface is a software interface, that is, a description of how one computer program interacts with others. In other words, a description that allows program A to understand program B and effectively interact with it.

Where do these APIs come from? Today, almost every online service has them. They can be free, require registration, or commercial. You can borrow a large number of available APIs, for example, from the following resources:

Let's start our work with Google Sheets by creating a new spreadsheet and choosing its future functionality. Let's assume that our task is to collect data on business entities (legal entities and sole proprietors). In this case, we will need the API from the following service:

This API only limits the request rate and maximum number of connections. It is perfect for a private project. Register and get an individual API key. An API key is a string of unique identifiers primarily intended to identify application traffic from API clients. Simply put, this is your identifier as an API user of a specific service.

Let's assume that we want to receive a response from an external service to a request for the OGRN (primary state registration number) of a legal entity. In order to integrate the ability to make an API request to datanewton.rugo to the “Extensions” tab and click on the “Apps Script” item. A new tab will open in which we can enter scripts to work with the table.

We write our script

So, let's write our first script for API integration in Google Sheets:

In this script we have:

• COMPANYLOOKUP — command name for querying datanewton.ru

• (input) — input data for the request (OGRN)

• ********** is the API key of datanewton.ru

Save the script and return to the table. Now we have access to the new COMPANYLOOKUP function, which will allow us to download legal entity data using the API service datanewton.ru. Let's run it using the following command:

Where:

The result, in JSON format, will be loaded into the cell where the =COMPANYLOOKUP(A2) function was entered.

Let's try to do similar things with the API of other services. So, to check the mobile phone number, we can use the API of the service http://htmlweb.ru/In this case, the data retrieval script may look like this:

Where:

• PHONENUMBERLOOKUP — command name for htmlweb.ru request

• (input) — input data for the request (phone number)

• ********** is the API key of htmlweb.ru

Save the script and return to the table. Enter the function:

And we get the phone data from htmlweb.ru. Everything worked out… But here we will need a small life hack.

Phone numbers often end up in tables in different forms of writing. This is very annoying. You can also bring all phones to a single format using spreadsheets. We write the following function:

Where:

• SUBSTITUTE and CONCATENATE are commands for replacing and combining characters

• (C2) is a call to a table cell with a phone number

Let's move on. Let's try to teach the table to punch credit card numbers for us. API https://lookup.binlist.net/ is very simple and does not require key registration. Therefore, to request information, the script will look like this:

Where:

We save the script and go back to the table, where we enter the function:

Great, the data has been uploaded. But there is a nuance.

Let's talk about convenience

You've probably noticed that external APIs provide information in JSON format. It's easy for computers to read, but almost unreadable for the human eye. How can you turn JSON data into a nice, easy-to-read report?

To understand the structure of the data contained in JSON, let's paste it into one of the JSON readers. For example, in https://jsonformatter.curiousconcept.com/#. Now we can see the JSON data with all the fields and tabs. Based on this, we can create a script that allows us to copy individual JSON items and paste them into our table.

Let's go back to our example with checking credit cards. Go to “Apps Script” and add a new script:

Where:

• PARSER — data parsing (copying) command

• (input) — data for parsing (copying)

• name=data.bank.name — name of the item and its path in JSON

We save, return to the table and enter the function:

The table now allows us to extract from the JSON data into a separate cell the details of the bank that registered the credit card.

Let's go back to the table with legal entities check and try to copy JSON data into it in a similar way. Let's say that we need to download data about:

This involves writing three separate scripts:

We save the created scripts and use the new commands in the table:

• =PARSERDIR(D2)

• =PARSERINN(D2)

• =PARSERSTATUS(D2)

What's next?

In order to get a full-fledged service for checks, of course, you will have to work hard and write all the necessary scripts – both for receiving data and for placing it in a spreadsheet. However, what can replace the feeling of pride in developing your own product? And without programming skills.

We will continue this series of articles and next time we will talk about scraping data from those sites that do not have an API interface. And this includes how you can get information about convictions and violations of citizens. And in the fourth article we will touch on the topic of visualization of the obtained data. Stay with us, it will be interesting!