СМС-рассылка с Google Sheets API

Недавно от знакомого фриланс-работодателя поступило предложение о небольшой работе, связанной с организацией смс-рассылки, данные для которой должны браться из .xls-таблицы. Плюс к этому было необязательное пожелание вынести эту же таблицу на корпоративный веб-сайт с обязательной возможностью её редактирования. При этом был озвучен весьма малый бюджет максимум на два человеко-дня.

Минимальное исследование существующих веб-инструментов работы с электронными таблицами показало, что разработка на их основе потребует времени в разы больше. Например, связка PHPExcel и какой-нибудь AJAX-таблицы выливалась в разработку функционала полноценного сайта. К тому же требующего отдельного хостинга в целях безопасности. Теоретически рассматривался вариант MS Excel + VB script, но и тут были свои подводные камни. В итоге выбор пал на Google Sheets.

Прежде всего нужно иметь учетную запись Google. Далее, зайдя в нее, переходим на http://sheets.google.com/. Сюда можно загрузить .xls-файл. После загрузки таблицы был написан первый скрипт отправки смс. Представляет собой формирование и отсылку определенного http POST запроса.

function sendHttpOneSms(msgStr, receiver) {
   var payload = 
   { // данные для sms-рассылки
     "user" : "xxxx",
     "pass" : "*******",
     "action": "post_sms",
     "message": msgStr,
     "target": receiver
   };

   var options = 
   { // опции для http-запроса
     "method" : "post", 
     "payload" : payload,
     "muteHttpExceptions" : true
   };

  var result = UrlFetchApp.fetch("http://сайт-рассылки/sendsms/", options);
   Logger.log(result.getContentText());
 }

Далее формируем простейший ежедневный триггер.

function dailySend()
{
  var sendText = "Текст простого уведомления";
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues(); // получение массива ячеек активного листа
  var dateToday = new Date(); // дата на сегодня
  
  for(var i=0; i<data.length; i++) // цикл по всем строкам
  {
   var dateSend = parseDate(data[i][3]); 
    if(compareDate(dateToday, dateSend)) // если дата совпадает с сегодняшней, то
      sendHttpOneSms(sendText, data[i][5]); // осуществить отправку смс
  }
}

В этом триггере есть две функции. Первая — простая — compareDate. Ее задача — сравнить две даты не учитывая время.

Вторая функция сложнее, и, несмотря на маленький объем, на её написание у меня ушло довольно много времени. Дело в том, что при попытке прочитать дату из ячейки я получал непонятное пятизначное число, автоматически не преобразуемое ни к какому другому типу. Google API здесь нагло врали, говоря, что должен быть объект типа Date. А здесь и не Date, и даже не строка, которую тоже можно было бы ожидать, а число. Оно очевидно не соответствовало unix-формату (количество миллисекунд с полуночи 1 января 1970 года). Эмпирически было установлено, что число, скорее всего, представляет собой количество дней с 1 января 1900 года. После этого функция преобразования даты в удобоваримый формат стала очевидной:

function parseDate(dec)
{
  return new Date((dec-70*365-19)*24*3600*1000);
}

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

ScriptApp.newTrigger("dailySend")
   .timeBased() // Тип триггера - по времени
   .atHour(12) // время вызова - около 12 часов
   .everyDays(1)  // Частота вызова - раз в день
   .create();

Полезные ссылки

Google API для электронных таблиц
Класс ClockTriggerBuilder
Google класс UrlFetchApp для использования http-запросов