【GAS】Google Apps Script 活用事例 顧客IDを打つと顧客情報とGoogle MapsのURLを返してくる、外回りの営業さんを助けるLINE Botを作ってみた話！！
先日、ルート営業の業務で役立つ、LINE Botを作成しました。顧客名か顧客IDを入れると、顧客ID、顧客名、基幹システムのリンクと、住所、Google MapsのURLを返してくれます。一方で個人情報の取り扱いに絡んでくるセンシティブな問題もあると思うので、例えば、住所を表示せず、リンクだけしか記載しないなど、参考にされる場合は、ご自身で判断して実装してください。
情報漏洩のインシデントの内訳を見てみると、紙での持ち出し、パソコンやスマホなどの覗き見が、パスワード破り、よりも多いそうです。ご注意ください。
喜んでもらえれば、そりゃ、開発者冥利に尽きる！！
今回は、膨大な前月の案件リストから重複を省いて、顧客リストを作成し、LINE Botで使用するDBを作成しました。そして全てをGASで実装しました。大変だった分、喜んでもらえて良かったと思いました。ホントに。
1次元配列の顧客IDリストの重複を省くスクリプト
元データは、B列に顧客IDがあったので、B列だけを2次元配列で読み込み、それを1次元配列に変換してあげます。flat()で書いても大丈夫です！！
function uniqueValues() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('UNIQUE');
const dataSheet = spreadsheet.getSheetByName('readDB');
let custmerIdCoumn = dataSheet.getRange(2, 1, dataSheet.getLastRow()-1, 1).getValues();
custmerIdCoumn = Array.prototype.concat.apply([], custmerIdCoumn);
/*顧客IDの重複を削除*/
let uniqueValues = custmerIdCoumn.filter(function (value, i, array) {
return array.indexOf(value) === i;
});
console.log(uniqueValues);
return uniqueValues
}//end2次元配列の重複を省き、行全体を切り取るような処理
前述で作成した、重複無しの顧客IDリストを元に一致した行を全て配列に入れて、最後に貼り付けします。Shift + Spaceのショートカットキーで、行を丸ごと選択出来ますが、アレに近いイメージです。
これを関数で書こうとすると、全列にVLOOKUPを入れる形になるので、結構大変です。1列目に顧客ID 2列目に顧客名 3列目に住所のようなシンプルなシート構成です。C列の住所を読み取って、動的にGoogle Mapのリンクを生成します。
/*顧客IDを元にVLOOKUPをする*/
function vlookup(){
//pkをprimary keyとしてシート[VLOOKUP:参照]のフィールド[氏名]を参照してnewValuesに格納してsetValuesする
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('UNIQUE');
let primaryKey = uniqueValues();
const dataSheet = spreadsheet.getSheetByName('readDB');
const dataSheetValues = dataSheet.getDataRange().getValues();
// console.log('dataSheetValues[1][0] %s',dataSheetValues[1][0]);
// console.log('dataSheetValues.length %s',dataSheetValues.length);
let newValues = [];
let count = 0;
for(let i = 0; i < primaryKey.length; i++){
for(let j = 0; j < dataSheetValues.length; j++){
if(primaryKey[i] === dataSheetValues[j][0]){
newValues.push(dataSheetValues[j]);
primaryKey.splice(i, 1);
}//if
}
}
console.log('newValues.length %s', newValues.length);
sheet.getRange(2, 1, newValues.length, newValues[0].length).setValues(newValues);
/*名字と名前の間のスペースを削除する*/
const textFinder = sheet.getRange(2, 2, sheet.getLastRow()-1, 1).createTextFinder(' ').useRegularExpression(true);
textFinder.replaceAllWith('');
return
}//endprimaryKey(重複無しの顧客IDのリスト)と案件リストの顧客IDが一致したら、1行を丸ごと新しい配列に入れ、一致したIDをprimaryKeyの配列から削除していきます。重複を含んだデータの場合、そうしないと、一致したものが、全て通過してしまします。せっかく重複を省いた意味がなくなってしまうからです。
if(primaryKey[i] === dataSheetValues[j][0]){
newValues.push(dataSheetValues[j]);
primaryKey.splice(i, 1);
}//ifetau 大先生が、もうちょっといい方法あるよーと教えてくれました。for of文初めて見ました。これだと、1次元配列をわざわざ作る必要もない、とてつもなくシンプルにソースコードが書けるな。ありがとうございます！！......凄い←ボキャブラリーの無さ....笑
etau大先生流 二次元配列の重複を省くスクリプト
function myFunction() {
const values = [[111, 'つじけ', '札幌'], [111, 'つじけ', '札幌'], [111, 'つじけ', '札幌'], [222, 'ぼくくぼ', '千葉'], [222, 'ぼくくぼ', '千葉'], [222, 'ぼくくぼ', '千葉'], [333, 'etau', '神田']];
const keys = [];
const newValues = [];
for (const record of values) {
if (values.includes(record[0])) continue;
newValues.push(record);
keys.push(record[0]);
}
}LINE Messaging API を利用したBotのコード
これがメインのコードです。
以前作成したSlackのSlash Commandと同じで、顧客名で検索した時は、苗字か名前のどちらかが一致すれば、一致した結果が、全て表示されます。
一方、顧客IDで検索した場合は、完全一致したものが表示されます。Maps Serviceは、無料で利用する場合、1日2,500リクエストまでという制限があるため、後者で検索された場合のみ、Google Mapsのリンクを返しています。それ以上使う場合は、Google Map APIの登録が必要です。
function doPost(e) {
const ACCESS_TOKEN = '*********************************';
// WebHookで受信した応答用Token
const replyToken = JSON.parse(e.postData.contents).events[0].replyToken;
// ユーザーのメッセージを取得
const userMessage = JSON.parse(e.postData.contents).events[0].message.text;
const url = 'https://api.line.me/v2/bot/message/reply';
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('UNIQUE');
const values = sheet.getDataRange().getDisplayValues();
values.shift();
let results = '';
for(let i = 0; i < values.length; i++){
/*B列の名前から、部分一致でフルネームを出力する*/
if(values[i][1].indexOf(userMessage) !== -1){
results += values[i][0] + ' ' + values[i][1] + '\n\n' +
values[i][2] + '\n\n';
else if(values[i][0] == userMessage){
/*Google Mapsのリンクを生成*/
const maps = Maps.newGeocoder()
.setLanguage("ja")
.geocode(values[i][2]);//住所
console.log(maps);
const geoCode = maps.results[0];
const addressText = geoCode.formatted_address;
const geoData = geoCode.geometry;
console.log(geoData);
const latitude = geoData.location.lat;//緯度
const longitude = geoData.location.lng;//経度
/*https://www.google.com/maps/search/?api=1&query= と緯度経度の情報を組み合わせる事で検索可能*/
const common = 'https://www.google.com/maps/search/?api=1&query=';
const googleMapsUrl = common + latitude + ',' + longitude;
console.log(googleMapsUrl);
results += values[i][0] + ' ' + values[i][1] + '\n\n' +
values[i][2] + '\n\n' +
'Google Maps URL' + '\n' +
googleMapsUrl + '\n\n';
}//else if
}//for
console.log(results);
UrlFetchApp.fetch(url, {
'headers': {
'Content-Type': 'application/json; charset=UTF-8',
'Authorization': 'Bearer ' + ACCESS_TOKEN,
},
'method': 'post',
'payload': JSON.stringify({
'replyToken': replyToken,
'messages': [{
'type': 'text',
'text': results,
}],
}),
});
return ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
}const ACCESS_TOKEN = '*********************************'; は、LINE Developers に書いてあります。
全国の顧客リストをソートしてLINEに表示される順序を変えたい。SORT機能の実装
例えば、本社が東京にある場合は、東京都の顧客が多いわけで、東京都の情報が一番目に欲しいなぁと思ったので、Spreadsheet（データベース代わりとなるもの）に検索の優先順位の番号を振り、結果的にLINEに表示される順番をSORTしました。
東京都だったら、1、神奈川だったら、2みたいな感じでIFS関数やSWITCH関数のようなものを振っていきます。
/*東京都が最初に来るように検索順位ランクを付与する。*/
function setSearchRank() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName('UNIQUE');
const range = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn());
const values = range.getDisplayValues();
let newValues = [];
for(let i = 0; i < values.length; i++ ){
// if(values[i][2])
let location = values[i][2];
let searchRank = 0;
switch(location){
case '東京都': searchRank = 1;
break;
case '神奈川県': searchRank = 2;
break;
case '大阪府': searchRank = 3;
break;
case '千葉県': searchRank = 4;
break;
case '埼玉県': searchRank = 5;
break;
default: searchRank = 47;
break;
}
newValues.push([searchRank]);
}//for
console.log(newValues.length);
sheet.getRange(2, 8, newValues.length, 1).setValues(newValues);
sheet.getDataRange().sort([
{ column: 8, ascending: false }
]);
}この機能を書いた際、意外と、同じような原理でGoogleなどの検索順位の表示を変えているんじゃないかなと思いました。
アクセス数や、解析しやすい構造か、広告がユーザーの妨げになっていないかとか、それこそ複雑な要素も加わっているのだと思いますが......
こんな記事も書いています。
Slackでも似たような機能を作っています。
この記事が気に入ったらサポートをしてみませんか？