【GAS】ジョギングのお伴にいかが？GASを使ったWEBアプリ「かんたん走行記録」で、GPSの位置情報をスプレッドシートに記録しよう！（5）スプレッドシート内での情報処理と、結果のWEBページへの反映

スマートフォンなどから、手軽に入手できる様になったGPS情報を日常生活で活かす例として、自身の位置情報をスプレッドシートに自動記録するWEBアプリを作ってみました。

ジョギングの時の記録に使えそうなので、「かんたん走行記録」アプリと称してみました。

これまでの記事では、本アプリのキーとなる、GPSの位置情報の取得についての説明をこちらに、

得られた位置情報をスプレッドシートに書き込む所については、こちらにご説明しました。

今回は、書き込んだ先のスプレッドシート内での情報処理についてご説明します。

データベース代わりのGoogleスプレッドシート内で、ワークシート関数を使った様々な処理をさせる

一般のＷＥＢアプリでは、データの蓄積は専用のデータベースで行っていますが、今回のＷＥＢアプリでは、Googleスプレッドシート上に書き込む事で、データベースの代わりとしています。

スプレッドシートは、データベースの様な堅牢で迅速なデータの保管はできませんが、専用のデータベース言語が不要な上、柔軟な運用ができるので、小規模でデータの出し入れも頻繁でないシステムでは、色々なメリットがあります。

特にワークシート関数が使えることはアマチュアプログラマには助かる点で、プログラム側で対処すべき内容がワークシート関数で対処できてしまうため、その分スクリプトの内容をシンプルにできます。

今回、データを書き込むのは、以下のワークシートですが、ここでワークシート関数を使って処理している内容をご説明します。

1．最終行の表示

現在データを何行記入しているかを、下の様にA列の頭の黄色いセルに表示させています。以下の黄色いセルがそれです。

セルには、3行目以下の最大数を表示させるワークシート関数が記入されています。

=MAX(A列※)

※正式な表記ではなく、概念的な表現をしています

スクリプトではこの数字を参考に、新しいデータは常に最終行の下に追加し、追加の際はA列に1つ大きな数字を記入する様にしています。

2．データを分ける

緯度や経度のデータを「■」を挟んで1つの文字にしたテキストは、B列に記入しますが、この文字は、以下の関数で再び複数のデータにバラしています。
=SPLIT(B列、”■”)

この関数により、C列以降、データのヘッダとしているダミーの文字列、緯度、経度、速度、時刻のシリアル値（タイムスタンプ）がそれぞれ自動代入されます。

3．直前の地点からの距離、およびその累積を計算する

地球上の2地点の距離を知るための数式を入れて自動計算させています。

計算は、直前の位置情報データとあわせたワークシート関数で行います。
（計算はやや複雑でネットから引用しました※）また、計算結果について直前の行のデータを足し込んだ累積値も計算し、隣の列で表示しています。

※2地点の緯度と経度から距離を知るための、ヒュベニの公式というものがいくつかのサイトで紹介されています。

以下はその1例ですが、ワークシート関数にした例が掲載されているので、参考にしました。サイトの管理者に感謝しつつ、紹介させていただきます。
https://zenn.dev/music_shio/articles/3c59e10842fcc7

4．Googleマップに経路を表示させるURLを表示

更に、緯度・経度を表す文字列をワークシートを使って結合させて、Googleマップ上でその地点を表示させるURLも生成しています。

Googleマップは、アクセス時に、
https：//www.google.co.jp/maps/dir/緯度1,経度1/緯度2,経度2/・・・
としたURLを使うことで、緯度・経路を結んだ経路を表示させることができます。

ワークシート関数を使ってこのURLを自動生成しています。

緯度・経度のセットをあまり増やすと正しく表示されませんので、指定の間隔でのみ、追加される様な関数にしています。

具体的には、間隔を指定し、行の番号をその間隔で割って、余りがゼロの場合だけ、緯度・経度のセットを追加しています。（間隔は、デフォルトでは、全体行数／１０としています）

また、URLの最後に、
・・・/＠緯度,経度,拡大率ｚ
を追加することで、全体の表示中央とズームの程度を指定できます。

ここでは緯度、経度それぞれで、最大値と最小値の平均を使用し、拡大率は１５ｚを使ったものにして、最終的なURLとしています。

これもワークシート関数を利用します。

WEBページへの表示

最後に、累積距離とGoogleマップ表示用URLをWEBページに反映させています。

まずこれらの値を、スプレッドシートへの書き込み関数の返り値にするコードを記述しておきます。

以下の部分はスクリプトです。

//★★★★ＨＴＭＬから呼び出されて実行する関数★★★★

//HTMLテーブルの値myCellsをシートに書き込む関数
function SetSheet(位置情報,書込シート){
・・・
  //返り値の変数を定義 
  var 配列 = new Array(3) ;

  //レコード数を取得
  配列[0]=mySheet.getRange(レコード数の表示セル,1,1).getValue();

  //走行距離を取得
  配列[1]=mySheet.getRange(走行距離の表示セル,1,1).getValue();

  //地図のリンクを取得
  配列[2]=mySheet.getRange(地図のリンクの表示セル,1,1).getValue();

  //結果を返す 
  return 配列;

}

そして、上記の関数を呼び出すコードを　Google.Script.run関数に、以下の様に記述しておきます。

以下の部分はテンプレートです。

  // スプレッドシートに転送
    google.script.run.withSuccessHandler(終了時関数).SetSheet(位置情報,書込シート);

.withSuccessHandler(終了時関数)

上記の子要素を付け加えたあとで、スクリプトの関数を呼び出すと、返り値を利用する処理を”終了時関数”として実行させることができます。

以下がその関数です。引数は”res"としておきます。

//GASの関数が成功したら実行する
function 終了時関数(res){

  //メッセージのテキストを取得する
  var msg = document.getElementById("テキスト要素");

  //地図へのリンクを取得する
  var link = document.getElementById('リンク要素');
  
  //メッセージのテキストの中身を変更する
  msg.textContent =res[0] +'回書き込みました。合計経路は' + res[1] + 'kmです。';

  //href属性の値を書き換える
  link.setAttribute('href', res[2]);
  
}

上記の処理では、WEBページ上の、テキスト要素やリンク要素を取得した上で、テキスト要素の表示文字、およびリンク要素のリンク先に、返り値を利用しています。

こうして、書き込みを行うたびに、ワークシート関数を使って計算された距離や地図のURLを利用した再表示をWEB上で行っています。

ワークシート関数を使った対応は、”アジャイル開発”にぴったり

最近の言葉で、開発時のスタイルを表すものに、「ウォーターフォール／アジャイル」というものがあります。

「ウォーターフォール」は、上流段階をきっちり仕上げて下流の段階へ進むスタイル、「アジャイル」はささやかなものでも、とにかくゴールまでたどりつく道を拓き、その後それを膨らましていくスタイルです。

アマチュアがアプリを作ろうという場合、仕様や自分のニーズはあまりしっかり把握していないケースが大半だと思います。

そんなとき、複雑な処理となる部分をワークシート関数という身軽な仕組みで作っておくと、何度もブラッシュアップして、望む仕様にたどりつき易くなります。

GASでWEBアプリを作ることには、そんなメリットもあります。

だいぶ仕様についてご説明し終わりましたが、まだ少し残っています。次回は、GPS情報取得のインターバルについてご説明します。

←前の記事はこちら

次の記事はこちら⇒