AppSheetで他のアプリと横連携させる【GAS】
以前からちょっと作ってみたいと思っていたドライブ専用アプリ。自分が気に入った場所や行ってみたい場所を登録しておくものですが、いつもスマフォのGoogle Maps => Navicon => カーナビといった具合に結構手順があって、面倒だなと思ってたのです。
そこで地点登録時に他のアプリに緯度経度送り込むURL Schemeを構築しておけばワンタッチでアプリ起動出来るのでは？と思いAppSheetで作成しました。登録時にGoogle Apps Script連携で書き込みしています。
今回利用するファイル
- ドラリス - AppSheet
- ドラリス - Spreadsheet
- GAS連携用スクリプトのコンテナ（ファイルのコピーはできません）
- Navicon API
- Navicon
- Yahooカーナビ
- やどここ
- Yahoo乗換案内
今回のアプリは地点登録時にGASでURL Schemeを生成し、スプシに書き込みしています。対象となるアプリは、Navicon, Google Maps, Yahooカーナビ, やどここ, Yahoo乗換案内の5つです。NaviconアプリとYahooカーナビアプリ, Yahoo乗換案内アプリを事前にAndroidスマフォに入れておく必要があります。
ドライブリスト、略してドラリスとしてアプリを構築していますので、コピーして使ってみてください。別途後述にあるスタンドアローンのGASコンテナを作ってアプリの手修正が必要です。スクリプトとしては後述に記載していますが、各種スクリプトプロパティの追加や読み書き先のスプシのIDの記入などが必要です。
今回のアプリは例えばトラック業者の配車管理アプリなどに応用することが可能です。
Google Mapsの問題点
自分も大いにGoogle Mapsを活用してたわけなのですが、色々と弊害にぶつかっています。
- Google TakeoutでSavedにてお気に入りと行ってみたいはURLだけが取得できる（緯度経度は含まれていない）。しかもCSV形式。
- またそのURLはアクセスすると緯度経度込のURLにリダイレクトされる。
- 同じくスター付きはJSON形式の緯度経度情報付きで取得できるが、スターは表向きある程度の件数以上で消える（データは存在してるが表示されなくなる）
- TakeoutでエクスポートしたデータをインポートするためのインターフェースはGoogle Mapsには存在しない。なので他のアカウントに引き継ぎが出来ない。
- Google Mapsはある程度数以上の自分のリストにお気に入りや行ってみたい場所を登録すると、スター以外表示されなくなる
- マイマップはいつサービスが終わってもおかしくないくらい放置されてる。（KMLで一括エクスポートできるのは便利）
- 自分のリストからマイマップへの移動は出来ない。
- Google Mapsから共有でNaviconへは位置情報を送れるけれど、Yahooカーナビには送れない。
- Google Mapsのカーナビ機能は酷道にすぐ案内しがち（自分は好物だから構わないんだが・・・）
とまぁ、結構時間経過と共に問題点が析出。故にこの問題を管理するには、プレーンな位置情報とプロパティを持って、スプシ上で管理する必要があるなと感じた為。スプシにまとめられれば、そこから先はAppSheetでマップアプリは作れる。
URLスキームとは？
概要
URL Schemeとは、一般的なURLのような形式で、実行すると対象Webサービスが開き、特定のアクションが自動で開かれて、パラメータまで渡してくれるというもので、有名どころで言えば「mailto」もそのうちの１つです（どちらかというとコレはプロトコルハンドラーと言われていたりします）。
httpsから始まるものもあれば、特殊な形式で始まるものまで様々で（Lineならばline://で始まる）、これがAndroidスマフォの場合には、他のアプリにパラメータを渡して、直接操作してくれるというものになります（Androidではこれをインテントと呼称してる）。
特別な操作やコーディング無しで、対象アプリをURL Schemeだけである程度操縦出来る為非常に手軽で、AppSheetとの相性も良さそうだと思い構築してみようと思いました。過去にもElectronでアプリを作り、URL Schemeで自作アプリを起動して対象画面までダイレクトジャンプするというものを作ったことがあります。
今回のアプリで使うURL Scheme
今回は４種類のアプリを操作する為に、AppSheetで地点登録時にGASでURLを生成してスプシに書き込みをしています。これをAppSheet側でタップすると、対象のアプリが起動して自動的に作動する仕掛けになっています。
Naviconを利用する為には次項の事前準備にて、無償のAPIキーが必要です。有償版ではアプリでワンクッションせず、ダイレクトにカーナビに位置情報が送信されるようになるみたい。過去にもGoogle Apps ScriptでKML生成の項目にて、1度取り上げていて生成したことがあります。APIキーは生成時に使われて、URL Scheme自体には含めたりはしません。
URL Schemeの例としては以下のようなものになります。
navicon://navicon.com/setPOI?ver=2.0&code=マップコード&mid=用途ID&lat1=緯度&lng1=経度&title1=地点名
navicon://という特殊なURLとなっていて、このURL自体はGASで生成することになります。AppSheetからの緯度経度情報と地点名が必須項目となっているので、仕様に従って作り、叩くとNaviconが起動して対象の地点まで一気にジャンプ。あとは手動でカーナビに送るだけという手順になります。
最大5箇所までの経由地指定の目的地登録に対応していて、一気に経由地込でカーナビに送れるスグレモノです（たぶん大抵のカーナビはNaviconに対応してる）。
複数箇所を登録する場合は、lat2やlat3といった形でURLに追加していくことで順番が決まり、一番最後に目的地の緯度経度をいれる必要があります。
図：こんな感じのアプリ
図：複数目的地投入してみた
Yahooカーナビ
自分の愛車のカーナビは2016年からナビが更新されておらず古い。それゆえに新しいバイパスや高速道などが掲載されていない。ということで補完目的で同時にYahooカーナビもドライブ中はセットしていたりします。
こちらも公式にURL Schemeが公開されており、仕様にしたがってURL Schemeを組んで実行すると現在地から目的地までのルートを指定した状態で起動されるので、後は自分でルートパターンを選択するだけという非常に手間が省ける便利機能になっています。
VICS対応、オービスエリア情報収録など本当に多彩で多機能なので、これ使ったらGoogle Mapsなんて使ってられないです。
URL Schemeの例としては以下のようなものになります。
yjcarnavi://navi/select?lat=緯度&lon=経度&name=地点名
こちらもyjcarnavi://という特殊な形式になっていますが、その後の緯度、軽度、地点名をAppSheetから取得してGASで生成しスプシに書き込んでいます。
複数の経由地をいれることも出来るのですが、最大で目的地1 + 経由地3つまでという制限がついています。またこの場合のリクエストURLは以下のようになります。
yjcarnavi://navi/select?point=current&point=1番目の緯度経度,&point=2番目の緯度経度,&point=目的地の緯度経度,
lat,lonでの指定ではなく緯度経度でpointにて指定する点が異なります。
図：Google Mapsなんかより全然良い
図：経由地を入れて送り込んでみた
Google Maps 検索
以前はぐるなびや、食べログなどもREST APIを出していて、URL Schemeなどもあったようですが、現在はそういう提供もなくなり、自分も使わなくなっていった理由は「Google Mapsのほうが全然情報収録されてるから」というのが理由だったりします。
カーナビとしては役に立たないGoogle Mapsですが、さすが検索情報はすごい。こちらもURLを指示に従って組み立てることでダイレクトに対象エリアの緯度経度高度とクエリキーワードで一発で出してくれます。
URL Schemeの例としては以下のようなものになります。
https://www.google.co.jp/maps/search/キーワード/@緯度,経度,高度?hl=ja&entry=ttu
こちらは標準のhttps://形式ですが、スマフォのデフォルトアプリ設定のリンクにより、ブラウザじゃなくアプリが起動するようになっています。AppSheetからは緯度経度情報だけ送っていますが、高度は17.57zで自分は見やすいので固定値指定しています。
図：データ検索はGoogle圧勝
やどここ
長距離ドライブしていて「ちょっと帰りの体力がヤバいな」と感じるケースが結構あります。1回で500km以上走ることも普通にあるため、そういった時は道の駅で車中泊の仮眠をするか？スポットで宿を取るか？前述のGoogle Mapsで同様のテクニックでも良いのですが、他のサイトで面白いサイトを見つけたので採用してみました。それが「やどここ」。 OpenStreetMapを採用してるみたい。
URL Schemeの例としては以下のようなものになります。
https://yadococo.net/?lat=緯度&lon=経度&z=高度
AppSheetから緯度経度情報を送り、高度は自分の場合13を指定しています。そのエリアの宿泊場所がマップに表示されるWebサービスでアプリではないです。あとはこれを元に宿を決めてチェックインを試みる。
図：目的地周辺の宿泊所情報
Yahoo乗換案内
ドライブではなく公共の交通機関を使ってのルート案内をするために、Yahoo乗換案内アプリのURLスキームを使って、現在地から目的地までの案内を一発で出す機能です。経路地指定は出来ませんが、以下のような形で実装しています。
- 観光スポットと温泉だけはスポット名で検索させています。
- それ以外は登録時の緯度経度情報に基づいて生成した住所を元に検索させています（全国に同じ店舗名のものがあったりするので）
- まだ、ごく一部の観光地等はたどり着けなかったり、アバウトな場所しか出ませんが、殆どの地名や店舗などはダイレクトなルートを表示可能です。
アクションにて、以下のような設定を行っています。
- Do ThisにてExternal : go to a websiteを指定
- Targetでは、以下のような数式を指定しています。
"https://transit.yahoo.co.jp/transit_app/androidToBrowser.php?from=現在地&to=" & if(IN([ジャンル],LIST("観光スポット","温泉")),[地点名称],[住所]) - タップするだけで判定してYahoo乗換案内がルートを提示します。
IF関数でジャンルを判定し、IN関数とLIST関数で観光スポットと温泉の時はそのまま、それ以外は住所の列を見て投げるようにしています。
図：Y乗換案内をタップする
図：現在地からのルートを提示してくれる
図：グルメなお店は住所で検索
図：数式だけでジャンプを実現
事前準備
スタンドアローンGASを準備する
コンテナの準備
まずはAppSheetで利用するスタンドアローンGASを準備してコードを記述しておきます。
今回のアプリの仕様としては
- 複数名で利用することが前提
- 各レコードには連番を振る必要がある
- 連番を元にレコードを特定してURL Schemeを書き込む
今回GAS側は引数として与えられたUNIQUEIDのID、緯度経度、地点名を元にスプシを探索し、レコードを特定し、対象のレコードの各列にURL Schemeを書き込んで通知を送る仕様になっています。尚、スタンドアローンのGASのファイルを作成する手順は以下の通りです（URLにcopyをつけても複製は出来ません）
- Google Driveを開いて、左上の新規をクリック
- その他 => Google Apps Scriptをクリック
- スクリプトエディタが直接開いて警告文が出るのでそのまま作成を続行する
- ドライブにはスタンドアローンファイルが生成される
見つからない場合には、Chrome Webstoreから拡張機能インストールしましょう（Google Apps Scriptで検索すると出てくる）。
図：新規から作成することが出来る
スクリプトのコード
空っぽのコンテナに対して、以下のスクリプトを書き込み一旦適当に関数を手動で実行して承認をしておきましょう。また、以下のコードの中のスプシのIDなどは事前にAppSheetサンプルをコピーすると複製されてるハズなので、そのスプシのIDを調べて書き込みをしておきます。
ここで使う関数は、「navgenerate関数」利用しますが、送られてくる緯度経度情報は半角スペースが入ってるので除去し、その後配列に変換して緯度と経度に分割しています。また、サブテーブルである経由地対応させる為に、サブテーブルのwaypointシートの中に親IDを持つものがいる場合は、それらをリクエストURLに含めるようにしています。
※おまかせドライブのコードは後述に別に記載しています。
//Navicon Request URL (POST通信）
var nvrequrl = "https://dev.navicon.com/webapi/cmd/navicon/createNaviConURL";
//Yahooカーナビ用URL Scheme
var yahoonavi = "yjcarnavi://navi/select?";
var yahoomulti = "yjcarnavi://navi/select?point=current&"
//Google Maps グルメ検索
var gmapguru = "https://www.google.co.jp/maps/search/";
//やどココ検索
var yado = "https://yadococo.net/?";
//スプレッドシートID
var ssid = "書き込み先のスプシのID";
//Navicon APIにアクセスしてURLを発行する
function navgenerate(id,pointname,address) {
//addressからスペース除去
address = address.replace(" ","");
//addressを配列に変換
let array = address.split(",")
//API Keyを取得する
let prop = PropertiesService.getScriptProperties();
let navapi = prop.getProperty("navapi");
let youto = prop.getProperty("youto");
//スプレッドシートを指定する
let ss = SpreadsheetApp.openById(ssid);
//中継点データを取得する
let waypoint = ss.getSheetByName("waypoint").getRange("A2:E").getValues();
let wayarray = [];
//対象のIDにぶら下がる中継点の有無
let counter = 0;
for(let i = 0;i<waypoint.length;i++){
//レコードを一個取り出す
let rec = waypoint[i];
//IDが一致するものがあるか？
if(id == rec[1]){
//カウンタを回す
counter = counter + 1;
//中継点データを追加する
wayarray.push(rec);
}
}
//カウンターの数でNaviconのリクエストを構築する
let formData;
let yahoo;
if(counter == 0){
//単一ポイントを構築する
//Navicon用
formData = "output=json&" + "apikey=" + navapi + "®id=" + youto + "&ver=2.0&name1=" + pointname + "&coordinates1=" + address;
//Yahooカーナビ用
yahoo = yahoonavi + "lat=" + array[0] + "&lon=" + array[1] + "&name=" + encodeURIComponent(pointname)
}else{
//複数ポイントのベースを構築する
//Navicon用
formData = "output=json&" + "apikey=" + navapi + "®id=" + youto + "&ver=2.0";
//Yahooカーナビ用
yahoo = yahoomulti;
//wayarrayを回す
let waycnt = 1;
for(let j = 0;j<wayarray.length;j++){
//レコードを一個取り出す
let way = wayarray[j];
//中継地の緯度経度情報を取得する
let tempway = way[4];
tempway = tempway.replace(" ","");
let temppoint = way[3];
//ポイントデータを構築する
formData = formData + "&name" + waycnt + "=" + temppoint + "&coordinates" + waycnt + "=" + tempway;
yahoo = yahoo + "&point=" + tempway + ",";
//waycntを回す
waycnt = waycnt + 1;
}
//目的地を最後にいれる
formData = formData + "&name" + waycnt + "=" + pointname + "&coordinates" + waycnt + "=" + address;
yahoo = yahoo + "&point=" + address + ",";
}
//エラートラップ
//リクエストオプション
let options = {
'method' : 'post',
'payload' : formData,
'contentType': "application/x-www-form-urlencoded"
};
//Navicon APIリクエスト
let json;
try{
let response = UrlFetchApp.fetch(nvrequrl, options);
//レスポンスデータからURIを取得する
json = JSON.parse(response.getContentText());
}catch(e){
//返り値は空にする
json = {
urlschema : ""
}
}
//スプシをflushしておく
SpreadsheetApp.flush();
//スプシデータを取得する
let sheet = ss.getSheetByName("drive").getRange("A2:E").getValues();
let driveman = ss.getSheetByName("drive");
//引数idと一致する行を特定する
for(let i = 0;i<sheet.length;i++){
//レコードを一個取り出す
let record = sheet[i];
//レコードIDを比較する
if(id == record[0]){
//書き込み対象アドレスの作成
let rec = i + 2;
let target0 = "C" + rec;
let target = "F" + rec;
let target2 = "G" + rec;
let target3 = "H" + rec;
let target4 = "I" + rec;
let videoman = "M" + rec;
//Y乗換案内用に住所変換して格納
let revman = revaddress(address);
driveman.getRange(target0).setValue(revman);
//Navicon用URLセット
driveman.getRange(target).setValue(json.urlschema);
//Yahooナビ用URLセット
driveman.getRange(target2).setValue(yahoo);
//Google Maps グルメ検索
let gmaps = gmapguru + "グルメ/@" + array[0] + "," + array[1] + ",17.57z?hl=ja&entry=ttu"
driveman.getRange(target3).setValue(gmaps);
//やどココ検索
let yadococo = yado + "lat=" + array[0] + "&lon=" + array[1] + "&z=13"
driveman.getRange(target4).setValue(yadococo);
//Youtube関連動画を調べる
let video = getYoutubeVideoUrl(pointname);
if(video == false){
//何もしないでスルー
}else{
//埋め込み用動画URLをセットする
driveman.getRange(videoman).setValue(video);
}
break;
}
}
return true;
}
//Youtubeから関連動画を取得する
function getYoutubeVideoUrl(keyword){
//キーワードがガソリンスタンドの場合はスルーする
if(keyword == "ガソリンスタンド"){
return "";
}
try {
//キーワードを元に動画を検索
//https://developers.google.com/youtube/v3/docs/search/list?hl=ja
const results = YouTube.Search.list('id,snippet', {
q: keyword,
maxResults: 2,
order:"date"
});
//エラートラップ
if (results === null) {
console.log('検索出来ず');
return false;
}
//1個目の動画を取得
let movie = results.items[0];
//動画から埋め込み用URLを取得
let videoid = movie.id.videoId;
let embedurl = "https://www.youtube.com/embed/" + videoid;
//URLを返す
return embedurl
}catch(e){
console.log(e.message);
return false;
}
}
やっている内容としては、
- Navicon APIリクエストでURLを取得する
- 引数idを元にスプシのレコード位置を特定する
- 登録地点名を基準にYoutube Data APIに関連動画の埋め込み用URLを取得
- 特定したレコードにNaviconのURL、YahooカーナビのURL Schemeなどを生成して書き込み
- 完了したらtrueを返却する
といった作業になります。
Youtubeで関連動画を取得する
あとから追加した機能なのですが、AppSheetには列の型に「Video」というものがあり、これはUI上からは一切編集は出来ないのですが、スプシに直接Youtubeの埋め込み形式のURLを記述すると動画が出てくる仕組みになっています。新規登録時や更新時に地点名を元にYoutube Data APIを使って最新の関連動画を１本表示できるようにしました。
動画のIDを元に「https://www.youtube.com/embed/動画のID」という形式にしてGAS側からスプシに書き込みをしています。これでAppSheet上でVideo型の列の値を参照して動画を表示する事が可能です。故に、GASのコンテナにて左サイドバーのサービスから「YouTube Data API v3」を有効にしています。
但し注意点として、このAPIのQuotaは思っている以上に低いので連続で何回も登録してると1日のリクエスト上限に到達して動画検索が出来なくなります。そのため、リクエスト時にoptionとしてmaxResultsは2に指定しています（どちらにせよ最初の１本目しか取らないので1でも良い）。
※地点名をキーワードとしてるので、同じ名前の別の地点であったり、オカシナ動画が引っかかる可能性があるので、県名を入れてみたりちょっとした工夫が必要な場合があります（例えば草津は滋賀県と群馬県にあるので間違ったほうがヒットする可能性がある）。
図：対象列の型をVideoにしておく
図：Youtube Data APIを有効化する
図：AppSheetに関連動画が表示された
スクリプトプロパティ
今回のアプリでは手動でスクリプトプロパティの追加が必要です。スクリプトエディタ左サイドバーのプロジェクト設定を開き、一番下にあります。編集ボタンをクリックして追加していくことが可能です。
- Navicon APIのキーをnavapiという名前で追加しておく
- Navicon APIの用途IDをyoutoという名前で追加しておく
- Claude3.5 SonnetのAPIキーをapikeyという名前で追加しておく（おまかせドライブで利用します）
これらのキーなどの入手は次項以降で記述しています。ClaudeのAPIの取得方法はこちらを参照してください。
図：APIキーなどを登録しておく
スタンドアローンGASを生成後に、以下の手順でNavicon APIの無償のキーと用途IDを取得し、GASのスクリプトプロパティにそれぞれ値を格納しておきます。
- アカウントを作成しログインする
- こちらにアクセスし、オープンタイプの自動発行をクリック
- この時点でAPI Keyを取得出来ます。
- 次に右上のマイページをクリックして、用途管理をクリック
- 新規登録でNaviconを選択し、新規追加をクリック（MapQRコードも作れるみたい）
- 用途の必須項目だけ入力して、保存する
- すると、用途IDを取得出来ます。
- 前述のスタンドアローンのGASを開く
- 左サイドバーより、プロジェクトの設定を開く
- スクリプトプロパティを追加にて、以下の2つを追加する
⇒ プロパティ：navapi、値：API Keyの値
⇒プロパティ：youto、値：用途IDの値 - 保存して終了する
図：スクリプトプロパティにキーを登録しておく
Botの参照スクリプトを変更する
本プロジェクトをコピーしたままだと、Botの参照するスクリプトが自分が作ったものを参照しています。これでは動作しません。スタンドアローンスクリプトを自分で用意できたら、以下の箇所を修正します。
AutomationのBot一覧を表示しておきましょう。
- GAS連携：naviconurlgetの中にあります。使用する関数はnavgenerateを指定します。
- おまかせ：以下の数カ所があります。
- New stepの中にあります。使用する関数はgetOmakaseを指定します。
- Deleteman, Deleteman2の中にあります。使用する関数はcleartempを指定します。
図：スクリプトの変更例
AppSheetで構築
テーブル構成
スプレッドシートをアプリに取り込んで作成していますが、各シートの構成は非常にシンプルです。junleシートはジャンル選択肢の為だけのシートでRefで連結してるのみです。
driveシートがすべての基準になるテーブルになりますが
- ID列はUNIQUEID()にて自動的にランダムなIDを格納します。
- ジャンル列はドロップダウンで構成し、選択していれるだけなので、Refで構成しています。
- 緯度経度列にはAppSheetで取得した緯度経度情報が格納されます（但し半角スペースが入ってる）。
- 以降の列は各アプリ用のURL Schemeが格納されます。
- その他URLとメモ用のフィールドを用意しています。
- 更新フラグ列は、経由地を登録した際にURL Schemeを再生成させる為に使うフラグです。
- 動画列は登録時にYoutube Data APIにて関連動画を取得し埋め込み用URLを格納しています。
- 住所列は登録時にGASの逆ジオコーディングメソッドにて緯度経度から住所を逆引きして格納しています。
また、サブテーブルとして経由地を登録する場合はwaypointシートが対象になります。AppSheet上でYahooの制限に合わせて経由地は3件まで登録可能にしています。
- ID列は自動的にランダムなIDを格納します。
- DriveID列は親のdriveテーブルのIDを格納します。
- 連番は数式で自動的に数字の連番を格納しソートなどに使おうかなと思っています。
- 経由地は経由ポイントの名称を入れます。
- 緯度経度は経由地の緯度経度情報を格納します。
AppSheet上のフォームでは、ID列やURL Scheme情報は非表示にしてるので実質、地点名称と緯度経度だけを入力して保存する形になります。列の型としては、緯度経度がLagLong、ジャンルはRef、URL SchemeはURLとして指定しています。
waypoint側のdriveid列はRefとして親のdriveテーブルに対して連結しています。
図：ドライブリストの様子
図：経由地リストの様子
図：AppSheet上での型指定の様子
ビューとフォーマット
Map View
一覧表示の状態から、緯度経度フィールドでピンを配置出来なくもないですが、やりづらいので、ピン配置用のマップビューを新たに追加しています。緯度経度のフィールドを元にMapとしてビューを追加するだけで完成します。
但し、これではピンのフォーマットがデフォルトのままなので、次項で各ジャンル毎にピンのデザインを行います。
図：マップビューを追加します
ピンのフォーマット
ジャンル列の値を元に、色やアイコンのデザインを今回は変えないと、全部のジャンルが同じピンになってしまいわかりにくいです。そこで、これらジャンルの項目毎にピンのフォーマットを作ります。
- 左サイドバーよりView ⇒ Format Rulesを開く
- 上部のAdd Format Rulesをクリックする
- Create a new format rulesをクリックする
- Rule Nameにはジャンルの名前をいれると良いでしょう。
- If this condition is trueでは、以下の数式を入れます。
[ジャンル] = "ジャンル名"
- Saveをクリックする
- Format these columns and actionsは緯度経度フィールドを指定します。
- Iconでは適当なものを選択します。
- Highlight colorは適当な色を選択します。
- 右上のSAVEをクリックして保存する
このフォーマットをジャンルシートの項目の数だけ作っておきます。
ちなみに、9.の色指定ですが、既存の色だけじゃなく、HTML Color Pickerのパレットから選んだ結果のHEXという値を手動で入力すると、その色を指定することが出来ます。
図：ジャンル毎にフォーマットを用意
図：数式で判定させる
図：ジャンル毎にピンが分かれた
図：色を手動で指定してみた
アクションボタンの色の変更
実はアクションボタンの色も変更出来るのですが、これActionsのボタン設定上では変更項目がありません・・・前述のピンのカラー同様にViewのFormat Rulesにて変更が可能です。カラフルな色合いを実現することが出来ます。
図：ボタンカラーが変わった
図：なぜかここから変更する必要がある
Map View Option
今回のマップビューでは、ピンを設置する為に使ってる側面が強いのですが、マップビューは検索が出来ないので、地図上の表記が無いとピンが配置しにくいです。よって、Hide points of interestはオフにして、様々なポイントデータを表示するようにします。
図：ここをオフにしておく
経由地登録件数規制
今回のアプリでは、メインの登録画面の一番下に経由地ポイントを登録する仕組みを設けています。そこに経由地を登録すると、NaviconやYahooカーナビ用のURL Schemeを再生成してテーブルに登録し直します。
しかし、Yahoo側の規制が最大経由地3つまでとなっているので、Naviconもそれに合わせて3つまでサブテーブルに登録出来るけれども、4件目からは登録させたくありません。この制限を行います。サブテーブル側に対して行いますが、DriveIDの件数が3件までなら許容しますが、それ以上の場合はエラーとするData Valid数式をいれるのですが、アプリ上ではDriveID列を表示させないようにしてるため、これではData Validが効かない。
故に、経由地名のほうにDriveIDの件数チェックをする数式を入れます。
- 左サイドバーより、Dataをクリックし、waypointテーブルを開く
- waypointの経由地名フィールドの横の鉛筆マークをクリックする
- Data Validityというところをクリックして開き、Valif Ifの欄をクリックする
- 数式入力欄に以下のような数式を投入して保存する
if(COUNT(SELECT(waypoint[DriveID], [DriveID] = [_THISROW].[DriveID])) >= 3,false,true)
- 合わせて、Invalid value errorにはエラー内容のメッセージを記入する
- Require?にはチェックを入れる
- ダイアログ上のDoneをクリックし、右上のSAVEをクリックする
これで、経由地の新規追加時に経由地名に値をいれると、チェックが走り、3件登録されてる時にはエラーメッセージが出て追加できないように規制することが可能です。
図：経由地名にデータチェック数式を入れる
図：4件目登録時に規制が掛かった様子
各ボタンのアクション
タップしたら別のアプリが起動するというボタンは以下のような感じで作成しています。特別なことをしてるのではなく、単純にウェブサイトを開くというアクションで叩いてるだけなのです。
- 左サイドバーのActionsをクリックする
- 上部にあるAdd Actionをクリックする
- Create a new actionをクリックする
- Action Nameを適当に入力する。ボタン名になります。
- Do thisでは「External: go to a website」を選択します。
- Targetは対象のURL Schemeが入ってる列を指定します。
- 右上のSAVEをクリックして保存する
URL Schemeを作成出来るのであればこの手法でどんどんボタンを追加することが可能になります。
図：アクションではURLを叩く処理をセット
ボットの作成
GAS連携
今回のサンプルはコピーしてもスタンドアローンコンテナは手動で用意する必要があります。あらかじめ作っておいて以下のような形で関数を指定し、応答を受け取ったらスマフォ通知を送る仕掛けにしてあります。
- 左サイドバーのAutomationをクリックし、上部にあるCreate a new botをクリックする
- Create a new botをクリックする
- Configure Eventをクリックする
- Create a custom eventをクリックする
- Data change typeは、AddsとUpdatesだけをチェックいれてDeleteは外しておく
- Add a stepをクリックする
- Create a custom stepをクリックする
- 作成したステップをクリックして、Call a scriptを選択する
- Apps Script Projectにてクリックし、作成したスタンドアローンスクリプトを選択する
- Function Nameではnavgenerate関数を選択する
- Function Parametersにはそれぞれどの値を渡すのか？を指定する
- Return Valueのスイッチをオンにする
- Booleanを選択する
- 下の青枠の中に[New step].[Output]というような文字列があるのでこれをコピーしておく
- Add a stepをクリックする
- Step名の下のrun taskをクリックして、Branch on a conditionに変更する
- 下の数式には14.でコピーしておいた項目をいれる
[ステップ名].[Output]
- yes / noそれぞれの＋をクリックする
- 各ステップをクリックして、send a notificationを選択
- Toは数式にしてから「USEREMAIL()」を入れる
- Use default content?のスイッチをオンにして、タイトルや説明文をいれる。
- もう一方の側も同様に作成する
- 右上のSAVEをクリックして保存する
手修正する場合は、9.のプロセスだけ実施してGASを選択し直すと良いでしょう。
また、これは単一の目的地の追加と更新時には発火しますが、中継点だけをあとから追加した時には発火しません。よって、waypointテーブルに値を追加や更新時・削除時にも発火するように同様のAutomationを入れておきましょう。その際の引数はDriveID, 経由地名,緯度経度になるので要注意ポイントです。
図：GAS連携と通知の設定
経由地編集時アクション
このままだと、単一の目的地の追加時には普通にURL Schemeが生成されるのですが、問題はその後経由地を追加したり削除した後には以前のURL Schemeのまま残ってる為、NaviconやYahooナビのSchemeは以前のデータで投げられてしまいます。きちんと経由地を経たものに自動で更新して欲しい。
そこでdriveシートの更新フラグ列をwaypointテーブル更新時に書き換えることで、URL Schemeを再生成するテクニックを使います。
- Actionにて、driveシートにてData: set the values of some columns in this rowにて更新フラグにUNIQUEID()で書き込むアクションを用意する（Scheme更新という名称にした）。
- 続けて、waypointシートにてData: execute an action on a set of rowsにて、driveシートをList([DriveID])でフィルタし、1.のScheme更新を実行するというアクションを作成する（更新実行という名称にした）。
- 次に、Automationを開いて、waypointの追加、更新、削除を起点とするBotを作成する
- ステップを追加し、Run a data actionを選択する
- 右サイドバーでは、Run action on rowsを選択し、reference tableはdriveを選択、referenced rowsはLIST([DriveID)を入力する。
- Referenced Actionでは、「Scheme更新」を選択する
- 下の方にあるOptionを開く
- Trigger other botsのスイッチをオンにする（これをしないとGAS更新のBotが作動しない）
- Wait for execution to complete?のスイッチをオンにする
- 右上のSAVEをクリックして保存する。
これで、サブテーブルであるwaypointにデータの追加や削除を行うと、更新フラグが書き換わり、GAS連携のボットが動いてURL Schemeの再生成が行われ、通知が飛んでくるようになります。
手動で実行をさせることも出来るのですが、人間忘れてしまうのでこのようなロジックを組んで、更新忘れを無くしているわけです。また、2つのアクションを使うことで、テーブルまたぎでアクションを実行出来るようになるというテクニックを利用しています。
図：まずは更新フラグを書き換えるアクションを用意
図：waypointからdriveのアクションを実行するアクションを用意
図：Botにて更新実行アクションとScheme更新アクションをセット
図：オプションセットを忘れずに
おまかせドライブ機能
概要
自分で目的地をセットするのは良いのだけれど、カーナビというのは常に「最短距離で最短時間で最小コストで行ける道」しか提示してくれないわけです。別に急いでるわけじゃないので、色々寄り道しながらのルートとか提示してくれないのかなと思い考えついたのが、生成AIを使って「現在地から目的地までの間で寄り道するポイントを適当に選んで生成」という機能。
ChatGPTとClaude3.5 Sonnetでは無事に生成出来てるので、AppSheetからボタン一発で適当に経由地を生成して取り込むという機能を「おまかせドライブ」として実装しました。残念ながらGeminiは答えすら返してくれませんでした。
以下のスクショはその生成してる様子。Claudeの場合ポイント名と緯度経度情報も小数点以下6桁まで出せてるので、十分寄り道ポイントに行けるのではないかと思ったら、霧島アートの森の緯度経度をGoogle Mapsに反映したらとんでもないズレた場所だった・・・。ということで、観光スポット名だけ列挙してもらって、GASでGeocodingするようにしましたら正確な緯度経度が割り出せました。
これをAppSheetからGASに依頼して、GASから解答をスプシに書き込んで返すという仕組みで実装しています。
※ちなみにClaude3.5 Sonnetはマップコードは未対応だが、ChatGPTはマップコードで返せるのがわかった。
図：Claude3.5 Sonnetの解答はバッチリ
図：ChatGPTもなかなか良かった
実装してみる
以下のような仕様で構築しています。この機能を利用するには、Claude APIのAPIキーの取得が必要です。
- Yahooカーナビに合わせて、最大3箇所まで
- Claude3.5 Sonnet APIに問い合わせしてスプシに格納する
- 生成した後にURL Schemeの生成を自動実行する
- 出発地はHERE関数を使って緯度経度を取得し、逆ジオコーディングで住所に変換する
今回のアプリの仕様だとdriveテーブルが更新されるとURL Scheme生成が走ってしまうので、これを避ける必要があるのと、HERE関数はGAS連携での引数で直接使えないのがわかったので、迂回策が必要になったので、以下のような手順で作成しています。
- tempシートを作成し、ID, 地点名称, 現在地の3列だけを用意する
- Actionにてdriveに作成し、Data add a new row to another table using values form this rowを用いて現在のカレントレコードの一部列だけをtempシートにコピーするボタンを作成（これがおまかせドライブボタンになる）
- 現在地はhere関数でこの時点で取得できるので、追加する現在地のフィールドに当てはめる。
- Automationのbotにて、tempに値が追加されたら発動するGAS連携を作成する。
- その際にGAS側のgetOmakase関数に対して、1.の列の値を引数に入れてGASへ渡し、結果を受け取る
- 処理後結果を受け取ったら、続けて通知を送って、Run a data actionにてScheme更新のActionを実行するフローを実行する
- 最後にGAS側でtempシートの中身を空にするコードを実行して消去する
これでdriveシートに直接書き込みをしていないので先にURL Schemeを生成されるのを防ぐことが出来ました。
図：レコードをtempにコピーする
図：GAS連携で3つの引数を渡す
図：最後にScheme自動生成を実行
Google Apps Scriptのコード
さて、GAS連携では何をやってるのか？ということですが以下のようなコードでClaude3.5 Sonnetに問い合わせをして、結果を受取り加工してスプシに書き込みをしています。あらかじめスクリプトプロパティに、APIキーをapikey : keyの値という事で登録しておく必要があります。
//エンドポイント
var endpoint = "https://api.anthropic.com/v1/messages"
//tempをクリアする
function cleartemp(){
let ss = SpreadsheetApp.openById(ssid);
ss.getSheetByName("temp").getRange("A2:C").clearContent();
}
//緯度経度を取得する
function getlatlon(spotname) {
//ジオコーディングする
let res = Maps.newGeocoder().setLanguage('ja').geocode(spotname);
let result = res.results[0];
//結果から緯度経度を取得する
let ret = result.geometry.location.lat + "," + result.geometry.location.lng;
//緯度経度情報を返す
return ret
}
//逆ジオコーディングをする
function revaddress(nowlocation){
//緯度経度情報から分割する
let latlon = nowlocation.replace(" ","");
let templatlon = latlon.split(",");
let lat = templatlon[0];
let lon = templatlon[1];
//逆ジオコーディングをする
let res = Maps.newGeocoder().setLanguage('ja').reverseGeocode(lat, lon);
let result = String(res.results[0].formatted_address);
//住所データだけ取得する
let tempaddress = result.split(" ")
let address = tempaddress[1];
//住所情報を返す
return address
}
//おまかせドライブリストを取得する
function getOmakase(recid,nowlocation,spotname){
//現在地から住所を逆ジオコーディング
let nowpoint = revaddress(nowlocation);
//指示を構築する
let fulltext = "現在地である" + nowpoint + "から目的地である" + spotname + "までのルートにおいて、以下の条件を全て満たすデータを返してください。\n"
+"・ルートの途中で3箇所だけ観光スポットを追加してください。隠れたスポットでもオッケーです。\n"
//+"・ただし、追加する観光スポットは、目的地までの間にある場所を追加してください。現在地から目的地の間には無い場所は追加しないでください。\n"
+"・ただし、追加する観光スポットは、緯度経度を比較した場合に現在地と目的地の間に存在する場所を追加してください。\n"
+"・ただし、追加する観光スポットは、実在する正確な名前を教えてください\n"
+"・ただし、追加する観光スポットは、毎回違う場所にしてください\n"
+"・ただし、現在地と目的地が同じ都道府県内にある場合には、追加して良い観光スポットは、なるべく同じ都道府県内にある違う市町村に所属する場所を追加してください。\n"
+"・ただし、現在地と目的地が違う都道府県内にある場合には、追加して良い観光スポットは、なるべく全部違う都道府県に所属する場所を追加してください。\n"
+"・ただし、追加する観光スポット名の最後に、湖や峰、桜島、山、峡を含む場合は、追加する観光スポット周辺の自動車で到達できる場所や駐車場のフルネームに置き換えてください\n"
+"・現在地と" + spotname + "もスポット名に追加してください。\n"
+"・解答はカンマ区切りで、正確な観光スポット名として返してください\n"
+"・解答の観光スポット名の頭には、観光スポットの所属する県名を付けて、半角スペースで区切って観光スポット名として返してください。\n"
+"・解答に説明等は一切含めないでください。"
+"・一番最後の処理として、解答として出力されたカンマ区切りのデータは、現在地と目的地以外のデータを、現在地から近い順に並び替えてください。"
console.log(fulltext)
//質問文（同時に複数指定可能）
let messages = [
{"role": "user", "content": fulltext}
]
//リクエスト本文
let payload = {
model: "claude-3-5-sonnet-20240620",
max_tokens: 1024,
messages: messages,
}
//APIキーを取得
let prop = PropertiesService.getScriptProperties();
let apikey = prop.getProperty("apikey");
//リクエストヘッダー
let headers = {
'Content-Type': 'application/json',
'x-api-key': apikey,
'anthropic-version': '2023-06-01',
}
//オプション構築
let options = {
method: 'POST',
headers: headers,
payload: JSON.stringify(payload),
}
//リクエストを実行
let res = "";
try{
res = UrlFetchApp.fetch(endpoint, options);
}catch(e){
return false;
}
//レスポンスを取得
const restext = res.getContentText();
//レスポンスコードを取得
const rescode = res.getResponseCode();
//返ってきた答えを出力
const json = JSON.parse(restext);
//カンマ区切り値を配列に戻す
let content = json.content[0].text
let array = content.split(",");
//配列からスプシ用にデータを整形
let record = [];
let cnt = 1;
for(let i = 1;i<array.length - 1;i++){
//ポイント名を取得する
let pointman = array[i];
//ジオコーディングをする
let polatlon = getlatlon(pointman);
//uuidを生成
let uuid = Utilities.getUuid();
//配列を生成する
let temparr = [uuid,recid,cnt,pointman,polatlon];
//書き込み用配列に追加する
record.push(temparr);
//カウンタを回す
cnt = cnt + 1;
//4になったら強制ループ脱出
if(cnt == 4){
break;
}
}
//シートを取得する
let ss = SpreadsheetApp.openById(ssid).getSheetByName("waypoint");
let sheet = ss.getRange("A2:E").getValues();
//recidと一致する行を削除する
let finalrow = ss.getLastRow();
for (let i = finalrow; i >= 2; i--) {
//レコードを取得する
let rec = sheet[i - 2];
//レコードのDriveIDを取得する
let driveid = rec[1];
//driveidと一致するか？
if(driveid == recid){
//対象行を削除する
ss.deleteRow(i);
}
}
//waypointシートのラストに一括書き出し
let endrow = Number(ss.getLastRow()) + 1;
let lastColumn = record[0].length; //カラムの数を取得する
let lastRow = record.length; //行の数を取得する
ss.getRange(endrow,1,lastRow,lastColumn).setValues(record);
//処理完了
return true;
}
- 現在地は緯度経度情報を逆ジオコーディングで住所に変換しています。
- Claude3.5 Sonnetへの問い合わせ結果はスポット名の配列に変換し、それぞれをジオコーディングで緯度経度情報に変換しています。
- スプシに合わせて配列を生成し、waypointシートに書き込む。（uuidの生成などもしています）
- 書き込み前に引数のidを使って、該当するレコードを一旦削除しています（洗替えする為）
- データは最後に一括書き込みをして、AppSheet側に返しています。
- 時々、3箇所だけ追加と言ってるのに4箇所返してくることがあるので、リミッターとして4回目にループを強制脱出させています。
- プロンプトで観光スポットのワードに県名を付与させているのは、同名の観光地が日本の別の場所にあった場合、ジオコーディング時にそちらをヒットさせてしまう可能性があるため。県名を入れてジオコーディングをすることで外れることがありません。
- 極端に遠い場所を排除してる理由は、近郊に行くドライブなのに「ナイアガラの滝も見てきなよ」とか言ってくるのを防ぐ為です。
- なるべく直線上にある観光地を追加させるべく、緯度経度での判定を入れています。
- 湖や峰、峡などは車で到達できないポイントを示してしまうことがあるので、その周辺の観光地や駐車場を出すようにしています（例：支笏湖だと到達できないので支笏湖ビジターセンターをこれで出してくれるようになる）
- 同じ市町村や県内で観光地が集中しないように、それぞれのケースで違う場所を指定するようにし、中継点を分散しています。
使用したプロンプトは以下のようなもの。だいぶチューニングできた気がする。
現在地である[現在地]から目的地である[目的地]までのルートにおいて、以下の条件を全て満たすデータを返してください。 ・ルートの途中で3箇所だけ観光スポットを追加してください。隠れたスポットでもオッケーです。 ・ただし、追加する観光スポットは、緯度経度を比較した場合に現在地と目的地の間に存在する場所を追加してください。 ・ただし、追加する観光スポットは、実在する正確な名前を教えてください ・ただし、追加する観光スポットは、毎回違う場所にしてください ただし、現在地と目的地が同じ都道府県内にある場合には、追加して良い観光スポットは、なるべく同じ都道府県内にある違う市町村に所属する場所を追加してください。 ・ただし、現在地と目的地が違う都道府県内にある場合には、追加して良い観光スポットは、なるべく全部違う都道府県に所属する場所を追加してください。 ・ただし、追加する観光スポット名の最後に、湖や峰、桜島、山、峡を含む場合は、追加する観光スポット周辺の自動車で到達できる場所や駐車場のフルネームに置き換えてください ・現在地と[目的地]もスポット名に追加してください。 ・解答はカンマ区切りで、正確な観光スポット名として返してください ・解答の観光スポット名の頭には、観光スポットの所属する県名を付けて、半角スペースで区切って観光スポット名として返してください。 ・解答に説明等は一切含めないでください。 ・一番最後の処理として、解答として出力されたカンマ区切りのデータは、現在地と目的地以外のデータを、現在地から近い順に並び替えてください。
ダーツの旅機能
ボタン一発で、登録済みの地域にジャンプしてくれる「ダーツの旅機能」になります。アクションボタンをつけて、以下のような数式を構築してるだけで実現出来ます。
- Do thisにはApp: go to another view within this appを指定する
- 以下の様なコードをTargetにいれる
linktorow(LOOKUP( RANDBETWEEN(1, MAX(drive[_RowNumber])) ,"drive","_RowNumber","ID"), 'Maps')
- ボタンを押すとランダムに数字を生成して、対象の詳細なビューへジャンプする
関数の機能的には
- MAX関数で対象テーブルの_RowNumberの最大値を取得
- RANDBETWEEN関数にて、1から最大値までの数値をランダムに生成
- LOOKUP関数にて、ランダム数値を元に対象テーブルの_RowNumberにヒットするID列の値を取得
- 取得したID列の値を元にLINKTOROW関数でMapsテーブルの詳細なビューへジャンプする
入れ子なので複雑ですがノーコードで実現が可能です。
図：ダーツボタン
図：数式だけでジャンプを実現
写真登録機能と問題点
各ドライブポイントに対して現地で撮影した写真を登録したいと思い、写真シートを用意してAppSheetに取り込みました。写真はフォーム上ではRefで連携してる別テーブルなので、サブフォーム的な表示として表示されます。しかし、マップのピンと編集ボタンに邪魔されてAddボタンが押しにくい。これどうにかならないかなぁ。
ということで、ドライブポイントデータの詳細画面から追加できるかな？と思ったら、Newボタンが出てこない・・・
調べてみるとRefで連携した場合、サブテーブル側のRef型のプロパティにある「Is a part of」のチェックが入っていないと、フォーム上で「New」としてボタンと言うか項目自体が出てこないことがわかりました。なので必ずチェックを入れましょう。但しこの場合親レコードを削除すると子レコードも連鎖削除されてしまうので要注意。
図：この項目が表示されていなかった・・・
図：is a part ofチェックで表示される
図：無事に装備出来ました。
アプリを使ってみる
今回のアプリはスマフォで動かすこと前提であるため、利用もスマフォでの利用の手順になっています。
地点登録
目的地の登録
drive画面から登録も出来なくはないのですが、非常にやりにくいので、Maps画面上からピンを置いて登録する流れを別に用意しています。
- Mapsのタブを開く
- 左下の「ピン」のボタンをタップして、対象の位置にピンを配置する
- 左下の「チェック」のボタンをタップする
- 地点名称を入れて、ジャンルを選択します。
- Saveをタップすると保存される
- GAS連携が走ってURL Schemeが追加書き込みされる
- スマフォに通知が飛んできて登録完了となる。
図：ピンを配置する
図：スポット登録
経由地登録
前述で登録した目的地を開いて、一番下の方に経由地があるので、Addをクリックすると経由地のポイントを3つまで登録することが可能です。各登録完了時にURL SchemeがBotによって再生成され、NaviconとYahooカーナビ用のSchemeが再登録されます。3つ以上登録しようとすると規制が掛かり、登録できないようになっています。
あとは、NaviconやYahooナビのボタンをタップすると、それぞれのアプリに経由地込の目的地が送り込まれます。Yahooカーナビのほうはこのあとのアクションとして、一番下のルートをタップ⇒確定ボタンを押し、ルート選択画面になるので、選んでナビ開始をタップすればナビゲーションが開始されます。
図：経由地込で送り込めます
各アプリ連携
各登録済みスポットをタップして開くと以下のような画面になります。
上部にある4種類のボタンをタップすると、URL Schemeが実行されてそれぞれのアプリが起動し、パラメータが渡されて、該当箇所にジャンプして各アクションが実行される仕組みになっています。
図：上部のボタンをタップするだけ
関連リンク
- Yahoo!カーナビに使えるURLスキームのご紹介（アプリ開発者向け）
- Googleマップアプリで保存済みマークが消えていた件・・・復活出来た
- Ref not showing in Form View
- Androidスマホで「リンク」と「アプリ」の関連付けを設定する方法
- 事例紹介：食べログ事件（優越的地位の濫用が争われた事例）
- 通れた道マップ - TOYOTA
- かごしまの旅
- JavaScriptにおける正しいURLエンコードの方法（encodeURIとencodeURIComponentの違い）
- JavaScriptでカンマ区切りの文字列を分割して配列に格納する方法を現役エンジニアが解説【初心者向け】
- JavaScriptで空白、スペース、ハイフンを削除する方法【trim,replace,replaceAll】
- #MA10 鹿児島初上陸！！Mashup Ideathon 九州 in 鹿児島 〜最新APIを使ってMashupサービスを発想するワークショップ~
- AppSheet | execute an action on a set of rows でテーブルをまたぐ処理
- 【AI活用の失敗例】ChatGPTでの地理的情報の取得（ジオコーディング）はむずかしい！
