发送twilio SMS消息的Google表脚本在手动运行时有效，但在按时间触发时失败 [英] Google sheets script sending twilio SMS messages works when run manually but fails when triggered by time

问题描述

我正在尝试构建一个Google工作表电子表格来发送短信提醒。在这个阶段，我仍然只是构建从谷歌表发送短信的基础知识。我已经用他们的名字替换了一些字段，例如API SID和令牌，以及我的电话号码。我的代码如下：

  //目前这似乎有效手动运行，但不是由时间或其他触发时。函数sendSms（to，body）{var messages_url =https://api.twilio.com/2010-04-01/Accounts/API_SID_GOES_HERE/Messages.json; //这包含我的Twilio API LIVE SID var payload = {To：to，Body：body，From：61123456789//这是twilio电话号码}; var options = {method：post，payload：payload}; options.headers = {授权：基本+ Utilities.base64Encode（API_LIVE_TOKEN_GOES_HERE）//这包含我的Twilio API LIVE Token}; UrlFetchApp.fetch（messages_url，options）;} function sendAll（）{var spreadsheet = SpreadsheetApp.openById（SPREADSHEET_ID_NUMBER）//这部分是电子表格的表格编号var sheet = SpreadsheetApp.setActiveSheet（spreadsheet.getSheets（）[ 2]）; //这个小小的feller设置了一个它操作的活动表。工作表计数从[0] var startRow = 2开始; //从第2行开始计数var numRows = sheet.getLastRow（） -  1; var dataRange = sheet.getRange（startRow，1，numRows，2）// getRange（整数行，整数列，整数numRows，整数numColumns）var data = dataRange.getValues（）; //只获取单元格的值，而不是（i in data）{var row = data [i];尝试{response_data = sendSms（row [0]，row [1]）; status =已发送; } catch（err）{Logger.log（err）; status =错误; } sheet.getRange（startRow + Number（i），4）.setValue（status）; //（i）之后的数字是setValue（Status）将着陆的列。函数myFunction（）{sendAll（）;}  

它似乎如果我手动运行代码，它工作正常，发送短信，更新'状态'字段，一切都很好。但是，如果我为脚本设置了触发器，无论是固定时间触发器还是每分钟触发器，脚本都会失败。谷歌应用程序脚本发送给我的失败摘要是：

请求失败

一旦我意识到这一点，并改变了它，它运作得很好。

感谢所有为他们提供帮助的人，特别是Darpan跟我一起追逐红鲱鱼。

I am trying to build a google sheets spreadsheet to send SMS reminders. At this stage, I'm still just building the basics of sending SMS from google sheets. I have replaced some fields with their names, eg the API SID and token, and my phone number. My code is below:

//At the moment this seems to work when manually run, but not when triggered by time or whatever.


function sendSms(to, body) {
  var messages_url = "https://api.twilio.com/2010-04-01/Accounts/API_SID_GOES_HERE/Messages.json"; //This contains my Twilio API LIVE SID
 
  var payload = {
    "To": to,
    "Body" : body,
    "From" : "61123456789" //This is the twilio phone number
  };
 
  var options = {
    "method" : "post",
    "payload" : payload
  };
 
  options.headers = { 
    "Authorization" : "Basic " + Utilities.base64Encode("API_LIVE_TOKEN_GOES_HERE") //This contains my Twilio API LIVE Token
  };
 
  UrlFetchApp.fetch(messages_url, options);
}
 
function sendAll() {
  var spreadsheet = SpreadsheetApp.openById("SPREADSHEET_ID_NUMBER") //This part is the ID number of the spreadsheet
  var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[2]); //This little feller sets an active sheet which it manipulates. Sheet count starts from [0]
  var startRow = 2; //starts counting from row #2
  var numRows = sheet.getLastRow() - 1; 
  var dataRange = sheet.getRange(startRow, 1, numRows, 2) //getRange(Integer row, Integer column, Integer numRows, Integer numColumns)
  var data = dataRange.getValues(); //Gets just the values of the cells, not the formulas
 
  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), 4).setValue(status); //The number after (i), is the column where the setValue(Status) will land.
  }
}
 
function myFunction() {
  sendAll();
}

It seems that if I run the code manually, it works fine, sends an SMS, updates the 'status' field and all is well. However, if I set a trigger for the script, be it a fixed time trigger or an every minute trigger, the script fails. The Summary of Failures that google apps script sends me is:

Request failed for https://api.twilio.com/2010-04-01/Accounts/MY_ACCOUNT_NUMBER/Messages.json 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) (line 19, file "Code")

I can't quite figure out why. I think it has something to do with var spreadsheet and var sheet, but I really don't know.

Most of the code has been copied from this twilio blog post.

解决方案

I figured this out, and it was something terribly obvious.

When setting up the trigger, I had completely overlooked the need to define which function I wanted it to trigger. By default, it was just running function sendSMS, which, at that point, had not been fed any data to send.

So my manual runs were running the function called myFunction, but the triggered runs were running sendSMS.

Once I realized this, and changed it, it worked just fine.

Thanks to all those who contributed for their help, especially Darpan for chasing the red herring with me.

这篇关于发送twilio SMS消息的Google表脚本在手动运行时有效，但在按时间触发时失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！