从Google表格发送短信 [英] Send SMS from Google Sheet

问题描述

我获得了使用我想从下面的Google工作表中向我的客户发送短信确认

I want to send SMS acknowledgement to my customers from the below google sheet

https://docs.google.com/spreadsheets/d/1Jpka0Wn8cQ6J6Be8Ks5vF-JJ50ykdCuMIetrWjAi7Kw/edit?usp = sharing

我希望SMS仅对状态不是以已发送"开头且电话号码以"+91"开头的所有客户发送一次

I want SMS to go only once to all the customers for whom the Status is not starting with "Sent" and where the phone number is starting with "+91"

短信将是这样

由于"CT失败"，我们向客户"TNEB"在"16/5/16"注册了编号为"SER 160530"的投诉.请致电18004257865了解详情

Complaint No "SER 160530" with us registered on "16/5/16" for customer "TNEB" due to "CT Failed". Please call 18004257865 for details

消息由某些文本和特定行中某些单元格的值组成 状态"列必须更新在xxxx上的xx:xx:xx:xx上将SMS发送到xxxxxxxx" 是否可以每隔一小时自动运行一次脚本? 是否有其他免费的方法可以从Google表格发送短信?

The message is made up of some text and the value from some cells in a particular row The "Status" column must get updated "Sent SMS to xxxxxxxx on xxxx at xx:xx:xx:xx" Is it possible to run the script every one hour automatically? Is there any free alternative to send SMS from Google sheet?

推荐答案

要每小时执行一次脚本，可以设置一个时间驱动的触发器. 此处是设置触发器的文档.

To execute your script every hour you can set up a time driven trigger. Here is the documentation for setting up a trigger.

这是我的解决方法，可用于免费从Google表格发送文本，它可能无法满足您的特定需求，但是可以选择:

Here is my workaround for sending texts out of a Google Sheet for free, it might not work for your specific need, but is an option:

每家手机运营商都提供免费的电子邮件发送至短信选项.这些电子邮件地址的示例是此处.例如，如果我想在Sprint网络上给某人发短信，则可以将电话号码附加到他们的域，例如:[e-mail].如果您能够收集或查找运营商的电话号码，则可以设置一个简单的功能以将两者连接起来，并使用如下所示的邮件合并选项来发送文本. 此处是邮件合并将标记的文档发送邮件，并防止多个文本发给同一个联系人.

Every mobile phone carrier offers an email to sms option that is free. Examples of these email addresses are here. For example if I am wanting to text someone on the Sprint network I would attach the phone number to their domain like: [e-mail]. If you are able to collect or look up the carrier for the phone numbers you can set up a simple function to connect the two and use a mail merge option like the one below to send out your texts. Here is the documentation the the mail merge that will mark sent messages and prevent multiple texts from going out to the same contact.

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var emailSent = row[2];     // Third column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      var subject = "Sending emails from a Spreadsheet";
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

这篇关于从Google表格发送短信的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！