Google Apps Script で VBAでやったようなスプレッドシートのマクロっぽいことをやってみた。

前回の記事で、Google Apps Script で作業の自動化みたいなことをやって見ました。
実際、あの「一定の条件に合致したメールが来たら」「添付ファイルを特定のGoogle Drive のディレクトリに保存する」、ということの応用で「ある一定の条件に合致したメールが来たら」、「メールの本文にある日時情報を取り出して」「スケジュールに予定を作成する」、なんてことも出来ることから、

テニススクールのウェブサイト経由で振替レッスンを入れたら発信されるメールをトリガーにして振替レッスンの予定をスケジュールに入れる

なんてことも出来るようになりました。そう考えると、IFTTTやZapier を通じて G Suite で出来ること以上のことが多分出来ちゃうんだろうな、という気分がして来ています。

パソコン仕事、といえばスプレッドシートで
チャート作り、ですよね(笑)
実際、仕事上でZapier を使って自動化した作業で、「送付されたcsvファイルを読み出して」、「一定のパラメータ情報をスプレッドシートから読み出し」、これらを組み合わせて計算した結果を「新しいcsvファイルとして生成して」、「メールに添付して送付」しつつ、「スプレッドシートに結果の履歴やパラメータを更新する」、なんて、20ステップ以上必要とするプロセスをGAS上で完全に再現し、かつZapier で制限のあった「Google Drive にテキストファイルを生成するときには必ず .txt ファイルにする」ことを回避して期待されている.csvで生成することすら可能にしたのです。

でも、そもそもGoogle Apps Scriptって。。。

が、もともと、Google版のVBA というか、Excel のマクロみたいなものなのだから、本源的なことでもちょっとやってみようかな、と思ったり。多分、そういう使い方をする人が普通な気がするので。。。

で、題材は自分のちょっと恥ずかしいネタでも

お気付きの方もいるかもしれませんが、個人的に運用しているブログの一つで私が日々の生活で付き合っております尿酸値に関するサイトというのが放置されておりますが、こちらでほぼ唯一更新している情報というのが何と私の体重。その昔twitter に連動したサービスを使って体重の推移をチャートにしてくれるものを使っていたら、そのtweet がfacebook に流れて近しい友人たちが毎日体重を見せられて困惑する、なんてこともありましたので、今ではSNS連動のサービスを使わずに、Googleスプレッドシートでデータを保管し、そのデータでチャートを書いてそれがWordPress のコンテンツの一部にこんな風に表示されるようにしてあるのです。


 

ですが、これ、スプレッドシートを見るとわかるように、日付と体重を入力して、上から5日平均と20日平均のfomula をコピペして作るようにしているのです。最初のうちは、まぁ一行丸ごとコピペして日付と体重を更新する、とやっていたのですが、GASをいじっていくうちに、体重を入れたら自動的にその日の日付と平均値を計算してセルに入れるなんて出来ることに気づいたのです。こちらのプログラムに関する情報満載のQiitaさんの記事を参考にしたのですが、コード自体はこんな感じ:


function myFunction() {  
var ss = SpreadsheetApp.getActiveSheet();  //作業するスプレッドシートを特定
  var currentRow = ss.getActiveCell().getRow(); //入力されたセルの行を特定 
  var currentCell = ss.getActiveCell().getValue(); //作業するセルを特定

  var dateRange = ss.getRange('A' + currentRow) //更新日をいれる列をstringで指定。この場合はA列
  var fiveRange = ss.getRange('C' + currentRow) //更新日をいれる列をstringで指定。この場合はC列
  var twRange = ss.getRange('D' + currentRow) //更新日をいれる列をstringで指定。この場合はD列

  //更新日の記入
  if(currentRow>1){ 
    if(currentCell) { 
      dateRange.setValue(Utilities.formatDate(new Date(), 'JST', 'MMMMM d, yyyy'));
    }
  }

// 平均値を出すために過去の数値情報を取り出す
  var dat = ss.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得
 
  var average5 = 0; // 5日間の数値の合計額を格納
  var average20 = 0; // 20日間の数値の合計額を格納
 
  for(var i=contents.length - 5 ; i > contents.length ; i++){
    average5 = average5 + contents[i][1]; //5日間の数値の合計額を足し上げ


  }
  fiveRange.setValue(average5/5); // 5日平均値をセルに入力


  for (var i=contents.length - 20; i > contents.length ; i++){ 
    average20 = average20 + contents[i][1];  // 20日間の数値の合計額を足し上げ
  }
  twRange.setValue(average20/20); //20日平均値をセルに入力
    


}
 

スプレッドシートのいいところとプログラムのいいところを組み合わせる

いくつかポイントはあるのですが、例えばGASはスプレッドシートの情報を取り出したり書き込んだりするとプロセス時間が長くなるというコメントがあちこちで見られることから、スプレッドシートの各セルの情報は一気に取り込んで作業するのがスクリプトの高速化に繋がるらしく、これでも

var dat = ss.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得

でデータを取り込んでいます。これの強いのは今回のようなセルの合計等をプログラムの変数を使って計算させて結果だけをセルに出力する、のような作業が出来ることだけでなく、Excel で言う所のvloopup のようなシートにある情報の検索を行うのにつどシートにアクセスすることなく配列で処理できたり、という普通のプログラムっぽいことが出来るところでしょうか。

あとは、このコードをセーブしたあとで、このスクリプトが起動する条件として前回の記事では1時間ごと、と設定したところを、今回はスプレッドシートのセルの変更があった時、と設定すればおっけー。

実際、これを仕掛けたこのスプレッドシートをスマホで開けて恥ずかしい体重情報だけを入れると。。。自動的に日付と平均値が目の前で追加されます。もうコピペする必要もなくなりました(それくらいしろよ、と言われそうですが。。。)。


ベタ褒めのGASですが、実は盲点が。。。


ということで、何でもできちゃいそうな気がするGASですが、もっとあれこれやりたい、と思ったときに色々と制限があることにも気づきました。こちらについて次の記事で取り上げたいと思います。

0 件のコメント:

Copyrights Emichanproduction, 1996 - 2011. Powered by Blogger.