An overview of the

Learn how to use Twilio’s programmable SMS to easily send text messages from your Google spreadsheets. If you have a spreadsheet containing information about the people you want to send messages to, this method lets you send messages from the spreadsheet without having to create a complex SMS sending system.

Here are some common use cases

  • Send sales information to customers
  • Bills and reminders for payment
  • Emergency Contact Notification
  • Ask the caller in the call center to investigate

In this article, we want to show you how to create a spreadsheet with a customer list and send SMS messages by checking the box for each customer.

Preliminary preparation

  • You should have created a Twilio account.
  • Have your AccountSID and AuthToken ready
  • You have purchased an American phone number from Twilio
  • Prepare a Google spreadsheet

Create a customer list

Create a list of customer names in a Google spreadsheet.

Create a new spreadsheet.

2. Name the spreadsheet “Customer List” and create the following table. Make sure you follow the order of the columns below. Any different order will result in an error. The telephone number should be used in the form [+] [national usage] [outer Usage including む]. If Google Sheet identifies a phone number as a formula, you can put a single quote ‘before the number to make it look like text. (for example: ‘+8190xxxxxxxx)

To create a check box, select the cell you want to insert the check box into the SMS sending bar and select “Insert “->” Check Box” from the menu.

Create GAS (Google Applications script)

In this section, we’ll write the main functions of SMS sending.

1. On the Customer List spreadsheet interface, choose ‘Tools ‘->’ Script Editor’ from the menu to open the script editor.

2. The script editor opens in a new TAB.

3. Click ‘Untitled Project’ at the top of the screen and change the project name to ‘Client List’. 4.

4. Delete the original script, then copy and paste the following Code into code.gs.

// @ts-nocheck
const sender_id = "Buy Knitus."; // NO word by SMS
const account_sid = "<ACCOUNT_SID>"; // Twilio by Account SID
const auth_token = "<AUTH_TOKEN>";    // Twilio by Auth Token
const phone_col = 4; // Telephone number / カ ム Location information
const msg_col = 5;   // Sweet and over

const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); / / ActiveSheet の
const rng = ss.getActiveCell(); // Attract (ィ
const flag = rng.getValue(); Attract gun ィ gun segundo チ Gun segundo
const active_col = rng.getColumn(); Dock is the final name of ィブ sechristianカ ム
const message = ss.getRange(rng.getRow(), msg_col).getValue(); // Sweet and back
const to_number = ss.getRange(rng.getRow(), phone_col).getValue(); // The telephone usage was acquired

////////////////////////
// Amp, プト
////////////////////////
function myFunction() {
mainSMSLogic();
}

////////////////////////
// SMS Main pairing
////////////////////////
function mainSMSLogic() {
if (flag) {
  チ Shoku, right?
  let status = Browser.msgBox(to_number + It has been used to carry letters., Browser.Buttons.YES_NO);
  if (status == "yes") {
    let http_status = reqSendSMS();
    if (http_status) {
      if (http_status.getResponseCode() >= 200 && http_status.getResponseCode() < 300) {
        //SMS 标 签 : verbal abuse
        rng.setBackground("#00FF00");
      } else {
        //SMS 标 签 : verbal abuse
        rng.setBackground("#FF0000");
        Browser.msgBox("HTTP Status code:" + http_status.getResponseCode() + " Reason:"+ http_status.getContentText()); }}else {
      rng.setBackground("#FF0000"); }}}else if(! flag) {// Usually the usage of the background is white
  rng.setBackground("#FFFFFF"); }}////////////////////////
// SMS with letter numbering
////////////////////////
function reqSendSMS() {
const payload =
{
  "Body": message,
  "From": sender_id,
  "To": to_number
};
const options =
{
  "method": "post"."payload": payload,
  "muteHttpExceptions": true
};
options.headers = { "Authorization": "Basic " + Utilities.base64Encode(account_sid + ":" + auth_token) };
let response = null;
try {
  response = UrlFetchApp.fetch("https://api.twilio.com/2010-04-01/Accounts/" + account_sid + "/Messages.json", options);
} catch (error) {
  Browser.msgBox("The exception bulge is born :" + error);
}
return response;
}
Copy the code

Please replace the red text with your own message as appropriate. 5.

5. Press the Save button at the top of the script editor or use ‘Ctrl+ S ‘to save the file.

  1. Select “Trigger” from the menu on the left.

6. Select ‘Create a new trigger’ in the middle of the screen or ‘+ Add Trigger ‘in the bottom right of the screen. 7.

Configure the trigger as shown in the following figure and save it.

Check how it works

Let’s go back to the spreadsheet and see how it works.

Enter the phone number you want to send (it should start with a number, such as +81) and write the message you want to send in the message box. When you’re done, check the box next to it and you should be able to send text messages.

When the message is successfully sent, the background of the checked cell will turn green.

If you send an error, the background will turn red.

If the message has been sent successfully, check whether it has been sent to the mobile phone that sent the message.

What should you do if you encounter the following errors?

If the message does not arrive at the specified phone number, you may see the following message when you check the execution log in the script editor. This happens when you don’t have enough permissions to call an external API.

Exception: You do not have permission to call UrlFetchApp.fetch. Required permissions: https://www.googleapis.com/auth/script.external_request
Copy the code

In this case, follow these steps to add permissions.

1. In the script editor, open “Project Settings” and set the “Display appsScript. json” manifest file in the editor “check box to” Open “.

2. Choose “Editor “->” appsScipt. json” and set the oauthScopes as follows. 3.

{
"timeZone": "Asia/Tokyo"."dependencies": {},
"exceptionLogging": "STACKDRIVER"."runtimeVersion": "V8"."webapp": {
  "executeAs": "USER_ACCESSING"."access": "DOMAIN"
},
"oauthScopes": [
  "https://www.googleapis.com/auth/script.external_request"]}Copy the code

3. Delete the trigger you created and recreate it.

Abstract

In this article, I showed you how to send text messages from a Google spreadsheet. Of course, if you use VBA in Excel, you can achieve the same thing. Twilio provides apis for many channels, so you can experiment on your own. We can’t wait to hear what you think.

We can’t wait to see what you build!