Trying to send multiple SMS via twilio in Google Appscript but keep getting errors

Question

Tried this, but the steps are not so clear : https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html

The author said to define "to" and "body" under Myfunction but the example doesn't show that.

Perhaps i understand it wrongly.

function sendSms(to, body) {

var ACCOUNT_SID = "#########################";
var ACCOUNT_TOKEN = "#########################";
var messages_url = "https://api.twilio.com/2010-04-01/Accounts/" + ACCOUNT_SID + "/Messages.json";

  var payload = {
    "To": "01#######",
    "Body" : "#### Test",
    "From" : "+1201########"
  };

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

  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode(ACCOUNT_SID + ":" + ACCOUNT_TOKEN)
  };

  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, 31) 
  var data = dataRange.getValues();

  for (i in data) {
    var row = data[i];
    try {
      response_data = sendSms(row[29], row[30]);
      status = "Msg Sent";
    } catch(err) {
      Logger.log(err);
      status = "Error";
    }
    sheet.getRange(startRow + Number(i), 31).setValue(status);
  }
}

function specialFunction() {
//var to = "";
//var body = "Tender"
//sendSms(to,body);
sendAll();
}

Error:

30008 - Unknown error
The "Error" or "Msg_Sent" always exceeds my current number of rows(11 rows) but goes upto 18 rows where there isnt a corresponding data to sms.

Please include the code and the errors so someone might be able to solve the issue — James Z, Apr 03 '21 at 17:53

score 0 · Accepted Answer · answered Apr 06 '21 at 06:08

In your payload in sendSms you're overwriting to, you don't want to do this:

var payload = {
  "To": "+60168522468",
  "Body" : "RM0.00 Test",
  "From" : "+12014313278"
};

Replace "To": "+60168522468", with "To": to, otherwise the SMS is always send to the same recipient.

Also make sure that the rest of the rows are really empty as getLastRow() returns the position of the last row that has content, see the doc.

If possible try to share a sample of the spreadsheet so that we can investigate further.

Thank you @yvesonline – King Michael King Apr 08 '21 at 11:08 — King Michael King, Apr 08 '21 at 11:08

Trying to send multiple SMS via twilio in Google Appscript but keep getting errors

1 Answers1