Scripts, scripts, scripts!

Ask for Help
41

A Script to send SMS notifications via Twilio or Email via your gmail account in case phone number is not available

    function Twilio() {
  
    
  var ss = SpreadsheetApp.getActive().getSheetByName("Notifications");
  var values = ss.getRange("A2:H").getValues();

  for (var i in values) {
     var row=i;
     var r = +row + 2;
     if (values[i][5] != '' && values[i][7] == '' ) {
       var number = values[i][5]
       var message = values[i][6]
       
       function sendSms(to, body) {
  var messages_url = "Your Twilio URL";

  var payload = {
    "To": to,
    "Body" : body,
    "From" : "Twilio phone number"
  };

  var options = {
    "method" : "post",
    "payload" : payload
  };

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("Your API Code")
  };

  UrlFetchApp.fetch(messages_url, options);
}
       try {
      response_data = sendSms(number, message);
      status = "sent";
    } catch(err) {
      Logger.log(err);
      status = "error";
    }
    ss.getRange(r, 8).setValue(status);
       
      
         
     }  
    if (values[i][5] == '' && values[i][7] == '' ) {
         Utilities.sleep(1000);
      var mail = values[i][2]
       var message = values[i][6]
       var row = +i + 2
       ss.getRange(row, 8).setValue("Email Sent");
         MailApp.sendEmail(mail, "Your Subject line", message, {name:"name of sender"})
        
       SpreadsheetApp.flush();
       }
  }
}

This script runs every minute and checks if there are new rows in the sheet, if there are, it will pull up the number, body of the SMS and send an SMS via Twilio. (In case there’s no number, it sends a mail)
Once it’s run, it sets a value in a trigger column which makes sure that the message is only sent once to each user.

10 Likes