用Twilio从谷歌表格发送短信通知的详细指南

方石剑
384 阅读13分钟

你是否曾经用电子表格来记录一个人的名单？也许你保留了一份朋友的生日名单，或一份你的副业的活跃客户名单。如果你有一个简单的方法来联系这些人，那不是很酷吗？

在本教程中，我们将使用Twilio的可编程消息API和谷歌表格API，向谷歌表格中列出的客户发送付款提醒。我们将使用谷歌基于JavaScript的语言，即谷歌表格界面中的Apps Script，当客户的付款逾期或即将到期时，以编程方式发送短信提醒。你可以很容易地对这个用例做一些小改动，以适应你自己的独特需要。

请跟着下面的代码片段学习吧

前提条件

A screenshot of the Twilio Console showing where to locate the Account SID and Auth token

创建谷歌工作表

首先，创建一个谷歌工作表。在第一行，按以下顺序分别输入这七个列的名称：

  1. 客户电话号码 - 使用 "纯文本 "单元格格式
  2. 客户名称
  3. 应付金额
  4. 应付日期 - 必须格式化为yyyy-mm-dd ，如2021-10-27
  5. 付款链接
  6. 付款信息 - 简要描述正在支付的内容
  7. 信息的状态 - 我们的脚本运行后将更新这个单元格

接下来，用你自己的名字和普通的（非Twilio）电话号码添加一整行的样本数据，这样你以后就可以测试脚本了。

请确保你在电子表格中输入的电话号码是E.164格式的。如果谷歌表格在你尝试输入这种格式的电话号码时出现问题，请尝试StackOverflow线程中建议的备选方案之一，并确保单元格的格式为 "纯文本"。这样做应该可以解决这个问题。

你的电子表格现在看起来会是这样：

A screenshot showing the column names and a row of sample data

将Google Sheet的列定义为变量

现在我们已经在Google Sheet中创建了列，并播种了一行样本数据，我们需要一种方法来表示我们要写的代码中的列。要访问谷歌表的代码编辑器，首先点击工具标签，然后打开脚本编辑器，如下图所示：

A screenshot showing how to access the Script Editor from the Tools menu in Google Sheets

当Apps脚本编辑器打开时，删除占位符代码(function myFunction() {})。

在Apps脚本编辑器中，通过复制和粘贴以下代码将列定义为变量：

var CUSTOMER_PHONE_NUMBER = 0;
var CUSTOMER_NAME = 1;
var AMOUNT_DUE = 2;
var PAYMENT_DUE_DATE = 3;
var PAYMENT_LINK = 4;    // A URL where the payment can be made
var PAYMENT_INFO = 5;    // Details of the service or product
var MESSAGE_STATUS = 6;  // Whether the SMS was sent or not

注意，代表列名的变量是根据列号定义的，使用的是零基索引。这意味着表单中的第一列(CUSTOMER_PHONE_NUMBER)与0有关，表单中的第二列(CUSTOMER_NAME)与1有关，以此类推。

安全地存储你的Twilio凭证

为了与任何Twilio API互动，你需要在代码中提供你的Twilio账户SID和Twilio Auth Token。因为在本教程中，我们将通过你的Twilio电话号码发送短信通知，你也需要提供你的Twilio电话号码。如果你还没有在Twilio控制台中找到这三个值，请回到本教程的先决条件部分，进一步了解如何找到它们。

当涉及到数字安全时，所有API密钥、认证令牌和任何其他唯一提供给你而不是其他人的值都应该被视为关键的私人信息（就像信用卡号码）。出于这个原因，把你的Twilio凭证复制并粘贴到Apps脚本编辑器中不是一个好主意。这样一来，任何能够访问你的电子表格的人都可以读到你的凭证，黑客可以利用你的凭证在你的Twilio账户上收费

此外，保持你的私人Twilio凭证的安全和不受版本控制是非常重要的。不要在git中检查它们，也不要在互联网上发布它们，包括GitHub上。

幸运的是，我们有办法通过使用UserProperties和自定义菜单，将你的凭证安全地提供给你的Google Sheet。在下面的步骤中，我们将在Google Sheet中创建一个自定义菜单，允许你在一个弹出式模版中输入你的Twilio凭证。然后，你的凭证将被保存在后台，而不会在应用程序脚本编辑器中显示出来。

为输入Twilio凭证创建一个自定义菜单

自定义菜单是一个菜单项，将出现在谷歌工作表的菜单栏中，与文件、编辑、查看等典型的菜单选择并列：

A screenshot showing where the main menus are located in Google Sheets

自定义菜单与一个或多个函数相关联，这些函数定义了选择自定义菜单时发生的动作。我们将创建一个名为Credentials 的自定义菜单，其中包括设置和删除Twilio账户SID、Twilio Auth Token和Twilio电话号码的下拉选择项。

复制并粘贴以下代码到Apps脚本编辑器，在你已经粘贴的变量下面。代码片断后有解释：

var TWILIO_ACCOUNT_SID = 'placeholder';
var TWILIO_PHONE_NUMBER = 'placeholder';
var TWILIO_AUTH_TOKEN = 'placeholder';

var ui = SpreadsheetApp.getUi();
var userProperties = PropertiesService.getUserProperties();

function onOpen() {
 ui.createMenu('Credentials')
   .addItem('Set Twilio Account SID', 'setTwilioAccountSID')
   .addItem('Set Twilio Auth Token', 'setTwilioAuthToken')
   .addItem('Set Twilio phone number', 'setTwilioPhoneNumber')
   .addItem('Delete Twilio Account SID', 'deleteTwilioAccountSID')
   .addItem('Delete Twilio Auth Token', 'deleteTwilioAuthToken')
   .addItem('Delete Twilio phone number', 'deleteTwilioPhoneNumber')
   .addToUi();
};

在上面的例子中，我们首先将三种凭证类型分别定义为变量，并给它们各自分配一个字符串值placeholder 。在实例化UserProperties的变量时，Apps Script语言不允许你使用空值或空字符串，这就是为什么我们要使用一个任意文本的字符串。这个任意文本将在以后被替换，一旦功能完成，你第一次使用自定义菜单。

然后，我们定义变量来表示谷歌表用户界面（ui ）和用户属性（userProperties ）。这两个变量将被我们在下一步创建的六个函数使用，以处理设置和删除我们需要存储的3种类型的凭证。

接下来，我们创建一个名为onOpen() 的内置函数，每当电子表格被打开时，它就会运行里面的代码。这就确保了我们的凭证菜单会一直出现在电子表格中。

在凭证菜单中，设置和删除凭证的六个动作都会出现，可以随时选择。我们必须提供这六个函数的名称，尽管我们还没有编写它们。我们将在下一步创建这些函数。

在Credentials自定义菜单中创建六个函数

现在，六个与凭证有关的函数在onOpen() 函数中被命名，现在是在代码中定义它们的时候了。将这段代码复制并粘贴到Apps脚本编辑器中，在现有代码的下面：

function setTwilioAccountSID(){
 var scriptValue = ui.prompt('Enter your Twilio Account SID' , ui.ButtonSet.OK);
 userProperties.setProperty('TWILIO_ACCOUNT_SID', scriptValue.getResponseText());
};

function setTwilioAuthToken(){
 var scriptValue = ui.prompt('Enter your Twilio Auth Token' , ui.ButtonSet.OK);
 userProperties.setProperty('TWILIO_AUTH_TOKEN', scriptValue.getResponseText());
};

function setTwilioPhoneNumber(){
 var scriptValue = ui.prompt('Enter your Twilio phone number in this format: +12345678900' , ui.ButtonSet.OK);
 userProperties.setProperty('TWILIO_PHONE_NUMBER', scriptValue.getResponseText());
};

function deleteTwilioAccountSID(){
 userProperties.deleteProperty('TWILIO_ACCOUNT_SID');
};

function deleteTwilioAuthToken(){
 userProperties.deleteProperty('TWILIO_AUTH_TOKEN');
};

function deleteTwilioPhoneNumber(){
 userProperties.deleteProperty('TWILIO_PHONE_NUMBER');
};

前3个函数将在用户从Credentials自定义菜单中选择动作时提示他们输入指定的凭证。最后3个函数将在用户从菜单中选择该选项时删除指定的凭证。

现在是测试代码的时候了!点击软盘（"保存项目"）图标，保存Apps脚本编辑器中的代码：

A screenshot of the Apps Script Editor showing the "save" icon

导航到你的Google Sheet并重新加载页面。重新加载后一会儿，你应该看到在主菜单栏的帮助菜单右边出现一个证书菜单。点击凭证菜单，你应该看到6个选项中的每一个，其中3个用于设置凭证，3个用于删除凭证。

如果你没有看到全权证书菜单，请尝试完全关闭网页并再次打开。

A screenshot of the new Credentials menu dropdown

逐一选择三个 "设置证书 "的选项，并按照出现的提示将你的证书加载到脚本中。例如，当你从 "**证书 "**下拉菜单中选择 "设置Twilio账户SID "选项时，你会看到这个结果。

A screenshot of the pop up that appears to enter your credentials

这些简单的表单没有输入验证，所以你需要确保你输入的值与表单要求的值相匹配（账户SID与账户SID，授权令牌与授权令牌，电话号码与电话号码）。

一旦你的三个Twilio凭证都被成功加载，就可以进入下一个步骤了。

请注意：每次你重新加载或刷新显示Google Sheet的浏览器页面时，你都需要重新输入你的凭证。

为发送短信通知创建一个自定义菜单

我们需要一个简单的方法来触发对到期付款的短信通知。我们可以在我们为输入Twilio凭证而写的onOpen() 函数中添加几行代码，然后写一些函数，当我们从这个新的自定义菜单中进行选择时，就可以创建另一个自定义菜单。

下面，你会看到onOpen() 函数的最终状态的代码片段。将突出显示的几行复制并粘贴到你现有的onOpen() 函数中，使其与下面显示的内容一致：

function onOpen() {
 ui.createMenu('Credentials')
   .addItem('Set Twilio Account SID', 'setTwilioAccountSID')
   .addItem('Set Twilio Auth Token', 'setTwilioAuthToken')
   .addItem('Set Twilio phone number', 'setTwilioPhoneNumber')
   .addItem('Delete Twilio Account SID', 'deleteTwilioAccountSID')
   .addItem('Delete Twilio Auth Token', 'deleteTwilioAuthToken')
   .addItem('Delete Twilio phone number', 'deleteTwilioPhoneNumber')
   .addToUi();
 ui.createMenu('Send SMS')
   .addItem('Send to all', 'sendSmsToAll')
   .addItem('Send to customers with due date 1st-15th', 'sendSmsByDateFilter')
   .addToUi();
};

现在，只要打开Google Sheet，onOpen() 函数就会同时创建一个Credentials自定义菜单和一个Send SMS 自定义菜单 。当点击发送短信菜单标签时，会出现两个选项。"发送至所有 "和 "发送至到期日为1-15日的客户"。接下来我们将为这些选项分别编写代码。

创建一个sendSms()函数

sendSms() 函数是我们脚本的主要功能。它使用Twilio的API和Google工作表中的数据，向工作表中的每个人发送一条定制的信息。将以下代码复制并粘贴到Apps脚本编辑器中的所有现有代码下面：

function sendSms(customerPhoneNumber, amountDue, paymentLink, customerName, paymentInfo, paymentDueDate) {
 var twilioAccountSID = userProperties.getProperty('TWILIO_ACCOUNT_SID');
 var twilioAuthToken = userProperties.getProperty('TWILIO_AUTH_TOKEN');
 var twilioPhoneNumber = userProperties.getProperty('TWILIO_PHONE_NUMBER');
  var twilioUrl = 'https://api.twilio.com/2010-04-01/Accounts/' + twilioAccountSID + '/Messages.json';
 var authenticationString = twilioAccountSID + ':' + twilioAuthToken;
 try {
   UrlFetchApp.fetch(twilioUrl, {
     method: 'post',
     headers: {
       Authorization: 'Basic ' + Utilities.base64Encode(authenticationString)
     },
     payload: {
       To: "+" + customerPhoneNumber.toString(),
       Body: "Hello, " + customerName + ", your payment of $" + amountDue + " is outstanding" + " for " + paymentInfo +". It was due on " + paymentDueDate +"."+ " Please visit "+ paymentLink + " to pay your balance. If you have any questions, contact us at support@example.com. Thanks!",
       From: twilioPhoneNumber,  // Your Twilio phone number
     },
   });
   return 'sent: ' + new Date();
 } catch (err) {
   return 'error: ' + err;
 }
};

在上面的代码片段中，我们获得Twilio的凭证，并将其存储为变量，然后使用这些凭证变量向Twilio Messaging API发出POST请求。POST请求包括消息的细节，这些细节被存储在payload 参数中。如果POST请求成功，客户将通过短信收到他们的付款通知，Google Sheet中的 "消息状态 "栏将被更新

创建一个sendSmsToAll()函数

现在我们有了一个发送短信的函数，我们要创建的下一个函数是利用我们刚才写的sendSms() ，以便向Google Sheet中的所有联系人发送短信通知。复制并粘贴以下代码片段到Apps脚本编辑器中，在你在上一步粘贴的sendSms() 函数下面：

 function sendSmsToAll() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange().getValues();
 var headers = rows.shift();
 rows.forEach(function(row) {row[MESSAGE_STATUS] =  sendSms(row[CUSTOMER_PHONE_NUMBER], row[AMOUNT_DUE], row[PAYMENT_LINK], row[CUSTOMER_NAME],row[PAYMENT_INFO], row[PAYMENT_DUE_DATE]);
 });
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
};

该代码段将收集谷歌工作表中每个联系人的数据，然后为每个联系人调用sendSms() 函数，以便他们收到短信通知。

运行sendSms()函数

现在是测试代码的时候了!再次保存Apps脚本编辑器中的代码，然后导航到Google Sheet，刷新或重新加载页面。发送短信 菜单 应该出现。如果没有，请尝试关闭并重新打开包含Google Sheet的网页。

在你继续之前，你需要使用我们之前创建的自定义凭证菜单重新输入你的Twilio凭证。在重新输入Twilio凭证的过程中，你可能会看到一个弹出窗口，表明需要授权。点击提示来授权新的应用程序：

A screenshot showing the Authorization Required popup

一旦你输入了你的三个Twilio凭证，点击发送短信菜单标签，并选择 "发送至所有人"：

A screenshot showing the new Send SMS menu

你应该在你在电子表格中作为样本数据输入的非Twilio手机号码上收到短信通知!

A screenshot showing the SMS notification that is received

如果你没有收到短信通知，请检查以下常见的问题：

  • 确保电话号码栏中的电话号码格式是E.164。如果你无法输入前面的加号（+），请在输入加号（+）之前在单元格中输入一个空格，正如StackOverflow线程中建议的那样，这应该可以解决这个问题。
  • 使用自定义凭证菜单重新输入你的Twilio凭证
  • 检查支付状态栏中由sendSms() 函数产生的错误信息。
  • 使用Apps脚本编辑器的调试工具，检查代码中是否有错别字。

根据日期范围发送短信通知

我们现在有一个函数可以向Google工作表中的所有客户发送短信通知，但很可能不是工作表中的所有客户都需要在同一时间收到通知。有些客户可能已经付款了，或者表中的一些人可能不再是客户。

下一步是添加过滤功能，以便不同用户群的客户能够在正确的时间收到通知。对于我们的例子，我们将添加一个日期过滤器，只将通知发送给那些在每月1号到15号之间应付款的客户。

下面的函数检查是否有任何客户的付款到期日在指定范围内。如果有，这些客户的信息就会被发送到我们的sendSms() ，这样他们就可以得到通知。

将下面的代码复制并粘贴到Apps脚本编辑器中，在已经存在的代码下面。如果需要，你可以在这个函数的第11行改变日期范围：

function sendSmsByDateFilter() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange().getValues();
 var headers = rows.shift();
 
rows.forEach(function(row) {
   var dueDate = new Date(row[PAYMENT_DUE_DATE]);
   var dateFormat = Utilities.formatDate(dueDate, "GMT-7", "MM/dd/yyyy")
   var dayDue = dateFormat.substring(3,5)
   
   if (dayDue >= 1 && dayDue <= 15) { // Change the date range if desired
     row[MESSAGE_STATUS] = sendSms(row[CUSTOMER_PHONE_NUMBER], row[AMOUNT_DUE], row[PAYMENT_LINK], row[CUSTOMER_NAME],row[PAYMENT_INFO], row[PAYMENT_DUE_DATE]);
   }
 });
  sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
};

上面的函数检查每一行的付款日期，并将其与指定的范围（本例中为每月1日至15日）相比较。如果付款日在这个范围内，就会向客户发送短信通知。

再次在Apps脚本编辑器中保存该项目。重新加载或刷新网页，然后使用自定义凭证菜单重新输入你的Twilio凭证。

改变你的样本数据行中的到期日期，使其落在sendSmsByDateFilter() 函数中指定的日期范围内。通过选择自定义发送短信菜单，然后选择发送至到期日为xx-xx的客户的选项来运行该功能。你会收到另一个通知!

最后，改变你的样本数据行中的到期日，使其不在sendSmsByDateFilter() 函数中指定的日期范围内。运行该函数，选择自定义发送短信菜单，然后选择向到期日为xx-xx的客户发送的选项。你应该不会再收到其他通知。

祝贺你!

在本教程中做得很好!你刚刚学会了如何：

  • 用Google的Apps Script编程语言编写一个程序
  • 在谷歌表格中创建一个自定义菜单
  • 使用Twilio的API发送短信通知
avatar
文章
阅读
粉丝