Google表格和Twilio集成-Java 学习之路

我正在使用Greg Baugues（https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html）的代码使用Google工作表和Twilio批量发送文本 .

我想告诉sendSMS和/或sendAll跳过现有的表单条目，其中'sent'出现在相关单元格中，以便允许表单作为数据库以及仅仅是消息传递机器 .

有任何想法吗？

（XXX条目只是简化我的Twilio帐户和电话号码） .

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();
}

2 回答

您需要编辑 sendAll() 函数，以便它测试状态列的值以确定它是否应该跳过该行 .

用这个：

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);
    }
}

回复于 2024-05-01T09:00:44+08:00

这是有效的（谢谢安迪）：

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);
}
}

回复于 2024-05-01T09:00:44+08:00

Google表格和Twilio集成

2 回答

相关问题