如何根据 Google 表格中的单元格内容发送短信

这是我上一个问题的后续如何循环 onEdit 函数从 Google 表格中的多行发送电子邮件？ https://stackoverflow.com/questions/56533837/how-to-loop-an-onedit-function-to-send-emails-from-multiple-rows-in-google-sheet现在为了完成这个项目，我需要能够根据单元格的内容发送短信。

我正在使用 Twilio 以及他们示例中的代码https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html https://www.twilio.com/blog/2016/02/send-sms-from-a-google-spreadsheet.html允许我在运行该函数时向电子表格中的所有数字发送文本。这个问题有一些帮助从 Google 表格发送短信 https://stackoverflow.com/questions/37498715/send-sms-from-google-sheet然而，由于我使用 Twilio 而不是运营商电子邮件，我仍然陷入困境。

截至目前，第一段代码允许在我运行该函数的同时发送所有文本（sendSMS 包含所有 API 信息，但未显示）：

function sendAll() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('HIVE');
  var startRow = 2; 
  var width = 16;
  var numRows = sheet.getLastRow() - 1; 
  var dataRange = sheet.getRange(startRow, 2, numRows, width) 
  var data = dataRange.getValues();

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

我尝试通过插入以下内容来复制上一个问题中用于发送电子邮件的模式：

if (sheet.getSheetName() == sheetname && range.columnStart == 1) {
    var data = sheet.getRange(range.getRow(), 1, 1, 21).getValues()[0];
    var object = {
      to: data[5] // Column "E"
    if (e.value == "Appt. Set (send text)") {
      object.subject = "Appt. Confirmation";
      object.body = apptText; // variable containing body text

      if (object.subject) sendSms(object);

我已将代码更新到将触发 onEdit 的位置，但仅限于第一个 IF 语句：

function onEditText(e) {
  var sheetname = "HIVE";
  var sheet = e.range.getSheet();
  var range = e.range;
  var timezone = "GMT-5";
  var timestamp_format = "MMMM dd 'at' HH:mm";
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
  var twilioNumber = 1234567890;

  if (sheet.getSheetName() == sheetname && range.columnStart == 1) {
    var data = sheet.getRange(range.getRow(), 1, 1, 21).getValues()[0];
    var object = {
      to: data[5] // Column "E"
    };
    var apptText = "Hey " + data[9] + "! ????\n\nThanks...;
    var leadText = "Hey " + data[9] + "! ????\n\nThanks so...;
    var followText = "Hey " + data[9] + "! ???? \n\nAre...;
    var confirmText = "Hey " + data[9] + "! ⏰ \n\nYour appointment...;

    if (e.value == "Appt. Set (send text)") {
      object.subject = "Appt. Confirmation";
      object.body = apptText; //
      SpreadsheetApp.getActiveSheet().getRange(range.getRow(),2,1,1).setValue('Appt. Set Text sent on ' + date);
    } else if (e.value == "Lead (send 1st text)") {
      object.subject = "Lead";
      object.body = leadText; //
      SpreadsheetApp.getActiveSheet().getRange(range.getRow(),2,1,1).setValue('Lead 1st Text sent on ' + date);
    } else if (e.value == "3rd Text") {
      object.subject = "Follow Up";
      object.body = followText; 
      SpreadsheetApp.getActiveSheet().getRange(range.getRow(),2,1,1).setValue('3rd Text sent on ' + date);
    }else if (e.value == "Day of Confirm (send text)") {
      object.subject = "Can't wait to meet you!";
      object.body = confirmText;  
      SpreadsheetApp.getActiveSheet().getRange(range.getRow(),2,1,1).setValue('Confirmation Text sent on ' + date);
    }
    if (object.subject) sendSms(data[5], twilioNumber);
  }
}