Google Sheets Twilio短信脚本如何实现正文动态替换列值变量

需求背景 #

• 目标：优化对接Twilio发送短信的Google Sheets脚本，实现表格列变量自动嵌入短信模板，无需在脚本中硬编码固定文本
• 预期效果：在短信模板列中用特定格式标记占位符，脚本发送短信时自动用对应行的变量值替换占位符

  模板示例：Hi! Congratulations on getting your license for {{companyName}}. Give us a call to get set up.
  实际发送效果：Hi! Congratulations on getting your license for ABC Company. Give us a call to get set up.

• 当前进度：表格已拆分公司名称列、短信正文模板列，脚本已完成各列变量的调用逻辑，但短信正文存在硬编码拼接的问题，需要优化

原始问题代码 #

function sendSms(customerPhoneNumber, companyName, messageBody)     {
 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: "Hi! Congratulations on getting your license for " + companyName + ", " + messageBody,
   From: twilioPhoneNumber,  // Your Twilio phone number
 },
   });
   return 'sent: ' + new Date();
 } catch (err) {
   return 'error: ' + err;
 }
};

优化后代码 #

核心改动是移除硬编码的文本拼接逻辑，新增通用占位符正则替换能力，模板内容完全由表格中的模板列控制：

function sendSms(customerPhoneNumber, companyName, messageBody)     {
 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;
 
 // 占位符映射配置，key对应模板中{{}}包裹的变量名，value为实际替换值
 const placeholderMap = {
   companyName: companyName
   // 后续新增变量直接在此处追加即可，例如客户名、license有效期等
 };

 // 自动替换模板中所有匹配的占位符
 let finalSmsContent = messageBody;
 for (const [placeholderKey, actualValue] of Object.entries(placeholderMap)) {
   const matchReg = new RegExp(`\\{\\{${placeholderKey}\\}\\}`, 'g');
   finalSmsContent = finalSmsContent.replace(matchReg, actualValue);
 }

 try {
   UrlFetchApp.fetch(twilioUrl, {
     method: 'post',
     headers: {
       Authorization: 'Basic ' + Utilities.base64Encode(authenticationString)
     },
     payload: {
       To: "+" + customerPhoneNumber.toString(),
       Body: finalSmsContent,
       From: twilioPhoneNumber,
     },
   });
   return 'sent: ' + new Date();
 } catch (err) {
   return 'error: ' + err;
 }
};

使用说明 #

• 模板统一使用{{变量名}}格式标记占位符，比如需要插入公司名的位置直接写{{companyName}}即可，脚本会自动全局匹配替换
• 占位符大小写敏感，需要和placeholderMap中配置的key完全一致才能正常替换
• 模板中如果没有写对应占位符，脚本不会额外拼接多余内容，会直接按模板原文发送
• 后续需要新增动态变量时，只需要在表格中新增对应列存储变量值、调用函数时传入新变量、在placeholderMap中追加对应映射关系即可，不需要修改核心替换逻辑

内容的提问来源于stack exchange，提问作者Nikki B