Apps Scripts Code

Copy Code

var TIME_ENTERED = 0;
var PHONE_NUMBER = 1;
var MESSAGE = 2;
var STATUS = 3;




var TWILIO_ACCOUNT_SID = 'ACbbb0b79a9e99d5e41052ed28d6fc8269*************';
var TWILIO_SMS_NUMBER = '18316030095';
var TWILIO_AUTH_TOKEN = 'ACbbb0b79a9e99d5e41052ed28d6fc8269*************:c614c4820abf5c9ddab2ac7658c518fd*************';


function onOpen() {
  // To learn about custom menus, please read:
  // https://developers.google.com/apps-script/guides/menus
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Send SMS')
      .addItem('Send to all', 'sendSmsToAll')
      .addToUi();
};  


function sendSmsToAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange().getValues();
  
  // The `shift` method removes the first row and saves it into `headers`.
  var headers = rows.shift();
  
  // Try to send an SMS to every row and save their status.
  rows.forEach(function(row) {
    row[STATUS] = sendSms(row[PHONE_NUMBER], row[MESSAGE]);
  });


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

function sendSms(phoneNumber, message) {
  var twilioUrl = 'https://api.twilio.com/2010-04-01/Accounts/' + TWILIO_ACCOUNT_SID + '/Messages.json';


  try {
    UrlFetchApp.fetch(twilioUrl, {
      method: 'post',
      headers: {
        Authorization: 'Basic ' + Utilities.base64Encode(TWILIO_AUTH_TOKEN)
      },
      payload: {
        To: phoneNumber.toString(),
        Body: message,
        From: TWILIO_SMS_NUMBER,
      },
    });
    return 'sent: ' + new Date();
  } catch (err) {
    return 'error: ' + err;
  }
}