2020年1月23日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

Google Apps Scriptでちょっとした顧客管理台帳を作る - GMailアドオン編【GAS】

江戸時代の商人は火事の時にまっさきに「大福帳」を持って逃げたと言います。この大福帳こそ今でいう「顧客台帳」。2020年現在ではこれらはCRMというシステムとして、導入・活用されています（医療の世界なら電子カルテがこれに該当する）。SalesforceやDynamics365が有名ですね。

ただ、このCRM、導入にあたってはいくつもの壁が存在しており、最終的には導入したのに使われない・・・ランニングコストが高すぎて・・・という事で、結局Excel管理というしょっぱい現実になってたりします。G Suiteだとサテライトオフィスから低価格のCRMアドオンがリリースされてたりもします。

今回これを踏まえてCRMアプリをGoogle Apps Scriptで作ってみる事にしました。人事の問い合わせを管理する管理台帳になります。GMailアドオン、フォーム、管理画面の3部構成です。

1 今回使用するスプレッドシートと事前準備
- 1.1 使用するファイル
- 1.2 事前準備
2 使われなくなる理由
3 ソースコード
4 関連リンク

今回使用するスプレッドシートと事前準備

使用するファイル

スプレッドシート de CRM

今回はダミーデータとして疑似個人情報データ生成サービスを利用しています。

事前準備

スプレッドシートを開いたら以下の手順でセット・アップが必要です。

メニューより[▶セットアップ]⇒「初期化」を実行
スクリプトプロパティに自身のスプレッドシートのIDが登録される。

これで他のプロジェクトから該当のシートを参照する場合、ライブラリを通じてシートIDが取得できるようになります。

使われなくなる理由

使われなくなる理由は、別にCRMに限った話ではないのですが、この手のソリューションが使われなくなる理由は主に以下のようなもの。

無駄に多機能でそれが全面に出てしまっていて、覚える上で障害になっている。
現場の業務をシステムに合わせようとしない点（日本は特にこの傾向が強い） - 現場の声無視しがちですね。
管理者が現場の業務を把握できておらず、現場業務がブラックボックス化と極端な属人化がまかり通ってる
3.に加え、業務命令として「システムを使え」といった強制を出来ない管理能力不足
そもそも、CRMサービス自体がオーバースペック。何するにしてもストレスになってる。
CRMで業務が完結せず、結局ローカルのExcelでサブ業務があるため、CRMへの登録自体が手間になっている
導入が目標になってしまっていて、導入後の研修やサポートの為の人員を現場に置いていない。ヘルプデスクで全てが片付くと思ってる。
結局の所、メールベースや電話ベースが主体で、フォームベースといったようなシステム寄りの手段を用意していない。
1アカウント辺りの月額料金が高い為、全員に行き渡らない。結果、一部の人間に業務が集中する羽目になる
一番よく言われるのが、入れたからといってExcel管理と比較して目に見える効果を感じない（やってる事置き換えただけだよね？）
用件のカテゴライズが面倒と言われたり、初期導入時のデータ不足の為に入力内容が多い事に現場から不満が出る（準備不足）

SIerや胡散臭いシステムコンサルの言葉を真に受けて、いざ入れてみたら案の定失敗というケースは過去にも何度も見てきましたが、かと言って、オープンソースのシステムを構築するのも非常に手間（手軽なシステムは正直存在しないし、いつ消滅するかもわからないようなものが多い）、ならば、現在のスプレッドシート作業の延長として構築したら、テストにもなるし良いのでは？というのが今回のテーマ。

Google Apps Scriptで作ったシステムが受け入れられやすい点は、スプレッドシートを土台として作る事が可能なので、現場のサブ業務の流れを潰さず、また内製する事で現場にフィットしたものをオーバースペックにならずにスピーディに作り込める点だと思います。

ソースコード

機能と目的

今回のアプリケーションでは、顧客からの問い合わせ管理をするのが目的となります。メール、電話、そしてフォームから受け付けた内容を登録し、それに対する処理などを記録していく。電話に関しては受付者は手入力する必要があるものの、メールとフォームに関してはある程度自動化出来ます。

今回のGMail Addonは、メールから直接問い合わせ登録をするべく、GASでアドオンを作り、カテゴリ選択だけでタスクを登録するのが目的です。

共通ライブラリと設定

今回の３部構成の為に、3つのプロジェクト共通で使うスクリプトプロパティの為に、4つ目のプロジェクト（共通ライブラリ）を作成して以下のコードを追加。版を保存し、スクリプトIDを取得したら、３つのプロジェクトにそれぞれライブラリとして追加します（識別子はpropとしました）。

//スプレッドシートのメニューを作る
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('▶セットアップ')
.addItem('初期化', 'getMySheetId')
.addToUi();
}
//プロパティを取得する
function getProp(value) {
var Properties = PropertiesService.getScriptProperties();  
var temp = Properties.getProperty(value);
return temp;
}
//プロパティをセットする
function setProp(key,value){
var Properties = PropertiesService.getScriptProperties();
Properties.setProperty(key,value);
}
//自分自身のIDを取得するコード
function getMySheetId(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var myid = sheet.getId();
var Properties = PropertiesService.getScriptProperties();
Properties.setProperty("sheetid", myid);
return myid;
}

//スプレッドシートのメニューを作る

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('▶セットアップ')

.addItem('初期化', 'getMySheetId')

.addToUi();

}

//プロパティを取得する

function getProp(value) {

var Properties = PropertiesService.getScriptProperties();

var temp = Properties.getProperty(value);

return temp;

}

//プロパティをセットする

function setProp(key,value){

var Properties = PropertiesService.getScriptProperties();

Properties.setProperty(key,value);

}

//自分自身のIDを取得するコード

function getMySheetId(){

var sheet = SpreadsheetApp.getActiveSpreadsheet();

var myid = sheet.getId();

var Properties = PropertiesService.getScriptProperties();

Properties.setProperty("sheetid", myid);

return myid;

}

マニフェストファイルの作成

GMailのアドオンをGASで作成するには、appsscript.json（マニフェスト）のファイルを手動で編集する必要があります。デフォルトでは表示されていないので、以下の手順で表示して編集します。

スクリプトエディタでGmailアドオン用のプロジェクトを追加しておく
そのプロジェクトに於いて、メニューより[表示]⇒[マニフェストファイルを表示]をクリック
appscript.jsonというものが左サイドバーに出てくるのでクリック

中に記述するコードは以下のような感じ。但し、既存のコードを壊さないように慎重に編集しましょう。

{
"timeZone": "Asia/Tokyo",
"dependencies": {
"libraries": [{
"userSymbol": "prop",
"libraryId": "ここに共通ライブラリのスクリプトIDを入れる",
"version": "1"
}]
},
"exceptionLogging": "STACKDRIVER",
"oauthScopes": [
"https://www.googleapis.com/auth/gmail.addons.execute", 
"https://www.googleapis.com/auth/gmail.readonly", 
"https://www.googleapis.com/auth/gmail.modify", 
"https://mail.google.com/", 
"https://www.googleapis.com/auth/drive", 
"https://www.googleapis.com/auth/script.external_request", 
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/spreadsheets",
],
"gmail": {
"name": "Gmailから登録", 
"logoUrl": "ここにアイコン画像の直リンクURL(40x40のサイズ)", 
"contextualTriggers": [
{
"unconditional": {}, 
"onTriggerFunction": "buildAddOn"
}
], 
"openLinkUrlPrefixes": [
"https://mail.google.com/"
],
"primaryColor": "#4285F4", 
"secondaryColor": "#4285F4", 
"version": "TRUSTED_TESTER_V1"
}
}

{

"timeZone": "Asia/Tokyo",

"dependencies": {

"libraries": [{

"userSymbol": "prop",

"libraryId": "ここに共通ライブラリのスクリプトIDを入れる",

"version": "1"

}]

"exceptionLogging": "STACKDRIVER",

"oauthScopes": [

"https://www.googleapis.com/auth/gmail.addons.execute",

"https://www.googleapis.com/auth/gmail.readonly",

"https://www.googleapis.com/auth/gmail.modify",

"https://mail.google.com/",

"https://www.googleapis.com/auth/drive",

"https://www.googleapis.com/auth/script.external_request",

"https://www.googleapis.com/auth/userinfo.email",

"https://www.googleapis.com/auth/spreadsheets",

"gmail": {

"name": "Gmailから登録",

"logoUrl": "ここにアイコン画像の直リンクURL(40x40のサイズ)",

"contextualTriggers": [

{

"unconditional": {},

"onTriggerFunction": "buildAddOn"

}

"openLinkUrlPrefixes": [

"https://mail.google.com/"

"primaryColor": "#4285F4",

"secondaryColor": "#4285F4",

"version": "TRUSTED_TESTER_V1"

}

共通設定ライブラリの登録をしてあると、Librariesの部分が追加されます。
nameがアドオンの名称となります。
logoUrlは40x40のサイズ指定のpngファイルを使うと良いです。なるべく単純な画像が望ましい。今回はDriveにアップした画像の直リンクを指定しています。今回は土星のアイコンを加工して使っています。
onTriggerFunctionがアドオンが開かれた時に実行される関数を指定する場所
versionはTRUSTED_TESTER_V1もしくはV2で固定値です
oauthScopesはGASから利用するAPI等によって変わります。今回はスプレッドシートの読み書きが含まれるので、https://www.googleapis.com/auth/spreadsheetsを追加しています。

ソースコード

通常スプレッドシート等のUIはSidebarなどを使って構築し、その際にはHTML Serviceを使ってHTMLにて作成をしています。しかし、GMailのAddonでは、Card Serviceという独自のクラスを使って部品を構築していきます。そのためui.alertなどは使えず、またUIも好き勝手には作れません。

作業完了通知にはNotificationメソッドを利用して構築し、CardService.newActionResponseBuilder()にて表示するといった具合です。また、メール選択時に開いていると自動で実行される仕組みなので、コードの書き方も少し気を使う必要があります。今回はこちらのサイトのコードをベースに、セットアップ済みのスプレッドシートへメールデータをタスクとして登録するコードを書いてみました。

//マニフェストに登録した起動時に実行するコード
function buildAddOn(e){
//Access Tokenを取得する
var accessToken = e.messageMetadata.accessToken;
GmailApp.setCurrentMessageAccessToken(accessToken);
//メールの内容を取得する
var messageId = e.messageMetadata.messageId; //メールのメッセージID 
var message = GmailApp.getMessageById(messageId);
var subject = message.getSubject(); 　　　　　 //メールのタイトル                 
var from = message.getFrom();     　　　　　   //メールの送信元アドレス   
//メールの本文の取得(プレーンテキストで取得)
var body = message.getPlainBody();
//シートデータを取得する
var ssid = prop.getProp("sheetid");
var ss = SpreadsheetApp.openById(ssid).getSheetByName("設定").getRange("A2:A").getValues();
//ドロップダウンメニューを作成する
var dropdown = CardService.newSelectionInput().setType(CardService.SelectionInputType.DROPDOWN)
.setTitle('カテゴリーを選択')
.setFieldName('category')
//メニューを構築
for(var i=0;i<ss.length;i++){
dropdown.addItem(ss[i][0], ss[i][0], false);
}
//Card ServiceにてUIを作る
var buttonSet = CardService.newButtonSet();
//ボタンを作成
var exportButton = CardService.newTextButton()
.setText('顧客台帳へタスク登録')
.setOnClickAction(CardService.newAction()
//ボタン押した時に実行する関数
.setFunctionName('addTask')
//実行時に渡す引数
.setParameters(
{
'messageId': messageId,
'from': from,
'body': body,
'subject': subject,
})
);
var card = CardService.newCardBuilder()
//メールのタイトルを表示
.setHeader(CardService.newCardHeader()
.setTitle(subject)
)
//メール送信元アドレスとカテゴリを表示
.addSection(CardService.newCardSection()
.addWidget(CardService.newKeyValue()
.setTopLabel('送信元')
.setContent(from))
.addWidget(dropdown)
.addWidget(exportButton)
).build();
return card;
}
//スプレッドシートにタスクを追加する
function addTask(e){
//排他制御開始
var lock = LockService.getDocumentLock();
//30秒間のロックを実施する
lock.tryLock(30000);
try{
//シートを取得する
var ssid = prop.getProp("sheetid");
var ss = SpreadsheetApp.openById(ssid).getSheetByName("問い合わせ");
//引数を取得する
var messageId = e.parameters['messageId'];
var body = e.parameters['body'];
var from = e.parameters['from'];
var subject = e.parameters['subject'];
//uidを生成する
var uid = prop.getProp("uid");
uid = Number(uid) + 1;
prop.setProp("uid", uid);
//選択されたドロップダウンの値を取得
var selected = String(e.formInput.category);
//書き込み用の配列を用意
var array = [];
array.push(uid);
array.push(new Date());
array.push("メール");
array.push(from);
array.push(messageId);
array.push(subject);
array.push(selected);
array.push(body);
array.push(GetUser());
array.push("");
array.push("未対応");
//スプレッドシートに書き込む
ss.appendRow(array);
//ロックを開放する
lock.releaseLock();
//処理結果をNotificationで表示する
var notify = CardService.newNotification().setText("【Success】無事にタスク登録が完了したよ");
return CardService.newActionResponseBuilder()
.setNotification(notify)
.build();
}catch(e){
//ロックを開放する
lock.releaseLock();
//ロック取得できなかった時の処理等を記述する
var checkword = "ロックのタイムアウト: 別のプロセスがロックを保持している時間が長すぎました。";
//通常のエラーとロックエラーを区別する
var msg;
if(e.message == checkword){
//ロックエラーの場合
msg = "誰かまだ使ってるみたい";
}else{
//ソレ以外のエラーの場合
msg = e.message;
}  
//エラーをNotificationで表示する
var notify = CardService.newNotification().setText("【Error】" + msg);
return CardService.newActionResponseBuilder()
.setNotification(notify)
.build();
}
}
//現在のユーザのアドレスを取得
function GetUser() {
var objUser = Session.getActiveUser();
return objUser.getEmail();
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

//マニフェストに登録した起動時に実行するコード

function buildAddOn(e){

//Access Tokenを取得する

var accessToken = e.messageMetadata.accessToken;

GmailApp.setCurrentMessageAccessToken(accessToken);

//メールの内容を取得する

var messageId = e.messageMetadata.messageId; //メールのメッセージID

var message = GmailApp.getMessageById(messageId);

var subject = message.getSubject(); 　　　　　 //メールのタイトル

var from = message.getFrom(); 　　　　　 //メールの送信元アドレス

//メールの本文の取得(プレーンテキストで取得)

var body = message.getPlainBody();

//シートデータを取得する

var ssid = prop.getProp("sheetid");

var ss = SpreadsheetApp.openById(ssid).getSheetByName("設定").getRange("A2:A").getValues();

//ドロップダウンメニューを作成する

var dropdown = CardService.newSelectionInput().setType(CardService.SelectionInputType.DROPDOWN)

.setTitle('カテゴリーを選択')

.setFieldName('category')

//メニューを構築

for(var i=0;i<ss.length;i++){

dropdown.addItem(ss[i][0], ss[i][0], false);

}

//Card ServiceにてUIを作る

var buttonSet = CardService.newButtonSet();

//ボタンを作成

var exportButton = CardService.newTextButton()

.setText('顧客台帳へタスク登録')

.setOnClickAction(CardService.newAction()

//ボタン押した時に実行する関数

.setFunctionName('addTask')

//実行時に渡す引数

.setParameters(

{

'messageId': messageId,

'from': from,

'body': body,

'subject': subject,

})

);

var card = CardService.newCardBuilder()

//メールのタイトルを表示

.setHeader(CardService.newCardHeader()

.setTitle(subject)

)

//メール送信元アドレスとカテゴリを表示

.addSection(CardService.newCardSection()

.addWidget(CardService.newKeyValue()

.setTopLabel('送信元')

.setContent(from))

.addWidget(dropdown)

.addWidget(exportButton)

).build();

return card;

}

//スプレッドシートにタスクを追加する

function addTask(e){

//排他制御開始

var lock = LockService.getDocumentLock();

//30秒間のロックを実施する

lock.tryLock(30000);

try{

//シートを取得する

var ssid = prop.getProp("sheetid");

var ss = SpreadsheetApp.openById(ssid).getSheetByName("問い合わせ");

//引数を取得する

var messageId = e.parameters['messageId'];

var body = e.parameters['body'];

var from = e.parameters['from'];

var subject = e.parameters['subject'];

//uidを生成する

var uid = prop.getProp("uid");

uid = Number(uid) + 1;

prop.setProp("uid", uid);

//選択されたドロップダウンの値を取得

var selected = String(e.formInput.category);

//書き込み用の配列を用意

var array = [];

array.push(uid);

array.push(new Date());

array.push("メール");

array.push(from);

array.push(messageId);

array.push(subject);

array.push(selected);

array.push(body);

array.push(GetUser());

array.push("");

array.push("未対応");

//スプレッドシートに書き込む

ss.appendRow(array);

//ロックを開放する

lock.releaseLock();

//処理結果をNotificationで表示する

var notify = CardService.newNotification().setText("【Success】無事にタスク登録が完了したよ");

return CardService.newActionResponseBuilder()

.setNotification(notify)

.build();

}catch(e){

//ロックを開放する

lock.releaseLock();

//ロック取得できなかった時の処理等を記述する

var checkword = "ロックのタイムアウト: 別のプロセスがロックを保持している時間が長すぎました。";

//通常のエラーとロックエラーを区別する

var msg;

if(e.message == checkword){

//ロックエラーの場合

msg = "誰かまだ使ってるみたい";

}else{

//ソレ以外のエラーの場合

msg = e.message;

}

//エラーをNotificationで表示する

var notify = CardService.newNotification().setText("【Error】" + msg);

return CardService.newActionResponseBuilder()

.setNotification(notify)

.build();

}

//現在のユーザのアドレスを取得

function GetUser() {

var objUser = Session.getActiveUser();

return objUser.getEmail();

}

メッセージIDを元にすでに登録済みかどうかチェックして登録済みの場合には登録ボタンは表示しないというのも良いテクニックです。
UI構築時にスプレッドシートのカテゴリ一覧を元にサイドバーのドロップダウンメニューを構築しています。
setFunctionNameにてボタンを押した時に実行される関数を指定。引数を一緒に渡せます。
addTask関数が今回の登録をするメイン関数。
e.formInput.categoryにて、categoryと名前をつけたドロップダウンのvalueを取得可能です。
複数名同時に書き込みを考慮して、排他制御を入れてあります。
スプレッドシートへのタスク書き込みは、appendRowにて一発書き込みです。
連番はuidにて生成しています。
ウェブアプリケーションと異なり、ソースの変更が即時に反映してしまいます。ソース変更後、再度GMailのアドオン表示をすると変更後のコードで動作し始めるので、リリース後の運用には注意が必要です。

GMailにアドオンを追加

デプロイ

全ての準備が整ったら、以下の手順でアドオンを使えるようにします。但し今回は、公開するアドオンではなく社内でだけ利用するアドオンであるため、以下のような手順です。

スクリプトエディタより[公開]⇒[マニフェストから配置]をクリック
DeploymentsというダイアログのGet IDをクリック。ダイアログに表示されるDeployment IDの値をコピーしておく

図：Deployment IDをGMail側で登録する

インストール

GMail側で取得したDeployment IDを用いてデベロッパーアドオンとして追加します。

GMailを起動する
右上にある歯車をクリックして設定を開く
アドオンタブを開く
「ご利用のアカウントでデベロッパーアドオンを有効にする」にチェックを入れる
すぐ下のテキストボックスにDeployment IDを入力して、インストールをクリック
「このアドオンのデベロッパーを信頼する」にチェックを入れてボタンをクリック
すると、右側のバーに自分の指定したアイコンでアドオンが表示される。
適当にメールを１通開いて、アドオンを開くと、承認要求が出てくる。「アクセスを承認」をクリック
GASじゃおなじみのアクセス権限承認の為のログイン画面が出るのでログインする。
oauth Scopesで指定した内容の承認画面が出るので、許可をクリック
あとは表示されてるメニューから操作が可能になります。
ストアで公開する人はさらに手順がありますが、今回は割愛します。

図：アドオンのインストール画面

図：アドオンでの承認要求画面

図：承認画面の例

スマフォアプリからも使える

このアドオンですが、PCのGMail上だけでなく、Androidの場合、Android用GMailアプリにもメールの一番下に出てきます。つまり、一連の作業をスマフォ上で完結させることが可能になっています。AMP for GMailを使うのは面倒ですが、この仕組を使ってなんらかのIDをメールタイトル取得できれば、例えばアドオンを使ってのワークフロー承認も可能になるのではないかと思います。

Android上のGoogleスプレッドシートではスクリプトを実行できませんが、GMail上のアドオンの場合はそれが可能になるため、結構使えるシーンがあるのではないかと思います。

図：メール下部にアドオンアイコンと内容が出てくる

Google Apps Scriptでちょっとした顧客管理台帳を作る - GMailアドオン編【GAS】

今回使用するスプレッドシートと事前準備

使用するファイル

事前準備

使われなくなる理由

ソースコード

機能と目的

共通ライブラリと設定

マニフェストファイルの作成

ソースコード

GMailにアドオンを追加

デプロイ

インストール

スマフォアプリからも使える

関連リンク

コメントを残すコメントをキャンセル

HTML5にてMSGファイルをドラッグ&ドロップで内容を取り込む

PowerPointの画像や動画をランダムに入れ替えたい

今回使用するスプレッドシートと事前準備

使用するファイル

事前準備

使われなくなる理由

ソースコード

機能と目的

共通ライブラリと設定

マニフェストファイルの作成

ソースコード

GMailにアドオンを追加

デプロイ

インストール

スマフォアプリからも使える

関連リンク

コメントを残す コメントをキャンセル

HTML5にてMSGファイルをドラッグ&ドロップで内容を取り込む

PowerPointの画像や動画をランダムに入れ替えたい

コメントを残すコメントをキャンセル