CODEWITHSUNDEEP
google-sheetstwilio
arul selvan
arul selvan

Reputation: 624

Send SMS from Google Sheet

I got the script to send SMS for all the rows in a google sheet using Twilio example.

I want to send SMS acknowledgement to my customers from the below google sheet

https://docs.google.com/spreadsheets/d/1Jpka0Wn8cQ6J6Be8Ks5vF-JJ50ykdCuMIetrWjAi7Kw/edit?usp=sharing

I want SMS to go only once to all the customers for whom the Status is not starting with "Sent" and where the phone number is starting with "+91"

The SMS will be like this

Complaint No "SER 160530" with us registered on "16/5/16" for customer "TNEB" due to "CT Failed". Please call 18004257865 for details

The message is made up of some text and the value from some cells in a particular row The "Status" column must get updated "Sent SMS to xxxxxxxx on xxxx at xx:xx:xx:xx" Is it possible to run the script every one hour automatically? Is there any free alternative to send SMS from Google sheet?

Upvotes: 1

Views: 1635

Answers (2)

Marcus Zethraeus
Marcus Zethraeus

Reputation: 13

Sending A2P sms, that is sms from an application to a person, costs money, because operators charge for accessing their networks. There might exist some free routes as Torey mentions, but they have limitations and could be pretty unreliable.

Instead of scripting your own sms service, you can try Cloudcom, which is a Google Sheets add-on with a user interface. Full disclosure, I have built and sell this product. Some of the features that could be useful in your case are scheduling and merging custom fields into the message body.

Upvotes: 0

Torey Price
Torey Price

Reputation: 397

To execute your script every hour you can set up a time driven trigger. Here is the documentation for setting up a trigger.

Here is my workaround for sending texts out of a Google Sheet for free, it might not work for your specific need, but is an option:

Every mobile phone carrier offers an email to sms option that is free. Examples of these email addresses are here. For example if I am wanting to text someone on the Sprint network I would attach the phone number to their domain like: [email protected]. If you are able to collect or look up the carrier for the phone numbers you can set up a simple function to connect the two and use a mail merge option like the one below to send out your texts. Here is the documentation the the mail merge that will mark sent messages and prevent multiple texts from going out to the same contact.

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Upvotes: 1

Related Questions