Борьба с Excel — боль бизнеса и разработчиков. Особенно, если множество процессов построены именно на этих пресловутых таблицах, а менеджеры не хотят отказываться от них в пользу «страшных» CRM. Такое случается часто, не зря пользователей Microsoft Office уже давно больше 1 млрд человек. Но есть выход для тех, кто собирает все цифры в таблицах Google и хочет через них выстроить коммуникации с клиентами.

Привет, Хабр! Меня зовут Анастасия Иванова, я технический писатель МТС Exolve. В этой статье я расскажу о том, как поместить информацию о звонках в привычный формат таблиц на примере нашей платформы (краткий обзор сервиса вы найдете тут) и Google Таблиц. В частности, вы узнаете о том, как импортировать статистику входящих и исходящих вызовов, а также исходящих голосовых SMS.

Для этого мы напишем скрипт на языке программирования от Google (Apps Script), который будет делать HTTP-запрос в Statistics API и сохранять полученные данные в Google Таблицу. Мы покажем пример импорта некоторых данных статистики, далее вы сами сможете изменить скрипт, чтобы подстроить его под свои нужды.

Что нам понадобится

аккаунт Google;
аккаунт разработчика в MTC Exolve;
API-ключ приложения в аккаунте разработчика. Инструкцию как создать приложение и найти его API-ключ вы можете в статьях «Создание приложения» и «API-ключ приложения».

Создание Google Таблицы

Для начала нужно создать Google Таблицу и заполнить ее предварительными данными. Мы выбрали для примера кажущиеся нам наиболее важными столбцы. Ответ на POST-запрос к Statistics API в МТС Exolve содержит больше информации, вы можете изменить набор предварительных данных таблицы по своему усмотрению. Мы предзаполнили таблицу следующими столбцами:

ID звонка;
вызывающий абонент;
принимающий абонент;
номер переадресации;
время вызова;
продолжительность (ms);
услуга.

С этими данными таблица будет выглядеть вот так:

Безопасная передача API-ключа

Чтобы успешно выполнить любой HTTP-запрос в МТС Exolve API, нужно авторизироваться с помощью API-ключа приложения.

API-ключ приложения — это конфиденциальная информация, в целях безопасности его нельзя передавать третьим лицам. Поэтому не стоит хранить API-ключ в коде, размещённом в Apps Script. Если злоумышленники получат доступ к вашему аккаунту Google и найдут код в Apps Script, они смогут использовать API-ключ вашего аккаунта в МТС Exolve в корыстных целях, например, для рассылки спама с купленных вами номеров.

Есть удобный и безопасный способ передать API-ключ вашего приложения в Google Таблицы с помощью свойств пользователя userProperties и пользовательских меню.

Создание пользовательского меню для ввода API-ключа

Пользовательское меню — это пункт, который будет отображаться в строке меню Google Таблиц вместе со стандартными пунктами, такими как Файл, Правка, Вид и так далее.

Пользовательское меню может быть связано с одной или несколькими функциями в Apps Script, которые выполняются при выборе пункта в пользовательском меню. Мы создадим пользовательское меню под названием «Учётные данные». В нем будет раскрывающийся список с пунктом для ввода API-ключа приложения.

Теперь начнём писать код для создания нашего пользовательского меню в Apps Scripts. Чтобы открыть редактор кода для Google Таблиц, перейдите во вкладку «Расширения», а затем откройте Apps Script, как показано ниже:

В открывшемся Apps Script редакторе замените предзаполненный код (function myFunction() {}) следующим:

var API_KEY = 'placeholder';

var ui = SpreadsheetApp.getUi();

var userProperties = PropertiesService.getUserProperties();

function onOpen() {

  ui.createMenu('Учетные данные')

   .addItem('Установить API-ключ приложения', 'setApiKey')

   .addToUi()

};

В приведённом выше примере мы сначала определяем переменную для API-ключа и присваиваем ей строковое значение placeholder в качестве «заглушки». Язык программирования Apps Script не позволяет присваивать переменным, которые являются UserProperties, значения null или пустые строки, поэтому мы сразу присваиваем ей произвольную строку текста. Когда мы используем пользовательское меню впервые, установленный изначально текст будет изменён на API-ключ, который мы введём в Google Таблицах.

Далее мы задаём две переменные для представления интерфейса Google Таблиц (ui) и свойств пользователя (userProperties). Эти переменные пригодятся на следующем шаге, при написании функций для ввода API-ключа в Google Таблицу.

Затем мы объявляем встроенную функцию Apps Script onOpen(), которая выполняется каждый раз, когда открывается Google Таблица. Таким образом, наше пользовательское меню «Учётные данные» всегда будет присутствовать в таблице при открытии.

В коде выше мы также добавили пункт пользовательского меню и дали название будущей функции, которая будет запускаться при выборе пункта «Установить API-ключ приложения».

Функция пользовательского меню для ввода учётных данных

В предыдущем шаге мы дали название функции пользовательского меню для ввода API-ключа приложения, теперь давайте напишем саму функцию для передачи ключа. Скопируйте и вставьте следующий фрагмент кода в редактор Apps Script под уже существующим кодом:

function setApiKey() {

  var apiKey = ui.prompt('Введите API-ключ приложения' , ui.ButtonSet.OK);

  userProperties.setProperty('API_KEY', apiKey.getResponseText());

};

После запуска функции в Google Таблицах из пользовательского меню мы должны иметь возможность ввести API-ключ приложения, который сохранится в userProperties и понадобится нам во время запросов в МТС Exolve API.

Сохраним наш код в Apps Script, запустим его и посмотрим, появится ли пользовательское меню в Google Таблицах.

Перейдите в Apps Script и нажмите на кнопку «Сохранить»:

После этого откройте Google Таблицу и перезагрузите вкладку браузера с ней. После перезагрузки вы должны увидеть новое пользовательское меню «Учётные данные». После нажатия на него появится пункт для ввода API-ключа приложения:

Выберете пункт ввода API-ключа и вы увидите форму для ввода:

Обратите внимание! Форма не предполагает валидацию введённых данных, поэтому убедитесь, что API-ключ введён правильно.

Создание пользовательского меню сбора статистики о звонках

Нам также нужно меню для запуска функции сбора статистики. Для этого мы создадим ещё одно пользовательское меню, добавив код в onOpen() ещё один метод ui.createMenu. Наша функция onOpen() будет выглядеть вот так:

function onOpen() {

  ui.createMenu('Учетные данные')

   .addItem('Установить API-ключ приложения', 'setApiKey')

   .addToUi()

  ui.createMenu('Статистика')

   .addItem('Получить статистику по звонкам', 'getStatistics')

   .addToUi();

};

Таким образом, при открытии Google Таблиц мы будем видеть два пользовательских меню: «Учётные данные» и «Отправка сообщений».

Функция ввода дат

Для отправки POST HTTP-запроса в Statistics API необходимо указать дату начала и конца поиска в JSON Body запроса в google.protobuf.Timestamp формате ({year}-{month}-{day}T{hour}:{min}:{sec}[.{frac_sec}]Z). Чтобы каждый раз не менять эти данные в коде в Apps Script, мы будем запрашивать эти данные при выборе пункта меню о запросе статистики. Напишем функцию для ввода дат, которую будем вызывать внутри функции для сбора статистики.

Для начала нужно задать переменные, которые будут храниться в коде как «заглушки» (как и в случае с переменной API_KEY). Для этого давайте добавим следующие переменные под ней:

var START_DATE = 'placeholder';

var END_DATE = 'placeholder';

Теперь добавим в наш код функцию установки дат:

function setDates() {

  var startDate = ui.prompt('Введите дату начала поиска' , ui.ButtonSet.OK);

  userProperties.setProperty('START_DATE', startDate.getResponseText());

  var endDate = ui.prompt('Введите дату конца поиска' , ui.ButtonSet.OK);

  userProperties.setProperty('END_DATE', endDate.getResponseText());

};

Функция для сбора статистики

Теперь напишем функцию getStatistics(), которая будет основой нашего скрипта. Эта функция будет получать API-ключ приложения и даты из userProperties, отправлять запрос в Statistics API и подставлять данные из ответа в Google Таблицу. Скопируйте и вставьте следующий фрагмент кода в редактор Apps Script под уже существующим кодом:

function getStatistics() {

  setDates();

  var apiKey = userProperties.getProperty('API_KEY');

  var startDate = userProperties.getProperty('START_DATE');

  var endDate = userProperties.getProperty('END_DATE');

  var url = 'https://api.exolve.ru/statistics/call-history/v1/GetList';

  var options = {

	"method": "post",

	"headers": {

  	"Authorization": "Bearer " + apiKey

	},

	"payload": JSON.stringify({

  	date_from: startDate,

  	date_to: endDate

	})

  };

  var response = UrlFetchApp.fetch(url, options);

  var data = JSON.parse(response);

  var rows = data.calls;

  var sheet = SpreadsheetApp.getActiveSheet();

  rows.forEach(function(row, index) {

	callId = row.call_id;

	callerNumber = row.number_a;

	recieverNumber = row.number_b;

	redirectNumber = row.redirects[0].number_c;

	callStart = row.call_start;

	duration = row.full_duration;

	service = row.service;

	rowData = [[callId, callerNumber, recieverNumber, redirectNumber, callStart, duration, service ]];

	sheet.getRange(index + 2, 1, 1, 7).setValues(rowData); 

  })

}

В приведённом выше фрагменте кода мы инициируем запуск функции для ввода дат начала и конца поиска, а потом получаем API-ключ и даты из userProperties. Эти переменные используются для выполнения HTTP POST-запроса к Statistics API, чтобы получить данные о звонках в указанные даты.

Если POST-запрос будет успешным, скрипт извлечёт данные из полученного JSON-ответа и заполнит предзаполненные столбцы данными.

Тестирование

Теперь сохраним код и протестируем его работу. Полный код в Apps Script должен выглядеть так:

var API_KEY = 'placeholder';

var START_DATE = 'placeholder';

var END_DATE = 'placeholder';

var ui = SpreadsheetApp.getUi();

var userProperties = PropertiesService.getUserProperties();

function onOpen() {

  ui.createMenu('Учетные данные')

   .addItem('Установить API-ключ приложения', 'setApiKey')

   .addToUi()

  ui.createMenu('Статистика')

   .addItem('Получить статистику по звонкам', 'getStatistics')

   .addToUi();

}; 

function setApiKey() {

  var apiKey = ui.prompt('Введите API-ключ приложения' , ui.ButtonSet.OK);

  userProperties.setProperty('API_KEY', apiKey.getResponseText());

};

 

function setDates() {

  var startDate = ui.prompt('Введите дату начала поиска' , ui.ButtonSet.OK);

  userProperties.setProperty('START_DATE', startDate.getResponseText());

  var endDate = ui.prompt('Введите дату конца поиска' , ui.ButtonSet.OK);

  userProperties.setProperty('END_DATE', endDate.getResponseText());

}; 

function getStatistics() {

  setDates();

  var apiKey = userProperties.getProperty('API_KEY');

  var startDate = userProperties.getProperty('START_DATE');

  var endDate = userProperties.getProperty('END_DATE');

  var url = 'https://api.exolve.ru/statistics/call-history/v1/GetList';

  var options = {

	"method": "post",

	"headers": {

  	"Authorization": "Bearer " + apiKey

	},

	"payload": JSON.stringify({

  	date_from: startDate,

  	date_to: endDate

	})

  };

  var response = UrlFetchApp.fetch(url, options);

  var data = JSON.parse(response);

  var rows = data.calls;

  var sheet = SpreadsheetApp.getActiveSheet();

  rows.forEach(function(row, index) {

	callId = row.call_id;

	callerNumber = row.number_a;

	recieverNumber = row.number_b;

	redirectNumber = row.redirects[0].number_c;

	callStart = row.call_start;

	duration = row.full_duration;

	service = row.service;

	rowData = [[callId, callerNumber, recieverNumber, redirectNumber, callStart, duration, service ]];

    sheet.getRange(index + 2, 1, 1, 7).setValues(rowData); 

  })

}

Google Таблица после перезагрузки должна иметь созданные пользовательские меню, выглядит это так:

Нажмите на пользовательское меню «Учётные данные» > «Ввести API-ключ приложения» и введите ваш ключ приложения.

После ввода данных перейдите в пользовательское меню «Статистика» > «Получить статистику по звонкам». Как только вы нажмёте на этот пункт меню, запустится функция getStatistics(). Введите даты даты начала и конца поиска, чтобы запустилась функция setDates().

После ввода функция getStatistics() продолжит выполняться и отправит запрос на поиск статистики по звонкам.

После выполнения скрипта данные из полученного ответа от МТС Exolve попадут в соответствующие столбцы:

Итак, мы разобрали все этапы создания Google Таблицы со статистикой звонков из МТС Exolve, в том числе безопасную передачу API-ключа, пользовательские меню, ввод данных, сам сбор статистики и, конечно, тестирование. Попробуйте этот кейс в своей компании при возможности. А рассказы о результатах и лайфхаки по агрегации статистики ждем в комментариях. Может быть, у вас есть свои интересные решения? Будем рады узнать о них!

Как усмирить данные о звонках в таблицах одним скриптом