Вы когда-нибудь хранили номера телефонов своих клиентов в Google Таблицах? Как думаете, было бы удобно оповещать клиентов о каких-то событиях или акциях, не копируя номера в другую систему, а прямо из Таблиц?

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

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

• аккаунт Google;

• аккаунт разработчика в MTC Exolve;

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

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

Для начала нам нужно создать Google Таблицу и заполнить её предварительными данными:

• номер получателя;

• ID голосового SMS-сообщения в приложении;

• ID вызова. Это поле будет заполняться после отправки сообщения, можно использовать для дальнейшего сбора статистики.

Ваша таблица будет выглядеть вот так:

Столбцы Google Таблицы как переменные

Мы создали три столбца в таблице и заполнили их тестовыми данными. Теперь давайте начнем писать код и и представим в нём столбцы как переменные.

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

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

var CUSTOMER_PHONE_NUMBER = 0;

var VOICE_SMS_ID = 1;

var CALL_ID = 2;

Таким образом мы определяем предварительно созданные в таблице столбцы как переменные.

Безопасная передача API-ключа и номера телефона в Google Таблицы

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

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

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

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

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

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

Скопируйте и вставьте следующий код в Apps Script под переменными, которые мы задали в предыдущем пункте:

var API_KEY = 'placeholder';

var EXOLVE_PHONE_NUMBER = 'placeholder';

var ui = SpreadsheetApp.getUi();

var userProperties = PropertiesService.getUserProperties();

function onOpen() {

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

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

   .addItem('Ввести номер приложения', 'setExolvePhoneNumber')

   .addToUi();

};

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

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

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

В коде выше мы добавили два пункта пользовательского меню и дали названия будущим функциям, которые будут запускаться при выборе этих пунктов.

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

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

function setApiKey() {

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

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

};

function setExolvePhoneNumber() {

 var scriptValue = ui.prompt('Введите номер приложения, с которого отправить голосовые SMS' , ui.ButtonSet.OK);

 userProperties.setProperty('EXOLVE_PHONE_NUMBER', scriptValue.getResponseText());

};

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

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

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

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

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

Обратите внимание! Форма не предполагает валидацию введенных данных, поэтому вам нужно убедиться, что API-ключ и номер телефона введены правильно: API-ключ должен быть полностью скопирован из личного кабинета, номер телефона должен быть в формате 7XXXXXXXXXX. 

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

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

function onOpen() {

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

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

   .addItem('Ввести номер приложения', 'setExolvePhoneNumber')

   .addToUi();

  ui.createMenu('Отправка сообщений')

   .addItem('Отправить голосовые SMS', 'sendVoiceSmsToAll')

   .addToUi();

};

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

Функция для отправки сообщения

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

function sendVoiceSms(customerPhoneNumber, voiceSmsId) {

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

 var exolvePhoneNumber = userProperties.getProperty('EXOLVE_PHONE_NUMBER');

 var url = 'https://api.exolve.ru/call/v1/MakeVoiceMessage';

 var options = {

  "method": "post",

  "headers": {

    "Authorization": "Bearer " + apiKey

  },

  "payload": JSON.stringify({

    from: exolvePhoneNumber.toString(),

    to: customerPhoneNumber.toString(),

    service_id: voiceSmsId  

    })

  };

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

  var data = JSON.parse(response);

  return data.call_id;

};

В приведённом выше фрагменте кода мы получаем свои учётные данные (API-ключ приложения и номер телефона) и сохраняем их как переменные, а затем используем эти переменные для выполнения HTTP POST запроса к МТС Exolve API. POST включает в себя номер телефона, с которого мы хотим отправить голосовое SMS (задается из пользовательского меню), номер телефона клиента, и ID голосового сообщения, загруженного в Личный кабинет разработчика. 

Если POST запрос будет успешным, клиент получит голосовое SMS, а столбец «ID вызова» заполнится уникальным идентификатором вызова. Вы сможете использовать этот ID, чтобы посмотреть детали отправки голосового SMS в статистике в личном кабинете.  

Функция для отправки сообщений всем пользователям в Google Таблице

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

 function sendVoiceSmsToAll () {

  var sheet = SpreadsheetApp.getActiveSheet();

  var rows = sheet.getDataRange().getValues();

  var headers = rows.shift();

  rows.forEach(function(row) {row[CALL_ID] = sendVoiceSms(row[CUSTOMER_PHONE_NUMBER], row[VOICE_SMS_ID]);});

  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

};

В приведенном выше фрагменте кода мы получаем все данные из Google Таблицы, удаляем строку с именем столбцов, а далее запускаем функцию отправки голосового SMS для каждой из строк с контактными данными. 

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

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

var CUSTOMER_PHONE_NUMBER = 0;

var VOICE_SMS_ID = 1;

var CALL_ID = 2;

var API_KEY = 'placeholder';

var EXOLVE_PHONE_NUMBER = 'placeholder';

var ui = SpreadsheetApp.getUi();

var userProperties = PropertiesService.getUserProperties();

function onOpen() {

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

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

   .addItem('Ввести номер приложения', 'setExolvePhoneNumber')

   .addToUi();

  ui.createMenu('Отправка сообщений')

   .addItem('Отправить голосовые SMS', 'sendVoiceSmsToAll')

   .addToUi();

};

function setApiKey() {

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

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

};

function setExolvePhoneNumber() {

 var scriptValue = ui.prompt('Введите номер приложения, с которого отправить голосовые SMS' , ui.ButtonSet.OK);

 userProperties.setProperty('EXOLVE_PHONE_NUMBER', scriptValue.getResponseText());

};

function sendVoiceSms(customerPhoneNumber, voiceSmsId) {

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

 var exolvePhoneNumber = userProperties.getProperty('EXOLVE_PHONE_NUMBER');

 var url = 'https://api.exolve.ru/call/v1/MakeVoiceMessage';

 var options = {

  "method": "post",

  "headers": {

    "Authorization": "Bearer " + apiKey

  },

  "payload": JSON.stringify({

    from: exolvePhoneNumber.toString(),

    to: customerPhoneNumber.toString(),

    service_id: voiceSmsId  

    })

  };

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

  var data = JSON.parse(response);

  return data.call_id;

};

 function sendVoiceSmsToAll () {

  var sheet = SpreadsheetApp.getActiveSheet();

  var rows = sheet.getDataRange().getValues();

  var headers = rows.shift();

  rows.forEach(function(row) {row[CALL_ID] = sendVoiceSms(row[CUSTOMER_PHONE_NUMBER], row[VOICE_SMS_ID]);});

  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);

};

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

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

После ввода данных, перейдите в пользовательское меню «Отправка сообщений» > «Отправить голосовые SMS». Как только вы нажмёте на этот пункт меню, запустится функция sendVoiceSmsToAll() и отправит голосовое SMS контактам из Google Таблицы.

Вот так выглядит пункт созданного пользовательского меню по отправке голосовых SMS:

После запуска, на указанный контактный номер в Google Таблицах поступит вызов с голосовой SMS, а столбец «ID вызова» в Google Таблице заполнится уникальными идентификаторами вызова:

Заключение

Вы можете хранить базу данных номеров и ID сообщений для отправки в Google Таблицах и с помощью подобного скрипта в Apps Scripts отправлять голосовые SMS своим клиентам в одно нажатие пункта из пользовательского меню. Автоматическое заполнение третьего столбца «ID вызова» покажет вам, что сообщение отправлено. Позже вы сможете использовать этот ID, чтобы посмотреть детали отправки голосового SMS сообщения в своём Личном кабинете разработчика.

Спасибо за уделенное статье время! Если у вас есть вопросы – с удовольствием отвечу на них в комментариях.