Solución para el agendamiento de clientes y notificaciones SMS de forma periódica mensual y trimestral.

Google Forms

Accedemos a Google Forms y creamos el un nuevo formulario con los campos que necesitemos. Es muy intuito construirlo así que no hará falta entrar en detalles.

Google Sheets

Procedemos a vincular una hoja de cálculo para almacenamiento de datos.

Nos dirigimos a la sección de Respuestas 1, creamos una hoja de cálculo dando clic en el icono 2 y por ultimo le damos al botón crear 3

Podemos personalizar el documento dando algún encabezado, color o agregando columnas, filas que se necesiten. Y si lo notaron la primera fila se esta utilizando para almacenar información que necesitaremos posteriormente.

Apps Scripts

Agendar Actividades.

Empezamos llenando el formulario para almacenar el primer registro en la hoja de calculo y proceder a programar el script para agendar. Para hacerlo debemos obtener el enlace y lo hacemos de la siguiente manera. Nos dirigimos al botón enviar 1 y luego damos a copiar 2.

Verificamos que el registro se haya insertado en la hoja de calculo y procedemos a dar clic en la opción Apps Script ubicada en la sección de Extensiones

Añadimos un nuevo archivo con el el nombre AgendarNotificacionCalendario.gs y creamos las siguientes funciones.

function insertLastDeclaracion() {
  /** Open the Event Calendar **/
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId= spreadsheet.getRange("B1").getValue();

  var lastRow = spreadsheet.getLastRow();
  var duracionActividad = spreadsheet.getRange("D1").getValue(); 

  var currentRecord= "A"+lastRow+":Q"+lastRow;
  var data = spreadsheet.getRange(currentRecord).getValues();

  if(data != null || data.length > 0)
  {
    scheduleNotificationCalendar(data[0],calendarId,duracionActividad);
  }

}

function scheduleNotificationCalendar(row, calendarId, duracionActividad) {
  /** Asignar registro **/
  var data = row;

  /** Open the Event Calendar **/
  var calendarId= calendarId;
  var eventCal = CalendarApp.getCalendarById(calendarId);

  /** Formatter current date for get current time **/
  var currentTime = Utilities.formatDate(new Date(),"GMT-5", "HH:mm:ss z");

  var formatter = Utilities.formatDate(data[8],"UTC","E MMM dd yyyy");
  var result = formatter+" "+currentTime;

  /** Set startTime and endTime **/
  var startTime = new Date(result);
  var endTime= new Date(startTime.getTime() + (duracionActividad * 60 * 60 * 1000));

  var name= data[3];
  var user= data[2];
  var datosVarios= data[9];

  var options = {
    description: '<b>Realizar la declaración de '+name
    +'.</b>\nCredenciales:'
    +'\nCedula: '+ user
    +'\nDatos Varios: '+datosVarios
  };

  var periodType = data[7];
  /** Schedule Activity depending on the type **/
  if (periodType == "Mensual")
  {
  eventCal.createEventSeries(name,startTime,endTime,CalendarApp.newRecurrence().addMonthlyRule(),options);
  }
  else
  {
    eventCal.createEventSeries(name,startTime,endTime,CalendarApp.newRecurrence().addMonthlyRule().interval(6),options);
  }
}

Activador - Ejecutar la función de agendar.

Nos dirigimos a la sección Activadores y le damos en Añadir activador

Seleccionamos la función que se va a ejecutar y cuando se va a ejecutar. En este caso será al enviarse la información llena del formulario que creamos, como podemos ver en la imagen.

Apis de envío de SMS.

Existen mas de las siguientes 3 plataformas que se muestran a continuación. En las 3 debemos crearnos una cuenta y esta nos dará un balance gratis o un numero limitado de SMS gratuitos que podemos utilizar para realizar envíos de pruebas.

Twilio.

Nos creamos una cuenta en Twilio. Con esto listo, creamos un contenedor para nuestra implementación dando clic en Nueva Cuenta. Cumplido estos pasos nos mostrará la consola de administración con un Balance Gratuito de $15.50.

Compramos un numero que va hacer el remitente de nuestro SMS. Contamos con una gran variedad de filtros y opciones que podemos escoger y le damos a comprar.

Para mas detalle podemos acceder al siguiente enlace Trial Account

Código Apps Script

Añadimos un nuevo archivo con el el nombre NotificacionSMS.gs y creamos las siguientes funciones.

function notificacionSMS() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 6; 
  var diasAnticipacion = sheet.getRange("F1").getValue(); 
  var numRows = sheet.getLastRow() - (startRow-1); 
  var dataRange = sheet.getRange(startRow, 4, numRows, 3); 
  var data = dataRange.getValues();

  var currentDate = new Date(Utilities.formatDate(new Date(),"GMT-5", "E MMM dd yyyy"));

  var currentDateInit= new Date(currentDate.getTime()+diasAnticipacion*(24*3600*1000));
  var currentDateEnd = new Date(Utilities.formatDate(currentDateInit,"GMT-5", "E MMM dd yyyy 23:59:59"));

  var calendarId= sheet.getRange("B1").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var events = eventCal.getEvents(currentDateInit,currentDateEnd);

  for ( var i in events ) {
    let event = eventCal.getEventById(events[i].getId());

    for (i in data) {
      var row = data[i];
      var result = sheet.getRange(startRow + Number(i), 19);
      var info = sheet.getRange(startRow + Number(i), 20);

      if(row[0] == event.getTitle())
      {
        var celular = row[2];
        //El límite de un mensaje de texto SMS es de 160 
        const messageTwilio="Estudio JJ. Informa que su declaración vence en "+diasAnticipacion+" días Acerquese y evite multas y sanciones Info: 0981243XXX";
        if(celular.toString().trim() != "")
        {
          celular ="+"+celular;
          try
          {
            var responde_data = sendMessagesTwilio(celular,messageTwilio);
            //var responde_data = null;
            result.setValue('SENT').setBackground('#93c47d');
            info.setValue('Sent on ' + new Date());
          }
          catch(err)
          {

            result.setValue('FAILED').setBackground('#e06666');
            info.setValue(String(err).replace('\n', ''));
            Logger.log(err);
          }

        }
      }
    }
  }
}

En nuestro caso se utiliza la plataforma de Twilio para el envío de SMS, se debe remplazar los datos como ACCOUNT_SID, AUTH_TOKEN y From con los que se les refleje en su Consola Twilio.

La URL que se encuentra en StatusCallback es lugar donde se recibirán el estado de cada envío de SMS (sent/delivered/undelivered). Puede crear una API gratuita desde Pipedream

function sendMessagesTwilio(to, body) {

  //ACCOUNT_SID = AC125dfe11d418c8e1ce90a9edf64222cb;
  //AUTH_TOKEN = e1c7cfe24a6b3a9c320f4c56b6c95a57;
  const headers = {
    "Authorization" : "Basic " + Utilities.base64Encode("AC125dfe11d418c8e1ce90a9edf64222cb:e1c7cfe24a6b3a9c320f4c56b6c95a57")
  };
//From, es el numero de remitente que compró.
  const inputBody = {
    "To": to,
    "Body" : body,
    "StatusCallback":"https://ensk2dz2ylzaz15.m.pipedream.net",
    "From" : "+15672021XXX"
  };

  return UrlFetchApp.fetch('https://api.twilio.com/2010-04-01/Accounts/AC125dfe11d418c8e1ce90a9edf64222cb/Messages.json',
  {
    method: 'POST',
    payload: inputBody,
    headers: headers
  });
}

A continuación se deja las 2 funciones para el envío de SMS por la plataforma:

MessageBird.

function sendMessagesMessageBird(to, body) {

  const headers = {
    "Authorization" : "AccessKey LrBM99Cx1RuIvREA0fPSRZQaS"
  };

  const inputBody = {
    "recipients": to,
    "body" : body,
    "reportUrl": "https://ensk2dz2ylzaz15.m.pipedream.net",
    "originator" : "Estudio"
  };

  return UrlFetchApp.fetch('https://rest.messagebird.com/messages',
  {
    method: 'POST',
    payload: inputBody,
    headers: headers
  });
}

Tyntec.

function sendMessagesTyntec(to, body) {
  const headers = {
    'Content-Type':'application/json',
    'Accept':'application/json',
    'apikey':'X8kwkbUN4l6gpsPCHmLFzvcoJgLz4IfX'
  };

  const inputBody = {
    "from" : "tyntec", 
    "to" : to,
    "message": body
  };

  try {
    var result =UrlFetchApp.fetch('https://api.tyntec.com/messaging/v1/sms',
      {
        method: 'POST',
        payload: inputBody,
        headers: headers
      });
    //Consultar el estado de un mensaje por su identificador único
    //var responde =UrlFetchApp.fetch('https://api.tyntec.com/messaging/v1/messages/5c944312-0c6f-4f91-8737-62070599099a');
    return JSON.stringify(result);
  } catch (err) {
    return JSON.stringify(err);
  }
}

Activador - Ejecutar la función de envío SMS.

Podemos crear el activador mediante una función, la que recibe parámetros como atHour, nearMinute y everyDays, que hace que nuestra función se ejecute cada día a las 10 horas 30 minutos aproximadamente.

function setUpTriggerNotificationSMS()
{
  ScriptApp.newTrigger('notificacionSMS')
  .timeBased()
  .atHour(10)
  .nearMinute(30)
  .everyDays(1)
  .create();
}

Referencias.

https://developers.google.com/apps-script/reference/calendar/calendar-app
https://developers.google.com/apps-script/reference/spreadsheet/sheet
https://cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
https://www.twilio.com/docs/usage/tutorials/how-to-use-your-free-trial-account
https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html

Blog

Notificaciones SMS

Jefferson Soria