CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstwilio
jon_win
jon_win

Reputation: 83

Google Sheets & Twilio Integration

I'm using this code from Greg Baugues (https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html) to send texts in bulk using Google sheets and Twilio.

I want to tell sendSMS and/or sendAll to skip existing sheet entries where 'sent' appears in the relevant cell, so as to allow the sheet to function as a database as well as a mere messaging machine.

Any ideas?

(The XXX entries are simply to anonymize my Twilio account and phone number). 

function onOpen() { 
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: "Send text msg to client", functionName: 
"sendAll"}, ];
ss.addMenu("Text", menuEntries);

}

function sendSms(to, body) {
var messages_url = "https://api.twilio.com/2010-04-
01/Accounts/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/Messages.json";

var payload = {
"To": to,
"Body" : body,
"From" : "+XXXXXXXXXX"
};

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

options.headers = { 
"Authorization" : "Basic " + 

Utilities.base64Encode
("XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX")
};

UrlFetchApp.fetch(messages_url, options);
}

function sendAll() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; 
var numRows = sheet.getLastRow() - 1; 
var dataRange = sheet.getRange(startRow, 1, numRows, 4) 
var data = dataRange.getValues();

for (i in data) {
var row = data[i];
try {
  response_data = sendSms(row[1], row[2]);
  status = "sent";
} catch(err) {
  Logger.log(err);
  status = "error";
}
sheet.getRange(startRow + Number(i), 4).setValue(status);
}
}

function myFunction() {
sendAll();
}

Upvotes: 0

Views: 451

Answers (2)

jon_win
jon_win

Reputation: 83

This works (thanks Andy): 

function sendAll() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; 
var numRows = sheet.getLastRow() - 1; 
var dataRange = sheet.getRange(startRow, 1, numRows, 5) 
var data = dataRange.getValues();


for (i in data) {

var status;

var row = data[i];
if (row[3] !== "sent") {
  try {
    response_data = sendSms(row[1], row[2]);
    status = "sent";
  } catch(err) {
    Logger.log(err);
     status = "error";   
  } 
    }
    sheet.getRange(startRow + Number(i), 4).setValue(status);
}
}

Upvotes: 0

Andy
Andy

Reputation: 698

You need to edit your sendAll() function so that it tests the value of the status column to decide if it should skip that row or not.

Use this:

function sendAll() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; 
    var numRows = sheet.getLastRow() - 1; 
    var dataRange = sheet.getRange(startRow, 1, numRows, 4) 
    var data = dataRange.getValues();

    for (i in data) {
        var row = data[i];
        try {
            if (row[3] != "sent") {
                response_data = sendSms(row[1], row[2]);
                status = "sent";
            }
        } catch(err) {
            Logger.log(err);
            status = "error";
        }
        sheet.getRange(startRow + Number(i), 4).setValue(status);
    }
}

Upvotes: 1

Related Questions