I'm trying to send texts on a time trigger on a google sheet using Twilio to send the texts. When I click the run button it works fine but when I set up a time trigger it says its in status it's completed but does not work. Also when I click on Executions I do get an error it says "Exception: Request failed for https://api.twilio.com returned code 400. Truncated server response: {"code": 21211, "message": "The 'To' number is not a valid phone number.", "more_info": "https://www.twilio.com/docs/errors/21211", "status": 400} (use muteHttpExceptions option to examine full response)"
I'm 100% new to Java and am learning about it any help would be great.
Here is my Code:
function RunThisToSendTexts() {
function sendSms(to, body) {
var messagesUrl = "https://api.twilio.com/2010-04-01/Accounts/AC9837381a431941024fc87xxxxxx/Messages.json";
var payload = {
"To": to,
"Body" : body,
"From" : "+197131999xx"
};
var options = {
"method" : "post",
"payload" : payload
};
options.headers = {
"Authorization" : "Basic " + Utilities.base64Encode("AC9837381a431941024fcxxxxxxx:22c7dec97dee0cef39exxxxxxx")
};
UrlFetchApp.fetch(messagesUrl, options);
}
function sendAll() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow() - 1;
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
try {
response_data = sendSms(row[0], row[1]);
status = "sent";
} catch(err) {
Logger.log(err);
status = "error";
}
sheet.getRange(startRow + Number(i), 3).setValue(status);
}
}
sendAll();
}
Try writing it like this:
function sendSms(to, body) {
var messagesUrl = "https://api.twilio.com/2010-04-01/Accounts/AC9837381a431941024fc87xxxxxx/Messages.json";
var payload = {
"To": to,
"Body" : body,
"From" : "+197131999xx"
};
var options = {
"method" : "post",
"payload" : payload
};
options.headers = {
"Authorization" : "Basic " + Utilities.base64Encode("AC9837381a431941024fcxxxxxxx:22c7dec97dee0cef39exxxxxxx")
};
UrlFetchApp.fetch(messagesUrl, options);
}
function sendAll() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow() - 1;
var dataRange = sheet.getRange(startRow, 1, numRows, 2)
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
try {
response_data = sendSms(row[0], row[1]);
status = "sent";
} catch(err) {
Logger.log(err);
status = "error";
}
sheet.getRange(startRow + Number(i), 3).setValue(status);
}
}
And just run sendAll();
Try sendAll() this way:
function sendAll() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getActiveSheet();
const sr = 2;
const data = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getValues();
var status = 'Error'
data.forEach((r, i) => {
sendSms(r[0], r[1]);
status = 'sent';
});
sh.getRange(i + sr, 3).setValue(status);
}