Google スプレッドシートにSMS送信機能を実装

読む所要時間: 11 分

August 10, 2021

執筆者

Mitsuharu Nakamura

Twilion

レビュー担当者

Stephenie Minami Nakajima

Twilion

Yoshika Tokutake

Twilion

Miyu Tsukenjo

Twilion

概要

TwilioのProgrammable SMSを使って簡単にGoogle スプレッドシートからSMSを配信する方法を説明します。この方法を用いると、SMSを送りたい相手の情報をスプレッドシートで管理している方が、敢えて複雑なSMS送信用のシステムを作らなくても簡単にスプレッドシートからSMSを送信できるようになります。

以下がよくあるユースケースです。

顧客への営業メッセージの送信
支払いの請求・督促
緊急連絡通知
コールセンターに問い合わせした方へのアンケート依頼

今回は、顧客リストをスプレッドシートに作成し、顧客毎に配置したチェックボックスをオンにすることでSMSの送信を行う機能をご紹介したいと思います。

事前準備

Twilioアカウントが作成済みであること
AccountSIDとAuthTokenを準備
TwilioでUSの電話番号が購入済みであること
Googleスプレッドシートが利用可能であること

顧客リストの作成

Google スプレッドシートに顧客リスト表を作成します。

1. 新規にスプレッドシートを作成します。

2. スプレッドシートに「顧客リスト」という名前を付けて、以下のように表を作成します。カラムの順番は必ず以下に従ってください。順番が異なるとエラーになります。
電話番号は[+] [国番号] [市外局番を含む送信先の番号]のフォーマットで入力してください。Google Sheetが電話番号を数式として認識してしまう場合は、番号の手前にシングルクォーテーション'を入力するとテキストとして認識されます。（例：'+8190xxxxxxxx）

※チェックボックスを作成するには、SMS送信列のチェックボックスの挿入したいセルを選択して、メニューから「Insert」→「Checkbox」を選択することで配置できます。

GAS(Google Apps Script)の作成

ここでは、SMS配信のメインとなる機能をスクリプトで記述していきます。

1. 顧客リストのスプレッドシート画面から、メニューの「Tools」→「Script editor」を選択してスクリプトエディタを開きます。

2. スクリプトエディタが別タブで開きます。

3. 画面上段にある「Untitled project」をクリックしてプロジェクト名を「Customer List」に変更します。

4. 元のスクリプトを削除してから、以下のコードをコピーして、Code.gsに貼り付けしてください。

// @ts-nocheck
const sender_id = "<購入したUS番号>"; //SMSの送信元番号
const account_sid = "<ACCOUNT_SID>"; // TwilioのAccount SID
const auth_token = "<AUTH_TOKEN>";    // TwilioのAuth Token
const phone_col = 4; // 電話番号のカラム位置情報
const msg_col = 5;   // メッセージのカラム位置情報

const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //ActiveSheetの取得
const rng = ss.getActiveCell(); //アクティブセルを取得
const flag = rng.getValue(); //アクティブセルのチェックボックスの値を取得
const active_col = rng.getColumn(); // アクティブセルのカラム番号
const message = ss.getRange(rng.getRow(), msg_col).getValue(); //メッセージ内容を取得
const to_number = ss.getRange(rng.getRow(), phone_col).getValue(); //架電先の電話番号を取得

////////////////////////
// スクリプトのメイン
////////////////////////
function myFunction() {
mainSMSLogic();
}

////////////////////////
// SMS Main処理
////////////////////////
function mainSMSLogic() {
if (flag) {
  //チェックボックスがオンになっていたらSMS送信前にチェックボックスでYES/NOを確認します。
  let status = Browser.msgBox(to_number + " にSMSを配信しますか？", Browser.Buttons.YES_NO);
  if (status == "yes") {
    let http_status = reqSendSMS();
    if (http_status) {
      if (http_status.getResponseCode() >= 200 && http_status.getResponseCode() < 300) {
        //SMSの実行結果が正常の場合はセルの背景を緑にします。
        rng.setBackground("#00FF00");
      } else {
        //SMSの実行結果が異常の場合にはセルの背景を赤にします。
        rng.setBackground("#FF0000");
        Browser.msgBox("HTTP Status code:" + http_status.getResponseCode() + " Reason:" + http_status.getContentText());
      }
    } else {
      rng.setBackground("#FF0000");
    }
  }
} else if (!flag) {
  //通常時はセルの背景を白に設定します
  rng.setBackground("#FFFFFF");
}
}
////////////////////////
// SMS配信処理関数
////////////////////////
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("例外発生：" + error);
}
return response;
}

※赤文字の箇所は適宜ご自身の情報に置き換えてください。

5. スクリプトエディタの上段にある保存ボタンを押すか、「Ctrl+s」で保存してください。

左側のメニューから「Triggers」を選択してください

6. 画面中央にある「create a new trigger」または画面右下の「+Add Trigger」を選択してください。

7. 以下のようにTriggerの設定を行い、保存してください。

動作確認

スプレッドシートに戻って実際に動作させてみましょう。

送り先の電話番号を入力（+81で始まる番号で入力）しメッセージ欄に送信するメッセージを記載してください。完了したら、横にあるチェックボックスをオンにすることでSMSが送信出来るはずです。

送信が正常に完了するとチェックしたセルの背景が緑色になります。

送信時にエラーが発生した場合には、赤になります。

送信が正常に完了していたら、SMSを送信した携帯電話にメッセージが届いていることを確認してください。

以下のようなエラーが出る場合の対処方法

SMSが指定した電話番号に届かない場合に、スクリプトエディタ内の実行ログを確認すると以下のようなメッセージが表示されることがあります。これは、外部APIをコールするための権限が不足している時に発生します。

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

この場合には以下の手順で権限を追加してください。

1. Script Editorの中で「Project Settings」を開いて、「Show "appsscript.json" manifest file in editor」のチェックボックスをOnにします。

2. 「Editor」→「appsscipt.json」を選択し以下のようにoauthScopesを設定します。

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

3. 最初に作成したTriggerを削除して、再作成すれば完了です。

まとめ

今回はGoogle スプレッドシートからSMSを送信する方法を記載させて頂きました。もちろんExcelでもVBAを使えば同じ事が実現出来ると思います。また、今回はSMSでしたが、Twilio Voiceを使って簡単にGoogle スプレッドシートからオートコールも実現できます。Twilioでは沢山のチャネルに対するAPIをご用意しておりますので、ご自身でも様々なトライをして頂ければと思います。

We can't wait to see what you build!

Google スプレッドシートにSMS送信機能を実装