![](https://habrastorage.org/getpro/habr/upload_files/4dc/a01/a1e/4dca01a1e64c83d4f344b0cd0877ac89.jpeg)
Всем привет! Продолжаем наш разговор об использовании электронных таблиц в работе OSINT-аналитиков. В первой части нашего цикла статей мы говорили об автоматизации генерации дорков (расширенных операторов поисковых запросов). Сегодня мы коснемся вопроса интеграции в таблицы данных из внешних API.
Что такое API?
![](https://habrastorage.org/getpro/habr/upload_files/12e/625/d6e/12e625d6e3e433a34bda137404b5c6e4.jpeg)
API или Application Programming Interface — это программный интерфейс, то есть описание способов взаимодействия одной компьютерной программы с другими. Иными словами, описание, которое позволяет программе А понимать программу Б и эффективно взаимодействовать с ней.
Откуда берутся эти самые API? Сегодня они есть практически у любого онлайн-сервиса. Они бывают бесплатные, требующие регистрации или коммерческие. Большое количество доступных API вы можете позаимствовать, к примеру, на следующих ресурсах:
Начнем нашу работу с Google Sheets с того, что создадим новую электронную таблицу и выберем ее будущую функциональность. Допустим, что нашей задачей будет сбор данных о субъектах бизнеса (юридических лицах и индивидуальных предпринимателях). В этом случае нам пригодится API от следующего сервиса:
Этот API ограничивает только частоту запросов и максимальное число соединений. Для частного проекта подойдет идеально. Регистрируемся и получаем индивидуальный API-ключ. Ключ API — это строка уникальных идентификаторов, предназначенных в первую очередь для определения трафика приложений от клиентов API. Проще говоря, это ваш идентификатор в качестве пользователя API конкретного сервиса.
Предположим, что мы хотим получать ответ внешнего сервиса по запросу ОГРН (основной государственный регистрационный номер) юридического лица. Чтобы интегрировать возможность делать API-запрос к datanewton.ru, перейдем во вкладку «Расширения» и кликнем на пункт «Apps Script». Откроется новая вкладка, в которой мы можем вводить скрипты для работы с таблицей.
Пишем наш скрипт
![](https://habrastorage.org/getpro/habr/upload_files/f5c/dd5/849/f5cdd58499b258864129e556413cf202.jpeg)
Итак, пишем наш первый скрипт для интеграции API в Google Sheets:
function COMPANYLOOKUP(input) { response=UrlFetchApp.fetch("https://api.datanewton.ru/v1/counterparty?key=**********&filters=OWNER_BLOCK%2CADDRESS_BLOCK&ogrn="+input).getContentText(); return response; } |
В этом скрипте у нас:
COMPANYLOOKUP — название команды для запроса datanewton.ru
(input) — вводимые данные для запроса (ОГРН)
********** — это API-ключ datanewton.ru
![](https://habrastorage.org/getpro/habr/upload_files/bfc/4fd/796/bfc4fd7966966b5e61056babaa288e61.png)
Сохраняем скрипт и возвращаемся к таблице. Теперь нам доступна новая функция COMPANYLOOKUP, которая позволит выгружать данные юрлица при помощи API сервиса datanewton.ru. Запустим ее при помощи следующей команды:
=COMPANYLOOKUP(A2)
Где:
COMPANYLOOKUP — название команды для запроса datanewton.ru
(A2) — это обращение к ячейке таблицы с данными ОГРН
![](https://habrastorage.org/getpro/habr/upload_files/b4a/f45/de7/b4af45de7c26d9b1d2fb90f3eb46f895.png)
Результат, в формате JSON, будет загружен в ту ячейку, в которой водилась функция =COMPANYLOOKUP(A2).
Попробуем проделать схожие вещи с API других сервисов. Так, для проверки номера мобильного телефона, мы можем воспользоваться API сервиса http://htmlweb.ru/. В таком случае, скрипт получения данных может выглядеть так:
function PHONENUMBERLOOKUP(input) { response=UrlFetchApp.fetch("http://htmlweb.ru/api/mnp/phone/"+input+"?api_key=**********").getContentText(); return response; } |
Где:
PHONENUMBERLOOKUP — название команды для запроса htmlweb.ru
(input) — вводимые данные для запроса (номер телефона)
********** — это API-ключ htmlweb.ru
![](https://habrastorage.org/getpro/habr/upload_files/212/986/ff7/212986ff768430894774eda030db5ba5.png)
Сохраняем скрипт и возвращаемся к таблице. Вводим функцию:
=PHONENUMBERLOOKUP(A1)
![](https://habrastorage.org/getpro/habr/upload_files/b02/b89/d37/b02b89d3793b2509a64f3dbe40ce4f29.png)
И получаем данные о телефоне из htmlweb.ru. Все получилось… Но здесь нам пригодится небольшой лайфхак.
Номера телефонов в таблицу часто попадают в различных формах написания. Это очень раздражает. Привести все телефоны к единому формату также можно при помощи электронных таблиц. Пишем следующую функцию:
=CONCATENATE(IF(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";""))=10;(CONCATENATE("7";SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";"")));"");IF(AND(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";""))=11;LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";""))="7");SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";"");"");IF(AND(LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";""))=11;LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";""))="8");CONCATENATE("7";MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2;",";"");";";"");" ";"");"+";"");")";"");"(";"");"-";"");2; 10));"")) |
Где:
SUBSTITUTE и CONCATENATE — команды для замены и объединения символов
(C2) - это обращение к ячейке таблицы с номером телефона
![](https://habrastorage.org/getpro/habr/upload_files/f61/97a/920/f6197a92006ccf78348318ab7b3d4be5.png)
Идем дальше. Попробуем научить таблицу пробивать нам номера кредитных карт. API https://lookup.binlist.net/ очень простой и не требует регистрации ключа. Поэтому для запроса информации скрипт будет выглядеть следующим образом:
function CARDNUMBERLOOKUP(input) { response=UrlFetchApp.fetch("https://lookup.binlist.net/"+input).getContentText(); return response; } |
Где:
CARDNUMBERLOOKUP — название команды для запроса по binlist.net
(input) — вводимые данные для запроса (номер кредитки)
![](https://habrastorage.org/getpro/habr/upload_files/4c1/625/c39/4c1625c39457a6f58c66f8d005c264dd.png)
Сохраняем скрипт и идем обратно к таблице, где вводим функцию:
=CARDNUMBERLOOKUP(A1)
Отлично, данные были выгружены. Но есть нюанс.
Поговорим об удобстве
![](https://habrastorage.org/getpro/habr/upload_files/499/fd5/c05/499fd5c0572ca33f60a5ba22d2901135.jpeg)
Вы же обратили внимание на то, что внешние API выдают информацию в JSON-формате? Он удобен для восприятия компьютером, но практически нечитаем для человеческого глаза. Как превратить данные JSON в красивый и удобный отчет?
Для того, чтобы понять структуру данных, содержащихся в JSON, вставим их в одну из читалок JSON. Например, в https://jsonformatter.curiousconcept.com/#. Теперь мы можем увидеть данные JSON со всеми полями и вкладками. Ориентируясь на них, мы можем составить скрипт, позволяющий копировать отдельные пункты JSON и вставлять их в нашу таблицу.
Вернемся к нашему примеру с проверкой кредитных карт. Заходим в «Apps Script» и добавляем новый скрипт:
function PARSER(input) { var data = JSON.parse(input); name=data.bank.name return name; } |
Где:
PARSER — команда парсинга (копирования) данных
(input) — данные для парсинга (копирования)
name=data.bank.name — название пункта и путь к нему в JSON
![](https://habrastorage.org/getpro/habr/upload_files/4d5/bf6/f59/4d5bf6f590becb9cade1d073ea03d550.png)
Сохраняем, возвращаемся в таблицу и вводим функцию:
=PARSER(B1)
![](https://habrastorage.org/getpro/habr/upload_files/83e/c2f/c1e/83ec2fc1e5192e4dca3e6ded4e7bd9f3.png)
Теперь таблица позволяет нам выгружать из данных JSON в отдельную ячейку сведения о банке, который осуществил регистрацию кредитной карты.
Вернемся к таблице с проверкой юридических лиц и попробуем скопировать в нее данные JSON аналогичным образом. Допустим, что нам будет нужно выгрузить данные об:
Адресе регистрации компании
ИНН
Текущем статусе
Это предполагает написание трех отдельных скриптов:
function PARSERDIR(input) { var data = JSON.parse(input); line_address=data.company.address.line_address return line_address; } |
function PARSERINN(input) { var data = JSON.parse(input); inn=data.inn return inn; } |
function PARSERSTATUS(input) { var data = JSON.parse(input); status_egr=data.company.status.status_egr return status_egr; } |
Сохраняем созданные скрипты и используем новые команды в таблице:
=PARSERDIR(D2)
=PARSERINN(D2)
=PARSERSTATUS(D2)
Что дальше?
![](https://habrastorage.org/getpro/habr/upload_files/037/f63/aee/037f63aee180420e59ee4c757b2ab4d0.png)
Для того, чтобы получить полноценный сервис для проверок, конечно, придется потрудиться и прописать все необходимые скрипты — как для получения данных, так и для их помещения в электронную таблицу. Однако что может заменить чувство гордости за разработку собственного продукта? Причем без навыков программирования.
Мы продолжим этот цикл статей и в следующий раз расскажем про скрапинг данных с тех сайтов, у которых нет API-интерфейса. И это в том числе про то, как можно получать сведения о судимостях и нарушениях граждан. А в четвертой статье затронем тему визуализации полученных данных. Оставайтесь с нами, будет интересно!